Re: postgres 8.4, COPY, and high concurrency

On Tue, Nov 13, 2012 at 12:03 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> On Tue, Nov 13, 2012 at 1:27 PM, Heikki Linnakangas
> <hlinnakangas(at)vmware(dot)com> wrote:
>>
>> On 13.11.2012 21:13, Jon Nelson wrote:
>>>
>>
>> I'd guess it's lock contention on WALInsertLock. That means, the system is
>> experiencing lock contention on generating WAL records for the insertions.
>> If that theory is correct, you ought to get a big gain if you have
>> wal_level=minimal, and you create or truncate the table in the same
>> transaction with the COPY. That allows the system to skip WAL-logging the
>> COPY.
>
>
> wal_level doesn't exist for 8.4, but I have archive_mode = "off" and I am
> creating the table in the same transaction as the COPY.

That should work to bypass WAL. Can you directly verify whether you
are generating lots of WAL (look at the churn in pg_xlog) during those
loads?

Maybe your contention is someplace else. Since they must all be using
different tables, I don't think it would be the relation extension
lock. Maybe buffer mapping lock or freelist lock?

Cheers,

Jeff