Re: COPY v. java performance comparison

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