Message-ID: <4CC6E07C.2050105@ca.afilias.info>
Date: Tue, 26 Oct 2010 10:06:52 -0400
From: Brad Nicholson <bnichols@ca.afilias.info>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US;
	rv:1.9.2.11) Gecko/20101007 Lightning/1.0b2 Thunderbird/3.1.5
MIME-Version: 1.0
To: pgsql-performance@postgresql.org
Subject: Re: AIX slow buffer reads
References: <789252381.9649.1288034789616.JavaMail.root@zimbra01a>
In-Reply-To: <789252381.9649.1288034789616.JavaMail.root@zimbra01a>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 10-10-25 03:26 PM, André Volpato wrote:
> | 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.
>
> Me either.
> Like I said, AIX do not touch the storage when runing the query.
> It became CPU-bound after data got into cache.

Have you confirmed that the hardware is ok on both servers?

Have both OS's been tuned by people that know how to tune the respective 
OS's?  AIX is very different than Linux, and needs to be tuned accordingly.

On AIX can you trace why it is CPU bound?  What else is taking the CPU 
time, anything?

Also, can you provide the output of pg_config from your AIX build?

-- 
Brad Nicholson  416-673-4106
Database Administrator, Afilias Canada Corp.