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