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 [EMAIL PROTECTED] 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/
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
