Re: postgres 8.4, COPY, and high concurrency

On Tue, Nov 13, 2012 at 2:43 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
> 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?

I had moved on to a different approach to importing the data which
does not work concurrently. However, I went back and tried to
re-create the situation and - at least a naive attempt failed. I'll
give it a few more tries -- I was creating two tables using CREATE
TABLE <unique name> LIKE (some other table INCLUDING <everything>).
Then I would copy the data in, add some constraints (FK constraints
but only within these two tables) and then finally (for each table)
issue an ALTER TABLE <unique name> INHERIT <some other table>. To be
clear, however, everything bogged down in the COPY stage which was
immediately following the table creation.

I'll note that my naive test showed almost no unexpected overhead at
all, so it's clearly not representative of the problem I encountered.

--
Jon