I’ll look into this, I did not really perform any PG optimization for large dataset. Might be the game changer for this case.
···
2015-06-18 19:49 GMT+02:00 Simone Giannecchini <simone.giannecchini@anonymised.com>:
Ciao Simone,
I see two sources of problems:
-1- how do you configure the connections to the DB? I would configure the pool to have enough connections to cope with initial bursts of load
-2- Did you give enough memory to postgis
-3- Did you configure postgis for performance? Standard set up is not fit for being fast on large datasets (there is a ton of recipes on the web on how to improve PG performance changing shared buffer configs and so on)
Regards,
Simone Giannecchini
GeoServer Professional Services from the experts!
Visit http://goo.gl/it488V for more information.
Ing. Simone Giannecchini
@simogeo
Founder/Director
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 333 8128928
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.
On Thu, Jun 18, 2015 at 7:13 PM, simone gadenz <simone@anonymised.com> wrote:
Thanks for the feedbacks.
I have already clustered the points for region, province and municipalities in different PostGIS layers, that are displayed at smaller zoom levels.
The viewer display points at province scale and few hundreds points per map extent are rendered. I have not tried the Marlin renderer, I’ll give it a try.
What I do not understand is the reason for the response to be slow (10-15 second for the first tile to draw) for the layer first load, and then turning to fast rendering while panning on the same zoom level.
Also, I need to keep an on-the-fly reprojection from 4326 to Google Mercator, because the points are managed through an external application that use lat,lon. The icon used is a jpeg.
Do you think I might have a significant improving re-projecting the data on the DB? Using standard symbology might load faster?
Cheers.
Simone
|
Simone Gadenz
BEECOME S.R.L.
Via Sansedoni 7, 53100 SIENA - ITALY
Phone/Fax: +39 0577 588408
www.beecome.it
simone@anonymised.com… |
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Informativa Privacy - Ai sensi del D. Lgs n. 196/2003 (Codice Privacy) si precisa che le informazioni contenute in questo messaggio sono riservate e ad uso esclusivo del destinatario. Qualora il messaggio in parola Le fosse pervenuto per errore, La preghiamo di eliminarlo senza copiarlo e di non inoltrarlo a terzi, dandocene gentilmente comunicazione. Grazie.
Privacy Information - This message, for the D. Lgs n. 196/2003 (Privacy Code), may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.
|
On Jun 18, 2015, at 6:52 PM, Andrea Aime wrote:
Hi,
another thing that might speed up rendering many points is installing the Marlin renderer, if you haven’t
done so already.
I bet other optimizations can be made by changing the rendering code (assuming you have dynamic filters,
caching and pre-generalization might not be an option), but one would first have to do a profiling sessions
on the code, check the bottlenecks, and see what can be changed/improved to remove them
Cheers
Andrea
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
On Thu, Jun 18, 2015 at 6:13 PM, Jonathan Moules <J.Moules@anonymised.com> wrote:
Yep, you really don’t want to be rendering a million points at once.
Assuming you’re only displaying a subset (a few thousand), make sure you have a spatial index on your PostGis dataset, so that GeoServer can pick out the rights points that need rendering quickly.
From: Simone Giannecchini [mailto:simone.giannecchini@anonymised.com]
Sent: Thursday, June 18, 2015 3:57 PM
To: simone gadenz
Cc: GeoServer Mailing List List
Subject: Re: [Geoserver-users] Help with tuning geoserver for 1 million PostGIS table
Ciao Simone,
if you are render 1M points altogheter via WMS with no caching then I believe you might have problems anyway.
If the data is static you may want to use clustering (as in render a single point for neighbooring points depending on the resultion) + caching.
Rendering 1M points on the fly can be fast only if you have a ton of hw to spread the tiles generation, unless you fiddle with things like clustering, caching, sharding on so on.
Regards,
Simone Giannecchini
GeoServer Professional Services from the experts!
Visit http://goo.gl/it488V for more information.
Ing. Simone Giannecchini
@simogeo
Founder/Director
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 333 8128928
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.
On Thu, Jun 18, 2015 at 1:06 AM, simone gadenz <simone@anonymised.com.> wrote:
Hello!
Hope anybody can help in improving the response time for a WMS working on a PostGIS table with 1 million point spread around Italy. The viewer use leaflet and consumes the layer as WMS. The system was developer to display over a website but now I need to display over a 55 inch touch screen completely offline. I setup a Virtualbox machine and use that as a local server. The virtual machine uses 8Gb of the total 16GB, the processor is an i7.
Each point of the layer has the same symbol generated from an image icon. The layer is displayed for layers greater than 12 and the first tile load takes 12 to 15 seconds, then it gets quicker to represent tiles. The points are on a PostGIS layer, spatial index built.
The geometry of the original table is 4326, the display is over OSM.
I tried to follow some of the hints for improving the performance of the server, and it is better than the beginning, but I think there is still room for improvements.
Using precaching might become complicated because the map might request WMS with some filter expression during the requests on that layer.
Any help, idea or suggestion is welcome.
Cheers.
Simone
|
Simone Gadenz
BEECOME S.R.L.
Via Sansedoni 7, 53100 SIENA - ITALY
Phone/Fax: +39 0577 588408
www.beecome.it
simone@anonymised.com
|
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Informativa Privacy - Ai sensi del D. Lgs n. 196/2003 (Codice Privacy) si precisa che le informazioni contenute in questo messaggio sono riservate e ad uso esclusivo del destinatario. Qualora il messaggio in parola Le fosse pervenuto per errore, La preghiamo di eliminarlo senza copiarlo e di non inoltrarlo a terzi, dandocene gentilmente comunicazione. Grazie.
Privacy Information - This message, for the D. Lgs n. 196/2003 (Privacy Code), may contain confidential and/or privileged information. If you are not the addressee or authorized to receive this for the addressee, you must not use, copy, disclose or take any action based on this message or any information herein. If you have received this message in error, please advise the sender immediately by reply e-mail and delete this message. Thank you for your cooperation.
|
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
This message has been scanned for viruses by MailControl, a service from BlackSpider Technology
Click here to report this email as spam.
HR Wallingford and its subsidiaries uses faxes and emails for confidential and legally privileged business communications. They do not of themselves create legal commitments. Disclosure to parties other than addressees requires our specific consent. We are not liable for unauthorised disclosures nor reliance upon them.
If you have received this message in error please advise us immediately and destroy all copies of it.
HR Wallingford Limited
Howbery Park, Wallingford, Oxfordshire, OX10 8BA, United Kingdom
Registered in England No. 02562099
Geoserver-users mailing list
Geoserver-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/geoserver-users
–
==
GeoServer Professional Services from the experts! Visit
http://goo.gl/it488V for more information.
Ing. Andrea Aime
@geowolf
Technical Lead
GeoSolutions S.A.S.
Via Poggio alle Viti 1187
55054 Massarosa (LU)
Italy
phone: +39 0584 962313
fax: +39 0584 1660272
mob: +39 339 8844549
http://www.geo-solutions.it
http://twitter.com/geosolutions_it
AVVERTENZE AI SENSI DEL D.Lgs. 196/2003
Le informazioni contenute in questo messaggio di posta elettronica e/o nel/i file/s allegato/i sono da considerarsi strettamente riservate. Il loro utilizzo è consentito esclusivamente al destinatario del messaggio, per le finalità indicate nel messaggio stesso. Qualora riceviate questo messaggio senza esserne il destinatario, Vi preghiamo cortesemente di darcene notizia via e-mail e di procedere alla distruzione del messaggio stesso, cancellandolo dal Vostro sistema. Conservare il messaggio stesso, divulgarlo anche in parte, distribuirlo ad altri soggetti, copiarlo, od utilizzarlo per finalità diverse, costituisce comportamento contrario ai principi dettati dal D.Lgs. 196/2003.
The information in this message and/or attachments, is intended solely for the attention and use of the named addressee(s) and may be confidential or proprietary in nature or covered by the provisions of privacy act (Legislative Decree June, 30 2003, no.196 - Italy’s New Data Protection Code).Any use not in accord with its purpose, any disclosure, reproduction, copying, distribution, or either dissemination, either whole or partial, is strictly forbidden except previous formal approval of the named addressee(s). If you are not the intended recipient, please contact immediately the sender by telephone, fax or e-mail and delete the information in this message that has been received in error. The sender does not give any warranty or accept liability as the content, accuracy or completeness of sent messages and accepts no responsibility for changes made after they were sent or for other risks which arise as a result of e-mail transmission, viruses, etc.