Hi,

We have started to build a tile server. I have installed Mapnik and mod_tile on the server (Fedora 21, 64GB RAM, 8 Cores 2.5GHz) and the standard openstreetmap-carto stylesheet is used for rendering tiles.

The problem is, openstreetmap-carto stylesheet's quries are really slow on our database. Some of the queries take few seconds to complete!

Here are few examples queries from postgresql log file with a very long duration time: log file
And here is "Explain Analyze" output for the two most expensive queries explain analyze output

For some queries (for instance this one) the result set contains more than 120K records! does standard layout really need to fetch this many records to render some of the tiles?

Here are more details about the steps I took to build the map server:

I used this command to import data into database: osm2pgsql --create --slim --latlong --hstore --hstore-match-only --number-processes 4 --cache 20000 --style default.style --input-reader pbf planet-latest.osm.pbf

All the indexes are in place (I added few more indexes suggested here which helped a bit but still queries are too slow)

I also ran vacuum(analyze, verbose);

kernel.shmmax is 8589934592

In postgres.config file we have:
shared_buffers = 12GB
work_mem = 4096MB
maintenance_work_mem = 4096MB
wal_buffers = 16MB
checkpoint_segments = 512
checkpoint_completion_target = 0.9
cpu_tuple_cost = 0.05
autovacuum=on
autovacuum_vacuum_scale_factor = 0.05
autovacuum_analyze_scale_factor = 0.2
track_counts=on

I really appriciate any help!
/Reza

asked 10 Nov '15, 17:29

khamooshi's gravatar image

khamooshi
146101219
accept rate: 50%

edited 10 May '16, 10:09


I have a similar issue, it turned out that in the osm carto style the srid parameter is missing and mapnik run an expensive query to find it.

Adding the line "srid: 900913" under "osm2pgsql" section of project.yaml improved the performances dramatically.

permanent link

answered 15 Dec '15, 16:18

Stefano%20Salvador's gravatar image

Stefano Salv...
111
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×371
×309
×70
×52
×23

question asked: 10 Nov '15, 17:29

question was seen: 4,459 times

last updated: 10 May '16, 10:09

powered by OSQA