Mapcache leaves hundreds of idle open connections to postgresql
Although we have raised the limit of open connections to 300
in https://gitlab.eox.at/vs/vs-starter/-/blob/main/vs_starter/templates/docker-compose.yml#L18 for example VHR_15 when bombarded by 100 clients from locust still exhibits Too many clients
error in the database
other collections or tilesets do not exhibit this behavior for 100 clients
The output does not change if 1 or 3 caches are deployed
example of one idle connection created by one thread of mapcache:
16384 | vhr15_db | 3654 | 10 | vhr15_user | | 10.1.191.73 | | 46288 | 2022-12-12 11:35:18.063911+00 | | 2022-12-12 11:35:21.229325+00 | 2022-12-12 11:35:21.23182+00 | Client | ClientRead | idle | | | SELECT * FROM (SELECT to_char(mapcache_items.begin_time, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') || '/' || to_char(mapcache_items.end_time, 'YYYY-MM-DD"T"HH24:MI:SS"Z"') AS "interval" FROM mapcache_items WHERE (mapcache_items.collection = 'VHR_IMAGE_2015_Level_1' AND ((mapcache_items."begin_time" < to_timestamp($7) AND mapcache_items."end_time" > to_timestamp($6)) or (mapcache_items."begin_time" = mapcache_items."end_time" AND mapcache_items."begin_time" <= to_timestamp($7) AND mapcache_items."end_time" >= to_timestamp($6)))) AND mapcache_items."footprint" && ST_Transform(ST_MakeEnvelope($2, $4, $3, $5, CAST(SUBSTRING($1, 6) AS INTEGER)), 4326) ORDER BY mapcache_items."end_time" DESC LIMIT 20) AS sub ORDER BY interval ASC; | client backend
I have tried adding following keepalives=0 connect_timeout=5
to connection string in mapcache.xml which changed nothing
We could try to forcibly repeatedly close the idle mapcache generated threads from inside the database container, but I do not think that is a good idea.
Increasing the number of allowed connections even higher is a mitigation but not solving the original issue that mapcache is simply messing up the connection pool.
I did not find any mapcache (or mapcache -> postgresql part) configuration option allowing to do something about this.
Connected issue asking the same: https://github.com/MapServer/mapcache/issues/280 without response.
This issue does not have a solution currently and is just documenting.