pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Grupos <grupos@carvalhaes.net>
To: pgsql-performance@postgresql.org
Subject: Improve BULK insertion
Date: Sat, 04 Dec 2004 11:39:39 -0200
Message-ID: <41B1BE1B.10301@carvalhaes.net> (raw)

Hi !

I need to insert 500.000 records on a table frequently. It´s a bulk 
insertion from my applicatoin.
I am with a very poor performance. PostgreSQL insert very fast until the 
tuple 200.000 and after it the insertion starts to be really slow.
I am seeing on the log and there is a lot of transaction logs, something 
like :

2004-12-04 11:08:59 LOG:  recycled transaction log file "0000000600000012"
2004-12-04 11:08:59 LOG:  recycled transaction log file "0000000600000013"
2004-12-04 11:08:59 LOG:  recycled transaction log file "0000000600000011"
2004-12-04 11:14:04 LOG:  recycled transaction log file "0000000600000015"
2004-12-04 11:14:04 LOG:  recycled transaction log file "0000000600000014"
2004-12-04 11:19:08 LOG:  recycled transaction log file "0000000600000016"
2004-12-04 11:19:08 LOG:  recycled transaction log file "0000000600000017"
2004-12-04 11:24:10 LOG:  recycled transaction log file "0000000600000018"

How can I configure PostgreSQL to have a better performance on this bulk 
insertions ? I already increased the memory values.

My data:
Conectiva linux kernel 2.6.9
PostgreSQL 7.4.6 - 1,5gb memory
max_connections = 30
shared_buffers = 30000
sort_mem = 32768
vacuum_mem = 32768
max_fsm_pages = 30000
max_fsm_relations = 1500

The other configurations are default.


Cheers,

Rodrigo Carvalhaes  





view thread (7+ messages)  latest in thread

Message-ID: <41B1BE1B.10301@carvalhaes.net>
Permalink:  ../41B1BE1B.10301@carvalhaes.net/
Also on:    postgresql.org/message-id/41B1BE1B.10301@carvalhaes.net

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-performance@postgresql.org
  Cc: grupos@carvalhaes.net
  Subject: Re: Improve BULK insertion
  In-Reply-To: <41B1BE1B.10301@carvalhaes.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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