<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>