Re: Best COPY Performance

Worky,

On 10/27/06 8:47 PM, "Worky Workerson" <worky(dot)workerson(at)gmail(dot)com> wrote:

>>>> 1 0 345732 29304 770272 12946764 0 0 16 16428 1192 3105 12 2 85 1
>>>> 1 0 345732 30840 770060 12945480 0 0 20 16456 1196 3151 12 2 84 1
>>>> 1 0 345732 32760 769972 12943528 0 0 12 16460 1185 3103 11 2 86 1
>>
>> iirc, he is running quad opteron 885 (8 cores), so if my math is
>> correct he can split up his process for an easy gain.
>
> Are you saying that I should be able to issue multiple COPY commands
> because my I/O wait is low? I was under the impression that I am I/O
> bound, so multiple simeoultaneous loads would have a detrimental
> effect ...

The reason I asked how many CPUs was to make sense of the 12% usr CPU time
in the above. That means you are CPU bound and are fully using one CPU. So
you aren't being limited by the I/O in this case, it's the CPU.

I agree with Merlin that you can speed things up by breaking the file up.
Alternately you can use the OSS Bizgres java loader, which lets you specify
the number of I/O threads with the "-n" option on a single file.

OTOH, you should find that you will only double your COPY speed with this
approach because your write speed as you previously found was limited to 30
MB/s.

For now, you could simply split the file in two pieces and load two copies
at once, then watch the same "vmstat 1" for 10 seconds and look at your "bo"
rate.

If this does speed things up, you really should check out the Bizgres Java
loader.

The other thing to wonder about though is why you are so CPU bound at 5
MB/s. What version of Postgres is this?

- Luke