QGIS + PostGIS in production environment

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

QGIS + PostGIS in production environment

Cap Diniz
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: QGIS + PostGIS in production environment

Regina Obe-2

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/

, 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:[hidden email]] On Behalf Of Cap Diniz
Sent: Thursday, June 15, 2017 2:00 PM
To: [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: QGIS + PostGIS in production environment

George Silva
In reply to this post by Cap Diniz
Hello Felipe,

My name is George and I'm also from Brazil.

From the top of my head here, I have a few questions for you:

1. Why are you using different ports for each database? Does this mean that you are using one database cluster for each database? If so, that might not be good. PostgreSQL is capable of holding multiple databases in the same cluster.

2. You might benefit from a connection pooler, such as pgpool. QGIS makes a few connections for each user with the database. Each open connection takes RAM memory from the server, which may take things slow after a while. The connection pooler will reuse the same available connections, saving memory and time when users need to connect to the database.

3. Your servers are using older versions of PostgreSQL and PostGIS. There is a lot of new and neat stuff on newer versions. Be careful with the upgrade, if you're going to do it.

4. Tune your database. This sort of database has specific needs. I don't have a link right now, but look for "tuning PostGIS". There was an article by Boundless that had a lot of information on tuning PostGIS. Important thing here: measure first, tune, measure, tune, etc.

If you need further assistance, let me know!

Thanks and good luck!

On Thu, Jun 15, 2017 at 3:00 PM, Cap Diniz <[hidden email]> wrote:
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



--
George R. C. Silva
Sigma Geosistemas LTDA
----------------------------

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: QGIS + PostGIS in production environment

Cap Diniz
In reply to this post by Regina Obe-2
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).




On Thu, Jun 15, 2017 at 3:38 PM, Regina Obe <[hidden email]> 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/

, 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:[hidden email]] On Behalf Of Cap Diniz
Sent: Thursday, June 15, 2017 2:00 PM
To: [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: QGIS + PostGIS in production environment

Richard Greenwood
On Thu, Jun 15, 2017 at 1:58 PM, Cap Diniz <[hidden email]> wrote:

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.

Why do you have some many different databases? I don't know what impact it would have on performance, but it seems like it would me challenging to manage.

Rich

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: QGIS + PostGIS in production environment

George Silva
I would not split each toposheet in a single database.

You could divide them using schemas or partitioned tables. With partitioned tables PostgreSQL is smart enough to query a single toposheet at the time, perhaps giving you a boost on the server.

The slow saving times might be mitigated by using the pooler and tuning.

First of all, you should plan an update on your versions.

The second step I would do is to tune the db.

We have a simple open source project called cartohelper (gitlab.sigmageosistemas.com.br) that automates a lot of things for large scale cartography. Validations, issues, etc, but its for an outdated qgis.

That might give you some ideas on how to organize your database.

Em 15 de jun de 2017 11:32 PM, "Richard Greenwood" <[hidden email]> escreveu:
On Thu, Jun 15, 2017 at 1:58 PM, Cap Diniz <[hidden email]> wrote:

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.

Why do you have some many different databases? I don't know what impact it would have on performance, but it seems like it would me challenging to manage.

Rich

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users

_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users
Reply | Threaded
Open this post in threaded view
|

Re: QGIS + PostGIS in production environment

Bo Victor Thomsen
In reply to this post by Cap Diniz

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




On Thu, Jun 15, 2017 at 3:38 PM, Regina Obe <[hidden email]> 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/

, 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:[hidden email]] On Behalf Of Cap Diniz
Sent: Thursday, June 15, 2017 2:00 PM
To: [hidden email]
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
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users



_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users


_______________________________________________
postgis-users mailing list
[hidden email]
https://lists.osgeo.org/mailman/listinfo/postgis-users