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
