Search Postgresql Archives

Re: Reducing pg_dump & pg_restore times

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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
 
On 10/27/06, Stefan Kaltenbrunner <stefan@xxxxxxxxxxxxxxxx> 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


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux