14 Improving Replication Performance

Adjust transaction log buffer size and CPU

If you are planning a replication scheme, ensure the following:

• The transaction log setting for LogBufMB should result in the value of LOG_FS_READS in the SYS.MONITOR table being 0 or close to 0. This ensures that the replication agent does not have to read any transaction log records from disk. If the value of LOG_FS_READS is increasing, then increase the transaction log buffer size.

• CPU resources are adequate. The replication agent on the master database spawns a thread for every subscriber database. Each thread reads and processes the transaction log independently and needs adequate CPU resources to make progress.

• If the sending side and receiving side of the replication scheme are mismatched in CPU power, place the replication receiver on the faster system.

Performance considerations when altering tables that are replicated

Altering a table to add or remove columns may lead to performance degradation or poor space utilization.

• When you alter a table to add one or more columns, the table is allocated a new partition for the additional columns. The additional partition causes extra processing when retrieving the data, resulting in reduced performance. See "ALTER TABLE" in the Oracle TimesTen In-Memory Database SQL Reference for more information on understanding how partitions are added when using ALTER TABLE.

• When you alter a table to drop a column, the space is not always freed resulting in poor space utilization.

Any replication scheme defined with the TABLE DEFINITION CHECKING EXACT attribute requires that the physical structure of the table be identical on both master databases in order to be able to replicate operations between them. When using the EXACT table definition checking attribute, the only method to free the extraneous space resulted from dropped columns or eliminate extra partitions resulting from added columns is to drop and recreate the table, and then reload the data into the table.

However, if you create the tables with the TABLE DEFINITION CHECKING RELAXED attribute, then (while they must have the same key definition, number of columns, and column data types) the physical structure does not need to be identical on both master databases. The TABLE DEFINITION CHECKING RELAXED attribute can result in slightly slower performance, but only if the tables on both masters are not identical. The change in performance depends on the workload and the number of partitions and columns in the tables.

To improve performance for databases set with RELAXED, you can use ttMigrate -r -relaxedUpgrade to coalesce tables eliminating extraneous space from dropped columns or multiple partitions that were created when adding columns. This can be performed on one database, while the other database is still up and accepting requests on behalf of the application. You do not have to take both databases involved in replication down at the same time, but can perform ttMigrate -r -relaxedUpgrade on each one individually one after the other. This is optimal for databases where the tables are altered often and where the database can only perform online upgrades.

You can only coalesce partitions and eliminate extraneous space with ttMigrate -r -relaxedUpgrade on replicated tables when the table definition checking to RELAXED. However, if your tables have been using the EXACT attribute, then you can temporarily set table definition checking to RELAXED, consolidate the partitions and space for your tables, and then reset it to EXACT.

For more information on the TABLE DEFINITION CHECKING RELAXED attribute, see "Column definition options for replicated tables".

Increase replication throughput for active standby pairs

Use the RecoveryThreads first connection attribute to increase the number of threads that apply changes from the active master database to the standby master database from 1 to 2. If you set RecoveryThreads to 2 on the standby, you should also set it to 2 on the active to maintain increased throughput if there is a failover.

You can also set RecoveryThreads to 2 on one or more read-only subscribers in an active standby pair to increase replication throughput from the standby master database.

Databases must be hosted on systems that are 2-way or larger to take advantage of setting this attribute to 2.

Limit replication transmitters, receivers, and XLA readers

Replication and XLA operations have significant overhead with transaction logging. Replication scales best when there are a limited number of transmitters or receivers. Check your replication topology and see if you can simplify it. Generally, XLA scales best when there are a limited number of readers. If your application has numerous readers, see if you can reduce the number.

Monitor XLA and replication to ensure they are reading from the transaction log buffer rather than from the disk. With a lot of concurrent updates, replication may not keep up. Updates are single-threaded at the subscriber. You can achieve better XLA throughput if the frequency of acknowledgements is reduced.

Estimate the number of readers and transmitters required by checking the values in the LOG_FS_READS and LOG_BUFFER_WAITS columns in the SYS.MONITOR table. The system updates this information each time a connection is made or released and each time a transaction is committed or rolled back.

Setting LogFlushMethod=2 can improve performance of RETURN TWOSAFE replication operations and RETURN RECEIPT with DURABLE TRANSMIT operations.