Received: from localhost (postgresql.org [64.49.215.8])
	by postgresql.org (Postfix) with ESMTP id ACFD4476196
	for <pgsql-sql@postgresql.org>; Mon, 20 Jan 2003 13:26:00 -0500 (EST)
Received: from email06.aon.at (WARSL401PIP3.highway.telekom.at [195.3.96.75])
	by postgresql.org (Postfix) with SMTP id B08CF476052
	for <pgsql-sql@postgresql.org>; Mon, 20 Jan 2003 13:25:59 -0500 (EST)
Received: (qmail 139266 invoked from network); 20 Jan 2003 18:25:58 -0000
Received: from m155p025.dipool.highway.telekom.at (HELO cantor) ([62.46.9.89])
	(envelope-sender <mkoi-pg@aon.at>)
	by qmail6rs.highway.telekom.at (qmail-ldap-1.03) with SMTP
	for <mlennert@club.worldonline.be>; 20 Jan 2003 18:25:58 -0000
From: Manfred Koizar <mkoi-pg@aon.at>
To: mlennert@club.worldonline.be
Cc: pgsql-sql@postgresql.org
Subject: Re: performance question
Date: Mon, 20 Jan 2003 19:27:11 +0100
Message-ID: <u4eo2vgpa3fh107rh9trtbnp2qtrj53vnu@4ax.com>
References: 
 <59665.164.15.128.4.1043062834.squirrel@http://moritz.homelinux.org>
	<1k3o2v01b5ceh937kibrojn2fjks3fc93t@4ax.com>
	<10031.164.15.128.4.1043077332.squirrel@http://moritz.homelinux.org>
	<j19o2vo4hn15ghmmjfc40d2116ej4eg46u@4ax.com>
	<44591.164.15.128.4.1043082624.squirrel@http://moritz.homelinux.org>
In-Reply-To: 
 <44591.164.15.128.4.1043082624.squirrel@http://moritz.homelinux.org>
X-Mailer: Forte Agent 1.8/32.548
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Virus-Scanned: by AMaViS new-20020517
X-Archive-Number: 200301/302
X-Sequence-Number: 11167

On Mon, 20 Jan 2003 18:10:24 +0100 (CET), "Moritz Lennert"
<mlennert@club.worldonline.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