dhis2-users team mailing list archive

[Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx>]

Maybe, but 2 days vs 50 minutes is a pretty big difference…

From: Bob Jolliffe [mailto:bobjolliffe@xxxxxxxxx]
Sent: Monday, 17 July 2017 2:33 PM
To: Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx>
Cc: Lars Helge Øverland <lars@xxxxxxxxx>; dhis2-users <dhis2-users@xxxxxxxxxxxxxxxxxxx>
Subject: Re: [Dhis2-users] Major speed issue with pg_restore

It is unlikely (though not impossible) that the problem is ubuntu specific.  It could be your windows for example, is making better use of the disk.

I think I see you had 2 sata disks which would probably work best in a raid 0 setup.  Perhaps windows has arranged them thus.  Maybe also some other tweaks to get the best out of the filesystem.  Good luck.

On 17 July 2017 at 17:19, Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx<mailto:erobinson@xxxxxxxxxxxxxxxxxx>> wrote:
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,
Cheers
Ed


From: Lars Helge Øverland [mailto:lars@xxxxxxxxx<mailto:lars@xxxxxxxxx>]
Sent: Monday, 17 July 2017 8:41 AM
To: Edward Robinson <erobinson@xxxxxxxxxxxxxxxxxx<mailto:erobinson@xxxxxxxxxxxxxxxxxx>>
Cc: Bob Jolliffe <bobjolliffe@xxxxxxxxx<mailto:bobjolliffe@xxxxxxxxx>>; dhis2-users <dhis2-users@xxxxxxxxxxxxxxxxxxx<mailto: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*

regards,

Lars

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:
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<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:
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<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
lars@xxxxxxxxx<mailto:lars@xxxxxxxxx>
http://www.dhis2.org<https://www.dhis2.org/>