[prev in list] [next in list] [prev in thread] [next in thread]
List: postgis-users
Subject: Re: [postgis-users] QGIS + PostGIS in production environment
From: Bo Victor Thomsen <bo.victor.thomsen () gmail ! com>
Date: 2017-06-16 14:14:17
Message-ID: 8c5c6acc-1a5a-76f2-eedd-10746be9c340 () gmail ! com
[Download RAW message or body]
[Attachment #2 (multipart/alternative)]
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@pcorp.us
> <mailto:lr@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@lists.osgeo.org
> <mailto:postgis-users-bounces@lists.osgeo.org>] *On Behalf Of *Cap
> Diniz
> *Sent:* Thursday, June 15, 2017 2:00 PM
> *To:* postgis-users@lists.osgeo.org
> <mailto:postgis-users@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@lists.osgeo.org <mailto:postgis-users@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@lists.osgeo.org
> https://lists.osgeo.org/mailman/listinfo/postgis-users
[Attachment #5 (text/html)]
<html>
<head>
<meta content="text/html; charset=utf-8" http-equiv="Content-Type">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<p>You mentioned, that you didn't tune Postgres at all ? As in not
changing any memory related parameters in postgresql.conf ??</p>
<p>If that's the case (and I find it unlikely..) I highly recommend
that you take a look at the following http pages:</p>
<p><a class="moz-txt-link-freetext" \
href="https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server">https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server</a>
or: <a class="moz-txt-link-freetext" \
href="http://pgtune.leopard.in.ua/">http://pgtune.leopard.in.ua/</a></p> <p>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.</p>
<p>Regards <br>
</p>
<p>Bo Victor Thomsen</p>
<p>AestasGIS</p>
<p>Denmark<br>
</p>
<p><br>
</p>
<p> <br>
</p>
<p><br>
</p>
<br>
<div class="moz-cite-prefix">Den 15/06/17 kl. 21:58 skrev Cap Diniz:<br>
</div>
<blockquote
cite="mid:CAL0H4DdA2tCJsMJT-tSHd9XeL9KfeqrDxjH3VA7OeY7TnsWUhg@mail.gmail.com"
type="cite">
<div dir="ltr">Thanks for the responses so far!
<div><br>
</div>
<div>Adding a bit more information.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div>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.</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>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.</div>
<div>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).</div>
<div><br>
</div>
<div><br>
</div>
<div><br>
</div>
<div>[1] <a moz-do-not-send="true"
href="https://i.dell.com/sites/doccontent/shared-content/data-sheets/en/Documents/Dell-PowerEdge-R430-Spec-Sheet.pdf"
target="_blank">https://i.dell.com/sites/<wbr>doccontent/shared-content/<wbr>data-sheets/en/Documents/Dell-<wbr>PowerEdge-R430-Spec-Sheet.pdf</a></div>
</div>
<div class="gmail_extra"><br>
<div class="gmail_quote">On Thu, Jun 15, 2017 at 3:38 PM, Regina
Obe <span dir="ltr"><<a moz-do-not-send="true"
href="mailto:lr@pcorp.us" target="_blank">lr@pcorp.us</a>></span>
wrote:<br>
<blockquote class="gmail_quote" style="margin:0 0 0
.8ex;border-left:1px #ccc solid;padding-left:1ex">
<div link="#0563C1" vlink="#954F72" lang="EN-US">
<div class="m_-8708785603511155327WordSection1">
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Felipe,</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Are
you having problems currently or you just asking a
general question for future consideration?</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Your
PostgreSQL and PostGIS are pretty old.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">PostgreSQL
9.2 is reaching end of life in a couple of months -
<a moz-do-not-send="true"
href="https://www.postgresql.org/support/versioning/"
\
target="_blank">https://www.postgresql.org/<wbr>support/versioning/</a> </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">,
so you should probably upgrade that soon to
something like PostgreSQL 9.6, or 10 when it comes
out in Sept/October.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">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.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">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. </span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">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.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">That
said questions on OS/ Hardware require more
consideration than performance. A lot these days is
just your comfortability with these things.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">For
PostgreSQL, whatever you do, you'll probably want
SSD disks and RAM tends to be more important than
CPU especially for PostGIS.</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Hope
that helps,</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Regina</span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><a
moz-do-not-send="true" href="http://postgis.us"
target="_blank">http://postgis.us</a></span></p>
<p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal"><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> \
</span></p> <p class="MsoNormal" style="margin-left:.5in"><b><span
\
style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span
style="font-size:11.0pt;font-family:"Calibri",sans-serif">
postgis-users [mailto:<a moz-do-not-send="true"
href="mailto:postgis-users-bounces@lists.osgeo.org"
target="_blank">postgis-users-bounces@<wbr>lists.osgeo.org</a>]
<b>On Behalf Of </b>Cap Diniz<br>
<b>Sent:</b> Thursday, June 15, 2017 2:00 PM<br>
<b>To:</b> <a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org"
target="_blank">postgis-users@lists.osgeo.org</a><br>
<b>Subject:</b> [postgis-users] QGIS + PostGIS in
production environment</span></p>
<div>
<div class="h5">
<p class="MsoNormal" style="margin-left:.5in"> </p>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt">Hello,</span></p>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt"> </span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt">I am from the
Cartographic Production Department from the
Brazilian Army, and we are trying to migrate
from ArcGIS to QGIS+PostGIS.</span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt"> </span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt">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).</span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt"> </span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt">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.</span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt"> </span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt"> </span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt">Regards,</span></p>
</div>
<div>
<p class="MsoNormal" style="margin-left:.5in"><span
style="font-size:9.5pt">Felipe Diniz</span></p>
</div>
</div>
</div>
</div>
</div>
</div>
<br>
______________________________<wbr>_________________<br>
postgis-users mailing list<br>
<a moz-do-not-send="true"
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a><br>
<a moz-do-not-send="true"
href="https://lists.osgeo.org/mailman/listinfo/postgis-users"
rel="noreferrer" \
target="_blank">https://lists.osgeo.org/<wbr>mailman/listinfo/postgis-users</a><br> \
</blockquote> </div>
<br>
</div>
<br>
<fieldset class="mimeAttachmentHeader"></fieldset>
<br>
<pre wrap="">_______________________________________________
postgis-users mailing list
<a class="moz-txt-link-abbreviated" \
href="mailto:postgis-users@lists.osgeo.org">postgis-users@lists.osgeo.org</a> <a \
class="moz-txt-link-freetext" \
href="https://lists.osgeo.org/mailman/listinfo/postgis-users">https://lists.osgeo.org/mailman/listinfo/postgis-users</a></pre>
</blockquote>
<br>
</body>
</html>
[Attachment #6 (text/plain)]
_______________________________________________
postgis-users mailing list
postgis-users@lists.osgeo.org
https://lists.osgeo.org/mailman/listinfo/postgis-users
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic