Tom Lane wrote:
>
> John Coers <coers@intrinsity.com> writes:
> > I am trying to figure out what is slowing down multiple concurrent COPY
> > commands to my database and all I see is lots of semops.
>
> Are you talking about concurrent copies into the same table? That would
> suffer a lot of short-term lock interference, no doubt, since all the
> copies are going to be writing the same disk page (ie, the current last
> page of the table).
>
> regards, tom lane
Yes, that is exactly the problem. So there is not a lock per se? DO they each
right to their own piece of shared memory and then try and flush that to disk and that
is when they interfere?
Are there any suggested techniques or tweaks I can make to avoid this interference?
The techniques I have tried, in order of my perception of their effectiveness are:
1) set N=2. I have 150-250 client machines each trying to connect and COPY 10k-70k rows
of 3 ints. If a connection can't be made, the client waits and tries again in a few secs.
This has the most positive effect by FAR of anything I've tried. The negative side is that
if someone wants to query, they will hold up the copying of data TO the db by taking up a connection
even though they wouldn't otherwise block.
2) Set up multiple dbs on different disks. I can't see that this has much effect, but maybe a little.
3) Jacking up the shared memory, wal buffers, and running in silent mode.
--
John Coers Intrinsity, Inc.
coers@intrinsity.com Austin, Texas