Postgres 9 slave lag
Postgres 9 slave lag
От:
Saurabh Agrawal <saurabh.miet@gmail.com>
Дата:
Hi All, I have setup postgres 9 master slave streaming replication but experiencing slave lagging sometimes by 50 min to 60 min. I am not getting exact reason for slave lag delay. Below are the details: 1. Master table contains partition tables with frequent updates. 2. Slave is used for report generation so long running queries hit slave. 3. ANALYZE runs every hour on partition table on master. 4. postgresql.conf: archive_timeout = 900 checkpoint_segments = 500 checkpoint_timeout = 30min checkpoint_warning = 2000s max_standby_archive_delay = -1 I noticed that whenever long running query executes on slave, slave starts lagging by master and as soon as query completes log files get applied immediately. Please help how can I avoid slave lag. Thanks Regards Saurabh Agrawal
Re: Postgres 9 slave lag
От:
"Kevin Grittner" <Kevin.Grittner@wicourts.gov>
Дата:
Saurabh Agrawal wrote: > max_standby_archive_delay = -1 > > I noticed that whenever long running query executes on slave, > slave starts lagging by master and as soon as query completes log > files get applied immediately. Please help how can I avoid slave > lag. You can adjust max_standby_archive_delay to the maximum amount of time you want replication to block waiting for your query. If that is exceeded, your query will be canceled to allow replication to continue. http://www.postgresql.org/docs/9.0/interactive/hot-standby.html#HOT-STANDBY-CONFLICT -Kevin