Re: Poor performance on seq scan

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