Thread: Speeding up operations

Speeding up operations

From
"Rahul_Iyer"
Date:
hi...
im on a project using Postgres. The project involves, at times, upto
5,000,000 inserts. I was checking the performance of Postgres for 5M inserts
into a 2 column table (one col=integer, 2nd col=character). I used the
Prepare... and execute method, so i basically had 5M execute statements and
1 prepare statement. Postgres took 144min for this... is there any way to
improve this performance? if so, how? btw, im using it on a SPARC/Solaris
2.6.
thanx in adv
rahul

P.S: Kindly point me towards any relevant documentation as well.



Re: Speeding up operations

From
Rod Taylor
Date:
> im on a project using Postgres. The project involves, at times, upto
> 5,000,000 inserts. I was checking the performance of Postgres for 5M inserts
> into a 2 column table (one col=integer, 2nd col=character). I used the
> Prepare... and execute method, so i basically had 5M execute statements and
> 1 prepare statement. Postgres took 144min for this... is there any way to
> improve this performance? if so, how? btw, im using it on a SPARC/Solaris
> 2.6.

7.4 beta2 will help -- when it comes out.

The fastest method will still be COPY.

Re: Speeding up operations

From
Frank Wiles
Date:
On Wed, 13 Aug 2003 10:53:39 +0530
"Rahul_Iyer" <rahul_iyer@persistent.co.in> wrote:

> hi...
> im on a project using Postgres. The project involves, at times, upto
> 5,000,000 inserts. I was checking the performance of Postgres for 5M
> inserts into a 2 column table (one col=integer, 2nd col=character). I
> used the Prepare... and execute method, so i basically had 5M execute
> statements and 1 prepare statement. Postgres took 144min for this...
> is there any way to improve this performance? if so, how? btw, im
> using it on a SPARC/Solaris 2.6.
> thanx in adv
> rahul
> 
> P.S: Kindly point me towards any relevant documentation as well.
 If this is a one time insert you'll want to remove any indexes and rebuild them after the inserts are done.  
 Also you'll want to look into the COPY command here: 
 http://www.postgresql.org/docs/7.3/static/sql-copy.html
 Loading the data from file like this is probably going to be much faster than from a script and/or program. 
---------------------------------  Frank Wiles <frank@wiles.org>
http://frank.wiles.org---------------------------------



Re: Speeding up operations

From
Tom Lane
Date:
"Rahul_Iyer" <rahul_iyer@persistent.co.in> writes:
> im on a project using Postgres. The project involves, at times, upto
> 5,000,000 inserts. I was checking the performance of Postgres for 5M inserts
> into a 2 column table (one col=integer, 2nd col=character). I used the
> Prepare... and execute method, so i basically had 5M execute statements and
> 1 prepare statement. Postgres took 144min for this... is there any way to
> improve this performance?

COPY, perhaps.  Have you read
http://www.postgresql.org/docs/view.php?version=7.3&idoc=0&file=populate.html
        regards, tom lane


Re: Speeding up operations

From
Hannu Krosing
Date:
Rahul_Iyer kirjutas K, 13.08.2003 kell 08:23:
> hi...
> im on a project using Postgres. The project involves, at times, upto
> 5,000,000 inserts. I was checking the performance of Postgres for 5M inserts
> into a 2 column table (one col=integer, 2nd col=character). I used the
> Prepare... and execute method, so i basically had 5M execute statements and
> 1 prepare statement. Postgres took 144min for this... is there any way to
> improve this performance? if so, how? btw, im using it on a SPARC/Solaris
> 2.6.

If you are inserting into an empty table with primary key (or other
constraints), you can run ANALYZE on that table in 1-2 minutes after you
have started the INSERTs, so that constraint-checking logic will do the
right thing (use inedex for pk).

in my tests I achieved about 9000 inserts/sec by using multiple
inserting frontends and ~100 inserts per transaction (no indexes, 6
columns, 4 processors, 2GB memory, test clients running on same
computer)

--------------
Hannu



Re: Speeding up operations

From
"Matthew T. O'Connor"
Date:
----- Original Message ----- 
From: "Hannu Krosing" <hannu@tm.ee>
> If you are inserting into an empty table with primary key (or other
> constraints), you can run ANALYZE on that table in 1-2 minutes after you
> have started the INSERTs, so that constraint-checking logic will do the
> right thing (use inedex for pk).

This is one of the things that pg_autovaccum attempts to do.  If you are
doing a lot of inserts into a table, it will perform periodic analyze
commands.  The threshold for performing analyze is much lower than the
threshold for vacuum.  Also, inserts don't effect the count towards the
vacuum threshold, only the analyze threshold.