Subject: Re: Performance PG 8.0 on dual opteron / 4GB / 3ware
From: Joost Kraaijeveld <J.Kraaijeveld@Askesis.nl>
To: Dave Cramer <pg@fastcrypt.com>
Cc: Pgsql-Performance <pgsql-performance@postgresql.org>
In-Reply-To: <F4E31443-6D3D-42CC-A946-C6A5AE98941E@fastcrypt.com>
References: <1131283854.15471.4.camel@Panoramix>
	<F4E31443-6D3D-42CC-A946-C6A5AE98941E@fastcrypt.com>
Content-Type: text/plain
Content-Transfer-Encoding: 7bit
Date: Tue, 15 Nov 2005 17:35:42 +0100
Message-Id: <1132072542.3250.16.camel@Panoramix>
Mime-Version: 1.0

Hi Dave,

On Mon, 2005-11-14 at 18:51 -0500, Dave Cramer wrote:
> Joost,
> 
> I've got experience with these controllers and which version do you  
> have. I'd expect to see higher than 50MB/s although I've never tried  
> RAID 5
> 
> I routinely see closer to 100MB/s with RAID 1+0 on their 9000 series
OK, than there must be hope.

> I would also suggest that shared buffers should be higher than 7500,  
> closer to 30000, and effective cache should be up around 200k
In my current 8.1 situation I use shared_buffers = 40000, 
effective_cache_size = 131072 .

> work_mem is awfully high, remember that this will be given to each  
> and every connection and can be more than 1x this number per  
> connection depending on the number of sorts
> done in the query.
I use such a high number because I am the only user querying and my
queries do sorted joins etc. 


> fsync=false ? I'm not even sure why we have this option, but I'd  
> never set it to false.
I want as much speed as possible for a database conversion that MUST be
handled in 1 weekend (it lasts now, with the current speed almost 7
centuries. I may be off a millenium). If it fails because of hardware
problem (the only reason we want and need fsync?) we will try next
weekend until it finally goes right. 

What I can see is that only the *write* performance of *long updates*
(and not inserts) are slow and they get slower in time: the first few
thousand go relatively fast, after that PostgreSQL crawls to a halt
(other "benchmarks" like bonnie++ or just dd'ing a big file don't have
this behavior).

I did notice that changing the I/O scheduler's nr_request from the
default 128 to 1024 or even 4096 made a remarkable performance
improvement. I suspect that experimenting with other I/O schedululers
could improve performance. But it is hard to find any useful
documentation about I/O schedulers.


-- 
Groeten,

Joost Kraaijeveld
Askesis B.V.
Molukkenstraat 14
6524NB Nijmegen
tel: 024-3888063 / 06-51855277
fax: 024-3608416
e-mail: J.Kraaijeveld@Askesis.nl
web: www.askesis.nl