pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Emre Hasegeli <emre.hasegeli@tart.com.tr>
To: pgsql-performance@postgresql.org
Cc: dba@tart.com.tr
Subject: PostgreSQL 9.2.3 performance problem caused Exclusive locks
Date: Fri, 08 Mar 2013 13:27:16 +0200
Message-ID: <op.wtmkrq0nk2xoe5@hasegeli.local> (raw)
List-Unsubscribe:  <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>

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?


-- 
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance



view thread (7+ messages)  latest in thread

Message-ID: <op.wtmkrq0nk2xoe5@hasegeli.local>
Permalink:  ../op.wtmkrq0nk2xoe5@hasegeli.local/
Also on:    postgresql.org/message-id/op.wtmkrq0nk2xoe5@hasegeli.local

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: emre.hasegeli@tart.com.tr, dba@tart.com.tr
  Subject: Re: PostgreSQL 9.2.3 performance problem caused Exclusive locks
  In-Reply-To: <op.wtmkrq0nk2xoe5@hasegeli.local>
  References: <op.wtmkrq0nk2xoe5@hasegeli.local>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox