問題の背景

加圧試験中にデータ挿入中にエラーが発生した:could not write to hash-join temporary file:設備にスペースがない.しかし、サーバーを表示するにはまだ空きスペースがたくさんありますが、何がこのようなエラーを引き起こしたのでしょうか.

実行スクリプトの表示

insert into db_zjgj.result_rule_cwjbxx_db_sacw_t_cw_cwjbxx 
select db_zjgj.uuid(),c_bh,'2E810338E4F2CEE0462E9A021A0E0816',' - ， ， ','7B7DCB103239F5CBAB4106016DE258D1' 
from db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx  where EXISTS (
SELECT
 1
FROM
 db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx
WHERE
 db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh = db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_bh
AND db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx.d_czsj IS NOT NULL
)
AND EXISTS (
 SELECT
  1
 FROM
  db_zjgj.temp_ajjbxx_db_sacw_t_aj_ajjbxx AS ajjbxx
 WHERE
  db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh = ajjbxx.c_bh
 AND ajjbxx.d_pjsxrq IS NOT NULL
)
AND EXISTS (
 SELECT
  1
 FROM
  db_zjgj.temp_ajjbxx_db_sacw_t_aj_ajjbxx AS ajjbxx,
  db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx
 WHERE
  db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh = ajjbxx.c_bh
 AND db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_bh = db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh
 AND db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx.d_czsj < ajjbxx.d_pjsxrq
)
-- 
Hash Semi Join (cost=270531577.85..324240042.87 rows=113055 width=33)
 Hash Cond: ((temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh)::text = (ajjbxx.c_bh)::text)
 -> Hash Semi Join (cost=270527939.60..324202660.37 rows=113055 width=99)
       Hash Cond: (((temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh)::text = (temp_cwczxx_db_sacw_t_cw_cwczxx_1.c_cwbh)::text) AND ((temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh)::text = (ajjbxx_1.c_bh)::text))
       -> Hash Semi Join (cost=10073.78..43895.94 rows=225857 width=99)
             Hash Cond: ((temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_bh)::text = (temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh)::text)
             -> Seq Scan on temp_cwjbxx_db_sacw_t_cw_cwjbxx (cost=0.00..17784.08 rows=451208 width=66)
             -> Hash (cost=5485.57..5485.57 rows=225857 width=33)
                   -> Seq Scan on temp_cwczxx_db_sacw_t_cw_cwczxx (cost=0.00..5485.57 rows=225857 width=33)
                         Filter: (d_czsj IS NOT NULL)
       -> Hash (cost=169739766.32..169739766.32 rows=3771811900 width=66)
             -> Nested Loop (cost=0.00..169739766.32 rows=3771811900 width=66)
                    Join Filter: (temp_cwczxx_db_sacw_t_cw_cwczxx_1.d_czsj < ajjbxx_1.d_pjsxrq)
                   -> Seq Scan on temp_cwczxx_db_sacw_t_cw_cwczxx temp_cwczxx_db_sacw_t_cw_cwczxx_1 (cost=0.00..5485.57 rows=225857 width=41)
                   -> Materialize (cost=0.00..2870.50 rows=50100 width=41)
                         -> Seq Scan on temp_ajjbxx_db_sacw_t_aj_ajjbxx ajjbxx_1 (cost=0.00..2620.00 rows=50100 width=41)
 -> Hash (cost=2620.00..2620.00 rows=50100 width=33)       -> Seq Scan on temp_ajjbxx_db_sacw_t_aj_ajjbxx ajjbxx (cost=0.00..2620.00 rows=50100 width=33)             Filter: (d_pjsxrq IS NOT NULL)

計画を実行すると、テーブル接続には全テーブルスキャンとnested loop接続が使用されていることがわかります.

テーブル・データ量とインデックスの表示

-- 
db_zjgj.temp_ajjbxx_db_sacw_t_aj_ajjbxx：50100
db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx：225857
db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx：451208

各テーブルにはインデックスがなく、プライマリ・キーがないことがわかります.(これらのテーブルはすべて抽出プロセスで生成されていることがわかり、抽出が完了すると削除されます).

最適化

-- 
alter table db_zjgj.temp_ajjbxx_db_sacw_t_aj_ajjbxx add primary key(c_bh);
create index i_ajjbxx_d_pjsxrq on db_zjgj.temp_ajjbxx_db_sacw_t_aj_ajjbxx(d_pjsxrq);
alter table db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx add primary key(c_bh);
create index i_cwczxx_c_cwbh on db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx(c_cwbh);
create index i_cwczxx_d_czsj on db_zjgj.temp_cwczxx_db_sacw_t_cw_cwczxx(d_czsj);
alter table db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx add primary key(c_bh);
create index i_cwjbxx_c_ajbh on db_zjgj.temp_cwjbxx_db_sacw_t_cw_cwjbxx(c_ajbh);
-- 
Hash Semi Join (cost=13712.87..298118.93 rows=113055 width=33)
 Hash Cond: ((temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh)::text = (ajjbxx.c_bh)::text)
 -> Nested Loop Semi Join (cost=10074.62..260736.42 rows=113055 width=99)
        Join Filter: ((temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh)::text = (temp_cwczxx_db_sacw_t_cw_cwczxx_1.c_cwbh)::text)
       -> Hash Semi Join (cost=10073.78..43895.94 rows=225857 width=99)
             Hash Cond: ((temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_bh)::text = (temp_cwczxx_db_sacw_t_cw_cwczxx.c_cwbh)::text)
             -> Seq Scan on temp_cwjbxx_db_sacw_t_cw_cwjbxx (cost=0.00..17784.08 rows=451208 width=66)
             -> Hash (cost=5485.57..5485.57 rows=225857 width=33)
                   -> Seq Scan on temp_cwczxx_db_sacw_t_cw_cwczxx (cost=0.00..5485.57 rows=225857 width=33)
                         Filter: (d_czsj IS NOT NULL)
       -> Nested Loop (cost=0.83..0.95 rows=1 width=66)
              Join Filter: (temp_cwczxx_db_sacw_t_cw_cwczxx_1.d_czsj < ajjbxx_1.d_pjsxrq)
             -> Index Scan using temp_ajjbxx_db_sacw_t_aj_ajjbxx_pkey on temp_ajjbxx_db_sacw_t_aj_ajjbxx ajjbxx_1 (cost=0.41..0.45 rows=1 width=41)
                   Index Cond: ((c_bh)::text = (temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_ajbh)::text)
             -> Index Scan using i_cwczxx_c_cwbh on temp_cwczxx_db_sacw_t_cw_cwczxx temp_cwczxx_db_sacw_t_cw_cwczxx_1 (cost=0.42..0.48 rows=1 width=41)
                   Index Cond: ((c_cwbh)::text = (temp_cwjbxx_db_sacw_t_cw_cwjbxx.c_bh)::text)
 -> Hash (cost=2620.00..2620.00 rows=50100 width=33)
       -> Seq Scan on temp_ajjbxx_db_sacw_t_aj_ajjbxx ajjbxx (cost=0.00..2620.00 rows=50100 width=33)
             Filter: (d_pjsxrq IS NOT NULL)

インデックスを追加するとcostが下がり、データがスムーズに挿入され、最終的にはsqlが6 s程度必要になります.

疑問

なぜサーバを表示するのにまだ空き容量があるのに、sqlを実行してディスク容量が不足していると報告したのでしょうか.

-- abase ：/opt/thunisoft/abdata/3.6/abase1/base/pgsql_tmp
[thunisoft@localhost base]$ du -sh *|sort
19M pgsql_tmp
3.2G 408143
6.3G 410629
7.0M 13236
7.1M 1
7.2M 13241
7.2M 16444
-- 
[thunisoft@localhost pgsql_tmp]$ ls |wc -w
65551
-- pg_sql_tmp23277 
[thunisoft@localhost pgsql_tmp]$ ll
...
-rw-------. 1 thunisoft thunisoft       0 Aug 27 14:24 pgsql_tmp23277.9998
-rw-------. 1 thunisoft thunisoft       0 Aug 27 14:24 pgsql_tmp23277.9999
...
​
--23277 ， pg_log sql

一時ディレクトリの下には0,pgsql_というファイルがたくさんあることがわかります.tmpが占有する空間は19 Mである.

シーンの復元

-- 
[thunisoft@localhost base]$ df -lh
Filesystem                    Size  Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   18G   12G  4.6G  73% /
tmpfs                         5.9G  4.8K  5.9G   1% /dev/shm
/dev/sda1                     485M   33M  427M   8% /boot
​
-- ， sql， pgsql_tmp 
[thunisoft@localhost base]$ du -sh *|sort
1.6G pgsql_tmp
3.2G 408143
6.3G 410629
7.0M 13236
7.1M 1
7.2M 13241
7.2M 16444
-- 
[thunisoft@localhost base]$ df -lh
Filesystem                   Size Used Avail Use% Mounted on
/dev/mapper/VolGroup-lv_root   18G   17G  254M  99% /
tmpfs                         5.9G  4.0K  5.9G   1% /dev/shm
/dev/sda1                     485M   33M  427M   8% /boot
--   ， ， 65551
[thunisoft@localhost base]$ du -sh *|sort
19M pgsql_tmp
...
[thunisoft@localhost pgsql_tmp]$ ls |wc -w
65551

このsqlが実行されると、一時ファイルは空き領域がいっぱいになるまで増大し続け、sqlがエラーを報告すると、一時ファイルの大部分が空になり、ディスク領域が解放されるので、見始めたディスク領域は満たされていませんが、エラーはディスク領域がいっぱいになります.

これらの場合、これらの一時ファイルが生成されます。

クエリーで使用するメモリがworkを超えていることがわかりました.memの大時間(ソート,DISTINCT,MERGE JOIN,HASH JOIN,デカルト積,ハッシュ集約,パケット集約,再帰クエリを含む)などの操作では,中間プロセスのデータを一時ファイルで格納する.このような操作を頻繁に行うと、一時ファイルは急速に増加します.この問題を解決できるのは再起動のみで、再起動するとすべての一時ファイルが空になります.

-- 
--1. ， ， ， 
#temp_file_limit = -1                   # limits per-process temp file space  
                                       # in kB, or -1 for no limit 
--2. ， ， 
#log_temp_files = -1                   # log temporary files equal or larger  
                                       # than the specified size in kilobytes; 
          # -1 disables, 0 logs all temp files 
--3. work_mem                                       
#work_mem (integer)

締めくくり

1.最初のsqlに戻ります.これらのテンポラリ・テーブルは、データがテーブルに挿入された後にインデックスを確立し、最後の抽出数を実行することができます.これにより、効率が高くなり、ネストされたループにcpu、ディスクio、テンポラリ・ファイルの消費量が高くなります.
2.abase実行効率を向上させるため、一時記憶の代わりにメモリを使用し、メモリが不足すると一時ファイルを使用して中間データを格納します.
3.適宜設定できるtemp_file_limitはディスク領域の10%で、一時ファイルがディスクを占有しすぎると、自動的にクエリーをキャンセルし、クエリー文を記録します.
4.一般的なクエリは、テンポラリ・ファイルを大量に消費すると、インデックスがないために発生する可能性があります.
転載先:https://www.cnblogs.com/zhangfx01/p/10563558.html