Date: Mon, 25 Oct 2010 17:26:29 -0200 (BRST)
From: =?utf-8?Q?Andr=C3=A9_Volpato?= <andre.volpato@ecomtecnologia.com.br>
To: Merlin Moncure <mmoncure@gmail.com>
Cc: PGSQL-Performance <pgsql-performance@postgresql.org>
Message-ID: <789252381.9649.1288034789616.JavaMail.root@zimbra01a>
In-Reply-To: <AANLkTik3ypwVA9SepMYBYzovpnLA2TiBR_AY3bDuW3L3@mail.gmail.com>
Subject: Re: AIX slow buffer reads
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


| On Mon, Oct 25, 2010 at 2:21 PM, Andr=C3=A9 Volpato
| <andre.volpato@ecomtecnologia.com.br> wrote:
| > Hi all,
| >
| > We are tuning a PostgreSQL box with AIX 5.3 and got stucked in a
| > very odd situation.
| > When a query got ran for the second time, the system seems to
| > deliver the results to slow.
| >
| > Here=C2=B4s some background info:
| >
| > AIX Box:
| > PostgreSQL 8.4.4, AIX 5.3-9 64bits, SAN IBM DS3400, 8x450GB SAS 15K
| > Raid-5
| > 8GB RAM, 2.3GB Shared buffers
| >
| > Debian Box:
| > PostgreSQL 8.4.4, Debian 4.3.2 64bits, SAN IBM DS3400, 5x300GB SAS
| > 15K Raid-0
| > 7GB RAM, 2.1GB Shared buffers
| >
| > Right now, we changed lots of AIX tunables to increase disk and SO
| > performance.
| > Of course, postgres got tunned as well. I can post all changes made
| > until now if needed.
| >
| > To keep it simple, I will try to explain only the buffer read issue.
| > This query [1] took like 14s to run at AIX, and almost the same time
| > at Debian.
| > The issue is when I run it for the second time:
| > AIX - 8s
| > Debian - 0.3s
| >
| > These times keep repeating after the second run, and I can ensure
| > AIX isn=C2=B4t touching the disks anymore.
| > I=C2=B4ve never seen this behaviour before. I heard about Direct I/O an=
d
| > I was thinking about givng it a shot.
| > Any ideas?
| >
|=20
| I doubt disk/io is the problem.
=20
Me either.
Like I said, AIX do not touch the storage when runing the query.
It became CPU-bound after data got into cache.


| *) Are the plans *exactly* the same?


The plan I sent refers to the AIX box:
http://explain.depesz.com/s/5oz
At Debian, the plan looks pretty much the same.


| *) Are you running explain analyze? There are some platform specific
| interactions caused by timing.

Yes. I=C2=B4m not concerned about timing because the difference (8s against=
 0.3s) is huge.


| *) Are you transferring the data across the network? rule out
| (horribly difficult to diagnose/fix) network effects.

Not likely... Both boxes are in the same Bladecenter, using the same storag=
e.


|=20
| merlin


[]=C2=B4s, Andre Volpato