PHP/Laravel连接PostgreSQL内存不释放，仅关连接才释放的原因

我有一个用PHP/Laravel编写的守护进程，通过PDO连接PostgreSQL数据库并执行队列中的任务。每个任务通常需要30分钟才能完成，包含多个使用PostGIS的PostgreSQL查询。

每次执行SQL查询后，服务器内存占用都会增加，且仅当连接关闭时内存才会释放。因此如果连续运行多个任务，服务器最终会耗尽内存。

我已通过让守护进程定期重新连接数据库的方式临时解决了问题。但为什么只有断开与服务器的连接时内存才会被释放？

环境详情 #

查询使用的函数 #

查询中使用PostGIS函数ST_Contains()、ST_GeomFromText()、ST_Area()执行SELECT操作，还有其他简单的普通SQL SELECT及类似的UPDATE操作。

查询示例

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;

PostgreSQL配置 #

初始仅修改了一项默认配置：

effective_cache_size = 1GB

使用数据库调优工具调整后的配置仍无改善：

# 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 PDO连接PostgreSQL
• Laravel版本：v5.7.29
• PHP版本：

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

• Docker镜像：postgres:12
• PostgreSQL版本：

# psql --version
psql (PostgreSQL) 12.13 (Debian 12.13-1.pgdg100+1)

• PostGIS版本：

# 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, 2018" LIBXML="2.9.4" LIBJSON="0.12.1" LIBPROTOBUF="1.3.1" WAGYU="0.5.0 (Internal)"
(1 row)

曾查找过PostgreSQL官方邮件列表相关内容，但未找到解决方案。

更新：2023-02-18 #

按照建议，分别使用PHP PDO（不依赖Laravel）和psql CLI执行以下查询100次：

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;

执行后，PostgreSQL进程内存增加了50MB，无论使用PHP PDO还是psql CLI都是如此，且仅在断开数据库连接后内存才被释放。

随后执行简单查询100次：

select id from gtn limit 100;

此时内存仅增加了1MB，且后续执行时不再增长，同样仅在断开连接后内存才被释放。

尝试使用PHP PDO配合gdb调试，在PostgreSQL的docker-compose.yml服务中添加配置：

cap_add:
   - SYS_PTRACE
security_opt:
   - seccomp:unconfined

并安装了postgresql-12-dbgsym postgresql-12-postgis-3-dbgym gcc gdb libc6-dbg libffi6-dbg libgcc1-dbg libkrb5-dbg libstdc++6-8-dbg libxml2-dbg zlib1g-dbg libkrb5-dbg包。

再次使用PHP PDO执行查询100次后，gdb输出如下：

$ 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.

甚至无法在gdb中执行p MemoryContextStats(TopMemoryContext)，报错Couldn't get extended state status: No such process.

内容的提问来源于stack exchange，提问作者V. Rzhavy