[postgis-users] QGIS + PostGIS in production environment
Bo Victor Thomsen
bo.victor.thomsen at gmail.com
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>
More information about the postgis-users
mailing list