Date: Tue, 26 Oct 2010 19:04:10 -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: <936162153.10447.1288127050905.JavaMail.root@zimbra01a>
In-Reply-To: <4CC6E07C.2050105@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-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 Direct I/=
O
| > |>  and I was thinking about givng it a shot.
| > |> =20
| > |>  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.
|=20
| Have you confirmed that the hardware is ok on both servers?
|=20

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 Deb=
ian 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 to un=
derstand 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
wal_sync_method =3D fdatasync
wal_buffers =3D 2MB
checkpoint_segments =3D 32
checkpoint_timeout =3D 10min
random_page_cost =3D 2.5
effective_cache_size =3D 7144MB

Like I said, there=C2=B4s more but this is the most important.


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


We=C2=B4re using iostat, svmon and vmstat to trace CPU, swap and IO activit=
y.
On 'topas' we saw no disk activity at all, but we get a Wait% about 70%, an=
d about 700 pages/s read in PageIn, no PageOut, no PgspIn and no PgspOut.
It=C2=B4s a dedicated server, no process runing besides postgres.


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

# pg_config
BINDIR =3D /usr/local/pgsql/bin
DOCDIR =3D /usr/local/pgsql/share/doc
HTMLDIR =3D /usr/local/pgsql/share/doc
INCLUDEDIR =3D /usr/local/pgsql/include
PKGINCLUDEDIR =3D /usr/local/pgsql/include
INCLUDEDIR-SERVER =3D /usr/local/pgsql/include/server
LIBDIR =3D /usr/local/pgsql/lib
PKGLIBDIR =3D /usr/local/pgsql/lib
LOCALEDIR =3D /usr/local/pgsql/share/locale
MANDIR =3D /usr/local/pgsql/share/man
SHAREDIR =3D /usr/local/pgsql/share
SYSCONFDIR =3D /usr/local/pgsql/etc
PGXS =3D /usr/local/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE =3D '--enable-integer-datetimes' '--with-readline' '--with-thread=
s' '--with-zlib' '--with-html' 'CC=3Dgcc -maix64' 'LDFLAGS=3D-Wl,-bbigtoc'
CC =3D gcc -maix64
CPPFLAGS =3D
CFLAGS =3D -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-aft=
er-statement -Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL =3D
LDFLAGS =3D -Wl,-bbigtoc -Wl,-blibpath:/usr/local/pgsql/lib:/usr/lib:/lib
LDFLAGS_SL =3D -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE
LIBS =3D -lpgport -lz -lreadline -lld -lm
VERSION =3D PostgreSQL 8.4.4


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

[]=C2=B4s, Andre Volpato