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:bobjolli...@gmail.com] Sent: Friday, 14 July 2017 8:07 AM To: Edward Robinson <erobin...@projectbalance.com> Cc: dhis2-users@lists.launchpad.net 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 <bobjolli...@gmail.com> 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 <erobin...@projectbalance.com> > 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@lists.launchpad.net >> Unsubscribe : https://launchpad.net/~dhis2-users >> More help : https://help.launchpad.net/ListHelp >> _______________________________________________ Mailing list: https://launchpad.net/~dhis2-users Post to : dhis2-users@lists.launchpad.net Unsubscribe : https://launchpad.net/~dhis2-users More help : https://help.launchpad.net/ListHelp
