Tile rendering is too slow, do we need to create additional indexes?

[timautin]

Hello,

I have set up a planet DB for tile rendering using osm-carto. The database is stored on a SSD, and the hardware is decent:

• Intel Core i7-8700 Hexa-Core (12 threads)
• 64 GB of RAM
• 2 TB SSD (for the DB)
• 2 * 6 TB HDD (set up in RAID 1, for the OS & the tile cache)

Import of the planet.dbf took 36h, creating a 900GB database. This seems normal.

Problem is that tiles rendering is way too slow: 20 seconds at zoom 13, progressively decreasing to 6 seconds at zoom 16. I have executed the indexes.sql script, and tuned PostgreSQL to my fit my hardware, as described on this post: https://wiki.openstreetmap.org/wiki/User:Species/PostGIS_Tuning .

Surprisingly, performance with render_list seems fine to me, I got those durations:

• zooms 0 - 4: 13mn
• zooms 5 - 6: 15mn
• zoom 7: 13mn
• zoom 8: 37mn
• zoom 9: 1h 29mn
• zoom 10: 5h 30mn
• zoom 11: 16h 30mn
• zoom 12: had to reboot so I stopped it, after 38h

Are we supposed to create additional indexes to get usable performance (I hopped that a zoom 13 metatile could take something like 1 second to get rendered)? If so, could the indexes.sql be updated accordingly?

If not, what could I be doing wrong?

Thanks for reading!

[kocio-pl]

It's a follow up to:

https://help.openstreetmap.org/questions/65467/slow-rendering-openstreetmap-carto

[timautin]

Thanks kocio-pl! Indeed same problem, I hesitated before reporting an issue, but thought it could be useful if something is wrong.

I ran some interesting tests:

If I run this:
select name, way from planet_osm_polygon where osm_id = '199929368';

It answers this in 54 ms:

Hara Building | 01030...67AA2E0C07F14F41

If I run this:
select osm_id, way from planet_osm_polygon where name = 'Hara Building';

It answers this in 38 seconds:

199929368 | 01030...67AA2E0C07F14F41

If I run this:
select name from planet_osm_polygon where way = '01030...67AA2E0C07F14F41';

It answers in 5 minutes (!!!):

Hara Building

Explain analyse tells me that the planet_osm_polygon_name was used in the 2nd query, but not the last. I would have expected the opposite. Still 38 seconds using an index seems way too much, and I'd expect the last query to use the index and roughly take the same amount of time as the 1st.

A quick reminder, the planet_osm_polygon_name index is created by this command (from indexes.sql script):
CREATE INDEX planet_osm_polygon_name ON planet_osm_polygon USING GIST (way)

[kocio-pl]

You might look at these numbers (without_this_PR column): #3357 (comment)

[timautin]

@kocio-pl Thanks. I'm not sure to read the numbers correctly though: did it take 82sec to render one 2048*1024 tile at zoom level 8?? Do you have some information about the hardware used?

If I understood correctly the numbers, do you mean that the performances I get are what we should expect?

[kocio-pl]

Please ask these persons for details. I guess you should at least have some comparison.

[timautin]

I will, thanks. The thing I do not understand is the performance difference between render_list and on the fly rendering while browsing the map. Here's the output of zoom 11 render_list:

---

Total for all tiles rendered
Meta tiles rendered: Rendered 65536 tiles in 59641.14 seconds (1.10 tiles/s)
Total tiles rendered: Rendered 4194304 tiles in 59641.14 seconds (70.33 tiles/s)
Total tiles handled: Rendered 65536 tiles in 59641.14 seconds (1.10 tiles/s)

---

Zoom 11: min: 0.6 avg: 0.9 max: 664.3 over a total of 59639.4s in 65536 requests

---

It rendered 1.1 metatile per second, which is excellent. While browsing, rendering one zoom 11 metatile takes something like 25 seconds (which is roughly what user rrzefox experienced on the page you linked).

[timautin]

Oh well. I just ran again my tests, and it is fast now. I don't know what changed :@ . I rebooted the server a few times but I did it already before opening this issue. I didn't work on the database since then. I stopped a few processes, but htop wasn't reporting anything heavy for the server, at worst I had only 1-2 cores used, and RAM usage was low (20GB / 60GB).

Rendering a zoom 13 metatile takes around 2 seconds now, which is perfect :) .

The 3 sql queries now run in 0.072 ms, 4.5 s & 5 mn again. So no improvement on the 3rd query, but I guess that it is just because this is not how we are supposed to query PostGIS, which I have no experience with (yet :) ).

Sorry for the trouble, and sorry to be unable to bring an answer for those have perf problems as well :/

EDIT: it still takes some time to render zoom 13 tiles of big cities, like 10-20 seconds. But these tiles will be among the first to be rendered by users and will thus be cached, so that's not a problem. Country side tiles take 2-4 seconds.