Re: Having some problems with concurrent COPY commands

On Mon, Oct 12, 2015 at 11:17 AM, Shaun Thomas <bonesmoses(at)gmail(dot)com> wrote:

> Hi guys,
>
> I've been doing some design investigation and ran into an interesting snag
> I didn't expect to find on 9.4 (and earlier). I wrote a quick python script
> to fork multiple simultaneous COPY commands to several separate tables and
> found that performance apparently degrades based on how many COPY commands
> are running.
>
> For instance, in the logs with one COPY, I see about one second to import
> 100k rows. At two processes, it's 2 seconds. At four processes, it's 4
> seconds. This is for each process. Thus loading 400k rows takes 16 seconds
> cumulatively. To me, it looked like some kind of locking issue, but
> pg_locks showed no waits during the load. In trying to figure this out, I
> ran across this discussion:
>
>
> http://www.postgresql.org/message-id/CAB7nPqQJeASxDr0Rt9CJiaf9OnfjoJstyk18iw+oXi-OBO4gYA@mail.gmail.com
>
> Which came after this:
>
> http://forums.enterprisedb.com/posts/list/4048.page
>
> It would appear I'm running into whatever issue the xloginsert_slots patch
> tried to address, but not much discussion exists afterwards. It's like the
> patch just kinda vanished into the ether even though it (apparently)
> massively improves PG's ability to scale data import.
>
> I should note that setting wal_level to minimal, or doing the load on
> unlogged tables completely resolves this issue. However, those are not
> acceptable settings in a production environment. Is there any other way to
> get normal parallel COPY performance, or is that just currently impossible?
>
> I also know 9.5 underwent a lot of locking improvements, so it might not
> be relevant. I just haven't gotten a chance to repeat my tests with 9.5
> just yet.
>

Can you provide the test script? Also, have you tuned your database for
high io throughput? What is your storage system like?