← Back to team overview

dhis2-users team mailing list archive

Re: Major speed issue with pg_restore


That is useful info for the forum for sure and I fully agree, thanks Lars!
Ultimately the server supports Linux – it’s just certified for different sistros.  Also, I noticed a number of Ubuntu 16.04 users complaining of the same speed issue, so while Ubuntu will probably remain my go-to distro, in the case of these servers, I’m probably better off getting Suse or one of the other supported distros on before I take any more troubleshooting steps.  For now it’s running fine and the future direction may be different – including cloud hosting to avoid this kind of hassle.
Thanks everyone for the support,

From: Lars Helge Øverland [mailto:lars@xxxxxxxxx]
Sent: Monday, 17 July 2017 8:41 AM
To: Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx>
Cc: Bob Jolliffe <bobjolliffe@xxxxxxxxx>; dhis2-users <dhis2-users@xxxxxxxxxxxxxxxxxxx>
Subject: Re: [Dhis2-users] 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<mailto:erobinson@xxxxxxxxxxxxxxxxxx>> wrote:
Hi Bob, apologies, it’s running on this hardware:
A bit dated, but definitely not a hardware error given the speedy / successful restore on Windows.

From: Bob Jolliffe [mailto:bobjolliffe@xxxxxxxxx<mailto:bobjolliffe@xxxxxxxxx>]
Sent: Saturday, 15 July 2017 3:02 PM
To: Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx<mailto:erobinson@xxxxxxxxxxxxxxxxxx>>
Cc: Knut Staring <knutst@xxxxxxxxx<mailto:knutst@xxxxxxxxx>>; dhis2-users <dhis2-users@xxxxxxxxxxxxxxxxxxx<mailto:dhis2-users@xxxxxxxxxxxxxxxxxxx>>; Hannan Khan <hannank@xxxxxxxxx<mailto: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<mailto: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:
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.

Mailing list: https://launchpad.net/~dhis2-users
Post to     : dhis2-users@xxxxxxxxxxxxxxxxxxx<mailto: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

Follow ups