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@gregsmith.com http://www.gregsmith.com Baltimore, MD