On Sun, 2 Dec 2007, Beyers Cronje wrote:
> Initially I tested this on my development PC, an old P4 system with 2GB
> RAM and 10,000 INSERTs took ~12 secs on average, which I was fairly
> satisfied with. I then moved everything over to our test server, a new
> Dell 1950 server with quad core Xeon processors, 4GB RAM and SCSI hdd
> expecting to see better performance, but instead performance dropped to
> ~44 secs for 10,000 INSERTs.
Your development system is probably running inexpensive IDE disks that
cache writes, while the test server is not caching. If you loop over
single inserts, PostgreSQL's default configuration will do a physical
commit to disk after every one of them, which limits performance to how
fast the disk spins. If your server has 15K RPM drives, a single client
can commit at most 250 transactions per second to disk, which means 10,000
inserts done one at a time must take at least 40 seconds no matter how
fast the server is.
There's a rambling discussion of this topic at
http://www.westnet.com/~gsmith/content/postgresql/TuningPGWAL.htm that
should fill in some background here.
If you use COPY instead of INSERT, that bypasses the WAL and you don't see
this. Also, if you adjust your loop to do multiple inserts as a single
transaction, that will change the behavior here as well.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD