Insert performance

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.