preliminary testing, two very slow situations...

Howdy.

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.

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