Обсуждение: Reducing pg_dump & pg_restore times
Hello all!
I have databases of sizes ranging from 30-60GB that have to be backed-up and restored. I want to reduce the pg_dump and pg_restore times for the same. Can someone throw some light on how to go about it. I have tried tweaking the postgresql.conf properties. Though, I am not sure what each property means. If someone could point to a document on the same it would be really helpful.
Thanksalot!
Shivkumar CM
I have databases of sizes ranging from 30-60GB that have to be backed-up and restored. I want to reduce the pg_dump and pg_restore times for the same. Can someone throw some light on how to go about it. I have tried tweaking the postgresql.conf properties. Though, I am not sure what each property means. If someone could point to a document on the same it would be really helpful.
Thanksalot!
Shivkumar CM
C.M. Shivkumar wrote: > Hello all! > > I have databases of sizes ranging from 30-60GB that have to be backed-up > and restored. I want to reduce the pg_dump and pg_restore times for the > same. Can someone throw some light on how to go about it. I have tried > tweaking the postgresql.conf properties. Though, I am not sure what each > property means. If someone could point to a document on the same it > would be really helpful. How long does a backup take (roughly) ? This page links off to each section you need to look at: http://www.postgresql.org/docs/8.1/static/runtime-config.html -- Postgresql & php tutorials http://www.designmagick.com/
Coder At Heart wrote: > Hi! > > Restore & backup take time in the tune of 6 hrs on a Linux, 4 proc, 32 G > RAM machine for a 60GB database. Please always cc the mailing list. I've never played with databases that large so I don't know if that's good or bad.. others on the list will be able to offer advice/suggestions. -- Postgresql & php tutorials http://www.designmagick.com/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/26/06 01:20, Chris wrote: > Coder At Heart wrote: >> Hi! >> >> Restore & backup take time in the tune of 6 hrs on a Linux, 4 proc, 32 >> G RAM machine for a 60GB database. > > Please always cc the mailing list. > > I've never played with databases that large so I don't know if that's > good or bad.. others on the list will be able to offer advice/suggestions. RAM certainly helps, but pg_dump is single-threaded, and, by definition, backing up and restoring 60GB of data is *extremely* IO constrained. So, - - What OS? - - What version of PG? - - what kind of disk system does the DB live on? - - How many controllers? - - What kind of tape drive? - - Is it on it's own SCSI controller? - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFQH10S9HxQb37XmcRAhFpAKCeBZbTPQW8mhY8EgG26R7OgpgrlQCeMAek KmPbaWmLx+N+kYQvHNOgiL0= =YsM3 -----END PGP SIGNATURE-----
On Thursday 26 October 2006 02:20, Chris wrote: > Coder At Heart wrote: > > Hi! > > > > Restore & backup take time in the tune of 6 hrs on a Linux, 4 proc, 32 G > > RAM machine for a 60GB database. > > Please always cc the mailing list. > > I've never played with databases that large so I don't know if that's > good or bad.. others on the list will be able to offer advice/suggestions. A while back I wrote something up on increasing restore times, might be worth checking out. http://people.planetpostgresql.org/xzilla/index.php?/archives/133-Getting-faster-database-restores-on-postgresql-8.1.html -- Robert Treat Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
Ron Johnson wrote: > On 10/26/06 01:20, Chris wrote: >>> Coder At Heart wrote: >>>> Hi! >>>> >>>> Restore & backup take time in the tune of 6 hrs on a Linux, 4 proc, 32 >>>> G RAM machine for a 60GB database. >>> Please always cc the mailing list. >>> >>> I've never played with databases that large so I don't know if that's >>> good or bad.. others on the list will be able to offer advice/suggestions. > > RAM certainly helps, but pg_dump is single-threaded, and, by > definition, backing up and restoring 60GB of data is *extremely* IO > constrained. well from what I have seen is that on large databases and a sufficiently fast disk-io subsystem actually CPU(or rather the speed of a single core) starts to be the bottleneck with current postgresql versions. That is true for both COPY and to a much greater effect index creation (I have seen restores that took days and most of that was purely index creation). 8.2 has improved considerably on that due to the massive improvments in the external sorting code but it will still be bottleneck by the single-process nature of psql. > > So, > - What OS? > - What version of PG? > - what kind of disk system does the DB live on? > - How many controllers? > - What kind of tape drive? > - Is it on it's own SCSI controller? in addition to that basic information we need there are: *) increasing maintainance_work_mem and work_mem massively *) increasing the number of checkpoint segments *) disabling fsync during the load Stefan
Hi!
Thanks so much for the responses. The system configuration is something like this-
Linux, PG 8.1, ext3, RAID 1 setup with two controllers
The machine has 4 processors with 32 Gig of RAM.
Thanks!
Shivkumar
Shivkumar
On 10/27/06, Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote:
Ron Johnson wrote:
> On 10/26/06 01:20, Chris wrote:
>>> Coder At Heart wrote:
>>>> Hi!
>>>>
>>>> Restore & backup take time in the tune of 6 hrs on a Linux, 4 proc, 32
>>>> G RAM machine for a 60GB database.
>>> Please always cc the mailing list.
>>>
>>> I've never played with databases that large so I don't know if that's
>>> good or bad.. others on the list will be able to offer advice/suggestions.
>
> RAM certainly helps, but pg_dump is single-threaded, and, by
> definition, backing up and restoring 60GB of data is *extremely* IO
> constrained.
well from what I have seen is that on large databases and a sufficiently
fast disk-io subsystem actually CPU(or rather the speed of a single
core) starts to be the bottleneck with current postgresql versions.
That is true for both COPY and to a much greater effect index creation
(I have seen restores that took days and most of that was purely index
creation).
8.2 has improved considerably on that due to the massive improvments in
the external sorting code but it will still be bottleneck by the
single-process nature of psql.
>
> So,
> - What OS?
> - What version of PG?
> - what kind of disk system does the DB live on?
> - How many controllers?
> - What kind of tape drive?
> - Is it on it's own SCSI controller?
in addition to that basic information we need there are:
*) increasing maintainance_work_mem and work_mem massively
*) increasing the number of checkpoint segments
*) disabling fsync during the load
Stefan
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 On 10/27/06 02:26, Coder At Heart wrote: > Hi! > > Thanks so much for the responses. The system configuration is something > like > this- That's a start... > Linux, Presumably a 2.6 kernel? Which, and which distro version? PG 8.1, OK ext3, OK RAID 1 setup with two controllers Not enough info. Soft RAID, controller RAID, IDE, SATA, SCSI U160, U320, SAS, 10K RPM, 15K RPM, etc, etc? And tape drive. What kind of tape drive? Are the disks on one controller and the tape drive on another? > The machine has 4 processors with 32 Gig of RAM. Presumably modern. AMD, Intel, GHz? And then there's SK's suggestions: >> *) increasing maintainance_work_mem and work_mem massively >> *) increasing the number of checkpoint segments >> *) disabling fsync during the load > Thanks! > Shivkumar > > On 10/27/06, Stefan Kaltenbrunner <stefan@kaltenbrunner.cc> wrote: >> >> Ron Johnson wrote: >> > On 10/26/06 01:20, Chris wrote: >> >>> Coder At Heart wrote: >> >>>> Hi! >> >>>> [snip] >> >> > >> > So, >> > - What OS? >> > - What version of PG? >> > - what kind of disk system does the DB live on? >> > - How many controllers? >> > - What kind of tape drive? >> > - Is it on it's own SCSI controller? >> >> in addition to that basic information we need there are: >> >> *) increasing maintainance_work_mem and work_mem massively >> *) increasing the number of checkpoint segments >> *) disabling fsync during the load - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) iD8DBQFFQdEoS9HxQb37XmcRAt2GAKCVARbdY5wwcoOIINP22T9u/1VA1wCgo9F+ Qi5ou/YfdLCjzcpmRHONlwE= =ONU4 -----END PGP SIGNATURE-----