← Back to team overview

dhis2-users team mailing list archive

Re: Major speed issue with pg_restore


Hi Edward,

this is besides the main point but make sure you create pg dumps without
the analytics tables, and instead let DHIS 2 re-generate the analytics
tables (which is faster as indexing is run in parallel).

pg_dump -T analytics*



On Sun, Jul 16, 2017 at 10:43 PM, Edward Robinson <
erobinson@xxxxxxxxxxxxxxxxxx> wrote:

> Hi Bob, apologies, it’s running on this hardware:
> http://www.dell.com/support/home/ht/en/htbsdt1/product-
> support/servicetag/d2fvfx1/configuration
> A bit dated, but definitely not a hardware error given the speedy /
> successful restore on Windows.
> Regards
> Ed
> *From:* Bob Jolliffe [mailto:bobjolliffe@xxxxxxxxx]
> *Sent:* Saturday, 15 July 2017 3:02 PM
> *To:* Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx>
> *Cc:* Knut Staring <knutst@xxxxxxxxx>; dhis2-users <
> dhis2-users@xxxxxxxxxxxxxxxxxxx>; Hannan Khan <hannank@xxxxxxxxx>
> *Subject:* RE: [Dhis2-users] Major speed issue with pg_restore
> Ed you never answered my earlier question about the hardware you were
> running on?
> On 15 Jul 2017 6:40 p.m., "Edward Robinson" <erobinson@xxxxxxxxxxxxxxxxxx>
> wrote:
> Sorry for this slow response, time has not been on my side 😊
> Also, thanks Bob, Knut and Hannan for your responses.
> So to fill everyone in, I went through postgres.conf with a fine toothed
> comb.  @ Bob, Shared buffers were set to 3200 though I'd set them to 1600
> for the restore as per this article's suggestions:
> http://www.databasesoup.com/2014/09/settings-for-fast-pgrestore.html
> Overall I'm convinced the PGSQL environment had nothing to do with the
> issue... what I had suspect is that the combination of Ubuntu 16.04 and our
> hardware was not a good match.  I suspect the SATA channel in particular
> was the bottleneck.
> To prove a point (I have two identical machines), I loaded Win Server 2012
> R2 and and installed PGSQL 9.5.5 on the box and, without so much as
> tweaking a single file, I restored the dump within 50 minutes.  We're
> likely upgrading the platform soon so I'm not going to break my back
> troubleshooting what is likely an issue that won't be there in 6 months
> time.  Dell didn't include Debian or Ubuntu on their list of supported OS
> for the hardware combination, though I have no reason to believe it
> couldn't work with a little effort and a bit of old school compiling, I
> just don't have the luxury of time to fiddle.  It worked well with 12.04
> out of the box.
> I just thought I'd post this in case anyone else was having similar
> trouble - if you've double checked the configs, it's quite possibly a lower
> level issue.
> 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

Lars Helge Øverland
Lead developer, DHIS 2
University of Oslo
Skype: larshelgeoverland
http://www.dhis2.org <https://www.dhis2.org/>

Follow ups