Hi,
I upgraded our master database server from 9.2.2 to 9.2.3 on Monday. We
have been experiencing performance problems since then. Yesterday, our
application hit the connection limit 5 times. It causes approximately
15 seconds of downtime. The database server hit 50 load average, then
everything came back to normal.
We have a very good database server dedicated to PostgreSQL. It has 64
cores and 200 GiB of RAM which is 2 times bigger than our database.
We run PostgreSQL on RHEL relase 6.2. The database executes 2k transactions
per second in busy hours. The server is running 1 - 2 load average
normally.
PostgreSQL writes several following logs during the problem which I never
saw before 9.2.3:
LOG: process 4793 acquired ExclusiveLock on extension of relation 305605
of database 16396 after 2348.675 ms
The relation 305605 was the biggest table of the database. Our application
stores web service logs as XML's on that table. It is only used to insert
new rows. One row is approximately 2 MB and 50 rows inserted per second
at most busy times. We saw autovacuum processes during the problem. We
disabled autovacuum for that table but is does not help. I tried to archive
the table. Create a new empty one, but it does not help, too.
We also have an unlogged table to used by our application for locking.
It is autovacuumed every 5 minutes as new rows are inserted and deleted
continuously.
Most of our configuration parameters remain default except the following:
max_connections = 200
shared_buffers = 64GB
max_prepared_transactions = 0
work_mem = 64MB
maintenance_work_mem = 512MB
shared_preload_libraries = '$libdir/pg_stat_statements'
wal_level = hot_standby
checkpoint_segments = 40
effective_cache_size = 128GB
track_activity_query_size = 8192
autovacuum = on
autovacuum_max_workers = 10
I will try to reduce autovacuum_max_workers and increase max_connections
to avoid downtime. Do you have any other suggestions? Do you know what
might
have caused this problem? Do you think downgrading to 9.2.2 is a good idea?
