From: Heikki Linnakangas <hlinnakangas@vmware.com>
To: Jon Nelson <jnelson+pgsql@jamponi.net>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: postgres 8.4, COPY, and high concurrency
Date: Tue, 13 Nov 2012 21:27:39 +0200
Message-ID: <50A29F2B.7070604@vmware.com> (raw)
In-Reply-To: <CAKuK5J28HKP7EqKaGGUQMT-FcpPCQQUHJ18OvOGwG9a7nLVS4w@mail.gmail.com>
References: <CAKuK5J28HKP7EqKaGGUQMT-FcpPCQQUHJ18OvOGwG9a7nLVS4w@mail.gmail.com>
On 13.11.2012 21:13, Jon Nelson 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). I tried larger
> and smaller shared buffers, all sorts of other tweaks, until I tried
> reducing the number of concurrent processes from 24 to 4.
>
> Disk I/O went up (on average) at least 10X and strace reports that the top
> system calls are write (61%), recvfrom (25%), and lseek (14%) - pretty
> reasonable IMO.
>
> 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. What is the likely
> cause of the semops?
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.
Or you could upgrade to 9.2. The WAL-logging of bulk COPY was optimized
in 9.2, it should help precisely the scenario you're facing.
- Heikki
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: hlinnakangas@vmware.com, jnelson+pgsql@jamponi.net
Subject: Re: postgres 8.4, COPY, and high concurrency
In-Reply-To: <50A29F2B.7070604@vmware.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox