From: Zoltan Boszormenyi <zb@cybertec.at>
To: Greg Smith <gsmith@gregsmith.com>
Cc: PostgreSQL Performance <pgsql-performance@postgresql.org>
Subject: Re: Postgres Benchmark Results
Date: Tue, 22 May 2007 08:26:54 +0200
Message-ID: <46528D2E.1060701@cybertec.at> (raw)
In-Reply-To: <Pine.GSO.4.64.0705220130001.22223@westnet.com>
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>
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/
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: zb@cybertec.at, gsmith@gregsmith.com
Subject: Re: Postgres Benchmark Results
In-Reply-To: <46528D2E.1060701@cybertec.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox