Which performance can I expect?

Question

I'm running a Postgres 9.1 database on a Debian Squeeze server. It's a paravirtualized XEN guest system with 4G RAM and 1 CPU core, based on a i7-2600 host system with Linux software-RAID1.

I'm trying to get a feel for how fast/slow my setup is, and am running pgbench. This is the command I've been running to initialize pgbench:

pgbench -i -s 100 -U jboss -p 5432 -h 127.0.0.1 project2_core

The first 5000000 tuples run by very quickly, but then it gets slower and slower. Now, it displayed

10000000 tuples done.

for a very long time (10 minutes or so) with no CPU load, and also RAM is not an issue. Only then, it continued with

set primary key...

and some NOTICEs about primary keys.

My question: is this runtime to be expected, or must I suspect there's something wrong with my system?

Answer 1

If it is dropping off after a large number of writes, my guess is that the first ones are all getting cached and the drop in performance is due to cache misses. You will get better performance by allocating more RAM.

BTW, virtualized databases are tricky, performance-wise. There could be all kinds of other things that could be issues, including disk I/O bottlenecks and you will have a fair bit of trouble troubleshooting this. My recommendation always is to run databases directly on the host OS. Virtualization usually adds needless complexity and there are better ways to consolidate PostgreSQL servers than virtualization.

Answer 2

I'd look at how you have your IO stack set up, which you say nothing about in the question. It sounds like actually committing the rows is taking a really long time. Virtual file systems on top of other file systems often display the worst characteristics of both layers.

You also should try doing an actual test run, as well as just populating a database.