On 31 Červenec 2013, 13:58, Tasos Petalas wrote:
> Hello team,
>
> We have serious performance issues with a production EDB 9.2AS
> installation
Are you sure you've sent this to the right list? Because this is a
community mailing list for PostgreSQL, not for EDB. If you have a support
contract with EDB it's probably better to ask them directly (e.g. they
might give you advices about some custom features not available in vanilla
PostgreSQL).
> 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.
So is that a dedicated DWH machine, and PostgreSQL is responsible for most
of the I/O load? Which processes are doing that? Backends handling queries
or some background processes (say, checkpoints)? Is that random or
sequential I/O, reads or writes, ...?
How much I/O are we talking about? Could it be that the SAN is overloaded
by someone else (in case it's not dedicated to the database)?
It might turn out that the most effective solution is tuning the queries
that are responsible for the I/O activity.
> 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)
I have no clue what device 253-3 is, but I assume you're using SAS disks.
>
> 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.
Ummmm, how is this related? AFAIK those are rather orthogonal features,
i.e. prepared statements should benefit from synchronous_commit=off just
like any other queries.
> 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).
Well, we can't really help you unless you give us this, so yes - attach
this info. And please try to identify what is actually causing most I/O
activity (e.g. using "iotop").
Tomas
