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.
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. The default setting is 7200 minutes for 5 days of data. While keeping some old data can help with troubleshooting, most production systems should use a lower setting. To keep 1 day of data, the following property is set:
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 = ?;
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.
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.
After you do so, you can bounce the SymmetricDS service and it will disable page level locking again.
You can disable the automatic setting of locking feature by setting the following:
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;
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 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.