Thread: Autovacuum Woes
Hi All, We have a PostgreSQL 9.2 database providing backend services for an online multiplayer game. Three (of several) tables in this DB are transactional tables - they in order, see 10 million, 7 million and 1.6 million new inserts daily. Several months back, we started noticing slow inserts to these tables (and high load average on the DB server), and eventually traced the problem to the autovacuum process. As a workaround, we started disabling autovacuum during our peak load times. We run from cron: ALTER TABLE table_name SET (autovacuum_enabled = FALSE) to disable and once our concurrent player count starts going down, set autovacuum_enabled to TRUE to enable it. This worked fine for many months, but of late a new problem has started. At the time of disabling and enabling, we are seeing large performance degradation. Several hundred connections like this: 19090 ? Ss 0:00 postgres: user dbname 10.13.36.19(42782) PARSE waiting 19091 ? Ss 0:00 postgres: user dbname 10.13.36.19(42783) PARSE waiting 19092 ? Ss 0:00 postgres: user dbname 10.13.36.19(42784) PARSE waiting 19093 ? Ss 0:00 postgres: user dbname 10.13.36.19(42785) PARSE waiting 19095 ? Ss 0:00 postgres: user dbname 10.13.36.19(42786) PARSE waiting So obviously, disabling/enabling autovacuum has side-effects. It is not clear to me what is going wrong here. What will happen if I permanently leave autovacuum off on these tables? The documentation has some points, but I think it is a bit confusing. Any links to a lucid explanation of the concept, algorithm, configuration and best practices? TIA, Binand
This worked fine for many months, but of late a new problem has
started. At the time of disabling and enabling, we are seeing large
performance degradation. Several hundred connections like this:
19090 ? Ss 0:00 postgres: user dbname 10.13.36.19(42782)
PARSE waiting
19091 ? Ss 0:00 postgres: user dbname 10.13.36.19(42783)
PARSE waiting
19092 ? Ss 0:00 postgres: user dbname 10.13.36.19(42784)
PARSE waiting
19093 ? Ss 0:00 postgres: user dbname 10.13.36.19(42785)
PARSE waiting
19095 ? Ss 0:00 postgres: user dbname 10.13.36.19(42786)
PARSE waiting
So obviously, disabling/enabling autovacuum has side-effects.
Use the queries in this link to find out what is blocking these transactions.
It is highly likely there are autovacuum processes to take care of transactionid wraparound. (See point no 4 under Vacuuming basics)
Regards,
Jayadevan
On Tue, Aug 9, 2016 at 1:02 AM, Binand Sethumadhavan <binand@gmx.net> wrote: > Several months back, we started noticing slow inserts to these tables > (and high load average on the DB server), and eventually traced the > problem to the autovacuum process. As a workaround, we started > disabling autovacuum during our peak load times. We run from cron: > ALTER TABLE table_name SET (autovacuum_enabled = FALSE) to disable and > once our concurrent player count starts going down, set > autovacuum_enabled to TRUE to enable it. I have found it safer and more effective to leave autovacuum running at all times and add VACUUM ANALYZE commands during the off peak hours. This will prevent autovacuum from kicking in during peak load periods unless there is, for example, a large number of updates to a small table -- in which case the autovacuum run will normally be pretty quick and will prevent a slowdown. Of course, you may need to run autovacuum a bit, too; but there aren't enough clues here to suggest anything specific. -- Kevin Grittner EDB: http://www.enterprisedb.com The Enterprise PostgreSQL Company
Agreed with kevin.
Also i would suggest to have a look on your pgcatalog table Of your database as they may be having bloat if temp tables are getting used too much which may cause slowness of sql performance as well.
Thanks
On 9 Aug 2016 7:43 p.m., "Kevin Grittner" <kgrittn@gmail.com> wrote:
On Tue, Aug 9, 2016 at 1:02 AM, Binand Sethumadhavan <binand@gmx.net> wrote:
> Several months back, we started noticing slow inserts to these tables
> (and high load average on the DB server), and eventually traced the
> problem to the autovacuum process. As a workaround, we started
> disabling autovacuum during our peak load times. We run from cron:
> ALTER TABLE table_name SET (autovacuum_enabled = FALSE) to disable and
> once our concurrent player count starts going down, set
> autovacuum_enabled to TRUE to enable it.
I have found it safer and more effective to leave autovacuum
running at all times and add VACUUM ANALYZE commands during the off
peak hours. This will prevent autovacuum from kicking in during
peak load periods unless there is, for example, a large number of
updates to a small table -- in which case the autovacuum run will
normally be pretty quick and will prevent a slowdown. Of course,
you may need to run autovacuum a bit, too; but there aren't enough
clues here to suggest anything specific.
--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice