Manchmal treten beim Ausführen langer oder schlecht geschriebener Abfragen in PostgreSQL verschiedene unangenehme Dinge auf, z. B. ein plötzlicher Prozessabsturz oder ein Absturz des gesamten Servers .
In solchen Fällen können "tote Seelen" auf den Mediendateien verbleiben (manchmal ziemlich groß, aber vergleichbar mit dem Rest der Datenbank), die während des Prozesses als temporäre Speicherung von Zwischendaten erstellt wurden .
Diese Daten werden von niemandem mehr benötigt, können von niemandem verwendet werden, aber der Server hat es immer noch nicht eilig, sie wie Plyushkin loszuwerden.
Heute werden wir sehen, wie sie gefunden und schmerzlos "aufgeräumt" werden können.
Auf der Suche nach temporären Puffern
Die erste Kategorie von Problemen, die auftreten, ist die vorübergehende Verwendung von Speicherplatz während der Ausführung des Plan-Knotens, wenn die erforderliche Speichermenge nicht in work_mem passt .
Einen solchen Effekt zu erzielen ist ganz einfach - zu vergessen, ein zu großes Rekursionslimit festzulegen oder zu wählen :
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;