[postgis-users] QGIS + PostGIS in production environment

Fri Jun 16 07:14:17 PDT 2017

You mentioned, that you didn't tune Postgres at all ? As in not changing 
any memory related parameters in postgresql.conf ??

If that's the case (and I find it unlikely..) I highly recommend that 
you take a look at the following http pages:

https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server or: 
http://pgtune.leopard.in.ua/

The last http page is a small web based application where you enter 
parameters regarding the number of concurrent users and the server 
memory size. It will give you a set of (rough) parameters to change in 
the postgres setup. It is *not* a substitute for careful memory 
optimization, but simply a fast method to get the ballpark figures for 
the most important postgres memory related parameters.

Regards

Bo Victor Thomsen

AestasGIS

Denmark

Den 15/06/17 kl. 21:58 skrev Cap Diniz:
> Thanks for the responses so far!
>
> Adding a bit more information.
>
> We are using Dell PowerEdge-R430 [1] for the PostGIS server with OS 
> Debian 8.7 on a 1gigabit network. For each topographic sheet we use 
> one database, this means that we have hundreds of databases in 
> production. We divide the databases in different ports by project, 
> just for organization, I don't know if that impacts performance.
>
> As for clients we usually use Debian 8.4, Windows 7 or Windows 10 with 
> latest LTR QGIS. The clients are i7, 8gb RAM, 1gb video card.
>
> We didn't do any tuning in PostgreSQL, I will try out the suggestions. 
> As configuration goes, we just disabled the Auto Vacuum, and run it by 
> a script at night (when no one is connected) along with backup.
>
>
>
> Then main problem that we are facing is slow saving time at peak hours 
> (when about 50 clients are digitizing in different databases). 
> Sometimes can take up to 1 minute to save all layers. We work with 
> autosave in QGIS that saves every 5 minutes, so slow saving time is 
> not ideal.
> The reason that we save so much is that sometimes we have errors while 
> saving, such as null geometries, that we cannot fix and we have to 
> discard the edits. (also we have other errors that usually we discard 
> the edits).
>
>
>
> [1] 
> https://i.dell.com/sites/doccontent/shared-content/data-sheets/en/Documents/Dell-PowerEdge-R430-Spec-Sheet.pdf 
> <https://i.dell.com/sites/doccontent/shared-content/data-sheets/en/Documents/Dell-PowerEdge-R430-Spec-Sheet.pdf>
>
> On Thu, Jun 15, 2017 at 3:38 PM, Regina Obe <lr at pcorp.us 
> <mailto:lr at pcorp.us>> wrote:
>
>     Felipe,
>
>     Are you having problems currently or you just asking a general
>     question for future consideration?
>
>     Your PostgreSQL and PostGIS are pretty old.
>
>     PostgreSQL 9.2 is reaching end of life in a couple of months -
>     https://www.postgresql.org/support/versioning/
>     <https://www.postgresql.org/support/versioning/>
>
>     , so you should probably upgrade that soon to something like
>     PostgreSQL 9.6, or 10 when it comes out in Sept/October.
>
>     PostGIS 2.3 is the latest version and PostGIS 2.4 we are going to
>     try to release around Sept to go along with PostgreSQL 10.
>
>     As far as OS, PostgreSQL/PostGIS runs best on FreeBSD  or Linux. 
>     Most high-end PostgreSQL users seem to prefer FreeBSD, but that
>     might be a historical thing rather than performance thing, maybe
>     some folks can speak to that.
>
>     I've had pretty good performance on windows, but I think as you
>     add more users the process spunning (vs. prefer thread spunning on
>     windows) might make it less performant. I have certain things that
>     necessitate me running often on windows.  For GIS usage, I've
>     found Ubuntu seems to have the best menu of packages and most
>     preferred by GIS folk, so probably a good one to go with if you
>     are new to Unix/Linux and just want to get stuff from repos and
>     have a good balance of performance and availability of prepackaged
>     goods.
>
>     That said questions on OS/ Hardware require more consideration
>     than performance.  A lot these days is just your comfortability
>     with these things.
>
>     For PostgreSQL, whatever you do, you'll probably want SSD disks
>     and RAM tends to be more important than CPU especially for PostGIS.
>
>     Hope that helps,
>
>     Regina
>
>     http://postgis.us
>
>     *From:*postgis-users [mailto:postgis-users-bounces at lists.osgeo.org
>     <mailto:postgis-users-bounces at lists.osgeo.org>] *On Behalf Of *Cap
>     Diniz
>     *Sent:* Thursday, June 15, 2017 2:00 PM
>     *To:* postgis-users at lists.osgeo.org
>     <mailto:postgis-users at lists.osgeo.org>
>     *Subject:* [postgis-users] QGIS + PostGIS in production environment
>
>     Hello,
>
>     I am from the Cartographic Production Department from the
>     Brazilian Army, and we are trying to migrate from ArcGIS to
>     QGIS+PostGIS.
>
>     We are currently using QGIS 2.14.15, PostgreSQL 9.2, PostGIS 2.1
>     and we mostly do data digitizing over an orthoimage. We have about
>     50 simultaneous users in a single PostGIS server, but in different
>     databases and ports (we use 5 different ports in production, one
>     per project).
>
>     I would like to know if there are any tips to improve performance
>     and QGIS reliability, such as tuning PostgreSQL, changing
>     versions, operating systems, hardware recommendations, or QGIS
>     specifics.
>
>     Regards,
>
>     Felipe Diniz
>
>
>     _______________________________________________
>     postgis-users mailing list
>     postgis-users at lists.osgeo.org <mailto:postgis-users at lists.osgeo.org>
>     https://lists.osgeo.org/mailman/listinfo/postgis-users
>     <https://lists.osgeo.org/mailman/listinfo/postgis-users>
>
>
>
>
> _______________________________________________
> postgis-users mailing list
> postgis-users at lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20170616/3083c0c7/attachment.html>