On Mon, Oct 25, 2010 at 2:21 PM, André 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´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.
*) Are the plans *exactly* the same?
*) Are you running explain analyze? There are some platform specific
interactions caused by timing.
*) Are you transferring the data across the network? rule out
(horribly difficult to diagnose/fix) network effects.
merlin