Re: AIX slow buffer reads

| On Mon, Oct 25, 2010 at 2:21 PM, André Volpato
| <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)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´s 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´t touching the disks anymore.
| > I´ve never seen this behaviour before. I heard about Direct I/O and
| > I was thinking about givng it a shot.
| > Any ideas?
| >
|
| I doubt disk/io is the problem.

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´m 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 storage.

|
| merlin

[]´s, Andre Volpato