11 times slower than old style :(

[itscz-org]

Expected behavior

Rendering at the same or better speed level due to optimizations and newer base software than the older german render stack based on https://github.com/giggls/mapnik-german-l10n

Actual behavior

Two machines, identical hardware, same configuration of base software (postgres etc.)

First with Debian 9, Postgres 9.6, Europe OSM Data, Mapnik 3, renderd, mod_tile and the old style
Second with Debian 11, Postgres 13, Europe OSM Data, Mapnik 3.1, renderd, mod_tile and this newer style

Testing with rendering of tile: 11/1099/672.png (renderd: Rendering projected coordinates 11 1096 672 -> 1408887.305353|6731350.458909 1565430.339281|6887893.492837 to a 8 x 8 tile)

First machine with production load renders the corresponding meta tile in 38 seconds
Second machine idling out of production renders the meta tile 427 seconds :(

Any advice?

[itscz-org]

I monitored system resources during the rendering and can see the following:

Old machine utizilies 100% one cpu core with postgres while rendering, then shortly renderd, done
New machine utizilies 100% hdd read using 3 postgres processes while rendering (SATA SSD RAID-0).

Seems it has so scan the whole database.

Just saw there is another indexes.sql than upstream (which doesn't work here), https://github.com/giggls/openstreetmap-carto-de/blob/master/indexes-hstore.sql - i will try that.

[giggls]

Will probably need additional indexes. Sorry, I can not really help here.

Did you apply indexes-hstore.sql?

Should even run faster as l10n is no longer done during rendering.

[itscz-org]

Applying this file helped. The example meta tile is now rendered in ~60s, which is more suitable but still slower than the old render stack.

[giggls]

There might well be more potential for query-optimisation. Send patches if you have some.

[itscz-org]

I am not fit in postgresql and i hate it.

Just did a "make test" on old and new style. It's 10.82s average per tile in old style and 12.63s in new style. I may use that but i expected something better. That is also a lot slower than the times mentioned here: #40 (comment) - maybe you can post what machine/settings/dataset you use to reach these times.

[giggls]

What do you expect from me as a reaction to such a comment?
Unfortunately I have no idea for a polite one :(

[itscz-org]

I thought the importlevel l10n was invented to be faster than on rendering level, now i get slower results in a good testing scenario because i have identical machines where one was updated and other not. If this is not interesting for you OK, and if it looks normal and fine to you that the newer is slower than the older OK. I thought you may have at least an advice as you are interested in a good and fast working style in what could possibly went wrong. You are digged deeper in this material than i am. I have no knowledge of postgresql or carto or whatsoever. I am just asking if the results of my installation are normal behaviour or not.

[giggls]

Well PostgreSQL query-optimization is actually a scope way beyond the purpose of this issue tracker here.

Are the PostgreSQL Servers configured in comparable ways regarding important performance stuff (sync settings, memory, ...)?

Which database layout, carto-de and osml10n versions are you using on your old machine?

And most important: Which indexes do you have there compared to the new machine? Do they differ?

Increased style complexity (inherited from upstream carto) might also be an issue I can not do that much against.

I can currently only compare the version (unfortunately) still running at https://tile.openstreetmap.de (Debian 10, PostgreSQL 11, carto-de v4.24.0-de1 and old l10n 2.5.8) and my new approach and these do not differ that much as far as the database layout is concerned.

The main difference is that there are separate columns for names now while the old layout did generate them on the fly from hstore.

I am unsure if we really have regressions regarding the new approach here. I seriously doubt this.

If you want to help me to finding bottlenecks run make test on both machines and extract the sql-queries for those tiles which render slower on the new machine (enable query-log to get them).

We can then re-run these queries with explain analyze to check if they differ in Index use.

This is complicated stuff and it is not a good prerequisite that you hate PostgreSQL.

[imagico]

Another hint: Make sure you turn off JIT in PostgreSQL configuration. This is in the OSM-Carto installation instructions meanwhile (https://github.com/gravitystorm/openstreetmap-carto/blob/master/INSTALL.md) - it is not yet here. This can lead to quite significant performance differences in my experience.

[itscz-org]

@imagico Nice one. Disabling JIT brings average time consumption per tile in make test down to 6,58s which is double as fast.

@giggls Will reply with giving details later.

[itscz-org]

Now some comparisons (old => new)

• postgresql: 9.6 => 13
• osm2pgsql: 1.2.0 => 1.7.2-17-gce5feeeb
• Libosmium: 2.15.4 => 2.17.3
• Lua: 5.2.4 => 5.3.3
• carto_de: 4.3.0 => 5.5.1
• old l10n: can't really see a version number and i am no git crack, git status gives me "13da8e5" ?
• new l10n: can't really see a version number (cloned ~2weeks ago)
• renderd: can't see / determine any old version number (compiled), new one from debian bullseye package (0.5-2)
• mod_tile: same as renderd
• Apache: 2.4.25 => 2.4.54

Relevant changes i applied to both postgres instances (didn't compare stock settings):

max_connections = 1000
shared_buffers = 2GB
work_mem = 2GB
maintenance_work_mem = 2GB
fsync = off
synchronous_commit = off
full_page_writes = off
wal_buffers = 16MB
checkpoint_timeout = 10min
random_page_cost = 1.1
effective_cache_size = 4GB
autovacuum = off

full_page_writes / autovacuum is turned on after import. Of course now JIT disabled in new instance.

Old database structure (sorry for German)

 Schema |           Name            |   Typ   |  Eigentümer
--------+---------------------------+---------+---------------
 public | country_languages         | Tabelle | renderaccount
 public | country_osm_grid          | Tabelle | renderaccount
 public | planet_osm_hstore_line    | Tabelle | renderaccount
 public | planet_osm_hstore_point   | Tabelle | renderaccount
 public | planet_osm_hstore_polygon | Tabelle | renderaccount
 public | planet_osm_hstore_roads   | Tabelle | renderaccount
 public | spatial_ref_sys           | Tabelle | renderaccount

New one

 Schema |                Name                 |   Typ   | Eigentümer
--------+-------------------------------------+---------+------------
 public | external_data                       | Tabelle | _renderd
 public | icesheet_outlines                   | Tabelle | _renderd
 public | icesheet_polygons                   | Tabelle | _renderd
 public | ne_110m_admin_0_boundary_lines_land | Tabelle | _renderd
 public | planet_osm_hstore_line              | Tabelle | _renderd
 public | planet_osm_hstore_point             | Tabelle | _renderd
 public | planet_osm_hstore_polygon           | Tabelle | _renderd
 public | planet_osm_hstore_roads             | Tabelle | _renderd
 public | planet_osm_hstore_route             | Tabelle | _renderd
 public | simplified_water_polygons           | Tabelle | _renderd
 public | spatial_ref_sys                     | Tabelle | _renderd
 public | water_polygons                      | Tabelle | _renderd

It may however not worth digging this down further because it is fast enough now and faster than the old one. Just wanted to reply. I'm fine if you're like to close the issue now. But i highly would recommend mention these two things in INSTALL-de.md:

1. applying of indexes-hstore.sql
2. Disabling JIT

Thanks,
Chris

[giggls]

I already added a hint regarding indexes-hstore.sql to INSTALL-de.md and removed indexes.sql from the repository to avoid confusion.

From what I see it looks like you almost certainly did apply indexes-hstore.sql to your old database as well.

Try the following on both machines:

osm=# \d planet_osm_hstore_polygon

Regarding the JIT issue I did not yet stumble upon this myself as my machine do not yet run the new code.

I will need to update this Repository to upstream version 5.6 anyway and change the INSTALL-de.md accordingly.

Hopefully I will be able to migrate https://tile.openstreetmap.de to an ansible based setup in the foreseeable future.

[giggls]

Oh forget to mention that you do not want to disable autovacuum.
This is at least what I was told in a workshop at FOSSGIS years ago.

[itscz-org]

autovacuum is just off for osm2pgsql - should it be left on there also?

[giggls]

It makes sense to disable it during initial import but not afterwards and during incremental updates.

[itscz-org]

Ahh ok then its fine, i do not run incremental updates.

[itscz-org]

osm=# \d planet_osm_hstore_polygon

Old:

    Table "public.planet_osm_hstore_polygon"
  Column  |          Type           | Modifiers
----------+-------------------------+-----------
 osm_id   | bigint                  |
 layer    | integer                 |
 way_area | real                    |
 z_order  | integer                 |
 tags     | hstore                  |
 way      | geometry(Geometry,3857) |
Indexes:
    "planet_osm_hstore_polygon_osm_id_idx" btree (osm_id)
    "planet_osm_hstore_polygon_way_idx" gist (way)
Triggers:
    planet_osm_hstore_polygon_osm2pgsql_valid BEFORE INSERT OR UPDATE ON planet_osm_hstore_polygon FOR EACH ROW EXECUTE PROCEDURE planet_osm_hstore_polygon_osm2pgsql_valid()

New:

                    Tabelle »public.planet_osm_hstore_polygon«
  Spalte   |           Typ           | Sortierfolge | NULL erlaubt? | Vorgabewert
-----------+-------------------------+--------------+---------------+-------------
 osm_id    | bigint                  |              | not null      |
 way       | geometry(Geometry,3857) |              |               |
 tags      | hstore                  |              |               |
 layer     | integer                 |              |               |
 z_order   | integer                 |              |               |
 way_area  | real                    |              |               |
 name_l10n | text[]                  |              |               |
Indexe:
    "planet_osm_hstore_polygon_admin" gist (st_pointonsurface(way)) WHERE (tags -> 'name'::text) IS NOT NULL AND (tags -> 'boundary'::text) = 'administrative'::text AND ((tags -> 'admin_level'::text) = ANY (ARRAY['0'::text, '1'::text, '2'::text, '3'::text, '4'::text]))
    "planet_osm_hstore_polygon_military" gist (way) WHERE ((tags -> 'landuse'::text) = 'military'::text OR (tags -> 'military'::text) = 'danger_area'::text) AND (tags -> 'building'::text) IS NULL
    "planet_osm_hstore_polygon_name" gist (st_pointonsurface(way)) WHERE (tags -> 'name'::text) IS NOT NULL
    "planet_osm_hstore_polygon_nobuilding" gist (way) WHERE (tags -> 'building'::text) IS NULL
    "planet_osm_hstore_polygon_water" gist (way) WHERE ((tags -> 'waterway'::text) = ANY (ARRAY['dock'::text, 'riverbank'::text, 'canal'::text])) OR ((tags -> 'landuse'::text) = ANY (ARRAY['reservoir'::text, 'basin'::text])) OR ((tags -> 'natural'::text) = ANY (ARRAY['water'::text, 'glacier'::text]))
    "planet_osm_hstore_polygon_way_area_z10" gist (way) WHERE way_area > 23300::double precision
    "planet_osm_hstore_polygon_way_area_z6" gist (way) WHERE way_area > 5980000::double precision
    "planet_osm_hstore_polygon_way_idx" gist (way) WITH (fillfactor='100')

[giggls]

Quite surprising that this did actually work reasonably fast at all without special indexes.

[itscz-org]

I am surprised too ^^