[Tile-serving] Performance problem serving tiles from planet file

[Theodor Foerster]

Hi all,
I am currently setting up an OSM instance serving tiles based on the planet
file. The spec is:
- Ubuntu 14.04
- 32 GB RAM, 8 cores, 1 TB of virtual volume (500 MB/sec throughput)

I followed the script by http://opentileserver.org/

I witness utterly bad performance. Parts of the query take around 300+
seconds. Serving a tile takes approx. 20 minutes (1 tile!).

autovaccuum and fsync are turned off. The obvious performance measures have
been applied.

Any help on this issue will be highly appreciated!

Cheers Theodor


log snippet:
2017-01-02 15:29:01 UTC LOG:  00000: execute <unnamed>: SELECT
ST_AsBinary("way") AS geom,"type" FROM ( SELECT way, way_area AS area,
COALESCE(landuse, leisure, "natural", highway, amenity, tourism) AS type
          FROM planet_osm_polygon
          WHERE way_area > 100000
          ORDER BY way_area DESC
        ) AS data WHERE "way" && ST_SetSRID('BOX3D(-10644926.3071125
-626172.1357125007,626172.1357124997 10644926.30711249)'::box3d, 900913)
2017-01-02 15:29:01 UTC LOCATION:  exec_execute_message, postgres.c:1935
2017-01-02 15:37:40 UTC LOG:  00000: duration: 519101.014 ms
2017-01-02 15:38:40 UTC LOG:  00000: execute <unnamed>: SELECT
ST_AsBinary("way") AS geom FROM ( SELECT way, "natural" AS type, way_area
AS area
          FROM planet_osm_polygon
          WHERE ("natural" IN ('water', 'pond')
            OR waterway IN ('basin', 'canal', 'mill_pond', 'pond',
'riverbank', 'stream'))
            AND way_area > 10000
        ) AS data WHERE "way" && ST_SetSRID('BOX3D(-10644926.3071125
-626172.1357125007,626172.1357124997 10644926.30711249)'::box3d, 900913)
2017-01-02 15:38:40 UTC LOCATION:  exec_execute_message, postgres.c:1935
2017-01-02 15:45:33 UTC LOG:  00000: duration: 412829.916 ms
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.openstreetmap.org/pipermail/tile-serving/attachments/20170102/3d5678dc/attachment.html>