0

I have my own tile server on Oracle Linux. My rendering is extremely slow. I rendered an 8x8 Z16 metatile that took 124 minutes to complete. Foreground Renderd put out:

renderd[5601]: DEBUG: Got incoming connection, fd 8, number 1
renderd[5601]: DEBUG: Got command RenderPrio fd(8) xml(default), z(16), x(17723), y(25170)
renderd[5601]: DEBUG: Connection 0, fd 8 closed, now 0 left
renderd[5601]: DEBUG: DONE TILE default 16 17720-17727 25168-25175 in 7474.548 seconds
debug: Creating and writing a metatile to /var/lib/mod_tile/default/16/0/70/82/53/128.meta

I have some PostgreSQL log statements on the order of 300,000 ms as in:

2013-07-15 18:31:04 EDT:::1(33790):user@osm_planet:[5610]:LOG:  duration: 340648.541 ms  execute <unnamed>: SELECT ST_AsBinary("way") AS geom,"amenity","ele","landuse","leisure","man_made","military","name","natural","place","point","shop","tourism","waterway" FROM (select way,aeroway,shop,access,amenity,leisure,landuse,man_made,"natural",place,tourism,NULL as ele,name,ref,military,waterway,historic,'no'::text as point
               from planet_osm_polygon
               where amenity is not null
                  or shop in ('supermarket','bakery','clothes','fashion','convenience','doityourself','hairdresser','department_store', 'butcher','car','car_repair','bicycle')
                  or leisure is not null
                  or landuse is not null
                  or tourism is not null
                  or "natural" is not null
                  or man_made in ('lighthouse','windmill')
                  or place='island'
                  or military='danger_area'
                  or historic in ('memorial','archaeological_site')
              ) as text WHERE "way" && ST_SetSRID('BOX3D(-9202100.961200738 4642173.601817815,-9196597.495164203 4647677.067854352)'::box3d, 900913)

2013-07-15 18:36:25 EDT:::1(33790):user@osm_planet:[5610]:LOG:  duration: 320782.717 ms  execute <unnamed>: SELECT ST_AsBinary("way") AS geom,"name","way_area" FROM (select way,way_area,name
               from planet_osm_polygon
               where name is not null
                 and (waterway is null or waterway != 'riverbank')
                 and place is null
               order by way_area desc
              ) as text WHERE "way" && ST_SetSRID('BOX3D(-9202100.961200738 4642173.601817815,-9196597.495164203 4647677.067854352)'::box3d, 900913)

2013-07-15 18:41:49 EDT:::1(33790):user@osm_planet:[5610]:LOG:  duration: 324224.378 ms  execute <unnamed>: SELECT ST_AsBinary("way") AS geom FROM (select way,way_area,name,boundary from planet_osm_polygon where boundary='national_park' and building is null) as boundary WHERE "way" && ST_SetSRID('BOX3D(-9202100.961200738 4642173.601817815,-9196597.495164203 4647677.067854352)'::box3d, 900913)

I am using Mapnik 2.1.0, PostgreSQL 9.2, PostGIS 2. I have a 16GB instance in my company's virtual environment.

I tried adjusting my postgres.conf settings with pg_tune, but no luck. Something must be really wrong for 1 metatile to take 2 hours to render LOL. Any thoughts at all????

In Response to Frederik's answer: I think you nailed it. On my sick tile server I ran EXPLAIN and got back:

Seq Scan on planet_osm_polygon  (cost=0.00..6997002.90 rows=2 width=362)
   Filter: ((way && '010300002031BF0D0001000000050000000D28C29E368D61C1DE2E84665FB551410D28C29E368D61C1C8B95744BFBA51419962D8AF868A61C1C8B95744BFBA51419962D8AF868A61C1DE2E84665FB551410D28C29E368D61C1DE2E84665FB55141'::geometry) AND ((ame
nity IS NOT NULL) OR (shop = ANY ('{supermarket,bakery,clothes,fashion,convenience,doityourself,hairdresser,department_store,butcher,car,car_repair,bicycle}'::text[])) OR (leisure IS NOT NULL) OR (landuse IS NOT NULL) OR (tourism IS NOT
NULL) OR ("natural" IS NOT NULL) OR (man_made = ANY ('{lighthouse,windmill}'::text[])) OR (place = 'island'::text) OR (military = 'danger_area'::text) OR (historic = ANY ('{memorial,archaeological_site}'::text[]))))
(2 rows)

On a different tile server that is working better I ran the same query and got back:

 Index Scan using planet_osm_polygon_index on planet_osm_polygon  (cost=0.00..9.44 rows=1 width=1133)
   Index Cond: (way && '010300002031BF0D0001000000050000000D28C29E368D61C1DE2E84665FB551410D28C29E368D61C1C8B95744BFBA51419962D8AF868A61C1C8B95744BFBA51419962D8AF868A61C1DE2E84665FB551410D28C29E368D61C1DE2E84665FB55141'::geometry)
   Filter: ((amenity IS NOT NULL) OR (shop = ANY ('{supermarket,bakery,clothes,fashion,convenience,doityourself,hairdresser,department_store,butcher,car,car_repair,bicycle}'::text[])) OR (leisure IS NOT NULL) OR (landuse IS NOT NULL) OR
(tourism IS NOT NULL) OR ("natural" IS NOT NULL) OR (man_made = ANY ('{lighthouse,windmill}'::text[])) OR (place = 'island'::text) OR (military = 'danger_area'::text) OR (historic = ANY ('{memorial,archaeological_site}'::text[])))
(3 rows)

To my untrained eyes, this looks like my indexes are messed up on the slow server? Here is the exact osm2pgsql statement I ran, do you see anything wrong with it? Or do you think something just got arbitrarily messed up during the import and I should just try again?

time /tiles/osm2pgsql/osm2pgsql-master/osm2pgsql -S /tiles/osm2pgsql/osm2pgsql-master/default.style --flat-nodes /tiles/flat --number-processes 4 --cache-strategy dense --slim -d osm_planet -C 8192 /tiles/planet-1304121/planet-latest.osm.pbf

asked 16 Jul '13, 17:30

maw269's gravatar image

maw269
1157714
accept rate: 0%

edited 16 Jul '13, 19:07


One Answer:
4

I'd normally say the performance of your virtual disk must be the culprit and it is quite possible to achieve rendering times in excess of one hour for a z7 or z8 tile if your disk is very slow, but a z16 tile - unlikely. I suggest you check whether your geometry indexes have been created correctly, by running one of the above queries past "explain" and see if it uses the index at all.

permanent link

answered 16 Jul '13, 18:05

Frederik%20Ramm's gravatar image

Frederik Ramm ♦
73.3k866641137
accept rate: 24%

I put my response in my question. I think you nailed it. Any thoughts?

(16 Jul '13, 19:09) maw269
1

Check whether the index actually exists (start the psql shell and type \d planet_osm_polygon). If the index does not exist: A standard osm2pgsql run will always create these indexes, so either the import was aborted or you accidentally deleted the indexes. Re-create them with "create index planet_osm_polygon_index on planet_osm_polygon using gist(way)". Same for line, road, point. If the index does exist, then for some reason PostgreSQL seems to think it isn't worth using; a simple "analyze" command might fix that (takes a couple hours though).

(16 Jul '13, 20:13) Frederik Ramm ♦
1

Perfect, I did a "\d" from the psql shell, and I am definitely missing the planet_osm_polygon_index. I am currently creating the index with "CREATE INDEX planet_osm_polygon_index ON planet_osm_polygon USING gist (way);" I compared a \d to a working tile server I have using PostgreSQL 8.4 (PostGIS 1.5), and noticed my broken 9.2 osm_planet DB also doesn't have the "geometry_columns_pg" constraint nor the "planet_osm_ways_nodes" index. Should I create these also on my broken 9.2 DB?

(16 Jul '13, 21:05) maw269
1

Problem Fixed. Now rendering Z15 8x8 metatiles in around 1 second. Thanks Frederik.

(16 Jul '13, 22:37) maw269

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:

×322
×175
×150
×91
×74

question asked: 16 Jul '13, 17:30

question was seen: 10,462 times

last updated: 16 Jul '13, 22:37

powered by OSQA