[OSM-dev] posgresql/postgis leaking memory in tirex rendering setup

[Stephan Knauss]

Hello Komяpa,

thanks for reaching out.

On 30.03.2021 21:04, Darafei "Komяpa" Praliaskouski wrote:
> Darafei from PostGIS here. Correct spot to report this is 
> http://trac.osgeo.org/postgis <http://trac.osgeo.org/postgis>.
> Please note your proj is 5 (from 2018) and that's 3 years old, there 
> was an API change to accomodate newer proj versions.

I am using the latest version as provided by the postgis project docker 
image. This installs postgis from a repository maintained by postgresql.org:

https://github.com/postgis/docker-postgis/blob/27f716f9b7212870c497d6d6caf68f7a44c26bc0/13-3.1/Dockerfile#L6

http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgis/

If you say that the configuration of postgis is outdated by having an 
old proj library, then this needs to be addressed with postgresql so 
they change the build process. Can you officially address this?


> What we'll need is a way to reproduce the issue. Just "tirex" is not 
> enough, a docker-compose with all the scripts running or isolated 
> query that causes the leak will help. At the very least - enable a 
> full log of queries with auto_explain and zero threshold and attach 
> the postgresql.log with all these, so we have something to look into.

I tried to follow Postgresql hints on checking details of backend memory 
usage:

https://wiki.postgresql.org/wiki/Developer_FAQ#Examining_backend_memory_use

Unfortunately gdb is not working as expected here. Could relate to 
something with the binary running within docker. I added capabilites and 
seccomp, but it seems to terminate the process while attaching gdb. Then 
printing the data structure fails as well.

I tried to get some feedback from postgresl mailing lists:

https://www.postgresql.org/message-id/9f73e655-14b8-feaf-bd66-c0f506224b9e%40stephans-server.de

I am going to enable the requested log. As this is likely to grow fast, 
how many logs are sufficient?

Sharing the docker-compose is in principle possible as well. The images 
are quite large. We speak about 600MB for the rendering stack and 500MB 
for the official postgis image plus 300MB for an image which does 
database preparation and updates. It will then require to load at least 
a smaller data extract into the database as well. Nothing too 
complicated, but certainly some work. It is not as simple as a 
"docker-compose up demo". I check if I can script something which will 
self-install, so it can be used directly to show-case the problem.

I thought that by checking the memory context I might be able to get at 
least a hint into which direction to search further. There are other 
reports regarding memory leaking in postgresql in a rendering setup, 
mixed with a leak in renderd: 
https://github.com/openstreetmap/mod_tile/issues/181


Stephan