pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Shridhar Daithankar <shridhar_daithankar@persistent.co.in>
To: pgsql-performance@postgresql.org
Subject: Re: Batch copying of databases
Date: Thu, 06 Mar 2003 12:23:42 +0530
Message-ID: <3E673DCE.24575.352CED2@localhost> (raw)
In-Reply-To: <LDEBIOHBEOBPKBBEPDFFKEBGCBAA.tamohler@nyc.rr.com>

On 5 Mar 2003 at 15:26, Tim Mohler wrote:

> Hi all,
> 
> I'm new to Postgres, and am not even the DBA for the system. I'm just a
> sysadmin trying to make things run faster. Every month, we copy over a 25
> million row table from the production server to the reporting server. Total
> size is something like 40 gigabytes.
> 
> The copy in takes close to 24 hours, and I see the disks being hammered by
> hundreds of small writes every second. The system is mostly waiting on I/O.
> Is there any facility in Postgres to force batching of the I/O transactions
> to something more reasonable than 8K?

Well, 8K has nothing to with transactions in postgresql.

You need to make sure at least two things.

1. You are using copy. By default postgresql writes each inserts in it's own 
transaction which is seriously slow for bulk load. Copy bunches the rwos in a 
single transaction and is quite fast.

if you need to preprocess the data, batch something like 1K-10K records in a 
single transaction.

2. Postgresql bulk load is not as fast as many of us would like, especially 
when compared to oracle. So if you know you are going to bulk load using say 
copy, don't load the data from a single connection. Split the data file in say 
5-10 parts and start loading all of them simaltaneously. It does speed up the 
things. At least it certainly saturates the disk bandwidth which single load 
does not do many times.

On a side note, for such a bulk load consider dropping any indexes and foreign 
key contraints. 

HTH


Bye
 Shridhar

--
Turnaucka's Law:	The attention span of a computer is only as long as its	
electrical cord.




view thread (3+ messages)

Message-ID: <3E673DCE.24575.352CED2@localhost>
Permalink:  ../3E673DCE.24575.352CED2@localhost/
Also on:    postgresql.org/message-id/3E673DCE.24575.352CED2@localhost

 · 

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 from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-performance@postgresql.org
  Cc: shridhar_daithankar@persistent.co.in
  Subject: Re: Batch copying of databases
  In-Reply-To: <3E673DCE.24575.352CED2@localhost>
  References: <LDEBIOHBEOBPKBBEPDFFKEBGCBAA.tamohler@nyc.rr.com> <3E673DCE.24575.352CED2@localhost>

* 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