poor performance: Mapnik2 + Spatialite

[Ludwig Meyerhoff]

Hallo!

I am currently setting up a Tilestache + Mapnik + Spatialite + OSM
environment. After several weeks of fiddling around with the most
different database structures and the styles, I finally have a
configuration which does neither abort nor crash (table mismatch in
SELECT statements, case sensitive tags in datasource.xml when defining
the extent, ...).

But my current setup has a very poor performance!
Rendering a simple tile at zoomlevel 1
(http://localhost:8080/example/1/1/1.png) takes around about 40s!

From the stats log, I can see Mapnik performs 81 distinct SELECT
queries on the database, even if at this zoom level there should not be
any layer active at all.


Do you have any suggestion on any optimizations or configurations I can
make?



Attached you can find my datasource configuration as well as the logfile
for rendering the /1/1/1.png and the 14/8644/5291.png.
Furthermore, you can find the Full SQL statement as returned by an error
message.


RENDER TIME FOR 1/1/1.png: 40s
COUNT FEATURE QUERIES FOR 1/1/1.png: 81

RENDER TIME FOR 14/8644/5291.png: 42s
COUNT FEATURE QUERIES FOR 14/8644/5291.png: 81

MAPNIK VERSION: 2.1.0 (built with stats logging)
DATA SOURCE: osm2spatialite 0.3




Saluti!

Ludwig

[Anne]

Hi,

I'm not familiar with the mapnik logfile, and I have been using Postgresql for my mapnik/OSM setup.  But I have recently been working on improving the render speed in my set up and I have learned a few things you might find useful.

I have found this page useful: https://github.com/mapnik/mapnik/wiki/OptimizeRenderingWithPostGIS

In the "Optimize Rendering With PostGIS" link above, you will notice that mapnik adds a geographic bounding box to the query to limit the amount of data selected.

 "way" && SetSRID('BOX3D(-39135.75848201022 6222585.598639628,665307.8941941741 6927029.251315812)'::box3d,900913)

It appears that your query (error-query.txt) contains nested subqueries.  I found that if I am using a SELECT statement that is complex and has nested subqueries, then the mapnik applied bounding box doesn't reduce the number of rows on the inner query, (as one would expect).   Rather, the inner subquery executes a full table scan --- OUCH!

I got around this by including the "way && ST_SetSRID(...)" as part of the where clause in the innermost subquery of my select query.   This then uses the index (fast look up) on the SRID for each entry in the table to reduce the number of rows retrieved first by the geographic extent of the map being drawn.    You could also possible create other indexes on the columns you are including in the "where" part of your query.

Also, check out using the "EXPLAIN" and "ANALYZE" SQL commands to understand what SQLLite is doing with the queries you are submitting.

I haven't experimented with it, but I expect mapnik will execute the query in the Layer-Datasource every time, unless the Layer is disabled using the minzoom and maxzoom parameters on the Layer section.  If you are just using the minzoom and maxzoom parameters for the Rule section in the XML, the Layer-Datasource query probably still executes, even if the result is sort of a no-operation, due to all the Rules rejecting the data returned.

Hope this helps.

Anne

[Jukka Rahkonen]

Hi,

For comparison, here is some log from a rendering test with Mapserver 6.2
and GDAL 1.10 today. Rendering a 931x705 pixel png image combined from 9
OSM layers took 0.18 seconds at this scale. There is nothing fundamental
that prevents Mapnik from being as fast or faster than Mapserver but the
current Spatialite driver and SQL queries must be far from optimal.

http://localhost:8060/cgi-bin/mapserv.exe?
map=/ms4w/apps/osm-uusi.map&
REQUEST=GetMap&
SERVICE=WMS&
VERSION=1.1.1&
WIDTH=931&
HEIGHT=705&
LAYERS=default&
TRANSPARENT=TRUE&
FORMAT=image%2Fpng&
BBOX=2652572.3240984855,8732712.011897746,2656217.5584801985,8735472.366612148&
SRS=EPSG:3857&
STYLES=

At this scale none of the layers is especially slow.
[Thu May 02 16:29:47 2013].107000 msDrawMap(): rendering using
outputformat named png (AGG/PNG).
[Thu May 02 16:29:47 2013].107000 msDrawMap(): WMS/WFS set-up and query,
0.000s
[Thu May 02 16:29:47 2013].125000 msDrawMap(): Layer 0 (waterarea4), 0.018s
[Thu May 02 16:29:47 2013].130000 msDrawMap(): Layer 10 (waterways15), 0.005s
[Thu May 02 16:29:47 2013].140000 msDrawMap(): Layer 24 (landuse15), 0.010s
[Thu May 02 16:29:47 2013].144000 msDrawMap(): Layer 39
(transport_areas15), 0.004s
[Thu May 02 16:29:47 2013].146000 msDrawMap(): Layer 58 (places15), 0.002s
[Thu May 02 16:29:47 2013].166000 msDrawMap(): Layer 72 (roads15), 0.020s
[Thu May 02 16:29:47 2013].196000 msDrawMap(): Layer 76 (buildings15), 0.030s
[Thu May 02 16:29:47 2013].202000 msDrawMap(): Layer 85 (aeroways15), 0.006s
[Thu May 02 16:29:47 2013].207000 msDrawMap(): Layer 96 (railways15), 0.005s
[Thu May 02 16:29:47 2013].207000 msDrawLabelCache():
labelcache_map_edge_buffer = 10
[Thu May 02 16:29:47 2013].212000 msDrawMap(): Drawing Label Cache, 0.005s
[Thu May 02 16:29:47 2013].212000 msDrawMap() total time: 0.105s
[Thu May 02 16:29:47 2013].286000 msSaveImage(stdout) total time: 0.074s
[Thu May 02 16:29:47 2013].286000 mapserv request processing time
(msLoadMap not incl.): 0.180s
[Thu May 02 16:29:47 2013].286000 msFreeMap(): freeing map at 004A8FD8.

-Jukka Rahkonen-

> --
> You received this message because you are subscribed to the Google Groups
> "mapnik" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to mapnik+un...@googlegroups.com.
> For more options, visit https://groups.google.com/groups/opt_out.
>
>
>

[Alex Mandel]

I'll also point out that Spatialite unlike Postgis does not use spatial
indexes automatically. You have to write queries specifically to take
advantage of them. I have not looked at if mapnik takes advantage of these.

Second example on this page shows an example
http://www.gaia-gis.it/spatialite-2.4.0-4/spatialite-cookbook/html/neighbours.html

If you have more questions feel free to jump on the spatialite mailing list.

Enjoy,
Alex

[Jukka Rahkonen]

Hi,

Spatialite is at version 4.0 nowadays and even the spatial index system is
basically the same, in simple cases it is possible to make the driver to
utilise spatial index so that it is transparent to user. Even spatial
views which are correctly registered in Spatialite are usable. GDAL 1.10
does that pretty well and I guess that the easiest way to improve the
speed of Mapnik with Spatialite would be to read data through GDAL.

-Jukka Rahkonen-

Alex Mandel kirjoitti:

[Max Demars]

I dont understand what is the problem here. I have spatialite installed in a DEV system and tiles rendered by Mapnik are faster than with the PostGIS backend that is installed in PROD.

[Frederik Ramm]

Hi,

Is the amount of data in your database by any chance so small that it
fits into RAM on your development machine? There's no way you will get
PostGIS performance out of your Spatialite database on a large setup.

Bye
Frederik

--
Frederik Ramm ## eMail fred...@remote.org ## N49�00'09" E008�23'33"

[Max Demars]

Yes you are completely right, just forgot that the spatialite is loaded into RAM. Do you know if it could be viable to use Mapnik with PostGIS without caching tools like TilesCache. Is possible to have good performance for on the fly tiling with a good db tuning?

Frederik Ramm  ##  eMail fred...@remote.org  ##  N49�00'09" E008�23'33"

[Alex Mandel]

I believe TileCache and TileStache both allow you to set the cache
expiration as immediate. You could also use
https://github.com/mapnik/OGCServer which is WMS directly via mapnik,
course you'd have to make sure clients can't request zooms other than
the ones you want to serve.

Enjoy,
Alex

>> Frederik Ramm ## eMail fred...@remote.org <javascript:> ##

[Ludwig Meyerhoff]

Hallo!

Thank you all for the good suggestions.

I was on vacation the last week and will be in vacation for the next two
weeks, so I can not follow them until my return.

I will keep you updated as soon as I have some news.


Saluti!

Ludwig

> <http://localhost:8080/example/1/1/1.png>) takes around about 40s!

>
> From the stats log, I can see Mapnik performs 81 distinct SELECT
> queries on the database, even if at this zoom level there should not be
> any layer active at all.
>
>
> Do you have any suggestion on any optimizations or configurations I can
> make?
>
>
>
> Attached you can find my datasource configuration as well as the
> logfile
> for rendering the /1/1/1.png and the 14/8644/5291.png.
> Furthermore, you can find the Full SQL statement as returned by an
> error
> message.
>
>
> RENDER TIME FOR 1/1/1.png: 40s
> COUNT FEATURE QUERIES FOR 1/1/1.png: 81
>
> RENDER TIME FOR 14/8644/5291.png: 42s
> COUNT FEATURE QUERIES FOR 14/8644/5291.png: 81
>
> MAPNIK VERSION: 2.1.0 (built with stats logging)
> DATA SOURCE: osm2spatialite 0.3
>
>
>
>
> Saluti!
>
> Ludwig
>