Обсуждение: slow speeds after 2 million rows inserted

Greetings,

Ive got a java application I am reading data from a flat file and
inserting it into a table.  The first 2 million rows (each file
contained about 1 million lines) went pretty fast.  Less than 40 mins to
insert into the database.

After that the insert speed is slow.  I think I may be able to type the
data faster than what is being done by the java application on the third
file.

Table looks like this:

CREATE TABLE data_archive
(
  id serial NOT NULL,
  batchid integer NOT NULL,
  claimid character varying(25) NOT NULL,
  memberid character varying(45) NOT NULL,
  raw_data text NOT NULL,
  status integer DEFAULT 0,
  line_number integer,
  CONSTRAINT data_archive_pkey PRIMARY KEY (id)
)


there is also an index on batchid.

The insert command is like so:

"INSERT INTO data_archive (batchid, claimid, memberid, raw_data, status,
line_number) VALUES ('" + commandBatchID + "', '', '', '" + raw_data +
"', '1', '" + myFilter.claimLine + "');";

where the raw_data variable is the line from the file.

How can I find out what is causing this slow down and how do I speed it up?

Database is 8.2.0 on x86_64-unknown-linux-gnu.

There is nothing else running on this database server (other than
standard linux background programs).  PS ax did not show anything else
running.  No locks other than the occasional lock by the INSERT query.

I have done a FULL vacuum on this table but not reindex (running now).

Thanks in advance,
James

On Fri, 2006-12-29 at 12:39 -0500, James Neff wrote:
> Greetings,
>
> Ive got a java application I am reading data from a flat file and
> inserting it into a table.  The first 2 million rows (each file
> contained about 1 million lines) went pretty fast.  Less than 40 mins to
> insert into the database.
>

You need to vacuum during the inserts :)

Joshua D. Drake



> After that the insert speed is slow.  I think I may be able to type the
> data faster than what is being done by the java application on the third
> file.
>
> Table looks like this:
>
> CREATE TABLE data_archive
> (
>   id serial NOT NULL,
>   batchid integer NOT NULL,
>   claimid character varying(25) NOT NULL,
>   memberid character varying(45) NOT NULL,
>   raw_data text NOT NULL,
>   status integer DEFAULT 0,
>   line_number integer,
>   CONSTRAINT data_archive_pkey PRIMARY KEY (id)
> )
>
>
> there is also an index on batchid.
>
> The insert command is like so:
>
> "INSERT INTO data_archive (batchid, claimid, memberid, raw_data, status,
> line_number) VALUES ('" + commandBatchID + "', '', '', '" + raw_data +
> "', '1', '" + myFilter.claimLine + "');";
>
> where the raw_data variable is the line from the file.
>
> How can I find out what is causing this slow down and how do I speed it up?
>
> Database is 8.2.0 on x86_64-unknown-linux-gnu.
>
> There is nothing else running on this database server (other than
> standard linux background programs).  PS ax did not show anything else
> running.  No locks other than the occasional lock by the INSERT query.
>
> I have done a FULL vacuum on this table but not reindex (running now).
>
> Thanks in advance,
> James
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

James Neff wrote:
> Greetings,
>
> Ive got a java application I am reading data from a flat file and
> inserting it into a table.  The first 2 million rows (each file
> contained about 1 million lines) went pretty fast.  Less than 40 mins to
> insert into the database.
>
> After that the insert speed is slow.  I think I may be able to type the
> data faster than what is being done by the java application on the third
> file.
>
> Table looks like this:
>
> CREATE TABLE data_archive
> (
>  id serial NOT NULL,
>  batchid integer NOT NULL,
>  claimid character varying(25) NOT NULL,
>  memberid character varying(45) NOT NULL,
>  raw_data text NOT NULL,
>  status integer DEFAULT 0,
>  line_number integer,
>  CONSTRAINT data_archive_pkey PRIMARY KEY (id)
> )
>
>
> there is also an index on batchid.
>
> The insert command is like so:
>
> "INSERT INTO data_archive (batchid, claimid, memberid, raw_data, status,
> line_number) VALUES ('" + commandBatchID + "', '', '', '" + raw_data +
> "', '1', '" + myFilter.claimLine + "');";
>
> where the raw_data variable is the line from the file.
>
> How can I find out what is causing this slow down and how do I speed it up?
>
> Database is 8.2.0 on x86_64-unknown-linux-gnu.
>
> There is nothing else running on this database server (other than
> standard linux background programs).  PS ax did not show anything else
> running.  No locks other than the occasional lock by the INSERT query.
> I have done a FULL vacuum on this table but not reindex (running now).
>
> Thanks in advance,
> James
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: Don't 'kill -9' the postmaster
>

Consider using copy

> > there is also an index on batchid.
> >
> > The insert command is like so:
> >
> > "INSERT INTO data_archive (batchid, claimid, memberid, raw_data, status,
> > line_number) VALUES ('" + commandBatchID + "', '', '', '" + raw_data +
> > "', '1', '" + myFilter.claimLine + "');";


Also as you are running 8.2 you can use multi valued inserts...

INSERT INTO data_archive values () () ()

> >
> > where the raw_data variable is the line from the file.
> >
> > How can I find out what is causing this slow down and how do I speed it up?
> >
> > Database is 8.2.0 on x86_64-unknown-linux-gnu.
> >
> > There is nothing else running on this database server (other than
> > standard linux background programs).  PS ax did not show anything else
> > running.  No locks other than the occasional lock by the INSERT query.
> > I have done a FULL vacuum on this table but not reindex (running now).
> >
> > Thanks in advance,
> > James
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: Don't 'kill -9' the postmaster
> >
>
> Consider using copy
>
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:
> Also as you are running 8.2 you can use multi valued inserts...
>
> INSERT INTO data_archive values () () ()
>

Would this speed things up? Or is that just another way to do it?

Thanks,
James

When do you commit these inserts? I occasionally found similiar problems, when I do heavy inserting/updating within one
singletransaction. First all runs fast, after some time everything slows down. If I commit the inserts every some 1000
rows(large rows, small engine), this phenomenon does not occur. Maybe some buffer chokes if the transaction ist too
big.

In your case I'd recommend to commit after every one or two million rows (if possible).

Regards, Frank.


On Fri, 29 Dec 2006 12:39:03 -0500 James Neff <jneff@tethyshealth.com> thought long, then sat down and wrote:

> Greetings,
> 
> Ive got a java application I am reading data from a flat file and 
> inserting it into a table.  The first 2 million rows (each file 
> contained about 1 million lines) went pretty fast.  Less than 40 mins to 
> insert into the database.
> 
> After that the insert speed is slow.  I think I may be able to type the 
> data faster than what is being done by the java application on the third 
> file.

-- 
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606    Mail: frank.finner@invenius.de
Telefax: 0271 231 8608    Web:  http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF  6E6A A74E 67E4 E788 2651

Joshua D. Drake wrote:
> You need to vacuum during the inserts :)
>
> Joshua D. Drake
>

I ran the vacuum during the INSERT and it seemed to help a little, but
its still relatively slow compared to the first 2 million records.

Any other ideas?

Thanks,
James

On Fri, 2006-12-29 at 13:21 -0500, James Neff wrote:
> Joshua D. Drake wrote:
> > Also as you are running 8.2 you can use multi valued inserts...
> >
> > INSERT INTO data_archive values () () ()
> >
>
> Would this speed things up? Or is that just another way to do it?

The fastest way will be copy.
The second fastest will be multi value inserts in batches.. eg.;

INSERT INTO data_archive values () () () (I don't knwo what the max is)

but commit every 1000 inserts or so.

Sincerely,

Joshua D. Drake


>
> Thanks,
> James
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: explain analyze is your friend
>
--

      === The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive  PostgreSQL solutions since 1997
             http://www.commandprompt.com/

Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate

Joshua D. Drake wrote:
> On Fri, 2006-12-29 at 13:21 -0500, James Neff wrote:
>> Joshua D. Drake wrote:
>>> Also as you are running 8.2 you can use multi valued inserts...
>>>
>>> INSERT INTO data_archive values () () ()
>>>
>> Would this speed things up? Or is that just another way to do it?
>
> The fastest way will be copy.
> The second fastest will be multi value inserts in batches.. eg.;
>
> INSERT INTO data_archive values () () () (I don't knwo what the max is)
>
> but commit every 1000 inserts or so.
>
> Sincerely,
>
> Joshua D. Drake
>
>
>> Thanks,
>> James
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 6: explain analyze is your friend
>>

Another thing....if you have to make validations and so on....creating
an temp file with the information validated and using COPY will be
faster. So you can validate and use COPY at the same time....

In Java, assuming you have a Connection c, you simply say "c.commit();" after doing some action on the database. After
everycommit, the transaction will be executed and closed and a new one opened, which runs until the next commit.
 

Regards, Frank.


On Fri, 29 Dec 2006 13:23:37 -0500 James Neff <jneff@tethyshealth.com> thought long, then sat down and wrote:

> I'm sorry to ask a stupid question, but how do I 'commit' the transactions?
> 
> Thanks,
> James
> 
> 
> 
> Frank Finner wrote:
> > When do you commit these inserts? I occasionally found similiar problems, when I do heavy inserting/updating within
onesingle transaction. First all runs fast, after some time everything slows down. If I commit the inserts every some
1000rows (large rows, small engine), this phenomenon does not occur. Maybe some buffer chokes if the transaction ist
toobig.
 
> >
> > In your case I'd recommend to commit after every one or two million rows (if possible).
> >
> > Regards, Frank.
> >
> >
> > On Fri, 29 Dec 2006 12:39:03 -0500 James Neff <jneff@tethyshealth.com> thought long, then sat down and wrote:
> >
> >   
> >> Greetings,
> >>
> >> Ive got a java application I am reading data from a flat file and 
> >> inserting it into a table.  The first 2 million rows (each file 
> >> contained about 1 million lines) went pretty fast.  Less than 40 mins to 
> >> insert into the database.
> >>
> >> After that the insert speed is slow.  I think I may be able to type the 
> >> data faster than what is being done by the java application on the third 
> >> file.
> >>     
> >
> >   
> 
> 
> -- 
> 
> James Neff
> Technology Specialist
> 
> Tethys Health Ventures
> 4 North Park Drive, Suite 203
> Hunt Valley, MD  21030
> 
> office:  410.771.0692 x103
> cell:    443.865.7874
> 
> 


-- 
Frank Finner

Invenius - Lösungen mit Linux
Köpfchenstraße 36
57072 Siegen
Telefon: 0271 231 8606    Mail: frank.finner@invenius.de
Telefax: 0271 231 8608    Web:  http://www.invenius.de
Key fingerprint = 90DF FF40 582E 6D6B BADF  6E6A A74E 67E4 E788 2651

Frank Finner wrote:
> In Java, assuming you have a Connection c, you simply say "c.commit();" after doing some action on the database.
Afterevery commit, the transaction will be executed and closed and a new one opened, which runs until the next commit. 
>
> Regards, Frank.
>
>

That did it, thank you!


--James

> The fastest way will be copy.
> The second fastest will be multi value inserts in batches.. eg.;
>
> INSERT INTO data_archive values () () () (I don't knwo what the max is)
>
> but commit every 1000 inserts or so.

Is this some empirical value? Can someone give heuristics as to how to calculate the optimal number of transactions
afterwhich to commit? Or at least guidelines. 

Nix.




__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

Frank Finner wrote:
> In Java, assuming you have a Connection c, you simply say
> "c.commit();" after doing some action on the database. After every
> commit, the transaction will be executed and closed and a new one
> opened, which runs until the next commit.

Assuming, of course, you started with c.setAutoCommit(false);

--
Guy Rouillier

> It was really some time ago, since I have experimented with this. My las experiment was on PG
> 7.2 or 7.3. I was inserting cca 800,000 rows. Inserting without transactions took 25 hrs.
> Inserting with 10,000 rows per transaction took about 2.5 hrs. So, the speedup was 10x. I have
> not experimented with the transaction batch size, but I suspect that 1,000 would not show much
> speedup.
>
> > 2. Vacuuming also makes no difference for a heavy insert-only table, only slows it down.
>
> Makes sense. Since my application was dumping all records each month and inserting new ones,
> vacuum was really needed, but no speedup.
>
> > 3. Table size plays no real factor.

Maybe this link my be useful,  it contains additional links to various postgresql preformance
test.
http://archives.postgresql.org/pgsql-general/2006-10/msg00662.php

Regards,

Richard Broersma Jr.