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 

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..:-)


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.

Attachment: insert.c
Description: Binary data

Attachment: Results.zip
Description: Zip archive

Attachment: 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

Reply via email to