Message-ID: <46528D2E.1060701@cybertec.at>
Date: Tue, 22 May 2007 08:26:54 +0200
From: Zoltan Boszormenyi <zb@cybertec.at>
User-Agent: Thunderbird 1.5.0.10 (X11/20070302)
MIME-Version: 1.0
To: Greg Smith <gsmith@gregsmith.com>
CC: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Postgres Benchmark Results
References: <op.tsmrv71ecigqcu@apollo13> <46508CB9.1040404@cybertec.at>
	<20070521210125.GM62346@nasby.net>
	<37E3AB9B-77D7-4757-A379-29BE25F63533@event-s.net>
	<Pine.GSO.4.64.0705220130001.22223@westnet.com>
In-Reply-To: <Pine.GSO.4.64.0705220130001.22223@westnet.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Greg Smith írta:
> On Mon, 21 May 2007, Guido Neitzer wrote:
>
>> Yes, that right, but if a lot of the transactions are selects, there 
>> is no entry in the x_log for them and most of the stuff can come from 
>> the cache - read from memory which is blazing fast compared to any 
>> disk ... And this was a pg_bench test - I don't know what the 
>> benchmark really does but if I remember correctly it is mostly reading.
>
> The standard pgbench transaction includes a select, an insert, and 
> three updates.  All five finished equals one transaction; the fact 
> that the SELECT statment in there could be executed much faster where 
> it to happen on its own doesn't matter.
>
> Because it does the most work on the biggest table, the entire 
> combination is usually driven mostly by how long the UPDATE to the 
> accounts table takes.  The TPS numbers can certainly be no larger than 
> the rate at which you can execute that.
>
> As has been pointed out, every time you commit a transacation the disk 
> has to actually write that out before it's considered complete.  
> Unless you have a good caching disk controller (which your nForce5 is 
> not) you're limited to 120 TPS with a 7200RPM drive and 250 with a 
> 15000 RPM one. While it's possible to improve slightly on this using 
> the commit_delay feature, I haven't been able to replicate even a 100% 
> improvement that way when running pgbench, and to get even close to 
> that level of improvement would require a large number of clients.
>
> Unless you went out of your way to turn it off, your drive is caching 
> writes; every Seagate SATA drive I've ever seen does by default.  
> "1062 tps with 3-4 clients" just isn't possible with your hardware 
> otherwise. If you turn that feature off with:
>
> hdparm -W0 /dev/hda (might be /dev/sda with the current driver)
>
> that will disable the disk caching and you'll be reporting accurate 
> numbers--which will be far lower than you're seeing now.

And AFAIR according to a comment on LKML some time ago,
it greatly decreases your disk's MTBF as well.
But thanks for the great insights, anyway.
I already knew that nForce5 is not a caching controller. :-)
I meant it's a good desktop performer.
And having a good UPS and a bit oversized Enermax PSU
helps avoiding crashes with the sometimes erratic power line.

> While your results are an interesting commentary on how fast the 
> system can run when it has a write cache available, and the increase 
> with recent code is interesting, your actual figures here are a 
> fantasy.  The database isn't working properly and a real system using 
> this hardware would be expected to become corrupted if ran for long 
> enough.  I have a paper at 
> http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm you 
> might want to read that goes into more detail than you probably want 
> to know on this subject if you're like to read more about it--and you 
> really, really should if you intend to put important data into a 
> PostgreSQL database.

Thanks, I will read it.

> -- 
> * Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>


-- 
----------------------------------
Zoltán Böszörményi
Cybertec Geschwinde & Schönig GmbH
http://www.postgresql.at/