Problem with bulk inserts

From: Lucas Madar <lucas(at)negaverse(dot)org>
To: pgsql-admin(at)postgresql(dot)org
Subject: Problem with bulk inserts
Date: 2002-09-20 20:16:29
Message-ID: 1032552989.3d8b821d44e78@wwwmail.negaverse.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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>

Browse pgsql-admin by date

  From Date Subject
Next Message Evan Kyle 2002-09-20 20:50:14 Expire data based on available space?
Previous Message shreedhar 2002-09-20 08:02:00 Is Postgre can configure with IIS