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"
Then do this:
time bash -c "dd if=/<dbdir>/bigfile of=/dev/null bs=8k"
>
> I made another test. I create a file with the identifiers and
> names of the products:
>
> psql#\o products.txt
> psql#select id,name from product;
>
> Then I can search using grep:
>
> grep "Mug" products.txt | cut -f1 -d\|
>
> There is a huge difference. This command runs within 0.5
> seconds. That is, at least 76 times faster than the seq scan.
The file probably fits in the I/O cache. Your disks will at most go
between 60-80MB/s, or from 5-7 times faster than what you see now. RAID
1 with one query will only deliver one disk worth of bandwidth.
- Luke