Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 82A9C632CDB for ; Thu, 8 Jul 2010 06:02:28 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 94700-06 for ; Thu, 8 Jul 2010 09:02:19 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from 64.mail-out.ovh.net (64.mail-out.ovh.net [91.121.185.65]) by mail.postgresql.org (Postfix) with SMTP id 1B67463280F for ; Thu, 8 Jul 2010 06:02:19 -0300 (ADT) Received: (qmail 5124 invoked by uid 503); 8 Jul 2010 09:01:46 -0000 Received: from b6.ovh.net (HELO mail412.ha.ovh.net) (213.186.33.56) by 64.mail-out.ovh.net with SMTP; 8 Jul 2010 09:01:46 -0000 Received: from b0.ovh.net (HELO queueout) (213.186.33.50) by b0.ovh.net with SMTP; 8 Jul 2010 11:02:16 +0200 Received: from par69-8-88-161-102-87.fbx.proxad.net (HELO apollo13) (lists%peufeu.com@88.161.102.87) by ns0.ovh.net with SMTP; 8 Jul 2010 11:02:14 +0200 Content-Type: text/plain; charset=utf-8; format=flowed; delsp=yes To: "Ryan Wexler" , pgsql-performance@postgresql.org Subject: Re: performance on new linux box References: Date: Thu, 08 Jul 2010 10:56:02 +0200 MIME-Version: 1.0 Content-Transfer-Encoding: 8bit From: "Pierre C" Message-ID: In-Reply-To: User-Agent: Opera Mail/10.10 (Linux) X-Ovh-Tracer-Id: 10670716368760604197 X-Ovh-Remote: 88.161.102.87 (par69-8-88-161-102-87.fbx.proxad.net) X-Ovh-Local: 213.186.33.20 (ns0.ovh.net) X-Spam-Check: DONE|U 0.5/N X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.9 tagged_above=-10 required=5 tests=BAYES_00=-1.9 X-Spam-Level: X-Archive-Number: 201007/102 X-Sequence-Number: 39491 > On the new system the bulk loads are extremely slower than on the > previous > machine and so are the more complex queries. The smaller transactional > queries seem comparable but i had expected an improvement. Performing a > db > import via psql -d databas -f dbfile illustrates this problem. If you use psql (not pg_restore) and your file contains no BEGIN/COMMIT statements, you're probably doing 1 transaction per SQL command. As the others say, if the old box lied about fsync, and the new one doesn't, performance will suffer greatly. If this is the case, remember to do your imports the proper way : either use pg_restore, or group inserts in a transaction, and build indexes in parallel.