Received: from localhost (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 715F54761C5 for ; Thu, 2 Jan 2003 09:57:16 -0500 (EST) Received: from ll.mit.edu (LLMAIL.LL.MIT.EDU [129.55.12.40]) by postgresql.org (Postfix) with ESMTP id ADF324760CE for ; Thu, 2 Jan 2003 09:57:15 -0500 (EST) Received: (from smtp@localhost) by ll.mit.edu (8.11.3/8.8.8) id h02EvFY20211 for ; Thu, 2 Jan 2003 09:57:15 -0500 (EST) Received: from sty.llan.ll.mit.edu( ), claiming to be "sty.llan" via SMTP by llpost, id smtpdAAAgEay9G; Thu Jan 2 09:56:57 2003 Date: Thu, 2 Jan 2003 10:57:27 -0500 From: george young To: pgsql-performance@postgresql.org Subject: Re: preliminary testing, two very slow situations... Message-Id: <20030102105727.4fa0bf7e.gry@ll.mit.edu> Reply-To: gry@ll.mit.edu Organization: MIT Lincoln Laboratory X-Mailer: Sylpheed version 0.8.5 (GTK+ 1.2.10; i686-pc-linux-gnu) Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: 7bit X-Virus-Scanned: by AMaViS new-20020517 X-Archive-Number: 200301/3 X-Sequence-Number: 650 On Tue, 31 Dec 2002 14:14:34 -0800 (PST) Michael Teter 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 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 > 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"