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

preliminary testing, two very slow situations...

From
Michael Teter
Date:
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

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

From
"Neil Conway"
Date:
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



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

From
Tom Lane
Date:
"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

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

From
george young
Date:
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"