问题概述

近期遇到了一个操作系统OOM把数据库进程杀死导致数据库recovery问题，查看OSW日志发现好多空闲状态的会话且每个进程占用内存都不少，top按内存占用排序，除了checkpoint和background writer进程排在前两位，接着排在后边的就是一堆backend子进程。

类似下图这样

排查原因时发现，执行频率高的几个SQL都是对两个分区表的查询，分区数量达到了惊人的数量（5*12*128=7680），且查询条件未使用分区键，怀疑是因为查询访问了太多分区导致内存占用，于是查相关资料并测试复现。

测试一下

首先创建分区表，test_rel的分区和子分区共2011个，0条数据

test_db=# select a.oid,               a.relname,               c.nspname,                             pg_size_pretty(sum(pg_table_size(b.relid))) as only_table,               pg_size_pretty(sum(pg_total_relation_size(b.relid))) as total_table, count(b.relid)      part_count    from pg_class a left join pg_namespace c on a.relnamespace = c.oid left join LATERAL pg_partition_tree(a.oid) b on true         where  relkind = 'p'           and relispartition = 'f'         group by a.oid, a.relname, c.nspname order by part_count desc;
  oid   |   relname   | nspname | only_table | total_table | part_count
--------+-------------+---------+------------+-------------+------------
 195503 | test_rel    | public  | 16 MB      | 16 MB       |       2011

test_db=# \d+ test_rel
                                                             Partitioned table "public.test_rel"
                             Column                              |       Type        | Collation | Nullable | Default | Storage  | Stats target | Description
-----------------------------------------------------------------+-------------------+-----------+----------+---------+----------+--------------+-------------
 id                                                              | integer           |           |          |         | plain    |              |
 name                                                            | integer           |           |          |         | plain    |              |
 t1                                                              | text              |           |          |         | extended |              |
.......             |
Partition key: RANGE (id)
Partitions: test_rel_p1 FOR VALUES FROM (1) TO (10), PARTITIONED,
            test_rel_p10 FOR VALUES FROM (90) TO (100), PARTITIONED,
            test_rel_p2 FOR VALUES FROM (10) TO (20), PARTITIONED,
            test_rel_p3 FOR VALUES FROM (20) TO (30), PARTITIONED,
            test_rel_p4 FOR VALUES FROM (30) TO (40), PARTITIONED,
            test_rel_p5 FOR VALUES FROM (40) TO (50), PARTITIONED,
            test_rel_p6 FOR VALUES FROM (50) TO (60), PARTITIONED,
            test_rel_p7 FOR VALUES FROM (60) TO (70), PARTITIONED,
            test_rel_p8 FOR VALUES FROM (70) TO (80), PARTITIONED,
            test_rel_p9 FOR VALUES FROM (80) TO (90), PARTITIONED

第一个窗口十个并行查询test_rel表两次，sleep 20s用于保持会话不关闭

[postgres@mydb1a ~]$ for i in `seq 1 10`; do (psql -d test_db -Atc "select * from test_rel;select * from test_rel;select pg_sleep(20)")&done
[1] 371640
[2] 371641
[3] 371642
[4] 371643
[5] 371644
[6] 371645
[7] 371646
[8] 371647
[9] 371648
[10] 371649

第二个窗口同时使用smem工具查看内存情况，可以看到排在前十的基本都是刚才的查询会话

[root@mydb1a smem-1.4]# ./smem  -r -k |head -10
  PID User     Command                         Swap      USS      PSS      RSS
165265 postgres /app/pg12/bin/postgres -D /        0    96.8M   141.4M   211.6M
371659 postgres postgres: postgres test_db         0   108.0M   111.8M   153.8M
371662 postgres postgres: postgres test_db         0   108.0M   111.7M   153.7M
371653 postgres postgres: postgres test_db         0   108.0M   111.7M   153.8M
371658 postgres postgres: postgres test_db         0   108.0M   111.7M   153.8M
371657 postgres postgres: postgres test_db         0   108.0M   111.7M   153.8M
371652 postgres postgres: postgres test_db         0   108.0M   111.7M   153.8M
371660 postgres postgres: postgres test_db         0   108.0M   111.7M   153.7M
371654 postgres postgres: postgres test_db         0   108.0M   111.7M   153.7M

同样方式测试查询普通表，并未发现内存占用情况

[postgres@mydb1a ~]$ for i in `seq 1 10`; do (psql -d test_db -Atc "select * from bank;select * from bank;select pg_sleep(20)")&done
[1] 379907
[2] 379908
[3] 379909
[4] 379910
[5] 379911
[6] 379912
[7] 379913
[8] 379914
[9] 379915
[10] 379916

[root@mydb1a smem-1.4]# ./smem  -r -k |head -10
  PID User     Command                         Swap      USS      PSS      RSS
165265 postgres /app/pg12/bin/postgres -D /        0    99.8M   145.3M   211.6M
 2062 root     /usr/bin/dockerd --live-res   100.0K    58.3M    58.4M    61.4M
165462 postgres postgres: walwriter                0   192.0K    40.5M    87.2M
 2595 root     containerd --config /var/ru        0    32.6M    32.6M    33.8M
 1765 root     /usr/sbin/rsyslogd -n -iNON        0    31.1M    32.1M    35.8M
  602 root     /usr/lib/systemd/systemd-jo        0    12.5M    30.4M    54.2M
 2069 root     /usr/sbin/libvirtd            384.0K    26.2M    28.0M    36.2M
 1862 root     /usr/bin/python3 -Es /usr/s     2.4M    14.1M    14.8M    21.9M
 1760 polkitd  /usr/lib/polkit-1/polkitd -   532.0K     9.4M    10.9M    16.4M

基本可以验证该问题

问题总结

pg中postmaster进程为每一个client 连接 fork一个 backend子进程用作服务交互，每个backend子进程都维护着自己的SysCache（系统表元组）和RelCache（表的模式信息），以提高表的访问效率。

所以要出现以上问题需要满足几个”有利“条件

1、长连接

2、分区数量巨大

3、查询条件未使用分区键，导致查询扫描所有分区

如何避免该问题就很明显了，正确使用分区表，如果查询条件不用分区键，那么建分区表的目的又是什么呢。