pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
INSERT extremely slow with large data sets (fwd)
6+ messages / 4 participants
[nested] [flat]

* INSERT extremely slow with large data sets (fwd)
@ 2003-11-14 09:38  Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
  0 siblings, 1 reply; 6+ messages in thread

From: Slavisa Garic @ 2003-11-14 09:38 UTC (permalink / raw)
  To: pgsql-performance

Hi Everyone,
 
I am using PostgreSQL 7.3.2 and have used earlier versions (7.1.x onwards) 
and with all of them I noticed same problem with INSERTs when there is a
large data set. Just to so you guys can compare time it takes to insert
one row into a table when there are only few rows present and when there
are thousands:

Rows Present		Start Time		Finish Time
------------------------------------------------------------
100			1068790804.12       	1068790804.12
1000			1068790807.87	  	1068790807.87
5000			1068790839.26		1068790839.27
10000			1068790909.24  		1068790909.26
20000			1068791172.82		1068791172.85
30000			1068791664.06		1068791664.09 
40000			1068792369.94		1068792370.0
50000			1068793317.53		1068793317.6
60000			1068794369.38		1068794369.47

As you can see if takes awfully lots of time for me just to have those
values inserted. Now to make a picture a bit clearer for you this table 
has lots of information in there, about 25 columns. Also there are few
indexes that I created so that the process of selecting values from there
is faster which by the way works fine. Selecting anything takes under 5
seconds.

Any help would be greatly appreciated even pointing me in the right
direction where to ask this question. By the way I designed the database
this way as my application that uses PGSQL a lot during the execution so
there was a huge need for fast SELECTs. Our experiments are getting larger
and larger every day so fast inserts would be good as well.

Just to note those times above are of INSERTs only. Nothing else done that
would be included in those times. Machine was also free and that was the
only process running all the time and the machine was Intel(R) Pentium(R)
4 CPU 2.40GHz.

Regards,
Slavisa







^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: INSERT extremely slow with large data sets (fwd)
@ 2003-11-14 13:37  Jeff <threshar@torgo.978.org>
  parent: Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
  0 siblings, 0 replies; 6+ messages in thread

From: Jeff @ 2003-11-14 13:37 UTC (permalink / raw)
  To: Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>; +Cc: pgsql-performance

On Fri, 14 Nov 2003 20:38:33 +1100 (EST)
Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au> wrote:

> Any help would be greatly appreciated even pointing me in the right
> direction where to ask this question. By the way I designed the
> database this way as my application that uses PGSQL a lot during the
> execution so there was a huge need for fast SELECTs. Our experiments
> are getting larger and larger every day so fast inserts would be good
> as well.
> 

First, you need to upgrade to 7.3.4, 7.4 is prefable if a dump/reload is
not too bad.

Standard set of questions:

1. Any foreign keys
2. are these inserts batched into transactions
3. CPU usage?
4. OS?
5. PG config? [shared_buffers, effective_cache_size, etc]
6. IO saturation?

Also, try searching the archives. lots of juicy info there too.

-- 
Jeff Trout <jeff@jefftrout.com>
http://www.jefftrout.com/
http://www.stuarthamm.net/



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: INSERT extremely slow with large data sets (fwd)
@ 2003-11-14 22:02  George Essig <george_essig@yahoo.com>
  0 siblings, 1 reply; 6+ messages in thread

From: George Essig @ 2003-11-14 22:02 UTC (permalink / raw)
  To: Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>; pgsql-performance

Slavisa Garic wrote:

> Hi Everyone,
 
> I am using PostgreSQL 7.3.2 and have used earlier versions (7.1.x 
> onwards) 
> and with all of them I noticed same problem with INSERTs when there is 
> a
> large data set. Just to so you guys can compare time it takes to insert
> one row into a table when there are only few rows present and when 
> there
> are thousands:

Try running VACUUM ANALYZE periodically during inserts.  I found this to help.

George Essig




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: INSERT extremely slow with large data sets (fwd)
@ 2003-11-15 01:20  Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
  parent: George Essig <george_essig@yahoo.com>
  0 siblings, 1 reply; 6+ messages in thread

From: Slavisa Garic @ 2003-11-15 01:20 UTC (permalink / raw)
  To: George Essig <george_essig@yahoo.com>; +Cc: Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>; pgsql-performance

Does VACUUM ANALYZE help with the analysis or it also speeds up the
process. I know i could try that before I ask but experiment is running
now and I am too curious to wait :),

Anyway thanks for the hint,
Slavisa

On Fri, 14 Nov 2003, George Essig wrote:

> Slavisa Garic wrote:
> 
> > Hi Everyone,
>  
> > I am using PostgreSQL 7.3.2 and have used earlier versions (7.1.x 
> > onwards) 
> > and with all of them I noticed same problem with INSERTs when there is 
> > a
> > large data set. Just to so you guys can compare time it takes to insert
> > one row into a table when there are only few rows present and when 
> > there
> > are thousands:
> 
> Try running VACUUM ANALYZE periodically during inserts.  I found this to help.
> 
> George Essig
> 
> 




^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: INSERT extremely slow with large data sets (fwd)
@ 2003-11-15 13:13  George Essig <george_essig@yahoo.com>
  parent: Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
  0 siblings, 1 reply; 6+ messages in thread

From: George Essig @ 2003-11-15 13:13 UTC (permalink / raw)
  To: Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>; +Cc: Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>; pgsql-performance


--- Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au> wrote:
> Does VACUUM ANALYZE help with the analysis or it also speeds up the
> process. I know i could try that before I ask but experiment is running
> now and I am too curious to wait :),
> 
> Anyway thanks for the hint,
> Slavisa
> 

VACUUM ANALYZE will reclaim disk space and update statistics used by the optimizer to help execute
queries faster.  This could speed up your inserts.  See
http://www.postgresql.org/docs/7.3/static/sql-vacuum.html.

George Essig



^ permalink  raw  reply  [nested|flat] 6+ messages in thread

* Re: INSERT extremely slow with large data sets (fwd)
@ 2003-11-17 14:42  Andrew Sullivan <andrew@libertyrms.info>
  parent: George Essig <george_essig@yahoo.com>
  0 siblings, 0 replies; 6+ messages in thread

From: Andrew Sullivan @ 2003-11-17 14:42 UTC (permalink / raw)
  To: pgsql-performance

On Sat, Nov 15, 2003 at 05:13:38AM -0800, George Essig wrote:
> 
> VACUUM ANALYZE will reclaim disk space and update statistics used

Strictly speaking, it does not reclaim disk space.  It merely marks
it as available, assuming you have enough room in your free space
map.  VACUUM FULL reclaims disk space, i.e. it compacts the data
files and returns that space to the operating system.

A
-- 
----
Andrew Sullivan                         204-4141 Yonge Street
Afilias Canada                        Toronto, Ontario Canada
<andrew@libertyrms.info>                              M2P 2A8
                                         +1 416 646 3304 x110




^ permalink  raw  reply  [nested|flat] 6+ messages in thread


end of thread, other threads:[~2003-11-17 14:42 UTC | newest]

Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-11-14 09:38 INSERT extremely slow with large data sets (fwd) Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
2003-11-14 13:37 ` Jeff <threshar@torgo.978.org>
2003-11-14 22:02 Re: INSERT extremely slow with large data sets (fwd) George Essig <george_essig@yahoo.com>
2003-11-15 01:20 ` Slavisa Garic <Slavisa.Garic@infotech.monash.edu.au>
2003-11-15 13:13   ` George Essig <george_essig@yahoo.com>
2003-11-17 14:42     ` Andrew Sullivan <andrew@libertyrms.info>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox