Re: COPY v. java performance comparison

[Rob Sargent <robjsargent@xxxxxxxxx>]

On 04/03/2014 09:01 AM, Thomas Kellerer wrote:

Rob Sargent, 02.04.2014 21:37:

I loaded 37M+ records using jOOQ (batching every 1000 lines) in 12+
hours (800+ records/sec).  Then I tried COPY and killed that after
11.25 hours when I realised that I had added on non-unque index on
the name fields after the first load. By that point is was on line
28301887, so ~0.75 done which implies it would have take ~15hours to
complete.

Would the overhead of the index likely explain this decrease in
throughput?

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 matches up with the java speed. Not sure if I should be elated
with jOOQ or disappointed with COPY.

This is not what I see with COPY FROM STDIN

When I load 2million rows using a batch size of 1000 with plain JDBC that takes about 4 minutes

Loading the same file through Java and COPY FROM STDIN takes about 4 seconds

The table looks like this:

                Table "public.products"
      Column       |          Type          | Modifiers
-------------------+------------------------+-----------
 product_id        | integer                | not null
 ean_code          | bigint                 | not null
 product_name      | character varying(100) | not null
 manufacturer_name | character varying      | not null
 price             | numeric(10,2)          | not null
 publish_date      | date                   | not null
Indexes:
    "products_pkey" PRIMARY KEY, btree (product_id)
    "idx_publish_date" btree (publish_date, product_id)


During the load both indexes are present.

Regards
Thomas
 

Thomas thanks for these numbers. 

I have to straighten out my environment, which I admit I was hoping to avoid. I reset checkpoint_segments to 12 and restarted my server.
I kicked of the COPY at 19:00. That generated a couple of the "too frequent" statements but 52 "WARNING:  pgstat wait timeout" lines during the next 8 hours starting at 00:37 (5 hours in) 'til finally keeling over at 03:04 on line 37363768.  That's the last line of the input so obviously I didn't flush my last println properly. I'm beyond getting embarrassed at this point.

Is turning auto-vacuum off a reasonable way through this?