Re: Poor performance on seq scan - Mailing list pgsql-performance

Lazlo,

You can ignore tuning postgres and trying to use indexes, your problem is a bad hardware / OS configuration.  The disks
youare using should read 4-5 times faster than they are doing.  Look to the SATA chipset driver in your FreeBSD config
-perhaps upgrading your kernel would help. 

Still, the most you should expect is 5-6 times faster query than before.  The data in your table is slightly larger
thanRAM.  When you took it out of the DBMS it was smaller than RAM, so it fit in the I/O cache. 

With a text scan query you are stuck with a seqscan unless you use a text index like tsearch.  Buy more disks and a
Raidcontroller and use Raid5 or Raid10. 

- Luke

Msg is shrt cuz m on ma treo

 -----Original Message-----
From:     Laszlo Nagy [mailto:gandalf@designaproduct.biz]
Sent:    Tuesday, September 12, 2006 08:16 AM Eastern Standard Time
To:    Luke Lonergan; pgsql-performance@postgresql.org
Subject:    Re: [PERFORM] 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