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@xxxxxxxxxxxxx http://www.gregsmith.com Baltimore, MD ---------------------------(end of broadcast)--------------------------- TIP 4: Have you searched our list archives? http://archives.postgresql.org
