Re: Postgres Benchmark Results

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(at)gregsmith(dot)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/