X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id C476F9DC8E1 for ; Mon, 20 Mar 2006 11:19:11 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00489-01 for ; Mon, 20 Mar 2006 11:19:15 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from mail.ecircle.de (mail.ecircle.de [195.140.186.200]) by postgresql.org (Postfix) with ESMTP id 858CB9DC888 for ; Mon, 20 Mar 2006 11:19:08 -0400 (AST) Received: from deimos.muc.ecircle.de (deimos.muc.ecircle.de [192.168.1.4]) by mail.ecircle.de (READY) with ESMTP id 98A2C55C01C; Mon, 20 Mar 2006 16:19:12 +0100 (CET) Received: from [192.168.1.110] ([192.168.1.110]) by deimos.muc.ecircle.de with Microsoft SMTPSVC(5.0.2195.6713); Mon, 20 Mar 2006 16:19:12 +0100 Subject: Re: Migration study, step 1: bulk write performance From: Csaba Nagy To: Mikael Carneholm Cc: postgres performance list In-Reply-To: <7F10D26ECFA1FB458B89C5B4B0D72C2B088286@sesrv12.wirelesscar.com> References: <7F10D26ECFA1FB458B89C5B4B0D72C2B088286@sesrv12.wirelesscar.com> Content-Type: text/plain Message-Id: <1142867952.16487.96.camel@coppola.muc.ecircle.de> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 20 Mar 2006 16:19:12 +0100 Content-Transfer-Encoding: 7bit X-OriginalArrivalTime: 20 Mar 2006 15:19:12.0523 (UTC) FILETIME=[A498E5B0:01C64C31] X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.108 required=5 tests=[AWL=0.108] X-Spam-Score: 0.108 X-Spam-Level: X-Archive-Number: 200603/332 X-Sequence-Number: 17769 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.