From: Tasos Petalas <tasos.petalas@upstreamsystems.com>
MIME-Version: 1.0
Thread-Index: Ac6N5S7pPP1voSEtSOOM3L9cX/Qm8g==
Date: Wed, 31 Jul 2013 14:58:53 +0300
Message-ID: <21cb334b6ca0c09c4faa60bdd46179eb@mail.gmail.com>
Subject: PG performance issues related to storage I/O waits
To: pgsql-performance@postgresql.org
Cc: Tasos Petalas <tasos.petalas@upstreamsystems.com>
Content-Type: multipart/alternative; boundary=001a11c24d16097e0304e2cd7887
Precedence: bulk
Sender: pgsql-performance-owner@postgresql.org

--001a11c24d16097e0304e2cd7887
Content-Type: text/plain; charset=ISO-8859-1

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

--001a11c24d16097e0304e2cd7887
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"Content-Type" content=3D"text/html; charset=
=3Dus-ascii"><meta name=3D"Generator" content=3D"Microsoft Word 14 (filtere=
d medium)"><style><!--
/* Font Definitions */
@font-face
	{font-family:Calibri;
	panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
	{margin:0in;
	margin-bottom:.0001pt;
	font-size:11.0pt;
	font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
	{mso-style-priority:99;
	color:blue;
	text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
	{mso-style-priority:99;
	color:purple;
	text-decoration:underline;}
span.EmailStyle17
	{mso-style-type:personal-compose;
	font-family:"Calibri","sans-serif";
	color:windowtext;}
.MsoChpDefault
	{mso-style-type:export-only;}
@page WordSection1
	{size:8.5in 11.0in;
	margin:1.0in 1.25in 1.0in 1.25in;}
div.WordSection1
	{page:WordSection1;}
--></style></head><body lang=3D"EN-US" link=3D"blue" vlink=3D"purple"><div =
class=3D"WordSection1"><p class=3D"MsoNormal">Hello team,<br><br>We have se=
rious performance issues with a production EDB 9.2AS installation<br><br>Th=
e 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 o=
n SAN and improve overall DB performance.<br>
<br>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 storag=
e (also presented by SAN to the same machine - device 253-3)<br><br>We have=
 set synchronous_commit to off but since almost all of application queries =
are using prepared statements we don&#39;t get any real benefit.<br>
<br>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 appl=
ication peak hours are 13:00-23:00 UTC, during night (04:00-06:00 UTC) ther=
e are some heavy reporting activity + backups)<br>
Archive logs are rsync-ed to a remote backup server every 20 minutes.<br><b=
r>Also please advise on any postgres.conf modification that could significa=
ntly affect storage load (WAL-checkpoint configuration etc.) (we have not t=
ried to move pg_xlog to a separate LUN since this is not an option - any ot=
her LUN would be using the same storage pool as the rest of the /pgdata fil=
es)<br>
We had some issues in the past with autovaccum deamon failing to work effic=
iently under high load so we have already applied your instructions for a m=
ore aggressive auto-vacumm policy (changes already applied on postgresql.co=
nf)<br>
<br>Let me know if you want me to attach all the usual info for tickets reg=
arding (OS, disks, PG conf, etc) plus the sar output and server logs from t=
he last 3 days (24,25,26 June). <br><br>Thanks,<br>Tasos</p></div></body></=
html>

--001a11c24d16097e0304e2cd7887--