On Thu, 2009-01-29 at 18:09 -0500, Robert Haas wrote: > On Thu, Jan 29, 2009 at 3:21 PM, henk de wit <henk53602@xxxxxxxxxxx> wrote: > > Hi, > > When I try to restore a database dump on PostgreSQL 8.3 > > that's approximately 130GB in size and takes about 1 hour, I noticed index > > creation makes up the bulk of that time. I'm using a very fast I/O subsystem > > (16 Mtron Pro 7535 SSDs using a dual 1.2Ghz IOP/4GB cache RAID controller), > > fast CPUs (2 quad core C2Q's at 2.6Ghz) and 32GB RAM. From monitoring the > > restore process, I learned that only 10 minutes is spend doing IO, while the > > rest of the time is spend on creating the indexes. Index creation seems to > > be completely CPU bound. > > The problem is that only 1 CPU core is used. My other 7 cores are just > > sitting there doing nothing. It seems to me that creating each index, > > especially for different tables, is something that can be done > > independently. > > Is there some way I can let PostgreSQL use multiple cores for creating the > > indexes? > > Andrew Dunstan has been working on this problem. His latest parallel > restore patch can be found here: > > http://archives.postgresql.org/message-id/4977E070.6070604@xxxxxxxxxxxx Yeah but that isn't useful for 8.3. What can be done in this specific situation is to make sure you dump with the -Fc option. You can then pull a TOC out with pg_restore and break that appart. Reading the TOC is pretty self evident. Once you get down to index creation you can create multiple files each with a group of indexes to create. Then call pg_restore multiple times in a script against the individual TOC and you will use all cores. Joshua D. Drake P.S. Increase maintenance_work_mem can help too > > ...Robert > -- PostgreSQL - XMPP: jdrake@xxxxxxxxxxxxxxxxxxxxx Consulting, Development, Support, Training 503-667-4564 - http://www.commandprompt.com/ The PostgreSQL Company, serving since 1997 -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
