Hi all, Couple of days ago, one of my colleague, Rahul Iyer posted a query regarding insert performance of 5M rows. A common suggestion was to use copy.
Unfortunately he can not use copy due to some constraints. I was helping him to get maximum out of it. We were playing with a data set of 500K rows on SunOS5.6 and postgresql 7.3.3 Best we could get was 500K records in 355 sec. That's roughly 1400 inserts per sec. This was with default settings and around 10K inserts per transaction. Postgresql was hogging all the CPU during load. Troubled by this, I set up a similar database at home. That is a Athlon XP2000+/512MB machine with a 40GB seagate disk. It is running slackware 9.0 with 2.4.20(IIRC). I have attached the results of my experiements and the code I used to benchmark. It was a simple table with an integer and a varchar(30) field. I was really amazed to see the numbers. First of all, it beat the sunOS machine left and right. Bruce posted some numbers of 9K inserts/sec. Here we see the same. Secondly I also played with filesystems. Ext3 does not seem to be performing as good. Reiser and ext2 did fine. Unfortunately the kernel didn't support XFS/JFS so could not test them. I have also attached the raw benchmark data in kspread format, for the curious. Didn't exported to anything else because kspread had troubles with exporting formula values. I also noticed that reiser takes hell lot more CPU than ext2 and ext3. It nearly peaks out all CPU capacity. Not so with ext2. Comments? One thing I can't help to notice is sunOs is not on same scale. The sunOS machine is a 1GB RAM machine. It has oracle and mysql running on it and have 300MB swap in use but I am sure it has SCSI disk and in all respect I would rather expect a RISC machine to perform better than an athlon XP machine, at least for an IO. If you want me to post details of sparc machine, please let me know how do I find it. I have never worked with sparcs earlier and have no intention of doing this again..:-) Bye Shridhar -- Fourth Law of Applied Terror: The night before the English History mid-term, your Biology instructor will assign 200 pages on planaria.Corollary: Every instructor assumes that you have nothing else to do except study for that instructor's course.
insert.c
Description: Binary data
Results.zip
Description: Zip archive
pgbenchmark.zip
Description: Zip archive
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match
