dhis2-users team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

Yes I saw that note on autovacuum in one of the links you gave.
Certainly can do no harm to turn it off during restoration.

Regarding postgresql.conf, it is sometimes worth taking into account
that later settings in the file override earlier ones.  This means
that you can gather all your tweaks together at the bottom of the file
rather than have them scattered throughout.  Along the same lines, you
can also just include them in a separate customisation file by having
something like

include 'mytweaks.conf'

at the bottom of the file.  This might be useful if you wanted to have
a configuration profile optimized for a particular type of operation.

On 14 July 2017 at 13:20, Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx> wrote:
> Apologies, I meant 650Mb on the compressed.
> I'll revert with more details shortly, it looks like a huge factor may be autovacuum being on (and possibly logging).  I'm tweaking a little more then going to restart it.
> Some sort of progress indicator would be nice for restoring large dumps like these.
> I'll post my postgresql.conf and other details shortly.  Thanks!
>
> -----Original Message-----
> From: Bob Jolliffe [mailto:bobjolliffe@xxxxxxxxx]
> Sent: Friday, 14 July 2017 8:07 AM
> To: Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx>
> Cc: dhis2-users@xxxxxxxxxxxxxxxxxxx
> Subject: Re: [Dhis2-users] Major speed issue with pg_restore
>
> I am also trying to understand how a plain sql dump is only 7G while the custom compressed format is 650G.  But given that is true, why aren't you using the plain text dump?
>
> On 14 July 2017 at 13:02, Bob Jolliffe <bobjolliffe@xxxxxxxxx> wrote:
>> Hi Edward
>>
>> Can you tell us a bit more about the machine.  In particular RAM size
>> and disk type/speed and filesystem type.
>>
>> Given that you are probably not going to be running anything else on
>> this machine while you are restoring I think you might be able to
>> tweak a bit more aggressively just for this operation.
>>
>> You don't mention shared_buffers above?  That is a dramatic control
>> lever.  Is that just something you left off your mail?
>>
>> Bob
>>
>> On 14 July 2017 at 02:54, Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx> wrote:
>>> I’ve setup a new Ubuntu 16.04 box with PostgreSQL 9.5.5 and I’m
>>> restoring a fairly large DHIS2 backup but having speed issues.  It’s
>>> a full pg_dump in custom format and about 650Gb compressed (a plain
>>> text dump produces a 7Gb file).  I made sure inserts were turned off,
>>> so that’s not the issue, but so far it’s been running for 33 hours – CPU at 100% - with no end in sight.
>>> This is a backup file that took 30 minutes to generate.
>>>
>>>
>>>
>>> If it is running synchronously, I calculated that it’s on around 20
>>> million DB rows of around 170 million after 33 hours!  Surely that’s not normal.
>>>
>>> I’ve tweaked PostgreSQL with the following settings:
>>>
>>>
>>>
>>> maintenance_work_mem=2GB
>>>
>>> max_wal_size = 1Gb
>>>
>>> checkpoint_timeout = 3600
>>>
>>> checkpoint_completion_target = 0.9
>>>
>>>
>>>
>>> Anyway, if anyone has insight or has had a similar experience, or
>>> suggestions, please let me know!
>>>
>>> I’m testing it on another (Windows) instance to see if there’s
>>> something amiss.
>>>
>>>
>>>
>>> Cheers,
>>>
>>> Ed
>>>
>>>
>>> _______________________________________________
>>> Mailing list: https://launchpad.net/~dhis2-users
>>> Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx
>>> Unsubscribe : https://launchpad.net/~dhis2-users
>>> More help   : https://help.launchpad.net/ListHelp
>>>