现场反馈在执行SQL时数据库进程因为内存溢出crash，经分析发现PG执行计划选用Hash Join，在数据倾斜很严重的情况会导致此问题。 下面是模拟重现： 创建数据表t1和t2，数据量1个亿，在c1列上只有一个值 ``` drop table if exists t1; drop table if exists t2; create table t1(id int,c1 varchar); create table t2(id int,c1 varchar); insert into t1 select x,1 from generate_series(1,100000000) as x; insert into t2 select x,1 from generate_series(1,100000000) as x; analyze t1; analyze t2; ``` 禁用merge join和NL join，执行SQL，PG走Hash Join，但由于连接键出现大量重复值都在同一个桶中，最终进程Crash ``` testdb=# set enable_mergejoin=off; SET testdb=# set enable_nestloop=off; SET testdb=# explain select * from t1,t2 where t1.c1 = t2.c1; QUERY PLAN ------------------------------------------------------------------------------------- Hash Join (cost=10003083093.36..112509573947445.36 rows=9999961600000000 width=12) Hash Cond: ((t2.c1)::text = (t1.c1)::text) -> Seq Scan on t2 (cost=0.00..1442478.00 rows=100000000 width=6) -> Hash (cost=1442474.16..1442474.16 rows=99999616 width=6) -> Seq Scan on t1 (cost=0.00..1442474.16 rows=99999616 width=6) (5 rows) testdb=# explain analyze select * from t1,t2 where t1.c1 = t2.c1; WARNING: terminating connection because of crash of another server process DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory. HINT: In a moment you should be able to reconnect to the database and repeat your command. server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request. The connection to the server was lost. Attempting reset: Failed. The connection to the server was lost. Attempting reset: Failed. ``` 查看OS日志，进程被Killed ``` Nov 4 14:13:20 dev kernel: Out of memory: Kill process 13573 (postgres) score 555 or sacrifice child Nov 4 14:13:20 dev kernel: Killed process 13573 (postgres), UID 1003, total-vm:4244196kB, anon-rss:418760kB, file-rss:8kB, shmem-rss:16kB ``` 虽然work_mem和shared_buffers设置为较小的值（4MB&128MB），但为了完成任务，PG仍然申请过量的内存导致进程被Kill ``` testdb=# show work_mem testdb-# ; work_mem ---------- 4MB (1 row) testdb=# show shared_buffers testdb-# ; shared_buffers ---------------- 128MB (1 row) ``` 查看PG的源码nodeHash.c，注释明确了出现这种情况，只能寄希望于服务器有更多的内存:( ``` ...... /* * If we dumped out either all or none of the tuples in the table, disable * further expansion of nbatch. This situation implies that we have * enough tuples of identical hashvalues to overflow spaceAllowed. * Increasing nbatch will not fix it since there's no way to subdivide the * group any more finely. We have to just gut it out and hope the server * has enough RAM. */ if (nfreed == 0 || nfreed == ninmemory) { hashtable->growEnabled = false; #ifdef HJDEBUG printf("Hashjoin %p: disabling further increase of nbatch\n", hashtable); #endif } ``` 悲催的是，在没有索引的情况下，Hash Join是最优选择 ``` testdb=# reset all; RESET testdb=# explain select * from t1,t2 where t1.c1 = t2.c1; QUERY PLAN ------------------------------------------------------------------------------------- Hash Join (cost=10003083093.36..112509573947445.36 rows=9999961600000000 width=12) Hash Cond: ((t2.c1)::text = (t1.c1)::text) -> Seq Scan on t2 (cost=0.00..1442478.00 rows=100000000 width=6) -> Hash (cost=1442474.16..1442474.16 rows=99999616 width=6) -> Seq Scan on t1 (cost=0.00..1442474.16 rows=99999616 width=6) (5 rows) testdb=# set enable_hashjoin=off; SET testdb=# explain select * from t1,t2 where t1.c1 = t2.c1; QUERY PLAN ----------------------------------------------------------------------------------- Merge Join (cost=37663437.62..149999462413435.69 rows=9999961600000000 width=12) Merge Cond: ((t1.c1)::text = (t2.c1)::text) -> Sort (cost=18831684.74..19081683.78 rows=99999616 width=6) Sort Key: t1.c1 -> Seq Scan on t1 (cost=0.00..1442474.16 rows=99999616 width=6) -> Materialize (cost=18831752.88..19331752.88 rows=100000000 width=6) -> Sort (cost=18831752.88..19081752.88 rows=100000000 width=6) Sort Key: t2.c1 -> Seq Scan on t2 (cost=0.00..1442478.00 rows=100000000 width=6) (9 rows) ``` 在Oracle下执行类似的操作 ``` drop table t1; drop table t2; create table t1(id int,c1 varchar2(20)); create table t2(id int,c1 varchar2(20)); truncate table t1; insert into t1 select rownum,1 from dba_tables; commit; insert /*+append*/ into t1 select * from t1; commit; -- 重复N次直至10个亿 truncate table t2; insert /*+append*/ into t2 select * from t1; commit; ANALYZE TABLE t1 COMPUTE STATISTICS FOR TABLE; ANALYZE TABLE t2 COMPUTE STATISTICS FOR TABLE; explain plan for select * from t1,t2 where t1.c1 = t2.c1; select * from table(dbms_xplan.display); explain plan for select /*+use_hash(t1,t2)*/ * from t1,t2 where t1.c1 = t2.c1; select * from table(dbms_xplan.display); set autotrace traceonly select /*+use_hash(t1,t2)*/ * from t1,t2 where t1.c1 = t2.c1; ``` 内存使用平稳，并不会出现OOM等问题。 **参考资料** [nodeHash.c](https://doxygen.postgresql.org/nodeHash_8c.html)