Re: [GENERAL] COPY v. java performance comparison

On 04/03/2014 01:28 PM, Jeff Janes wrote: On Thu, Apr 3, 2014 at 9:04 AM, Rob Sargent > wrote: 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 CO

Re: [GENERAL] COPY v. java performance comparison

On Thu, Apr 3, 2014 at 9:04 AM, Rob Sargent wrote: > 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 "WAR

Re: [GENERAL] COPY v. java performance comparison

On Wed, Apr 2, 2014 at 5:11 PM, Rob Sargent wrote: > On 04/02/2014 04:36 PM, Jeff Janes wrote: > > On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent 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

Re: [GENERAL] COPY v. java performance comparison

On Wed, Apr 2, 2014 at 3:46 PM, Rob Sargent wrote: > On 04/02/2014 04:36 PM, Jeff Janes wrote: > > Are you sure you actually dropped the indices? (And the primary key?) > > I get about 375,000 lines per second with no indexes, triggers, > constraints. > > perl -le 'my $x=""; fo

Re: [GENERAL] COPY v. java performance comparison

On 4/3/2014 12:57 PM, Andy Colson wrote: On 4/3/2014 12:15 PM, Rob Sargent wrote: Is the java app cpu bound? > Also watch "vmstat 3" for a minute or two. The last two numbers (wa > & id) (some vmstat's have a steal, ignore that) will tell you if you > are io bound. > > -Andy > > Durin

Re: [GENERAL] COPY v. java performance comparison

On 4/3/2014 12:15 PM, Rob Sargent wrote: Is the java app cpu bound? > Also watch "vmstat 3" for a minute or two. The last two numbers (wa > & id) (some vmstat's have a steal, ignore that) will tell you if you > are io bound. > > -Andy > > During COPY, with autovaccume off (server resta

Re: [GENERAL] COPY v. java performance comparison

Is the java app cpu bound? > Also watch "vmstat 3" for a minute or two. The last two numbers (wa > & id) (some vmstat's have a steal, ignore that) will tell you if you > are io bound. > > -Andy > > During COPY, with autovaccume off (server restarted, manual vacuum to get things going). Immed

Re: [GENERAL] COPY v. java performance comparison

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

Re: [GENERAL] COPY v. java performance comparison

On 4/2/2014 7:30 PM, Rob Sargent wrote: Well things slow down over time, and lots of "too frequent"s: Have done 500 batches in 24219 ms Have done 1000 batches in 52362 ms Have done 1500 batches in 82256 ms Have done 2000 batches in 113754 ms Have done 2500 batches in 14

Re: [GENERAL] COPY v. java performance comparison

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

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 08:40 PM, Adrian Klaver wrote: On 04/02/2014 05:30 PM, Rob Sargent wrote: On 04/02/2014 06:06 PM, Adrian Klaver wrote: On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adri

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 05:30 PM, Rob Sargent wrote: On 04/02/2014 06:06 PM, Adrian Klaver wrote: On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: On 04/02/2014 02:36 PM, Adrian Klaver wrote: On 04/02/2014 01:14 PM, Rob

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 06:06 PM, Adrian Klaver wrote: On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: 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, Stev

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 04:36 PM, Jeff Janes wrote: On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent > 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 r

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 02:27 PM, Rob Sargent wrote: On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: 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: Have you tried moving the

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 04:36 PM, Jeff Janes wrote: On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent > 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 r

Re: [GENERAL] COPY v. java performance comparison

On Wed, Apr 2, 2014 at 12:37 PM, Rob Sargent 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

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 03:11 PM, Adrian Klaver wrote: On 04/02/2014 02:04 PM, Rob Sargent wrote: 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: Well indeed there are copious LOG/HINT pairs along the lines of LO

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 02:04 PM, Rob Sargent wrote: 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: Well indeed there are copious LOG/HINT pairs along the lines of LOG: checkpoints are occurring too frequently (

Re: [GENERAL] 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

Re: [GENERAL] COPY v. java performance comparison

On Apr 2, 2014, at 1: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 >> 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

Re: [GENERAL] COPY v. java performance comparison

Just for a comparison ... I wrote a Java program that copies data from MySQL -> Postgres, using the Copy impelmentation in the JDBC driver. I've occasionally seen 50,000+ rows/sec from this program, but the speed is highly dependent on the table structure. Tables that are very wide tend to run

Re: [GENERAL] 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: On Apr 2, 2014, at 12:37 PM, Rob Sargent 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

Re: [GENERAL] COPY v. java performance comparison

On 04/02/2014 01:56 PM, Steve Atkins wrote: On Apr 2, 2014, at 12:37 PM, Rob Sargent 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 c

Re: [GENERAL] COPY v. java performance comparison

On Apr 2, 2014, at 12:37 PM, Rob Sargent 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 i

[GENERAL] COPY v. java performance comparison

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"