現場反饋在執行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)

PostgreSQL DBA(193) - 資料傾斜下的HashJoin

相關文章