Re: postgres 8.4, COPY, and high concurrency

Поиск
Список
Период
Сортировка
От Jon Nelson
Тема Re: postgres 8.4, COPY, and high concurrency
Дата
Msg-id CAKuK5J3n-aZ9iXYej-KfevYQC6_AjpeDDYbxnRyhsdn4qFTH0w@mail.gmail.com
обсуждение исходный текст
Ответ на Re: postgres 8.4, COPY, and high concurrency  (Heikki Linnakangas <hlinnakangas@vmware.com>)
Ответы Re: postgres 8.4, COPY, and high concurrency  (Jeff Janes <jeff.janes@gmail.com>)
Список pgsql-performance



On Tue, Nov 13, 2012 at 1:27 PM, Heikki Linnakangas <hlinnakangas@vmware.com> wrote:
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.

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.
 

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.

Unfortunately, that's what I was expecting.



--
Jon

В списке pgsql-performance по дате отправления:

Предыдущее
От: Jeff Janes
Дата:
Сообщение: Re: postgres 8.4, COPY, and high concurrency
Следующее
От: Jeff Janes
Дата:
Сообщение: Re: postgres 8.4, COPY, and high concurrency