Re: performance question

On Mon, 20 Jan 2003 18:10:24 +0100 (CET), "Moritz Lennert"
<mlennert(at)club(dot)worldonline(dot)be> wrote:
>I'll try that, although I haven't changed any of the tuples since import
>of the data (this is a static table...)

Then I must have miscalculated something :-( What does VACUUM VERBOSE
ANALYZE <yourtable> say?

>> From what I've seen I think that the planner is right to choose a seq
>> scan. 226 seconds for reading 120K pages (~ 1GB) is not very
>> impressive, though. What kind of disk do you have?
>
>IDE, Samsung, 7200rpm
>
>> Is your disk heavily fragmented?
>
>It shouldn't be.
>
>> Did you enable DMA?
>
>No, should I ?

Yes. Here is what I got on a P IV 2 GHz with a Seagate 7200rpm(?)
disk:

~ # hdparm -t -T /dev/hda

/dev/hda:
Timing buffer-cache reads: 128 MB in 0.39 seconds =328.21 MB/sec
Timing buffered disk reads: 64 MB in 2.49 seconds = 25.70 MB/sec

vs.

~ # hdparm -t -T /dev/hda

/dev/hda:
Timing buffer-cache reads: 128 MB in 0.37 seconds =345.95 MB/sec
Timing buffered disk reads: 64 MB in 23.38 seconds = 2.74 MB/sec

~ # l xx
-rw-r--r-- 1 root root 1332104434 2003-01-20 19:04 xx
~ # time dd if=xx of=/dev/null bs=8k
162610+1 Records in
162610+1 Records out

real 0m48.665s
user 0m0.150s
sys 0m1.690s
~ # hdparm -d 0 /dev/hda
~ # time dd if=xx of=/dev/null bs=8k
162610+1 Records in
162610+1 Records out

real 7m42.666s
user 0m0.270s
sys 1m27.160s

With DMA: More than 3000 pages / second
Without DMA: ~ 350 pages / second

Your throughput: ~ 530 pages / second

>> recommend setting shared_buffers to something in the range [1000,
>> 4000].
>> And one of my favorites: effective_cache_size = 40000
>
>I will have to increase /proc/sys/kernel/shmmax for that, or ?

Maybe for shared_buffers. Certainly not for effective_cache_size.
The latter does not consume resources, it's just a hint for the
planner.

Servus
Manfred