Maintenance and Tuning

Periodic maintenance of the runtime tables will keep the system performing well. Most database platforms have procedures to reduce fragmentation and minimize table storage size. Without maintenance, the system can gradually slow down, leading to timeouts and lock contention.

Purge Job

The Purge Job is a built-in job that deletes old rows from the runtime tables, including incoming batch, outgoing batch, data gap, data event, and data tables. After data is synchronized successfully, the runtime rows are left in place for the Purge Job to remove later. The amount of old data allowed in the tables is determined by setting the number of minutes to retain. Prior to version 3.6.0, the default setting was 7200 minutes for 5 days of data. In version 3.6.0, the retention was changed to 1 day of data. In version 3.10, the retention was changed to 2 hours of data. While keeping some old data can help with troubleshooting, most production systems should use a lower setting. To keep 2 hours of data, the following property is set:

purge.retention.minutes=120

Prior to version 2.5.5, the Purge Job would only run if the node had received an initial load, which set the initial load time for the node. If the initial load feature is not being used, then the initial load time must be set manually:

update sym_node_security set initial_load_time = current_timestamp where node_id = ?;

Reorganization

The runtime tables are constantly inserted, updated, and deleted, which causes fragmentation over time. The most dynamic runtime tables are data, data event, data gap, outgoing batch, and incoming batch. A periodic reorganization of theses tables keeps the fragmentation low so that query performance is consistent. Each database platform has a different procedure for optimizing the tables.

Oracle

The Oracle shrink space command (introduced in 10g) performs online segment shrinks for tables and LOBs. It moves rows between existing blocks to compact the data, so it needs row movement enabled for the table.

-- Enable row movement
ALTER TABLE scott.emp ENABLE ROW MOVEMENT;

-- Recover space and update the high water mark
ALTER TABLE scott.emp SHRINK SPACE;

SQL Server

From SQL Server Management Studio

Navigate to the runtime tables and find their indexes. Right-click on each index and select "Reorganize". This performs an online reorganization of the indexes.

From Command Line

Perform an online reorganization of the indexes:

ALTER INDEX ALL ON SYM_INCOMING_BATCH REORGANIZE;
ALTER INDEX ALL ON SYM_OUTGOING_BATCH REORGANIZE;
ALTER INDEX ALL ON SYM_DATA_GAP REORGANIZE;
ALTER INDEX ALL ON SYM_DATA_EVENT REORGANIZE;
ALTER INDEX ALL ON SYM_DATA REORGANIZE;

For later versions of SymmetricDS we automatically disable page level locking to avoid deadlocks. In order to run the above statements you need to reenabled page level locking.

https://www.mssqltips.com/sqlservertip/4247/resolve-sql-server-database-index-reorganization-page-level-locking-problem/

After you do so, you can bounce the SymmetricDS service and it will disable page level locking again.

The parameters that control the locking feature are below. By default, SymmetricDS will use row level locking for improved concurrency.

mssql.allow.only.row.level.locks.on.runtime.tables=true
mssql.lock.escalation.disabled=true

MySQL

Rows that are deleted are marked as no longer used, which can waste space and affect speed. The table can be reorganized with an OPTIMIZE operation that copies the data to a temporary location and back again. This command should be run periodically when the database is offline for scheduled maintenance.

OPTIMIZE TABLE SYM_INCOMING_BATCH;
OPTIMIZE TABLE SYM_OUTGOING_BATCH;
OPTIMIZE TABLE SYM_DATA_GAP;
OPTIMIZE TABLE SYM_DATA_EVENT;
OPTIMIZE TABLE SYM_DATA;

DB2

RUNSTATS ON TABLE SYM_INCOMING_BATCH FOR DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYM_OUTGOING_BATCH FOR DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYM_DATA_GAP FOR DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYM_DATA_EVENT FOR DETAILED INDEXES ALL;
RUNSTATS ON TABLE SYM_DATA FOR DETAILED INDEXES ALL;

REORG INDEXES ALL FOR TABLE SYM_INCOMING_BATCH;
REORG INDEXES ALL FOR TABLE SYM_OUTGOING_BATCH;
REORG INDEXES ALL FOR TABLE SYM_DATA_GAP;
REORG INDEXES ALL FOR TABLE SYM_DATA_EVENT;
REORG INDEXES ALL FOR TABLE SYM_DATA;

Firebird

Firebird uses a multi-generational architecture that keeps versions of rows to prevent readers from blocking writers. Old versions of rows are still in the database until garbage collection runs. Query to see when garbage collection will run:

select mon$database_name, mon$sweep_interval, mon$oldest_snapshot - mon$oldest_transaction from mon$database

The oldest snapshot transaction (OST) minus the oldest interesting transaction (OIT) is the current interval. When the current interval is greater than the sweep interval, garbage collection will run automatically. A DBA can use the gfix command line utility to manually run garbage collection with gfix -sweep mydb.