From: Laszlo Nagy <gandalf@designaproduct.biz>
To: Luke Lonergan <LLonergan@greenplum.com>
To: pgsql-performance@postgresql.org
Subject: Re: Poor performance on seq scan
Date: Tue, 12 Sep 2006 14:15:30 +0200
Message-ID: <4506A4E2.706@designaproduct.biz> (raw)
In-Reply-To: <3E37B936B592014B978C4415F90D662D04589725@MI8NYCMAIL06.Mi8.com>
References: <3E37B936B592014B978C4415F90D662D04589725@MI8NYCMAIL06.Mi8.com>
Luke Lonergan írta:
> Lazlo,
>
>
>> Meanwhile, "iostat 5" gives something like this:
>>
>> tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id
>> 1 14 128.00 1 0.10 128.00 1 0.10 5 0 94 1 0
>> 0 12 123.98 104 12.56 123.74 104 12.56 8 0 90 2 0
>>
>
> This is your problem. Do the following and report the results here:
>
> Take the number of GB of memory you have (say 2 for 2GB), multiply it by
> 250000. This is the number of 8KB pages you can fit in twice your ram.
> Let's say you have 2GB - the result is 500,000.
>
> Use that number to do the following test on your database directory:
> time bash -c "dd if=/dev/zero of=/<dbdir>/bigfile bs=8k
> count=<number_from_above> && sync"
>
I have 1GB RAM. The data directory is in /usr/local/pgsql/data. The root
of this fs is /usr.
time sh -c "dd if=/dev/zero of=/usr/test/bigfile bs=8k count=250000 &&
sync "
250000+0 records in
250000+0 records out
2048000000 bytes transferred in 48.030627 secs (42639460 bytes/sec)
0.178u 8.912s 0:48.31 18.7% 9+96k 37+15701io 0pf+0w
> Then do this:
> time bash -c "dd if=/<dbdir>/bigfile of=/dev/null bs=8k"
>
time sh -c "dd if=/usr/test/bigfile of=/dev/null bs=8k"
250000+0 records in
250000+0 records out
2048000000 bytes transferred in 145.293473 secs (14095609 bytes/sec)
0.110u 5.857s 2:25.31 4.1% 10+99k 32923+0io 0pf+0w
At this point I thought there was another process reading doing I/O so I
retried:
250000+0 records in
250000+0 records out
2048000000 bytes transferred in 116.395211 secs (17595226 bytes/sec)
0.137u 5.658s 1:56.51 4.9% 10+103k 29082+0io 0pf+1w
and again:
250000+0 records in
250000+0 records out
2048000000 bytes transferred in 120.198224 secs (17038521 bytes/sec)
0.063u 5.780s 2:00.21 4.8% 10+98k 29776+0io 0pf+0w
This is a mirrored disk with two SATA disks. In theory, writing should
be slower than reading. Is this a hardware problem? Or is it that "sync"
did not do the sync?
Laszlo
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gandalf@designaproduct.biz, LLonergan@greenplum.com
Subject: Re: Poor performance on seq scan
In-Reply-To: <4506A4E2.706@designaproduct.biz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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