On 04/02/2014 01:14 PM, Rob Sargent wrote:
On 04/02/2014 01:56 PM, Steve Atkins wrote:
On Apr 2, 2014, at 12:37 PM, Rob Sargent<robjsargent@xxxxxxxxx> wrote:
I'm playing with various data models to compare performance and practicalities and not sure if I should be surprised by the numbers I'm getting. I hope this report isn't too wishy-washy for reasoned comment.
One model says a genotype is defined as follows:
Table "public.oldstyle"
+-------------+--------------+-----------+
| Column | Type | Modifiers |
+-------------+--------------+-----------+
| id | uuid | not null |
| sample_name | text | not null |
| marker_name | text | not null |
| allele1 | character(1) | |
| allele2 | character(1) | |
+-------------+--------------+-----------+
(0. id is a Primary Key)
(1. Take what you will from the table name.)
(2. I hadn't thought of "char" type at this point)
(3. Ultimately the names would become ids, RI included)
(4. We're loading 39 samples and ~950K markers)
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 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,
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
