Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id E52702E0049 for ; Tue, 19 Feb 2008 15:48:17 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 09536-01-8 for ; Tue, 19 Feb 2008 15:48:08 -0400 (AST) Received: from serv01.siteground137.com (ns1.siteground137.com [67.15.250.10]) by postgresql.org (Postfix) with ESMTP id E210F2E0030 for ; Tue, 19 Feb 2008 15:46:11 -0400 (AST) Received: from cragnux.laika.com ([209.162.219.253]:65500 helo=dogma.ljc.laika.com) by serv01.siteground137.com with esmtpa (Exim 4.68) (envelope-from ) id 1JRYPq-0007Td-On; Tue, 19 Feb 2008 13:46:10 -0600 Subject: Re: 7 hrs for a pg_restore? From: Jeff Davis To: Douglas J Hunley Cc: pgsql-performance@postgresql.org In-Reply-To: <200802191420.11347.doug@hunley.homeip.net> References: <200802191303.58840.doug@hunley.homeip.net> <1203445403.3846.86.camel@dogma.ljc.laika.com> <200802191420.11347.doug@hunley.homeip.net> Content-Type: text/plain Date: Tue, 19 Feb 2008 11:46:07 -0800 Message-Id: <1203450367.3846.101.camel@dogma.ljc.laika.com> Mime-Version: 1.0 X-Mailer: Evolution 2.0.4 (2.0.4-7) Content-Transfer-Encoding: 7bit X-AntiAbuse: This header was added to track abuse, please include it with any abuse report X-AntiAbuse: Primary Hostname - serv01.siteground137.com X-AntiAbuse: Original Domain - postgresql.org X-AntiAbuse: Originator/Caller UID/GID - [47 12] / [47 12] X-AntiAbuse: Sender Address Domain - j-davis.com X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200802/224 X-Sequence-Number: 28766 On Tue, 2008-02-19 at 14:20 -0500, Douglas J Hunley wrote: > > Keep in mind, if you have several GB worth of indexes, they take up > > basically no space in the logical dump (just the "CREATE INDEX" command, > > and that's it). But they can take a lot of processor time to build up > > again, especially with localized text. > > > > that could be a factor here. It is a UNICODE db, and we do a lot of text-based > indexing for the application I assume you're _not_ talking about full text indexes here. These factors: * unicode (i.e. non-C locale) * low I/O utilization * indexes taking up most of the 7 hours mean that we've probably found the problem. Localized text uses sorting rules that are not the same as binary sort order, and it takes much more CPU power to do the comparisons, and sorts are already processor-intensive operations. Unfortunately postgresql does not parallelize this sorting/indexing at all, so you're only using one core. I'd recommend restoring everything except the indexes, and then you can restore the indexes concurrently in several different sessions so that it uses all of your cores. Build your primary key/unique indexes first, and then after those are built you can start using the database while the rest of the indexes are building (use "CREATE INDEX CONCURRENTLY"). Regards, Jeff Davis