I have a daemon written in PHP/Laravel that connects to a PostgreSQL database using PDO and executes jobs from a queue. A job typically takes 30 minutes to run. It consists of several PostgreSQL queries with PostGIS.
With every SQL query server memory usage increases. The memory is freed only when the connection is closed. So if I run several jobs consecutively the server eventually runs out of memory.
I have been able to temporarily solve the problem by having my daemon periodically reconnect to the database. However, why does the memory not get released unless I disconnect from the server?
Some details about the environment:
In queries I use PostGIS functions ST_Contains(), ST_GeomFromText(), ST_Area() for SELECTs and other simple plain SQL SELECTs with similar UPDATE.
Example of query:
select id
from gtn
where ST_Contains(geo_polygon::geometry, ST_GeomFromText('POINT(0.000000 0.000000)', 4326))
order by ST_Area(geo_polygon::geometry) asc;
All PostgreSQL settings are default, except one:
effective_cache_size = 1GB
Tuning the settings using this tool did not help:
# DB Version: 12
# OS Type: linux
# DB Type: web
# Total Memory (RAM): 4 GB
# CPUs num: 2
# Data Storage: ssd
max_connections = 200
shared_buffers = 1GB
effective_cache_size = 3GB
maintenance_work_mem = 256MB
checkpoint_completion_target = 0.9
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 2621kB
min_wal_size = 1GB
max_wal_size = 4GB
PHP 7.4.33 (cli) (built: Nov 15 2022 06:05:55) ( NTS )
Copyright (c) The PHP Group
Zend Engine v3.4.0, Copyright (c) Zend Technologies
with Zend OPcache v7.4.33, Copyright (c), by Zend Technologies
with Xdebug v2.9.8, Copyright (c) 2002-2020, by Derick Rethans
# psql --version
psql (PostgreSQL) 12.13 (Debian 12.13-1.pgdg100+1)
# psql -U postgres -c 'SELECT PostGIS_Full_Version();'
postgis_full_version
------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------
POSTGIS="3.3.2 4975da8" [EXTENSION] PGSQL="120" GEOS="3.7.1-CAPI-1.11.1 27a5e771" PROJ="Rel. 5.2.0, September 15th, 201
8" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1" WAGYU="0.5.0 (Internal)"
(1 row)
Also, I found this, but there has not any solution.
UPD: 2023-02-18
As said jjanes, I've executed the follow query 100 times using PHP PDO (without Laravel at all) and psql CLI:
select id
from gtn
where "view" is not null
and geo_polygon is not null
and ST_Contains(geo_polygon::geometry, ST_GeomFromText('POINT(0.000000 0.000000)', 4326))
order by ST_Area(geo_polygon::geometry) asc
limit 2;
After this, memory of postgres process was increased by 50 Mb, both PHP PDO and psql CLI. Only after disconnect from the DB the memory got released.
Then I've run the simple query 100 times:
select id from gtn limit 100;
when my memory has been increased only by 1 Mb and didn't increase during execution anymore. The memory got released only after disconnect too.
Using PHP PDO I tried to run gdb. I added to dockere-compose.yml service of postgres the following config:
cap_add:
- SYS_PTRACE
security_opt:
- seccomp:unconfined
and installed postgresql-12-dbgsym postgresql-12-postgis-3-dbgsym gcc gdb libc6-dbg libffi6-dbg libgcc1-dbg libkrb5-dbg libstdc++6-8-dbg libxml2-dbg zlib1g-dbg libkrb5-dbg packages.
I run my query again using PHP PDO and after executed query 100 times, gdb showed me next:
$ gdb -p 70
...
(No debugging symbols found in /usr/lib/x86_64-linux-gnu/libmd.so.0)
Cannot access memory at address 0x7f5264494088
warning: Unable to find dynamic linker breakpoint function.
GDB will be unable to debug shared library initializers
and track explicitly loaded dynamic code.
Failed to read a valid object file image from memory.
Unable to read JIT descriptor from remote memory
0x00007f526367ad16 in epoll_wait (epfd=3, events=0x55f87ab9cfb8, maxevents=1, timeout=-1)
at ../sysdeps/unix/sysv/linux/epoll_wait.c:30
30 ../sysdeps/unix/sysv/linux/epoll_wait.c: No such file or directory.
I could not even execute p MemoryContextStats(TopMemoryContext) for gdb, because I get a Couldn't get extended state status: No such process. error.
According to a few answers at SO (this, this and this), I have found the solution for me. I just have made postgresql.conf more polite for my local development:
max_connections = 30
shared_buffers = 128MB
effective_cache_size = 384MB
maintenance_work_mem = 32MB
checkpoint_completion_target = 0.9
wal_buffers = 3932kB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 1456kB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 6
max_parallel_workers_per_gather = 3
max_parallel_workers = 6
max_parallel_maintenance_workers = 3
I suppose the resources distribute for each connection individually and multiply at max_connection if it's necessary. My last configuration, where max_connections = 100, was too big. Once I have changed configuration as above I have seen not more 1 Gb for long repeatedly requests and OOM doesn't occur anymore.
Therefore, I think that my problem was in misconfiguration, not in memory leak.