pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedAIX slow buffer reads
13+ messages / 5 participants
[nested] [flat]
* AIX slow buffer reads
@ 2010-10-25 18:21 André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
0 siblings, 1 reply; 13+ messages in thread
From: André Volpato @ 2010-10-25 18:21 UTC (permalink / raw)
To: pgsql-performance
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?
1 - http://explain.depesz.com/s/5oz
[]´s, André Volpato
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
@ 2010-10-25 18:50 ` Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Merlin Moncure @ 2010-10-25 18:50 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
@ 2010-10-25 19:26 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 13+ messages in thread
From: André Volpato @ 2010-10-25 19:26 UTC (permalink / raw)
To: Merlin Moncure <mmoncure@gmail.com>; +Cc: pgsql-performance
| 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.
| *) Are the plans *exactly* the same?
The plan I sent refers to the AIX box:
http://explain.depesz.com/s/5oz
At Debian, the plan looks pretty much the same.
| *) Are you running explain analyze? There are some platform specific
| interactions caused by timing.
Yes. I´m not concerned about timing because the difference (8s against 0.3s) is huge.
| *) Are you transferring the data across the network? rule out
| (horribly difficult to diagnose/fix) network effects.
Not likely... Both boxes are in the same Bladecenter, using the same storage.
|
| merlin
[]´s, Andre Volpato
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
@ 2010-10-26 14:06 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Brad Nicholson @ 2010-10-26 14:06 UTC (permalink / raw)
To: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
@ 2010-10-26 21:04 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
0 siblings, 1 reply; 13+ messages in thread
From: André Volpato @ 2010-10-26 21:04 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-performance
----- 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
wal_sync_method = fdatasync
wal_buffers = 2MB
checkpoint_segments = 32
checkpoint_timeout = 10min
random_page_cost = 2.5
effective_cache_size = 7144MB
Like I said, there´s more but this is the most important.
|
| On AIX can you trace why it is CPU bound? What else is taking the CPU
| time, anything?|
We´re using iostat, svmon and vmstat to trace CPU, swap and IO activity.
On 'topas' we saw no disk activity at all, but we get a Wait% about 70%, and about 700 pages/s read in PageIn, no PageOut, no PgspIn and no PgspOut.
It´s a dedicated server, no process runing besides postgres.
| Also, can you provide the output of pg_config from your AIX build?
# pg_config
BINDIR = /usr/local/pgsql/bin
DOCDIR = /usr/local/pgsql/share/doc
HTMLDIR = /usr/local/pgsql/share/doc
INCLUDEDIR = /usr/local/pgsql/include
PKGINCLUDEDIR = /usr/local/pgsql/include
INCLUDEDIR-SERVER = /usr/local/pgsql/include/server
LIBDIR = /usr/local/pgsql/lib
PKGLIBDIR = /usr/local/pgsql/lib
LOCALEDIR = /usr/local/pgsql/share/locale
MANDIR = /usr/local/pgsql/share/man
SHAREDIR = /usr/local/pgsql/share
SYSCONFDIR = /usr/local/pgsql/etc
PGXS = /usr/local/pgsql/lib/pgxs/src/makefiles/pgxs.mk
CONFIGURE = '--enable-integer-datetimes' '--with-readline' '--with-threads' '--with-zlib' '--with-html' 'CC=gcc -maix64' 'LDFLAGS=-Wl,-bbigtoc'
CC = gcc -maix64
CPPFLAGS =
CFLAGS = -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -fwrapv
CFLAGS_SL =
LDFLAGS = -Wl,-bbigtoc -Wl,-blibpath:/usr/local/pgsql/lib:/usr/lib:/lib
LDFLAGS_SL = -Wl,-bnoentry -Wl,-H512 -Wl,-bM:SRE
LIBS = -lpgport -lz -lreadline -lld -lm
VERSION = PostgreSQL 8.4.4
|
| --
| Brad Nicholson 416-673-4106
| Database Administrator, Afilias Canada Corp.
[]´s, Andre Volpato
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
@ 2010-10-27 12:42 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 17:05 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 1 reply; 13+ messages in thread
From: Brad Nicholson @ 2010-10-27 12:42 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
@ 2010-10-27 17:05 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 19:24 ` Re: AIX slow buffer reads Greg Smith <greg@2ndquadrant.com>
0 siblings, 1 reply; 13+ messages in thread
From: André Volpato @ 2010-10-27 17:05 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: pgsql-performance
----- Mensagem original -----
| 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.
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 bootleneck now.
We´re about to try RedHat EL6 in the next few days.
|
| 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.
|
[]´s, Andre Volpato
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 17:05 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
@ 2010-10-27 19:24 ` Greg Smith <greg@2ndquadrant.com>
2010-10-27 20:10 ` Re: AIX slow buffer reads Tom Lane <tgl@sss.pgh.pa.us>
2010-10-27 20:56 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-28 12:33 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
0 siblings, 3 replies; 13+ messages in thread
From: Greg Smith @ 2010-10-27 19:24 UTC (permalink / raw)
To: André Volpato <andre.volpato@ecomtecnologia.com.br>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance
André Volpato wrote:
> |
> | 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's point is that it probably doesn't do anything at all on AIX, and
is already disabled accordingly. But on Linux, it is doing something,
and that might be contributing to why it's executing so much better on
that platform. If you disable that parameter on your Debian box, that
should give you an idea whether that particular speed-up is a major
component to the difference you're seeing or not.
Also, if the system check was done by the "vendor team" team, don't
trust them at all. It doesn't sound like a disk problem is involved in
your case yet, but be sure to do your own basic disk benchmarking too
rather than believing what you're sold. There's a quick intro to that
at http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm
and a much longer treatment of the subject in my book if you want a lot
more details. I don't have any AIX-specific tuning advice in there though.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services and Support www.2ndQuadrant.us
"PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 17:05 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 19:24 ` Re: AIX slow buffer reads Greg Smith <greg@2ndquadrant.com>
@ 2010-10-27 20:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-27 22:37 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2 siblings, 1 reply; 13+ messages in thread
From: Tom Lane @ 2010-10-27 20:10 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: André Volpato <andre.volpato@ecomtecnologia.com.br>; Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance
Greg Smith <greg@2ndquadrant.com> writes:
> André Volpato wrote:
>> I disabled effective_io_concurrency at AIX but it made no changes on bitmap index times.
> Brad's point is that it probably doesn't do anything at all on AIX, and
> is already disabled accordingly.
AFAICT from googling, AIX does have posix_fadvise, though maybe it
doesn't do anything useful ...
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 17:05 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 19:24 ` Re: AIX slow buffer reads Greg Smith <greg@2ndquadrant.com>
2010-10-27 20:10 ` Re: AIX slow buffer reads Tom Lane <tgl@sss.pgh.pa.us>
@ 2010-10-27 22:37 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 22:44 ` Re: AIX slow buffer reads Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 13+ messages in thread
From: Brad Nicholson @ 2010-10-27 22:37 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Greg Smith <greg@2ndquadrant.com>; André Volpato <andre.volpato@ecomtecnologia.com.br>; pgsql-performance
On 10/27/2010 4:10 PM, Tom Lane wrote:
> Greg Smith<greg@2ndquadrant.com> writes:
>> André Volpato wrote:
>>> I disabled effective_io_concurrency at AIX but it made no changes on bitmap index times.
>> Brad's point is that it probably doesn't do anything at all on AIX, and
>> is already disabled accordingly.
> AFAICT from googling, AIX does have posix_fadvise, though maybe it
> doesn't do anything useful ...
>
> regards, tom lane
If there is an easy way to check if it does do anything useful? If so,
I can check it out.
--
Brad Nicholson 416-673-4106
Database Administrator, Afilias Canada Corp.
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 17:05 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 19:24 ` Re: AIX slow buffer reads Greg Smith <greg@2ndquadrant.com>
2010-10-27 20:10 ` Re: AIX slow buffer reads Tom Lane <tgl@sss.pgh.pa.us>
2010-10-27 22:37 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
@ 2010-10-27 22:44 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 13+ messages in thread
From: Tom Lane @ 2010-10-27 22:44 UTC (permalink / raw)
To: Brad Nicholson <bnichols@ca.afilias.info>; +Cc: Greg Smith <greg@2ndquadrant.com>; André Volpato <andre.volpato@ecomtecnologia.com.br>; pgsql-performance
Brad Nicholson <bnichols@ca.afilias.info> writes:
> On 10/27/2010 4:10 PM, Tom Lane wrote:
>> AFAICT from googling, AIX does have posix_fadvise, though maybe it
>> doesn't do anything useful ...
> If there is an easy way to check if it does do anything useful? If so,
> I can check it out.
If you don't see any performance change in bitmap scans between
effective_io_concurrency = 0 and effective_io_concurrency = maybe 4 or
so, then you could probably conclude it's a no-op.
regards, tom lane
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 17:05 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 19:24 ` Re: AIX slow buffer reads Greg Smith <greg@2ndquadrant.com>
@ 2010-10-27 20:56 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2 siblings, 0 replies; 13+ messages in thread
From: André Volpato @ 2010-10-27 20:56 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance
----- Mensagem original -----
| André Volpato wrote:
| > |
| > | 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's point is that it probably doesn't do anything at all on AIX,
| and is already disabled accordingly. But on Linux, it is doing something,
| and that might be contributing to why it's executing so much better on
| that platform. If you disable that parameter on your Debian box, that
| should give you an idea whether that particular speed-up is a major
| component to the difference you're seeing or not.
Cant do it right now, but will do it ASAP and post here.
| Also, if the system check was done by the "vendor team" team, don't
| trust them at all. It doesn't sound like a disk problem is involved in
| your case yet, but be sure to do your own basic disk benchmarking too
| rather than believing what you're sold. There's a quick intro to that
| at
| http://www.westnet.com/~gsmith/content/postgresql/pg-disktesting.htm
| and a much longer treatment of the subject in my book if you want a
| lot
| more details. I don't have any AIX-specific tuning advice in there
| though.|
I´m gonna read your sugestion, thanks.
We tested the disks also, and we did a lot of tuning to get acceptable transfer rates at AIX.
Yesterday I tried your "stream-scaling" and get around 7000MB/s (single thread) and 10000MB/s (eight threads) at AIX, and a little less than that at Debian, since its a virtual box.
I found that even my notebook is close to that transfer rates, and both boxes are limited by DDR2 speeds.
| --
| Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
| PostgreSQL Training, Services and Support www.2ndQuadrant.us
| "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
[]´s, André Volpato
^ permalink raw reply [nested|flat] 13+ messages in thread
* Re: AIX slow buffer reads
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Re: AIX slow buffer reads Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Re: AIX slow buffer reads Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 17:05 ` Re: AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 19:24 ` Re: AIX slow buffer reads Greg Smith <greg@2ndquadrant.com>
@ 2010-10-28 12:33 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2 siblings, 0 replies; 13+ messages in thread
From: André Volpato @ 2010-10-28 12:33 UTC (permalink / raw)
To: Greg Smith <greg@2ndquadrant.com>; +Cc: Brad Nicholson <bnichols@ca.afilias.info>; pgsql-performance
----- Mensagem original -----
| André Volpato wrote:
| > |
| > | 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's point is that it probably doesn't do anything at all on AIX,
| and
| is already disabled accordingly. But on Linux, it is doing something,
| and that might be contributing to why it's executing so much better on
| that platform. If you disable that parameter on your Debian box, that
| should give you an idea whether that particular speed-up is a major
| component to the difference you're seeing or not.
Here´s new explains based on Debian box:
(1) effective_io_concurrency = 5
# /etc/init.d/postgresql stop
# echo 3 > /proc/sys/vm/drop_caches
# /etc/init.d/postgresql start
http://explain.depesz.com/s/br
(2) effective_io_concurrency = 0
# /etc/init.d/postgresql stop
# echo 3 > /proc/sys/vm/drop_caches
# /etc/init.d/postgresql start
http://explain.depesz.com/s/3A0
BitmapAnd really gets improved a little bit in (1), but Bitmap index scans got a lot worse.
| --
| Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
| PostgreSQL Training, Services and Support www.2ndQuadrant.us
| "PostgreSQL 9.0 High Performance": http://www.2ndQuadrant.com/books
[]´s, Andre Volpato
^ permalink raw reply [nested|flat] 13+ messages in thread
end of thread, other threads:[~2010-10-28 12:33 UTC | newest]
Thread overview: 13+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2010-10-25 18:21 AIX slow buffer reads André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-25 18:50 ` Merlin Moncure <mmoncure@gmail.com>
2010-10-25 19:26 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-26 14:06 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-10-26 21:04 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 12:42 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 17:05 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-27 19:24 ` Greg Smith <greg@2ndquadrant.com>
2010-10-27 20:10 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-27 22:37 ` Brad Nicholson <bnichols@ca.afilias.info>
2010-10-27 22:44 ` Tom Lane <tgl@sss.pgh.pa.us>
2010-10-27 20:56 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
2010-10-28 12:33 ` André Volpato <andre.volpato@ecomtecnologia.com.br>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox