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