X-Original-To: pgsql-performance-postgresql.org@postgresql.org
Received: from localhost (wm.hub.org [200.46.204.128])
	by postgresql.org (Postfix) with ESMTP id 76A9C9FA435
	for <pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 12 Sep 2006 09:15:48 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.128]) (amavisd-new, port 10024)
	with ESMTP id 32983-02 for
	<pgsql-performance-postgresql.org@postgresql.org>;
	Tue, 12 Sep 2006 12:15:36 +0000 (UTC)
X-Greylist: from auto-whitelisted by SQLgrey-
Received: from smtp.enternet.hu (smtp.enternet.hu [62.112.192.21])
	by postgresql.org (Postfix) with ESMTP id 8234A9F95CB
	for <pgsql-performance@postgresql.org>;
	Tue, 12 Sep 2006 09:15:36 -0300 (ADT)
Received: from [83.216.41.252] (helo=[172.16.0.43])
	by smtp.enternet.hu with esmtpa (Exim 4)
	id 1GN7Ap-000F7f-8i; Tue, 12 Sep 2006 14:15:31 +0200
Message-ID: <4506A4E2.706@designaproduct.biz>
Date: Tue, 12 Sep 2006 14:15:30 +0200
From: Laszlo Nagy <gandalf@designaproduct.biz>
User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
MIME-Version: 1.0
To: Luke Lonergan <LLonergan@greenplum.com>, pgsql-performance@postgresql.org
Subject: Re: Poor performance on seq scan
References: <3E37B936B592014B978C4415F90D662D04589725@MI8NYCMAIL06.Mi8.com>
In-Reply-To: <3E37B936B592014B978C4415F90D662D04589725@MI8NYCMAIL06.Mi8.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=
X-Spam-Level: 
X-Archive-Number: 200609/76
X-Sequence-Number: 20762

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