Re: postgresql 8.3 tps rate

On Sun, 25 Jan 2009, M. Edward (Ed) Borasky wrote:

> I started out running pgbench on the same machine but just
> moved the driver to another one trying to get better results.

That normally isn't necessary until you get to the point where you're
running thousands of transactions per second. The CPU load of the pgbench
isn't that big, and moving it to a network client does add its own
overhead.

> I can understand the "both" times -- I didn't move any "system-level"
> things like the write-ahead logs. But what I can't understand is the
> periods when it isn't using sda6, where the tablespace is.

Writes to the database are buffered by the shared_buffers mechanism. If
you dirty a block, it has to be unused for a bit after that before it will
be written out. The OS also buffers writes to the database disk. The
combination of the two means that you can write things sometimes that
won't turn into physical disk I/O to the database for a while. That is
not true of the WAL, which will always be generating activity when running
pgbench.

> number of transactions actually processed: 3000/3000

Generally, pgbench results start to be useful when you've pushed through
around 100,000 transactions or run for a few minutes. It looks like your
largest client test might be approaching that threshold only because the
TPS rate is so slow.

I'm not sure what is going on with your system, but the advice showing up
earlier in this thread is well worth heeding here: if you haven't
thoroughly proven that your disk setup works as expected on simple I/O
tests such as dd and bonnie++, you shouldn't be running pgbench yet.
It's not a tranparent benchmark unless you really understand what it's
doing, and you can waste endless time chasing phantom database setup
problems that way when you should be staring at hardware, driver, or OS
level ones instead. Do you know the disks are working as they should
here? Does the select-only pgbench give you reasonable results?

--
* Greg Smith gsmith(at)gregsmith(dot)com http://www.gregsmith.com Baltimore, MD