Re: COPY v. java performance comparison

On 04/02/2014 02:36 PM, Adrian Klaver wrote:
> On 04/02/2014 01:14 PM, Rob Sargent wrote:
>> On 04/02/2014 01:56 PM, Steve Atkins wrote:
>>>
>>>> Impatience got the better of me and I killed the second COPY. This
>>>> time it had done 54% of the file in 6.75 hours, extrapolating to
>>>> roughly 12 hours to do the whole thing.
>>> That seems rather painfully slow. How exactly are you doing the bulk
>>> load? Are you CPU limited or disk limited?
>>>
>>> Have you
>>> readhttp://www.postgresql.org/docs/current/interactive/populate.html ?
>>>
>>> Cheers,
>>> Steve
>>>
>> The copy command was pretty vanilla:
>>
>> copy oldstyle from '/export/home/rob/share/testload/<file-redacted>'
>> with delimiter ' ';
>
>> I've been to that page, but (as I read them) none sticks out as a sure
>> thing. I'm not so worried about the actual performance as I am with the
>> relative throughput (sixes so far).
>
> Have you looked at the Postgres logs from that time period to see if
> there is anything of interest, say complaining about checkpoints.
>
>>
>> I'm not cpu bound, but I confess I didn't look at io stats during the
>> copy runs. I just assume it was pegged :)
>>
>> Thanks,
>
>
Well indeed there are copious LOG/HINT pairs along the lines of

LOG: checkpoints are occurring too frequently ([8 <= n <=29]
seconds apart)
HINT: Consider increasing the configuration parameter
"checkpoint_segments".

and these are during non-load periods. During the COPYs I see
uncountable numbers of

WARNING: pgstat wait timeout

As you guys have probably already concluded, I have some config/tuning
to do.

Cheers,
rjs