| Slony-I 2.2.10 Documentation | ||||
|---|---|---|---|---|
| Prev | Fast Backward | Chapter 5. Deployment Considerations | Fast Forward | Next |
5.6. 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 a negative impact on your databases performance. Predicting this impact is more difficult because the amount 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
5.6.1. Vacuum Concerns
Tables sl_event and sl_confirm need to be regularly vacuumed because Slony-I regularly adds and deletes rows to and from 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 may 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.
Up to PostgreSQL 8.3, tables requiring special handling for autovacuum were captured in the system catalog pg_autovacuum table.
In PostgreSQL 8.4 and later, autovacuum control is captured as part of table "storage parameters" using CREATE TABLE or ALTER TABLE.
When Slony-I detects that autovacuum has been disabled for any
or all of the Slony-I tables then it will try to vacuum such tables
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 determine applicable vacuuming concerns.
It is generally to be expected that the use of autovacuum is to be preferred, as it allows Slony-I tables to be vacuumed as often or as seldom as their update patterns indicate. In cases where autovacuum does not provide appropriate maintenance, it seems likely that either:
Configuration parameters for autovacuum are poorly set, in which case the "fix" is to improve the configuration values, or
There is a bug in autovacuum, in which case it warrants fixing or improving that portion of PostgreSQL.
There have been some substantial enhancements done to the autovacuum facility over the course of its history, and it is very much preferable to take advantage of those efforts rather than reinventing it, likely badly.
5.6.2. Log Switching
Slony-I will frequently switch between sl_log_1 and sl_log_2 as the table into which the Slony-I triggers capture data. Once all of the transactions in one of these tables have been replicated and confirmed across the whole cluster, Slony-I will TRUNCATE the table. This usage of TRUNCATE eliminates the need to vacuum sl_log_1 and sl_log_2.
5.6.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.