Re: Concurrent COPY commands - Mailing list pgsql-novice

Sorry about the late reply.

I only have two fast SATA drives on software RAID, but that really isn't the issue- while the copy commands are going, disk activity is relatively low. By relatively I mean that I have seen it a lot higher under certain circumstances, and I know for sure the disks aren't holding anything back. I know it's a bad comparison, but the process generating this huge amount of data can write directly to the disk very fast and still be CPU-bound, while it eventually ends up waiting for postgres when I try to pipe it into the database. I figured some overhead was to be expected and that's why I tried the parallel setup in the first place.

What I see is that after some buffering (not sure it is buffering, but after it gets some data), one postgres process will ramp up to 100% CPU (on one core) for some time, thus blocking its input FIFO. That is when the hard drive activity goes up a bit, but whatever it is doing is definitely CPU-bound on that core.

No more than one worker process does this at a time. And no matter what kind of FIFO buffers and select() calls I use, the calling process eventually gets blocked because the postgres processes don't appear to be working in parallel as well as they could be; hence, postgres doesn't take in any more data for a while. I'm really curious about why going parallel x6 is so much slower than one process when the disks aren't being pushed that hard compared to their capabilities.

I am suspecting something wrong with my config, but I can't be sure. Is 1-2 GB for work_mem ok? Would that hurt it?

On a positive note, I let the single-process version run to completion and I now have a solid TB of data that I can access and use at lightning speed :)

Cheers,

  Phillip