pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: george young <gry@ll.mit.edu>
To: pgsql-performance@postgresql.org
Subject: Re: preliminary testing, two very slow situations...
Date: Thu, 2 Jan 2003 10:57:27 -0500
Message-ID: <20030102105727.4fa0bf7e.gry@ll.mit.edu> (raw)

On Tue, 31 Dec 2002 14:14:34 -0800 (PST)
Michael Teter <mt_pgsql@yahoo.com> wrote:
> I've used PostgreSQL in the past on a small project,
> and I thought it was great.
> 
> Now I'm trying to evaluate it as a possible
> replacement for MS SQL Server.
> 
> I have two issues:
> 
> 1. I have a homegrown Java migration tool I wrote that
> seems to work reasonably well, but I'm hoping to
> understand how to improve its performance.
> 
> 2. After migrating, I found pg_dump to be plenty
> quick, but psql < (to completely reload the database)
> to be very very slow during the COPY stage.

I've found that "psql -f myfile mydb" is Much faster than
"psql mydb <myfile".  I'm not too sure why, but it's worth
a try.  

> Now for more detail.  On problem 1., I have autocommit
> off, and I'm doing PreparedStatement.addBatch() and
> executeBatch(), and eventually, commit.
> 
> I've been playing with the amount of rows I do before
> executeBatch(), and I seem to do best with 20,000 to
> 50,000 rows in a batch.  Some background: this is
> RedHat8.0 with all the latest RedHat patches, 1GB
> RAMBUS RAM, 2GHz P4, 40GB 7200RPM HD.  Watching
> gkrellm and top, I see a good bit of CPU use by
> postmaster duing the addBatch()es, but then when
> executeBatch() comes, CPU goes almost totally idle,
> and disk starts churning.  Somehow it seems the disk
> isn't being utilized to the fullest, but I'm just
> guessing.
> 
> I'm wondering if there's some postmaster tuning I
> might do to improve this.
> 
> Then on problem 2., a pg_dump of the database takes
> about 3 minutes, and creates a file of 192MB in size. 
> Then I create testdb and do psql -e testdb
> <thedump.sql, and it creeps once it gets to the COPY
> section.  So far it's been running for 45 minutes,
> mostly on one table (the biggest table, which has
> 1,090,000 rows or so).  During this time, CPU use is
> very low, and there's no net or lo traffic.
> 
> In contrast, using MSSQL's backup and restore
> facilities, it takes about 15 second on a previous
> generation box (with SCSI though) to backup, and 45
> seconds to a minute to restore.
> 
> Suggestions?
> 
> Thanks,
> MT
> 
> __________________________________________________
> Do you Yahoo!?
> Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
> http://mailplus.yahoo.com
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
> 


-- 
 I cannot think why the whole bed of the ocean is
 not one solid mass of oysters, so prolific they seem. Ah,
 I am wandering! Strange how the brain controls the brain!
	-- Sherlock Holmes in "The Dying Detective"


-- 
 I cannot think why the whole bed of the ocean is
 not one solid mass of oysters, so prolific they seem. Ah,
 I am wandering! Strange how the brain controls the brain!
	-- Sherlock Holmes in "The Dying Detective"



view thread (2+ messages)

Message-ID: <20030102105727.4fa0bf7e.gry@ll.mit.edu>
Permalink:  ../20030102105727.4fa0bf7e.gry@ll.mit.edu/
Also on:    postgresql.org/message-id/20030102105727.4fa0bf7e.gry@ll.mit.edu

 · 

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: gry@ll.mit.edu
  Subject: Re: preliminary testing, two very slow situations...
  In-Reply-To: <20030102105727.4fa0bf7e.gry@ll.mit.edu>
  References: <20030102105727.4fa0bf7e.gry@ll.mit.edu>

* 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