Re: Migration study, step 1: bulk write performance

From: Csaba Nagy
Subject: Re: Migration study, step 1: bulk write performance
Date: ,
Msg-id: 1142867952.16487.96.camel@coppola.muc.ecircle.de
(view: Whole thread, Raw)
In response to: Migration study, step 1: bulk write performance optimization  ("Mikael Carneholm")
Responses: Re: Migration study, step 1: bulk write performance  ("Jim C. Nasby")
List: pgsql-performance

Tree view

Migration study, step 1: bulk write performance optimization  ("Mikael Carneholm", )
 Re: Migration study, step 1: bulk write performance optimization  ("Craig A. James", )
  update == delete + insert?  ("Craig A. James", )
   Re: update == delete + insert?  ("Jim Buttafuoco", )
   Re: update == delete + insert?  (Tom Lane, )
   Re: update == delete + insert?  ("Jaime Casanova", )
    Re: update == delete + insert?  ("Jim C. Nasby", )
     Re: update == delete + insert?  ("Merlin Moncure", )
      Re: update == delete + insert?  ("Jim C. Nasby", )
  Re: Migration study, step 1: bulk write performance optimization  (Tom Lane, )
 Re: Migration study, step 1: bulk write performance  (Csaba Nagy, )
  Re: Migration study, step 1: bulk write performance  ("Jim C. Nasby", )
   Re: Migration study, step 1: bulk write performance  (Csaba Nagy, )
    Re: Migration study, step 1: bulk write performance  ("Steinar H. Gunderson", )
     Re: Migration study, step 1: bulk write performance  (Csaba Nagy, )
     Re: Migration study, step 1: bulk write performance  ("Jim C. Nasby", )
      Re: Migration study, step 1: bulk write performance  ("Steinar H. Gunderson", )
       Re: Migration study, step 1: bulk write performance  ("Jim C. Nasby", )
        Re: Migration study, step 1: bulk write performance  ("Steinar H. Gunderson", )
         Re: Migration study, step 1: bulk write performance  ("Jim C. Nasby", )
      Re: Migration study, step 1: bulk write performance  (Michael Stone, )
 Re: Migration study, step 1: bulk write performance optimization  (PFC, )
 Re: Migration study, step 1: bulk write performance optimization  (Dave Cramer, )
 Re: Migration study, step 1: bulk write performance  (Simon Riggs, )
  Re: Migration study, step 1: bulk write performance  (Ron, )
   Re: Migration study, step 1: bulk write performance  (Scott Marlowe, )
    Re: Migration study, step 1: bulk write performance  (Tom Lane, )
     Re: Migration study, step 1: bulk write performance  (Scott Marlowe, )
   Re: Migration study, step 1: bulk write performance  (Alvaro Herrera, )

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.




pgsql-performance by date:

From: Miguel
Date:
Subject: Re: Best OS & Configuration for Dual Xeon w/4GB & Adaptec
From: "Luke Lonergan"
Date:
Subject: Re: Best OS & Configuration for Dual Xeon w/4GB &