PostgreSQLで長いクエリや記述が不十分なクエリを実行すると、突然のプロセスクラッシュやサーバー全体のクラッシュなど、さまざまな不快なことが発生することがあります。
このような場合、「死んだ魂」がメディアに残る可能性があります-中間データの一時的なストレージとしてプロセス中に作成されたファイル（非常に大きい場合もありますが、データベースの他の部分とサイズがかなり匹敵します）。
このデータはもはや誰も必要とせず、誰も使用することはできませんが、サーバーはPlyushkinのようにそれを取り除くために急いでいません。
今日は、それらがどのように見つけられ、痛みを伴わずに「クリーンアップ」されるかを見ていきます。
一時バッファを探しています
発生する問題の最初のカテゴリは、必要な量のメモリがwork_memに収まらない場合に、プランノードの実行中に一時的にディスク領域を使用することです。
このような効果を得るのは非常に簡単です-大きすぎる再帰制限を設定または選択するのを忘れる：
explain (analyze, buffers)
WITH RECURSIVE T AS (
SELECT
0 i
, '' s
UNION ALL
SELECT
i + 1
, repeat('a', i + 1)
FROM
T
WHERE
i < 1e4 -- 10k
)
TABLE T ORDER BY s DESC LIMIT 1;
, T
- , temp written
:
-> CTE Scan on t (actual time=0.008..374.157 rows=10001 loops=1)
Buffers: temp written=6126
, - :
SELECT pg_backend_pid();
-- 15004 - PID ,
explain (analyze, buffers)
WITH RECURSIVE T AS (
SELECT
0 i
, '' s
UNION ALL
SELECT
i + 1
, repeat('a', i + 1)
FROM
T
WHERE
i < 1e5 -- 100k
)
TABLE T ORDER BY s DESC LIMIT 1;
kill -9 15004
- . 4GB - ?
SELECT * FROM pg_ls_tmpdir();
name | size | modification
pgsql_tmp15004.0 | 1073741824 | 2021-05-12 10:46:10+03
pgsql_tmp15004.1 | 1073741824 | 2021-05-12 10:46:38+03
pgsql_tmp15004.2 | 1073741824 | 2021-05-12 10:47:06+03
pgsql_tmp15004.3 | 958078976 | 2021-05-12 10:47:31+03
PostgreSQL 12, , pg_ls_dir
<data>/base/pgsql_tmp
- , , .
PID , . , , , :
WITH dir AS (
SELECT
current_setting('data_directory') || '/base/pgsql_tmp' dir
)
, ls AS (
SELECT
*
, pg_ls_dir(dir) fn
FROM
dir
)
, tmp AS (
SELECT
*
, regexp_replace(fn, '^pgsql_tmp(\d+).*$', '\1')::integer pid
, (pg_stat_file(dir || '/' || fn)).*
FROM
ls
)
SELECT
dir || '/' || fn
FROM
tmp
LEFT JOIN
pg_stat_activity sa
USING(pid)
WHERE
sa IS NOT DISTINCT FROM NULL;
. , "" pg_terminate_backend(pid)
, "", "" .
TEMPORARY TABLE
CREATE TEMPORARY TABLE x AS
SELECT
i
, repeat('a', i::integer) s
FROM
generate_series(1, 1e5) i;
pg_temp_5
:
SELECT current_schemas(true);
-- {pg_temp_5,pg_catalog,public}
pg_temp
- TABLE x
, TABLE pg_temp.x
TABLE pg_temp_5.x
, .
, "", temp buffers
, pg_class
:
SELECT
oid
, relnamespace::regnamespace
, relname
, relfilenode
FROM
pg_class
WHERE
relnamespace::regnamespace::text ~ '^pg(_toast)?_temp';
oid | relnamespace | relname | relfilenode
66112 | pg_toast_temp_5 | pg_toast_66109 | 66112
66114 | pg_toast_temp_5 | pg_toast_66109_index | 66114
66109 | pg_temp_5 | x | 66109
Tom Lane 2003:
> What is the origin of these schemas? local temporary tables? sorts?
Right, they're made to hold temporary tables. The first time a givenbackend does CREATE TEMP TABLE, it looks for a pg_temp_n schema, and makes it if it's not there. On shutdown, it removes the temp tables, but it seemed like a waste of cycles to remove the pg_temp_n schema itself.
(ObTrivialFact: the 'n' is the backend's pgproc slot number, so it's known not to be in use by any concurrently running backend. But it will certainly be used again in future.)
, . , ?
temp buffers, , , :
WITH dir AS (
SELECT
current_setting('data_directory') || '/base/' || oid dir
FROM
pg_database
WHERE
datname = current_database()
)
, ls AS (
SELECT
*
, pg_ls_dir(dir) fn
FROM
dir
)
SELECT
*
FROM
ls
WHERE
fn ~ '^t';
dir | fn
.../data/base/16393 | t5_66109
.../data/base/16393 | t5_66112
.../data/base/16393 | t5_66114
t<temp schema N>_<temp object OID>
. "" , , pg_class
.
, VACUUM FULL
, , . autovacuum
:
LOG: autovacuum: found orphan temp table "tst.pg_temp_5.x"
, , - .
, "" , , "" :
WITH dir AS (
SELECT
current_setting('data_directory') || '/base/' || oid dir
FROM
pg_database
WHERE
datname = current_database()
)
, ls AS (
SELECT
*
, pg_ls_dir(dir) fn
FROM
dir
)
, lsid AS (
SELECT
*
, (pg_stat_file(dir || '/' || fn)).*
FROM
ls
WHERE
fn ~ '^t'
)
, sch AS (
SELECT DISTINCT
regexp_replace(fn, '^t(\d+)_.*$', '\1') sch
FROM
lsid
WHERE
modification < (
SELECT
stats_reset
FROM
pg_stat_database
WHERE
datid = 0
)
)
SELECT
string_agg('DROP SCHEMA ' || nspname || ' CASCADE;', E'\n') s
FROM
sch
JOIN
pg_namespace nsp
ON nsp.nspname IN ('pg_temp_' || sch, 'pg_toast_temp_' || sch);
, :
DROP SCHEMA pg_temp_5 CASCADE;
DROP SCHEMA pg_toast_temp_5 CASCADE;