I am using mapnik to generate tiles from a full planet osm2pgsql db, and am finding it extremely slow. It is taking minutes to generate a single tile. I generated full planet tiles for levels 0-7 in a about a week, but now I've narrowed it to a single medium-sized country for levels 8-16. I'm currently at level 11 and generating only about 4000 tiles in 24 hours, using 6 threads.

I am using someone else's planet db on a good-sized Ubuntu machine with plenty of cores and RAM. However, the postgres on this machine just has the default installed settings and was not tuned for the import (which took a few weeks). Unfortunately I cannot tune it myself, though I can ask for changes.

Any suggestions as to why the speed is so awful? Are there some specific postgres tuning recommendations for tile generation that would help? I have two other planet imports going on two other machines with postgres that was tuned for import, but the imports are not going to finish any time soon.

FOLLOWUP: This may have been due to using a database created by Nominatim's setup.php rather than a database created by using osm2pgsql in a straightforward way.

asked 12 Feb '13, 15:44

dhalbert's gravatar image

dhalbert
61338
accept rate: 0%

edited 26 Feb '13, 21:27


I'm not sure how long tiles should take for levels 0-7, but this sounds to me like an absence of GIST indexes on the geometry columns (or failure to update the stats associated with the indexes).

Whatever is the cause the way to diagnose the problem is to look at query execution plans. You should be able to turn query logging on for the PostgreSQL instance and grab a few representative queries. Using PGAdmin (or something similar) get the query execution plans for these queries and look at which indexes are being used. A typical mapnik tile will require 30+ individual queries so once you've worked out what the problem is for one the issue should be fine for all.

permanent link

answered 12 Feb '13, 17:03

SK53's gravatar image

SK53 ♦
24.0k46240378
accept rate: 21%

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:

×325
×250

question asked: 12 Feb '13, 15:44

question was seen: 3,798 times

last updated: 26 Feb '13, 21:27

powered by OSQA