pg.ddx.io  pgsql-performance@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Re: preliminary testing, two very slow situations...
2+ messages / 2 participants
[nested] [flat]

* Re: preliminary testing, two very slow situations...
@ 2003-01-01 21:01 Tom Lane <tgl@sss.pgh.pa.us>
  0 siblings, 0 replies; 2+ messages in thread

From: Tom Lane @ 2003-01-01 21:01 UTC (permalink / raw)
  To: Neil Conway <neilc@samurai.com>; +Cc: mt_pgsql@yahoo.com; pgsql-performance

"Neil Conway" <neilc@samurai.com> writes:
> Michael Teter said:
>> Now I'm trying to evaluate it as a possible
>> replacement for MS SQL Server.

> What version of PostgreSQL are you using?
> [suggestions for tuning]

The only reason I can think of for COPY to be as slow as Michael is
describing is if it's checking foreign-key constraints (and even then
it'd have to be using very inefficient plans for the check queries).
So we should ask not only about the PG version, but also about the
exact table declarations involved.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 2+ messages in thread

* Re: preliminary testing, two very slow situations...
@ 2003-01-02 15:57 george young <gry@ll.mit.edu>
  0 siblings, 0 replies; 2+ messages in thread

From: george young @ 2003-01-02 15:57 UTC (permalink / raw)
  To: pgsql-performance

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"



^ permalink  raw  reply  [nested|flat] 2+ messages in thread


end of thread, other threads:[~2003-01-02 15:57 UTC | newest]

Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-01-01 21:01 Re: preliminary testing, two very slow situations... Tom Lane <tgl@sss.pgh.pa.us>
2003-01-02 15:57 Re: preliminary testing, two very slow situations... george young <gry@ll.mit.edu>

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