Skip site navigation (1) Skip section navigation (2)

Re: AIX slow buffer reads

From: André Volpato <andre(dot)volpato(at)ecomtecnologia(dot)com(dot)br>
To: Merlin Moncure <mmoncure(at)gmail(dot)com>
Cc: PGSQL-Performance <pgsql-performance(at)postgresql(dot)org>
Subject: Re: AIX slow buffer reads
Date: 2010-10-25 19:26:29
Message-ID: 789252381.9649.1288034789616.JavaMail.root@zimbra01a (view raw or flat)
Thread:
Lists: pgsql-performance
| 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

In response to

Responses

pgsql-performance by date

Next:From: Scott MarloweDate: 2010-10-25 19:26:41
Subject: Re: Postgres insert performance and storage requirement compared to Oracle
Previous:From: Ray StellDate: 2010-10-25 19:21:57
Subject: Re: Postgres insert performance and storage requirementcompared to Oracle

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group