Re: postgres 8.4, COPY, and high concurrency

On Tue, Nov 13, 2012 at 11:13 AM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> I was working on a data warehousing project where a fair number of files
> could be COPY'd more or less directly into tables. I have a somewhat nice
> machine to work with, and I ran on 75% of the cores I have (75% of 32 is
> 24).
>
> Performance was pretty bad. With 24 processes going, each backend (in COPY)
> spent 98% of it's time in semop (as identified by strace).

They are probably fighting over the right to insert records into the WAL stream.

This has been improved in 9.2

> Given that each COPY is into it's own, newly-made table with no indices or
> foreign keys, etc, I would have expected the interaction among the backends
> to be minimal, but that doesn't appear to be the case.

On newer versions if you set wal_level to minimal and archive_mode to
off, then these operations would bypass WAL entirely. I can't figure
out if there is a corresponding optimization in 8.4, though.

Cheers,

Jeff