🥇Cuando VACUUM no puede más — limpiamos la tabla manualmente

VACUUM puede "limpiar" de la tabla en PostgreSQL solo aquello que nadie puede ver — es decir, no hay ninguna consulta activa que haya comenzado antes de que estos registros fueran modificados.

¿Y si hay un tipo molesto (carga OLAP prolongada en una base de datos OLTP) que sí existe? ¿Cómo limpiar una tabla que cambia activamente en un entorno de consultas largas y no caer en un error?

Desglosamos los errores

Primero definamos en qué consiste y cómo puede surgir el problema que queremos resolver.

Normalmente, esta situación ocurre en una tabla relativamente pequeña, pero en la que hay muchos cambios. Normalmente se trata de diferentes contadores/aggregados/calificaciones, que tienen un UPDATE realizado con mucha frecuencia, o una cola de búfer para procesar un flujo constante de eventos, cuyos registros se INSERTAN/ELIMINAN continuamente.

Intentemos reproducir un caso con calificaciones:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- construiremos la calificación sobre este índice

INSERT INTO
  tbl
SELECT
  chr(ascii('a'::text) + i) k
, 0 v
FROM
  generate_series(0, 25) i;

Y paralelamente, en otra conexión, se inicia una consulta larga y compleja, que recopila estadísticas complicadas, pero no afecta nuestra tabla:

SELECT pg_sleep(10000);

Ahora actualizamos muchas veces el valor de uno de los contadores. Para la claridad del experimento, lo haremos en transacciones separadas usando dblink, como ocurriría en la realidad:

DO $$
DECLARE
  i integer;
  tsb timestamp;
  tse timestamp;
  d double precision;
BEGIN
  PERFORM dblink_connect('dbname=' || current_database() || ' port=' || current_setting('port'));
  FOR i IN 1..10000 LOOP
    tsb = clock_timestamp();
    PERFORM dblink($e$UPDATE tbl SET v = v + 1 WHERE k = 'a';$e$);
    tse = clock_timestamp();
    IF i % 1000 = 0 THEN
      d = (extract('epoch' from tse) - extract('epoch' from tsb)) * 1000;
      RAISE NOTICE 'i = %, exectime = %', lpad(i::text, 5), lpad(d::text, 5);
    END IF;
  END LOOP;
  PERFORM dblink_disconnect();
END;
$$ LANGUAGE plpgsql;

NOTICE:  i =  1000, exectime = 0.524
NOTICE:  i =  2000, exectime = 0.739
NOTICE:  i =  3000, exectime = 1.188
NOTICE:  i =  4000, exectime = 2.508
NOTICE:  i =  5000, exectime = 1.791
NOTICE:  i =  6000, exectime = 2.658
NOTICE:  i =  7000, exectime = 2.318
NOTICE:  i =  8000, exectime = 2.572
NOTICE:  i =  9000, exectime = 2.929
NOTICE:  i = 10000, exectime = 3.808

¿Qué sucedió? ¿Por qué incluso para un simple UPDATE de un único registro el tiempo de ejecución se degradó en 7 veces — de 0.524 ms a 3.808 ms? Además, nuestro ranking se está construyendo cada vez más lento.

Todo es culpa de MVCC

Todo se debe al mecanismo MVCC, que hace que la consulta revise todas las versiones anteriores del registro. Entonces, limpiemos nuestra tabla de versiones "muertas":

VACUUM VERBOSE tbl;

INFO:  limpiando "public.tbl"
INFO:  "tbl": se encontraron 0 versiones de fila removibles, 10026 versiones de fila no removibles en 45 de 45 páginas
DETALLE:  10000 versiones de fila muertas no se pueden eliminar aún, xmin más antiguo: 597439602

¡Vaya, no hay nada que limpiar! Paralelamente la consulta en ejecución nos molesta — porque en algún momento podría querer acceder a estas versiones (¿y si no?), y deben estar disponibles para él. Por lo tanto, incluso VACUUM FULL no nos ayudará.

«Compactamos» la tabla

Pero sabemos con certeza que esa consulta no necesita nuestra tabla. Por eso intentaremos devolver el rendimiento del sistema a límites razonables, eliminando todo lo innecesario de la tabla — aunque sea «manualmente», dado que VACUUM no lo hace.

Para que sea más claro, consideremos el ejemplo de una tabla de búfer. Es decir, hay un gran flujo de INSERT/DELETE, y a veces la tabla queda vacía. Pero si no está vacía, debemos conservar su contenido actual.

#0: Оцениваем ситуацию

Está claro que se puede intentar hacer algo con la tabla después de cada operación, pero no tiene mucho sentido — los costos de mantenimiento serán claramente mayores que la capacidad de procesamiento de las consultas de destino.

Formularemos criterios — "ya es hora de actuar", si:

VACUUM fue lanzado hace bastante tiempo
Esperamos una gran carga, así que dejemos que esto sea 60 segundos desde el último [auto]VACUUM.
el tamaño físico de la tabla es mayor que el objetivo
Lo definiremos como el doble del número de páginas (bloques de 8KB) con respecto al tamaño mínimo — 1 blk en el heap + 1 blk en cada uno de los índices — para una tabla potencialmente vacía. Sin embargo, si esperamos que el búfer "normalmente" mantenga un cierto volumen de datos, es razonable ajustar esta fórmula.

Consulta de verificación

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- aquí sería más correcto hacer * current_setting('block_size')::bigint, pero ¿quién cambia el tamaño del bloque?..
, pg_total_relation_size(oid) size
, coalesce(extract('epoch' from (now() - greatest(
    pg_stat_get_last_vacuum_time(oid)
  , pg_stat_get_last_autovacuum_time(oid)
  ))), 1 << 30) vaclag
FROM
  pg_class cl
WHERE
  oid = $1::regclass -- tbl
LIMIT 1;

relpages | size_norm | size    | vaclag
-------------------------------------------
       0 |     24576 | 1105920 | 3392.484835

#1: Все равно VACUUM

No podemos saber de antemano cuánto nos afecta la consulta paralela: cuántos registros han "caducado" desde que comenzó. Por lo tanto, cuando finalmente decidamos procesar la tabla, es recomendable ejecutar primero VACUUM — a diferencia de VACUUM FULL, permite que los procesos paralelos trabajen con datos en lectura y escritura.

Además, puede limpiar gran parte de lo que queremos eliminar de inmediato. Y las consultas subsecuentes sobre esta tabla nos irán por el "caché caliente", lo que reducirá su duración; y, por lo tanto, el tiempo total de bloqueo de otras transacciones que estamos manejando.

#2: Есть кто-нибудь дома?

Verifiquemos si hay algo en la tabla:

TABLE tbl LIMIT 1;

Si no queda ni un solo registro, podemos ahorrar mucho en el procesamiento simplemente ejecutando TRUNCATE:

Funciona de la misma manera que el comando DELETE sin condición para cada tabla, pero es mucho más rápido, ya que no escanea las tablas de manera efectiva. Además, libera espacio en disco de inmediato, por lo que no es necesario ejecutar la operación VACUUM después de ella.

Decidir si necesitas restablecer el contador de la secuencia de la tabla (RESTART IDENTITY) es asunto tuyo.

#3: Все — по-очереди!

Dado que estamos trabajando en condiciones de alta concurrencia, mientras verificamos la ausencia de registros en la tabla, alguien podría haber escrito algo en ella. No debemos perder esta información, así que, ¿qué debemos hacer? Correcto, debemos asegurarnos de que nadie pueda escribir.

Para ello, necesitamos habilitar SERIALIZABLE-la aislamiento para nuestra transacción (sí, aquí iniciamos la transacción) y bloquear la tabla "de manera firme":

BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;

Este nivel de bloqueo es consecuencia de las operaciones que queremos realizar en ella.

#4: Конфликт интересов

Llegamos aquí y queremos "bloquear" la tabla, ¿y si en ese momento alguien estaba activo en ella, por ejemplo, leyendo de ella? Quedaremos "atrapados" esperando que se libere este bloqueo, y otros que quieran leer se encontrarán con nosotros...

Para que esto no suceda, "nos sacrificamos"; si no conseguimos obtener el bloqueo en un tiempo determinado (aunque sea breve), obtendremos una excepción de la base de datos, pero al menos no molestaremos mucho a los demás.

Para ello, estableceremos una variable de sesión lock_timeout (para versiones 9.3+) o/y statement_timeoutLo más importante es recordar que el valor de statement_timeout se aplica solo al siguiente statement. Es decir, de esta manera en la concatenación — no funcionará:

SET statement_timeout = ...; LOCK TABLE ...;

Para no tener que restaurar después el "valor antiguo" de la variable, utilizamos la forma SET LOCAL, que limita el alcance de la configuración a la transacción actual.

Recuerde que statement_timeout se aplica a todas las consultas posteriores, para que la transacción no pueda extenderse a niveles inaceptables, si resulta que hay muchos datos en la tabla.

#5: Копируем данные

Si la tabla no está completamente vacía, los datos deberán ser reescritos a través de una tabla temporal auxiliar:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Firma ON COMMIT DROP significa que al finalizar la transacción la tabla temporal dejará de existir, y no es necesario preocuparse por eliminarla manualmente en el contexto de la conexión.

Dado que suponemos que no hay muchos datos "vivos", esta operación debería completarse bastante rápido.

¡Así que eso es todo! No olvide después de completar la transacción ejecutar ANALYZE para normalizar las estadísticas de la tabla, si es necesario.

Reunimos el script final

Utilizamos este "pseudo-python":

# собираем статистику с таблицы
stat <-
  SELECT
    relpages
  , ((
      SELECT
        count(*)
      FROM
        pg_index
      WHERE
        indrelid = cl.oid
    ) + 1) << 13 size_norm
  , pg_total_relation_size(oid) size
  , coalesce(extract('epoch' from (now() - greatest(
      pg_stat_get_last_vacuum_time(oid)
    , pg_stat_get_last_autovacuum_time(oid)
    ))), 1 << 30) vaclag
  FROM
    pg_class cl
  WHERE
    oid = $1::regclass -- table_name
  LIMIT 1;

# таблица больше целевого размера и VACUUM был давно
if stat.size > 2 * stat.size_norm and stat.vaclag is None or stat.vaclag > 60:
  -> VACUUM %table;
  try:
    -> BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
    # пытаемся захватить монопольную блокировку с предельным временем ожидания 1s
    -> SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
    -> LOCK TABLE %table IN ACCESS EXCLUSIVE MODE;
    # надо убедиться в пустоте таблицы внутри транзакции с блокировкой
    row <- TABLE %table LIMIT 1;
    # если в таблице нет ни одной "живой" записи - очищаем ее полностью, в противном случае - "перевставляем" все записи через временную таблицу
    if row is None:
      -> TRUNCATE TABLE %table RESTART IDENTITY;
    else:
      # создаем временную таблицу с данными таблицы-оригинала
      -> CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE %table;
      # очищаем оригинал без сброса последовательности
      -> TRUNCATE TABLE %table;
      # вставляем все сохраненные во временной таблице данные обратно
      -> INSERT INTO %table TABLE _tmp_swap;
    -> COMMIT;
  except Exception as e:
    # если мы получили ошибку, но соединение все еще "живо" - словили таймаут
    if not isinstance(e, InterfaceError):
      -> ROLLBACK;

¿Se puede evitar copiar los datos por segunda vez?En principio, se puede, si no hay otras actividades atadas al oid de la propia tabla por parte del BL o FK desde la BD:

CREATE TABLE _swap_%table (LIKE %table INCLUDING ALL);
INSERT INTO _swap_%table TABLE %table;
DROP TABLE %table;
ALTER TABLE _swap_%table RENAME TO %table;

Ejecutamos el script en la tabla original y verificamos las métricas:

VACUUM tbl;
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
  SET LOCAL statement_timeout = '1s'; SET LOCAL lock_timeout = '1s';
  LOCK TABLE tbl IN ACCESS EXCLUSIVE MODE;
  CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;
  TRUNCATE TABLE tbl;
  INSERT INTO tbl TABLE _tmp_swap;
COMMIT;

relpages | size_norm | size   | vaclag
-------------------------------------------
       0 |     24576 |  49152 | 32.705771

¡Todo salió bien! La tabla se redujo 50 veces, y todas las actualizaciones nuevamente se ejecutan rápidamente.

Fuente: habr.com

Cuando se activa VACUUM — limpiamos la tabla manualmente