PostgreSQL Performance - Re: Migration study, step 1: bulk write performance

Author

Re: Migration study, step 1: bulk write performance

Csaba Nagy

2006-03-21, 3:34 am

Mikael,

I've just recently passed such an experience, i.e. migrating from
another vendor to postgres of a DB about the same size category you
have.

I think you got it right with the fsync turned off during migration
(just don't forget to turn it back after finishing ;-), and using tables
without indexes/foreign keys. In our case recreating all the
indexes/foreign keys/other constraints took actually longer than the raw
data transfer itself... but it's possible that the process was not tuned
100%, we are still learning how to tune postgres...

What I can add from our experience: ext3 turned out lousy for our
application, and converting to XFS made a quite big improvement for our
DB load. I don't have hard figures, but I think it was some 30%
improvement in overall speed, and it had a huge improvement for heavy
load times... what I mean is that with ext3 we had multiple parallel big
tasks executing in more time than if we would have executed them
sequentially, and with XFS that was gone, load scales linearly. In any
case you should test the performance of your application on different FS
and different settings, as this could make a huge difference.

And another thing, we're still fighting with performance problems due to
the fact that our application was designed to perform well with the
other DB product... I think you'll have more work to do in this regard
than just some search/replace ;-)

Cheers,
Csaba.

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Jim C. Nasby

2006-03-21, 7:28 am

On Mon, Mar 20, 2006 at 04:19:12PM +0100, Csaba Nagy wrote:
> What I can add from our experience: ext3 turned out lousy for our
> application, and converting to XFS made a quite big improvement for our
> DB load. I don't have hard figures, but I think it was some 30%
> improvement in overall speed, and it had a huge improvement for heavy
> load times... what I mean is that with ext3 we had multiple parallel big
> tasks executing in more time than if we would have executed them
> sequentially, and with XFS that was gone, load scales linearly. In any
> case you should test the performance of your application on different FS
> and different settings, as this could make a huge difference.

Did you try mounting ext3 whith data=writeback by chance? People have
found that makes a big difference in performance.

http://archives.postgresql.org/pgsq...01/msg00320.php
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Steinar H. Gunderson

2006-03-21, 7:28 am

On Tue, Mar 21, 2006 at 12:52:46PM +0100, Csaba Nagy wrote:
> They knew however that for the data partitions no FS journaling is
> needed, and for the WAL partition meta data journaling is enough, so I
> guess they tuned ext3 for this.

For the record, that's the wrong way round. For the data partitioning
metadata journaling is enough, and for the WAL partition you don't need any
FS journaling at all.

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Jim C. Nasby

2006-03-21, 7:28 am

On Tue, Mar 21, 2006 at 12:56:18PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 12:52:46PM +0100, Csaba Nagy wrote:
>
> For the record, that's the wrong way round. For the data partitioning
> metadata journaling is enough, and for the WAL partition you don't need any
> FS journaling at all.

Are you sure? Metadate changes are probably a lot more common on the WAL
partition. In any case, I don't see why there should be a difference.
The real issue is: is related filesystem metadata sync'd as part of a
file being fsync'd?
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Steinar H. Gunderson

2006-03-21, 7:28 am

On Tue, Mar 21, 2006 at 06:01:58AM -0600, Jim C. Nasby wrote:
> Are you sure? Metadate changes are probably a lot more common on the WAL
> partition. In any case, I don't see why there should be a difference.
> The real issue is: is related filesystem metadata sync'd as part of a
> file being fsync'd?

I've been told on this list that PostgreSQL actually takes care to fill a new
WAL file with zeroes etc. when initializing it; dig a few months back and I'm
sure it's there.

/* Steinar */
--
Homepage: http://www.sesse.net/

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Jim C. Nasby

2006-03-21, 7:28 am

On Tue, Mar 21, 2006 at 01:10:32PM +0100, Steinar H. Gunderson wrote:
> On Tue, Mar 21, 2006 at 06:01:58AM -0600, Jim C. Nasby wrote:
>
> I've been told on this list that PostgreSQL actually takes care to fill a new
> WAL file with zeroes etc. when initializing it; dig a few months back and I'm
> sure it's there.

That's fine and all, but does no good if the filesystem doesn't know
that the file exists on a crash. The same concern is also true on the
data partition, although it's less likely to be a problem because if you
happen to crash soon after a DDL operation it's likely that you haven't
had a checkpoint yet, so the operation will likely be repeated during
WAL replay. But depending on that is a race condition.

Basically, you need to know for certain that if PostgreSQL creates a
file and then fsync's it that that file is safely on disk, and that the
filesystem knows how to find it (ie: the metadata is also on disk in
some fashion).
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Sponsored Links