| Slony-I 2.0.8 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 4. Advanced Topics | Fast Forward | Next |
4.8. Performance Considerations
Slony-I is a trigger based replication engine. For each row of application data you insert, update, or delete in your database Slony-I will insert an additional row into the sl_log_1 or sl_log_2 tables. This means that Slony-I will likely have some negative impact on your databases performance. Predicting this impact is difficult because the degree of impact is dependent on your database workload and hardware capabilities.
The following Slony-I operations are likely to impact performance:
Changing data in a replicated table will result in rows being added to sl_log_1 or sl_log_2
When a slon daemon generates a SYNC event on each node it will need to add to the sl_event table.
Each remote slon daemon will need to query the sl_log_1/sl_log_2 and sl_event tables to pull the data to replicate
4.8.1. Vacuum Concerns
The sl_event and sl_confirm tables need to be regularly vacuumed because Slony-I will often add and delete rows to these tables. The autovacuum feature of PostgreSQL included in 8.3 and above is the recommended way of handling vacuums. If autovacuum does is not working well it can be configured to not vacuum the sl_event and sl_confirm tables (See the PostgreSQL documentation information on how to disable autovacuum on a per table basis). If Slony-I detects that autovacuum has been disabled for any of the Slony-I tables then it will try to vacuum the table itself as part of cleanupThread processing.
Note: Older versions of Slony-I and older versions of PostgreSQL had different vacuuming concerns. If your using an older version of Slony-I (prior to 2.0) then you should refer to the documentation for your Slony-I version to learn about the vacuuming concerns that apply to you.
4.8.2. Log Switching
Slony-I will frequently switch between sl_log_1 and sl_log_2 as the table that the Slony-I triggers log data into. Once all of the transactions in one of these tables have been replicated Slony-I will TRUNCATE the table. The usage of TRUNCATE in this manner eliminates the need to vacuum sl_log_1 and sl_log_2.
4.8.3. Long Running Transactions
Long running transactions can impact the performance of Slony-I because they prevent Log Switching from occurring. As long as your oldest transaction is open it will sl_log_1 or sl_log_2 from being truncated. This means that the other sl_log table will continue to grow in size. Long running transactions can also stop sl_event and sl_confirm from being vacuumed. The table bloat that occurs due to a long running transaction will mean that queries to these tables will take longer. This can lead to replication falling behind. If replication is behind then the data in these tables has remain until that data is replicated. The increased size of the Slony-I tables can cause replication to fall even further behind.