Thread: 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
Michael Teter said: > 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. [ ... ] What version of PostgreSQL are you using? Have you made any changes to the default configuration parameters? If not, that's probably the first thing to look at. Several settings (e.g. shared_buffers) are set to very conservative values by default. You can also consider trading some reliability for better performance by disabling fsync. For more info on configuration, see: http://www.ca.postgresql.org/users-lounge/docs/7.3/postgres/runtime-config.html Another low-hanging fruit is kernel configuration. For example, what OS and kernel are you using? Have you enabled DMA? What filesystem are you using? Cheers, Neil
"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
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"