I am running into a strange problem with postgresql when I attempt to insert
1200 rows per second into a database.
The problem is not due to the machine (Dual Xeon 2.2ghz, 2GB, RAID 1/0, running
FreeBSD 4.6.2-REL).
Postgres consumes approximately 3 to 4% CPU time. Very little disk activity
occurs (0.02MB/sec usually, sometimes there are bursts up to 5MB/sec).
However, postgresql sits there and takes its time processing the insert
queries, while not using any resources. This is leading to a huge backlog of
insert queries.
My configuration is as follows:
shared_buffers = 32768 # 32768 * 8192 = 256MB
sort_mem = 33554432 # 16mb of memory
vacuum_mem = 134217728 # 128mb of memory
fsync = false
Turning 'fsync' on or off does not seem to make a difference.
In top, postgresql generally sits in the 'sbwait' state. I'm assuming this
means it's waiting for a blocking socket. I have tried sending more inserts per
query, but this does not seem to make a difference.
Is this a bug? Is there a better way to handle inserts of this volume?
Thanks,
Lucas Madar <lucas at negaverse dot org>