PG performance issues related to storage I/O waits

From: Tasos Petalas <tasos(dot)petalas(at)upstreamsystems(dot)com>
To: pgsql-performance(at)postgresql(dot)org
Cc: Tasos Petalas <tasos(dot)petalas(at)upstreamsystems(dot)com>
Subject: PG performance issues related to storage I/O waits
Date: 2013-07-31 11:58:53
Message-ID: 21cb334b6ca0c09c4faa60bdd46179eb@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello team,

We have serious performance issues with a production EDB 9.2AS installation

The issue is mostly related to storage I/O bottlenecks during peak hours
and we are looking for tunables on any level that could reduce the I/O
spikes on SAN and improve overall DB performance.

Our storage array consists of 16 disks in RAID-10 topology (device 253-2 on
our OS configuration). We are also using RAID-5 for archive_log storage
(also presented by SAN to the same machine - device 253-3)

We have set synchronous_commit to off but since almost all of application
queries are using prepared statements we don't get any real benefit.

We are using VMware , VMFS and LVM so we need your feedback on any kind of
tunable that could remove load from storage during peak hours (FYI
application peak hours are 13:00-23:00 UTC, during night (04:00-06:00 UTC)
there are some heavy reporting activity + backups)
Archive logs are rsync-ed to a remote backup server every 20 minutes.

Also please advise on any postgres.conf modification that could
significantly affect storage load (WAL-checkpoint configuration etc.) (we
have not tried to move pg_xlog to a separate LUN since this is not an
option - any other LUN would be using the same storage pool as the rest of
the /pgdata files)
We had some issues in the past with autovaccum deamon failing to work
efficiently under high load so we have already applied your instructions
for a more aggressive auto-vacumm policy (changes already applied on
postgresql.conf)

Let me know if you want me to attach all the usual info for tickets
regarding (OS, disks, PG conf, etc) plus the sar output and server logs
from the last 3 days (24,25,26 June).

Thanks,
Tasos

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Tomas Vondra 2013-07-31 12:40:54 Re: PG performance issues related to storage I/O waits
Previous Message Michael Paquier 2013-07-31 05:18:21 Re: to many locks held