Re: preliminary testing, two very slow situations...

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"