Re: Postgres Benchmark Results - Mailing list pgsql-performance

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.

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.

--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD