Re: postgres 8.4, COPY, and high concurrency

From: Jon Nelson <jnelson+pgsql(at)jamponi(dot)net>
To: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
Cc: Heikki Linnakangas <hlinnakangas(at)vmware(dot)com>, "pgsql-performance(at)postgresql(dot)org" <pgsql-performance(at)postgresql(dot)org>
Subject: Re: postgres 8.4, COPY, and high concurrency
Date: 2012-11-14 14:41:45
Message-ID: CAKuK5J1pgN0njLiY_ZgLG4FAAePr60-Sa1XhqbSE6H6AJ5cFVQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

On Tue, Nov 13, 2012 at 7:10 PM, Jon Nelson <jnelson+pgsql(at)jamponi(dot)net> wrote:
> 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.

I'm still unable to replicate the problem, but I can show I wasn't
crazy, either. The average time to perform one of these COPY
operations when things are working is in the 15-45 second range. I
had configured PG to log any statement that look longer than 3
seconds, so I got a bunch of those in the logs. I have since
reconfigured to log *everything*. Anyway, when things were going
badly, COPY would take anywhere from 814 seconds to just under 1400
seconds for the exact same files.

UPDATE: I have been able to replicate the issue. The parent table (the
one referenced in the LIKE portion of the CREATE TABLE statement) had
three indices.

Now that I've been able to replicate the issue, are there tests that I
can perform that would be useful to people?
I will also try to build a stand-alone test.

--
Jon

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message David Greco 2012-11-14 15:23:15 Poor performance using CTE
Previous Message Peter Geoghegan 2012-11-14 12:04:52 Re: PostreSQL v9.2 uses a lot of memory in Windows XP