| From: | Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> |
|---|---|
| To: | "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org> |
| Subject: | postgres 8.4, COPY, and high concurrency |
| Date: | 2012-11-13 19:13:40 |
| Message-ID: | CAKuK5J28HKP7EqKaGGUQMT-FcpPCQQUHJ18OvOGwG9a7nLVS4w@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-performance |
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 can't really try a newer version of postgres at this time (perhaps soon).
I'm using PG 8.4.13 on ScientificLinux 6.2 (x86_64), and the CPU is a 32
core Xeon E5-2680 @ 2.7 GHz.
--
Jon
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Heikki Linnakangas | 2012-11-13 19:27:39 | Re: postgres 8.4, COPY, and high concurrency |
| Previous Message | Craig Ringer | 2012-11-13 02:08:06 | Re: PostreSQL v9.2 uses a lot of memory in Windows XP |