From performance and scalability perspective, the infrastructure must provide enough capacity for I/O handling, fault tolerance, and transfer of synchronizationmessages. The key components that affect performance and scalability are the master database, the replica database, and the network capacity.
Tuning master database performance
From performance perspective, the master database is a critical component of the system. All synchronized transactions created in the system are eventually committed in the master database. Similarly, publication data is refreshed from the master database. From the system infrastructure point of view, this means two things:
▪The capacity of the master server must be sufficient to manage the CPU and disk-I/O load caused by the replica transactions and refreshes. Some additional disk I/O is caused by the store and forward messaging of the synchronization architecture. If the additional disk I/O is caused by the store and forward messaging becomes a problem, consider using the synchronous refresh method described in Synchronous replication - messageless REFRESH
▪The fault tolerance of the master server must be at a sufficient level. Since the replica databases communicate with each other only through the master, the master server is the single point of failure. If the master server goes down, synchronization between replicas stop. To avoid problems cause by master server failure, consider using the solidDB® high availability component.
Optimizing the load of the master database
In a typical database system, most of the database load is read I/O load caused by the read-intensive online usage of the database. In a multi-database system, this load can be distributed to a large number of databases. The capacity of the master database is then left for processing the transactions that have been propagated from the replica databases of the system.
Because all shared or synchronized transactions of the system are committed in the master database, it is very important that the resources of the master database are used as efficiently as possible. The following actions can help optimize the resource usage of the master database:
▪If possible, dedicate the master database for synchronization use only. Online access to this database might have unpredictable response times if heavy synchronization processes are being run simultaneously.
▪Optimize the indexing of the database for synchronization use only. Forexample, if the database has no online usage, provide only those indices that are used by the search criteria and joins of the publications, as well as those needed by the transactions.
▪If a database for centralized online use is needed, it is often preferable to have a full replica of the master database available for that purpose. This database can have indexing that is optimized for the online usage.
▪Keep the publications simple. Complex publications with lots of joins between tables mean more complex queries that require more server resources.
Since incremental refreshes usually use fewer resources than full refreshes, you can enable incremental refreshes by setting the SYNCHISTORY property for the tables of the publication. This allows the master server to send only the master data that has changed in the publication, rather than a full publication.
▪Do not synchronize more frequently than necessary.
▪Utilize the off-peak hours in the synchronization processes. Synchronize the large masses of less-urgent data when the online usage of the system is at its minimum.
The usage pattern of replica servers of the system is usually fairly "traditional." The servers are accessed by applications that perform queries and write operations to the database. The capacity of the replica databases should be sufficient to serve the normal online usage of the server. Reserve some additional capacity to cover the overhead caused by database synchronization.
If possible, deny user access to the physical database file to ensure the maximum level of data security in the system.
Network
Be sure to place the master server on a machine that has the best possible throughput. Carefully estimate and test the maximum amount of data transferred during synchronization to ensure that the bandwidth of the network is sufficient for database synchronization.
Be sure to test the network for transmission of the synchronization messages. These messages contain:
▪Header data (insignificant)
▪Transactions which include:
– procedure calls as strings
– parameters as binary data
▪Refreshes to publications which include:
– all inserted and updated rows from the master database
– primary keys of rows that are deleted from the master database