i'm developing a database for one of my clients.
unfortunately, the combination of PostgreSQL 6.4 and FreeBSD 3.0 don't
seem to be giving me the performance i would expect.
i have a table which has some 60 fields, largely fixed length strings
(of a variety of sizes) and indexed on a 20 character string field.
currently i have some 5,866,667 records in the table and when i use
a c program to insert records, i get a max of 102 inserts per second.
updates average about 40/second, sometimes much slower.
i intend to have a sliding group of about 10-15 million records in this
table (deleting some each night, while constantly adding new records).
if 100 inserts/second and 40 updates/second is all i can realistically
expect, then i will have to investigate alternate databases (likely
commercial ones like Progress on SCO, yech!).
if anyone has any pointers as to why this is so slow, lemme know.
the system is:
FreeBSD 3.0-RELEASE
CPU: Pentium II (299.17-MHz 686-class CPU)
avail memory = 62836736 (61364K bytes)
ahc0: <Adaptec aic7880 Ultra SCSI adapter> rev 0x00 int a irq 15 on pci1.4.0
da1: <IBM DDRS-34560W S71D> Fixed Direct Access SCSI2 device
da1: 40.0MB/s transfers (20.0MHz, offset 8, 16bit), Tagged Queueing Enabled
da2: <IBM DDRS-34560W S71D> Fixed Direct Access SCSI2 device
da2: 40.0MB/s transfers (20.0MHz, offset 8, 16bit), Tagged Queueing Enabled
da3: <QUANTUM VIKING 4.5 WSE 880P> Fixed Direct Access SCSI2 device
da3: 40.0MB/s transfers (20.0MHz, offset 8, 16bit), Tagged Queueing Enabled
da4: <QUANTUM VIKING 4.5 WSE 880P> Fixed Direct Access SCSI2 device
da4: 40.0MB/s transfers (20.0MHz, offset 8, 16bit), Tagged Queueing Enabled
postgres v6.4 (not 6.4.2 yet)
the databases live on a 16 gig striped (not RAID) array across the
above 4 drives.
the operating system lives on a separate drive.
we are going to upgrade to 512M RAM soon, but i don't think that RAM is the
issue on this beast.
bonnie and iozone show that the array is kicking ass as far as throughput goes.
--
[ Jim Mercer Reptilian Research jim@reptiles.org +1 416 410-5633 ]
[ The telephone, for those of you who have forgotten, was a commonly used ]
[ communications technology in the days before electronic mail. ]
[ They're still easy to find in most large cities. -- Nathaniel Borenstein ]