dhis2-users team mailing list archive

[Bob Jolliffe <bobjolliffe@xxxxxxxxx>]

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
>>