Date: Wed, 27 Oct 2010 15:05:09 -0200 (BRST)
From: =?utf-8?Q?Andr=C3=A9_Volpato?= <andre.volpato@ecomtecnologia.com.br>
To: Brad Nicholson <bnichols@ca.afilias.info>
Cc: pgsql-performance@postgresql.org
Message-ID: <1141414014.10692.1288199109474.JavaMail.root@zimbra01a>
In-Reply-To: <4CC81E39.7000001@ca.afilias.info>
Subject: Re: AIX slow buffer reads
MIME-Version: 1.0
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable


----- Mensagem original -----
| On 10-10-26 05:04 PM, Andr=C3=A9 Volpato wrote:
| > ----- Mensagem original -----
| > | On 10-10-25 03:26 PM, Andr=C3=A9 Volpato wrote:
| > |>  | On Mon, Oct 25, 2010 at 2:21 PM, Andr=C3=A9 Volpato
| > |>  |<andre.volpato@ecomtecnologia.com.br> wrote:
| >
| > (...)
| >
| > |>  |>   These times keep repeating after the second run, and I can
| > |>  |>   ensure AIX isn=C2=B4t touching the disks anymore.
| > |>  |>   I=C2=B4ve never seen this behaviour before. I heard about Dire=
ct
| > |>  |>   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=C2=B4ve been tuning AIX for the last 3 weeks, and lots of tuneables
| > got changed.
| > On Debian, we have far more experience, and it=C2=B4s been a chalenge t=
o
| > understand how AIX works.
| >
| > Most important tunes:
| > page_steal_method=3D1
| > lru_file_repage=3D0
| > kernel_heap_psize=3D64k
| > maxperm%=3D90
| > maxclient%=3D90
| > minperm%=3D20
| >
| > Disk:
| > chdev -l hdisk8 -a queue_depth=3D24
| > chdev -l hdisk8 -a reserve_policy=3Dno_reserve
| > chdev -l hdisk8 -a algorithm=3Dround_robin
| > chdev -l hdisk8 -a max_transfer=3D0x400000
| >
| > HBA:
| > chdev -l fcs0 -P -a max_xfer_size=3D0x400000 -a num_cmd_elems=3D1024
| >
| > Postgres:
| > shared_buffers =3D 2304MB
| > effective_io_concurrency =3D 5
|=20
| 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.
|=20
| 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.


I changed the querys in order to make a more valuable comparison.

Debian first run (23s):
http://explain.depesz.com/s/1fT

AIX first run (40s):
http://explain.depesz.com/s/CRG

Debian cached consecutive runs (8s)
http://explain.depesz.com/s/QAi

AIX cached consecutive runs (12s)
http://explain.depesz.com/s/xJU

Both boxes are runing with DDR2 667, so RAM speed seems to be the bootlenec=
k now.
We=C2=B4re about to try RedHat EL6 in the next few days.

|=20
| 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.

I disabled effective_io_concurrency at AIX but it made no changes on bitmap=
 index times.


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

[]=C2=B4s, Andre Volpato