Обсуждение: pg_restore -j
I'm trying to do a parallel restore with pg_restore -j but I'm only seeing one CPU being used really. The file is custom format, but was made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup made with 8.4 to run parallel restore?
On Thu, 2009-09-17 at 11:48 -0600, Scott Marlowe wrote: > I'm trying to do a parallel restore with pg_restore -j but I'm only > seeing one CPU being used really. The file is custom format, but was > made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup > made with 8.4 to run parallel restore? Yes I believe but I don't recall. You could dump the TOC and note differences. > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander
On Thu, Sep 17, 2009 at 12:00 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > On Thu, 2009-09-17 at 11:48 -0600, Scott Marlowe wrote: >> I'm trying to do a parallel restore with pg_restore -j but I'm only >> seeing one CPU being used really. The file is custom format, but was >> made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup >> made with 8.4 to run parallel restore? > > Yes I believe but I don't recall. You could dump the TOC and note > differences. I kinda figured, I'm making a dump with pg84 now to test with. I'm really hoping for a noticeable improvement in restore times, as we're in the 1.5 to 2 hour range right now.
On Thu, 2009-09-17 at 12:05 -0600, Scott Marlowe wrote: > On Thu, Sep 17, 2009 at 12:00 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > > On Thu, 2009-09-17 at 11:48 -0600, Scott Marlowe wrote: > >> I'm trying to do a parallel restore with pg_restore -j but I'm only > >> seeing one CPU being used really. The file is custom format, but was > >> made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup > >> made with 8.4 to run parallel restore? > > > > Yes I believe but I don't recall. You could dump the TOC and note > > differences. > > I kinda figured, I'm making a dump with pg84 now to test with. I'm > really hoping for a noticeable improvement in restore times, as we're > in the 1.5 to 2 hour range right now. > If you have the concurrency and disk IO, you should get that down below 30 minutes. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander
On Thu, Sep 17, 2009 at 12:12 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > On Thu, 2009-09-17 at 12:05 -0600, Scott Marlowe wrote: >> On Thu, Sep 17, 2009 at 12:00 PM, Joshua D. Drake <jd@commandprompt.com> wrote: >> > On Thu, 2009-09-17 at 11:48 -0600, Scott Marlowe wrote: >> >> I'm trying to do a parallel restore with pg_restore -j but I'm only >> >> seeing one CPU being used really. The file is custom format, but was >> >> made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup >> >> made with 8.4 to run parallel restore? >> > >> > Yes I believe but I don't recall. You could dump the TOC and note >> > differences. >> >> I kinda figured, I'm making a dump with pg84 now to test with. I'm >> really hoping for a noticeable improvement in restore times, as we're >> in the 1.5 to 2 hour range right now. >> > > If you have the concurrency and disk IO, you should get that down below > 30 minutes. On our two big servers we have 12 Disk RAID-10 for pgdata, and 2 disk RAID-1 for pg_xlog, and 8 cores. What's a good -j number to start at there? I'm leaning towards 8 or 10 or 12 for testing. Woohoo late night testing. :)
On Thu, 2009-09-17 at 12:15 -0600, Scott Marlowe wrote: > On Thu, Sep 17, 2009 at 12:12 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > > On Thu, 2009-09-17 at 12:05 -0600, Scott Marlowe wrote: > >> On Thu, Sep 17, 2009 at 12:00 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > >> > On Thu, 2009-09-17 at 11:48 -0600, Scott Marlowe wrote: > >> >> I'm trying to do a parallel restore with pg_restore -j but I'm only > >> >> seeing one CPU being used really. The file is custom format, but was > >> >> made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup > >> >> made with 8.4 to run parallel restore? > >> > > >> > Yes I believe but I don't recall. You could dump the TOC and note > >> > differences. > >> > >> I kinda figured, I'm making a dump with pg84 now to test with. I'm > >> really hoping for a noticeable improvement in restore times, as we're > >> in the 1.5 to 2 hour range right now. > >> > > > > If you have the concurrency and disk IO, you should get that down below > > 30 minutes. > > On our two big servers we have 12 Disk RAID-10 for pgdata, and 2 disk > RAID-1 for pg_xlog, and 8 cores. What's a good -j number to start at > there? I'm leaning towards 8 or 10 or 12 for testing. Woohoo late > night testing. :) I found 1.5*num_cpus to be most beneficial but it obviously depends on data set etc.. Joshua D. Drake > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander
On Thu, 2009-09-17 at 11:48 -0600, Scott Marlowe wrote: > I'm trying to do a parallel restore with pg_restore -j but I'm only > seeing one CPU being used really. The file is custom format, but was > made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup > made with 8.4 to run parallel restore? Yes I believe but I don't recall. You could dump the TOC and note differences. > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander
On Thu, 2009-09-17 at 12:05 -0600, Scott Marlowe wrote: > On Thu, Sep 17, 2009 at 12:00 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > > On Thu, 2009-09-17 at 11:48 -0600, Scott Marlowe wrote: > >> I'm trying to do a parallel restore with pg_restore -j but I'm only > >> seeing one CPU being used really. The file is custom format, but was > >> made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup > >> made with 8.4 to run parallel restore? > > > > Yes I believe but I don't recall. You could dump the TOC and note > > differences. > > I kinda figured, I'm making a dump with pg84 now to test with. I'm > really hoping for a noticeable improvement in restore times, as we're > in the 1.5 to 2 hour range right now. > If you have the concurrency and disk IO, you should get that down below 30 minutes. Joshua D. Drake -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander
On Thu, 2009-09-17 at 12:15 -0600, Scott Marlowe wrote: > On Thu, Sep 17, 2009 at 12:12 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > > On Thu, 2009-09-17 at 12:05 -0600, Scott Marlowe wrote: > >> On Thu, Sep 17, 2009 at 12:00 PM, Joshua D. Drake <jd@commandprompt.com> wrote: > >> > On Thu, 2009-09-17 at 11:48 -0600, Scott Marlowe wrote: > >> >> I'm trying to do a parallel restore with pg_restore -j but I'm only > >> >> seeing one CPU being used really. The file is custom format, but was > >> >> made by pg_dump for pgsql 8.3. Is that a problem? Do I need a backup > >> >> made with 8.4 to run parallel restore? > >> > > >> > Yes I believe but I don't recall. You could dump the TOC and note > >> > differences. > >> > >> I kinda figured, I'm making a dump with pg84 now to test with. I'm > >> really hoping for a noticeable improvement in restore times, as we're > >> in the 1.5 to 2 hour range right now. > >> > > > > If you have the concurrency and disk IO, you should get that down below > > 30 minutes. > > On our two big servers we have 12 Disk RAID-10 for pgdata, and 2 disk > RAID-1 for pg_xlog, and 8 cores. What's a good -j number to start at > there? I'm leaning towards 8 or 10 or 12 for testing. Woohoo late > night testing. :) I found 1.5*num_cpus to be most beneficial but it obviously depends on data set etc.. Joshua D. Drake > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering If the world pushes look it in the eye and GRR. Then push back harder. - Salamander