Message-ID: <4CC81E39.7000001@ca.afilias.info>
Date: Wed, 27 Oct 2010 08:42:33 -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: =?UTF-8?B?QW5kcsOpIFZvbHBhdG8=?=
 <andre.volpato@ecomtecnologia.com.br>
CC: pgsql-performance@postgresql.org
Subject: Re: AIX slow buffer reads
References: <936162153.10447.1288127050905.JavaMail.root@zimbra01a>
In-Reply-To: <936162153.10447.1288127050905.JavaMail.root@zimbra01a>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

On 10-10-26 05:04 PM, André Volpato wrote:
> ----- Mensagem original -----
> | 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:
>
> (...)
>
> |>  |>   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?
> |
>
> The hardware was recently instaled and checked by the vendor team.
> AIX box is on JS22:
> PostgreSQL 8.4.4, AIX 5.3-9 64bits, SAN IBM DS3400, 8x450GB SAS 15K Raid-5
> 8GB RAM (DDR2 667)
>
> # lsconf
> System Model: IBM,7998-61X
> Processor Type: PowerPC_POWER6
> Processor Implementation Mode: POWER 6
> Processor Version: PV_6
> Number Of Processors: 4
> Processor Clock Speed: 4005 MHz
> CPU Type: 64-bit
> Kernel Type: 64-bit
> Memory Size: 7680 MB
>
> Debian box is on HS21:
> PostgreSQL 8.4.4, Debian 4.3.2 64bits, SAN IBM DS3400, 5x300GB SAS 15K Raid-0
> 7GB RAM (DDR2 667)
> We are forced to use RedHat on this machine, so we are virtualizing the Debian box.
>
> # cpuinfo
> processor       : [0-7]
> vendor_id       : GenuineIntel
> cpu family      : 6
> model           : 23
> model name      : Intel(R) Xeon(R) CPU           E5420  @ 2.50GHz
> stepping        : 6
> cpu MHz         : 2500.148
> cache size      : 6144 KB
>
>
>
> | 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.
>
> We´ve been tuning AIX for the last 3 weeks, and lots of tuneables got changed.
> On Debian, we have far more experience, and it´s been a chalenge to understand how AIX works.
>
> Most important tunes:
> page_steal_method=1
> lru_file_repage=0
> kernel_heap_psize=64k
> maxperm%=90
> maxclient%=90
> minperm%=20
>
> Disk:
> chdev -l hdisk8 -a queue_depth=24
> chdev -l hdisk8 -a reserve_policy=no_reserve
> chdev -l hdisk8 -a algorithm=round_robin
> chdev -l hdisk8 -a max_transfer=0x400000
>
> HBA:
> chdev -l fcs0 -P -a max_xfer_size=0x400000 -a num_cmd_elems=1024
>
> Postgres:
> shared_buffers = 2304MB
> effective_io_concurrency = 5

I wonder if effective_io_concurrency has anything to do with it.  It was 
implemented and mainly tested on Linux, and I am unsure if it will do 
anything on AIX.  The plan you posted for the query does a bitmap index 
scans which is what effective_io_concurrency will speed up.

Can you post the output of explain analyze for that query on both AIX 
and Linux?  That will show where the time is being spent.

If it is being spent in the bitmap index scan, try setting 
effective_io_concurrency to 0 for Linux, and see what effect that has.


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