Clever Geek Handbook

DBA: limpiando almas muertas

A veces, al ejecutar consultas largas o mal escritas en PostgreSQL, suceden varias cosas desagradables, como un bloqueo repentino del proceso o un bloqueo de todo el servidor .





En tales casos, las "almas muertas" pueden permanecer en los archivos multimedia (a veces bastante grandes, pero bastante comparables en tama帽o al resto de la base de datos) que se crearon durante el proceso como un almacenamiento temporal de datos intermedios .





Estos datos ya no son necesarios para nadie, no pueden ser utilizados por nadie, pero el servidor todav铆a no tiene prisa por deshacerse de ellos como Plyushkin.





Hoy veremos c贸mo se pueden encontrar y "limpiar" sin dolor.





Buscando amortiguadores temporales

La primera categor铆a de problemas que surgen es el uso temporal del espacio en disco durante la ejecuci贸n del nodo del plan, si la cantidad de memoria requerida no cabe en work_mem .





Obtener tal efecto es bastante simple: olvidarse de establecer o elegir un l铆mite de recursi贸n demasiado grande :





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;

[ explain.tensor.ru]





, 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
Un proceso de cliente muy "eliminado" atrae al administrador de correo y a todo el servidor PostgreSQL detr谩s
"" postmaster PostgreSQL-

- . 4GB - ?





pg_ls_tmpdir:





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;


More articles:


All Articles