renderd throws reason: Postgis Plugin: bad connection error when rendering with render_list

[a14stoner]

I set up mod_tile together with renderd. Rendering the map works well. When manually opening the map it renders non existing tiles fast an seamless. I already prerenderd my map from Z0 to Z17 (actually only half of Z17).

When going to new spots on my map where the tiles are not prerenderd the tiles get generated without any problem.

When trying to continue to render the whole Z17 (or Z18) i get the following error in the renderd.log:

** (process:1911): ERROR **: 14:25:34.040: failed to render TILE default 18 134760-134767 92840-92847

** (process:1911): ERROR **: 14:25:34.044:   reason: Postgis Plugin: bad connection

** (process:1911): ERROR **: 14:25:46.972: failed to render TILE default 17 70344-70351 45848-45855

** (process:1911): ERROR **: 14:25:46.972:   reason: Postgis Plugin: bad connection

** (process:1911): ERROR **: 14:25:46.972: failed to render TILE default 18 134760-134767 93080-93087

** (process:1911): ERROR **: 14:25:46.973:   reason: Postgis Plugin: bad connection

** (process:1911): ERROR **: 14:25:46.973: failed to render TILE default 18 134760-134767 93112-93119

** (process:1911): ERROR **: 14:25:46.972: failed to render TILE default 18 134760-134767 93000-93007

** (process:1911): ERROR **: 14:25:46.973:   reason: Postgis Plugin: bad connection

** (process:1911): ERROR **: 14:25:46.973:   reason: Postgis Plugin: bad connection

** (process:1911): ERROR **: 14:25:46.976: failed to render TILE default 18 134760-134767 93032-93039

** (process:1911): ERROR **: 14:25:46.976:   reason: Postgis Plugin: bad connection

since the rendering works when i manually go to new spots on the map i really dont know what the error is. also i dont know how to enable the debug logs on renderd... maybe some more information is logged there.

here are my configurations:

renderd.conf:

[renderd]
pid_file=/var/run/renderd/renderd.pid
stats_file=/var/run/renderd/renderd.stats
socketname=/var/run/renderd/renderd.sock
num_threads=32
tile_dir=/usr/local/renderd/cache

[mapnik]
plugins_dir=/usr/lib/mapnik/3.1/input
font_dir=/usr/share/fonts
font_dir_recurse=true

[default]
URI=/tile/
XML=/usr/local/mapnik/openstreetmap-carto/CARTO_DE_AT_CH.xml
HOST=localhost
TILEDIR=/usr/local/renderd/cache

[topo]
URI=/topo/
XML=/usr/local/mapnik/OpenTopoMap/mapnik/opentopomap.xml
HOST=localhost
TILEDIR=/usr/local/renderd/cache

the start of the .mml file looks like this:

# Various parts to be included later on
_parts:
  srid: &srid "3857"
  srs: &srs "+proj=merc +a=6378137 +b=6378137 +lat_ts=0.0 +lon_0=0.0 +x_0=0.0 +y_0=0.0 +k=1.0 +units=m +nadgrids=@null +wktext +no_defs +over"
  world: &world
    - -180
    - -85.05112877980659
    - 180
    - 85.05112877980659
  dach: &dach
    - 562163
    - 5733998
    - 1975921
    - 7424221
  # Extents are used for tilemill, and don't actually make it to the generated XML
  extents: &extents
    extent: *dach
    srs-name: *srid
    srs: *srs
  osm2pgsql: &osm2pgsql
    type: "postgis"
    geometry_field: "way"
    key_field: ""
    estimate_extend: "false"
    srid: *srid
    host: "POSTGIS"
    port: "5432"
    extent: *dach
    user: "gis"
    dbname: "DE_AT_CH_20240911"
    max_size: 100
    max_async_connection: 4

maybe if you have any idea what i did wrong... i would be very happy since I found nothing similar in any other issue (or I am just a bad googler ;) )

[hummeltech]

Hmm, yeah I'm not sure exactly what is causing that, but I suspect renderd is not the cause. What version of PostgreSQL are you using?

You might try removing max_async_connection: 4, which seems to have caused trouble for some people in the past: https://github.com/hotosm/HDM-CartoCSS/commit/8107437af439bfcfbdefdc1565a6055751a28c0c#diff-4f3430205157bae48d1786fcd16dbaaa50f9a1dc46b93ff9ccd26487c269b317L30

Also, can you provide the full render_list command you're using and also which renderd --version you are running?

[a14stoner]

hello, thank you for your suggestion. I applied them to my .mml file and it seems this fixes the problem. wow... 🥳🥳🥳🥳

renderd version:

0.8.0

I dont render the whole world so my render_list command is generated from this repo: render_list_geo.pl

the command which is executed right now is the following:

render_list -a -z 17 -Z 17 -x 67374 -X 71999 -y 41253 -Y 46783 -c /usr/local/renderd/renderd.conf --max-load 20 -n 16 -m default

I use PostgresSQL Version 15.2 with some optimized configurations from pgtune:

# DB Version: 15
# OS Type: linux
# DB Type: dw
# Total Memory (RAM): 94 GB
# CPUs num: 20
# Connections num: 1000
# Data Storage: hdd

max_connections = 1000
shared_buffers = 24064MB
effective_cache_size = 72192MB
maintenance_work_mem = 2GB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 500
random_page_cost = 4
effective_io_concurrency = 2
work_mem = 1232kB
huge_pages = try
min_wal_size = 4GB
max_wal_size = 16GB
max_worker_processes = 20
max_parallel_workers_per_gather = 10
max_parallel_workers = 20
max_parallel_maintenance_workers = 4

autovacuum_work_mem = 4GB
wal_level = minimal
checkpoint_timeout = 60min
max_wal_senders = 0
full_page_writes=off
fsync=off
jit=off

the last 7 parameters i found in different forum posts .. they seem to fasten the render process.

do you recommend any other config change on my postgresql.conf?

i started up rendering from Z17 to Z18 - lets see how it goes. Anyway, i will report back in the coming days if the config change was 100% sucessful.

[a14stoner]

Rendering works well. No errors in renderd service. This is the chart from munin of renderd troughput:

Good speed?

[hummeltech]

Yep, it looks to be about on par with some of the OSM tile servers: https://prometheus.openstreetmap.org/d/wyyzhZKMk/tile-rendering?orgId=1&refresh=1m&from=now-1M&to=now