Cuando se activa VACUUM — limpiamos la tabla manualmente

VACUUM solo se puede «limpiar» de la tabla en PostgreSQL lo que nadie puede ver es decir, no hay ninguna consulta activa que haya comenzado antes de que se modificaran estos registros.

¿Y si hay un tipo molesto (una carga OLAP prolongada en una base OLTP) después de todo? ¿Cómo limpiar una tabla que cambia activamente en medio de largas consultas y sin pisar un rastrillo?

Desplegando los rastrillos

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 son actualizados frecuentemente, o una cola de búfer para procesar algún flujo de eventos en constante movimiento, cuyos registros son constantemente INSERTADOS/ELIMINADOS.

Intentemos reproducir una variante con calificaciones:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- construyendo la clasificación por 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, larga, recopilando alguna estadística compleja, pero que no afecta nuestra tabla:

SELECT pg_sleep(10000);

Ahora actualizamos muchas, muchas veces el valor de uno de los contadores. Para la claridad del experimento, hagamos esto en transacciones individuales utilizando dblink, como sucederí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 una simple actualización de un único registro el tiempo de ejecución se degradó 7 veces — de 0.524ms a 3.808ms? Y nuestra clasificación se está construyendo cada vez más lenta.

Todo es culpa del MVCC

Todo se debe al mecanismo MVCC, que obliga a la consulta a revisar todas las versiones anteriores del registro. Así que limpiemos nuestra tabla de versiones "muertas":

VACUUM VERBOSE tbl;

INFO:  limpiando "public.tbl"
INFO:  "tbl": encontró 0 versiones de fila eliminables, 10026 versiones de fila no eliminables en 45 de 45 páginas
DETALLE:  10000 versiones de fila muertas no pueden ser eliminadas aún, xmin más antiguo: 597439602

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

«Compactamos» la tabla

Pero nosotros sabemos que esa consulta no necesita nuestra tabla. Así que intentemos regresar el rendimiento del sistema a niveles adecuados, desechando todo lo innecesario de la tabla — al menos de manera "manual", dado que VACUUM no respalda.

Para hacerlo más claro, consideremos ya el caso de la tabla de buffer. Es decir, hay un gran flujo de INSERT/DELETE, y a veces la tabla queda completamente vacía. Pero si no está vacía, debemos mantener su contenido actual.

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

Es claro que se puede intentar hacer algo con la tabla incluso 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 objetivo.

Formulemos los criterios — "ya es hora de actuar", si:

• VACUUM se ha ejecutado desde hace bastante tiempo
  Esperamos una gran carga, así que que 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 de la cantidad de páginas (bloques de 8KB) 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 en el buffer siempre permanezca 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 una consulta paralela: cuántos registros "se han vuelto obsoletos" desde su inicio. Por lo tanto, cuando finalmente decidamos procesar la tabla de alguna manera, es imprescindible ejecutar primero VACUUM — a diferencia de VACUUM FULL, no interfiere con los procesos paralelos que trabajan con datos de lectura y escritura.

Además, puede limpiar la mayor parte de lo que quisiéramos eliminar. Y las siguientes consultas en 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 nuestra operación está atendiendo.

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

Verifiquemos si hay algo en la tabla:

TABLE tbl LIMIT 1;

Si no queda ningún registro, podemos ahorrar mucho en el procesamiento: solo ejecutando TRUNCATE:

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

Decidan si desean restablecer el contador de la secuencia de la tabla (RESTART IDENTITY) — eso depende de ustedes.

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

Dado que trabajamos en un entorno de alta concurrencia, mientras verificamos la ausencia de registros en la tabla, alguien ya podría haber escrito algo allí. No debemos perder esa información, así que, ¿qué hacemos? Correcto, debemos asegurarnos de que nadie pueda escribir.

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

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

Este nivel de bloqueo es necesario debido a las operaciones que queremos realizar sobre ella.

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

Aquí llegamos y queremos "bloquear" la tabla — pero si en ese momento alguien está activo en ella, por ejemplo, leyendo? Nos "quedaremos colgados" esperando que se libere ese bloqueo, y otros que quieran leer quedarán bloqueados por nosotros...

Para que eso no ocurra, "nos sacrificaremos" — si en un tiempo determinado (que sea aceptablemente corto) no logramos obtener el bloqueo, recibiremos una excepción de la base de datos, pero al menos no interferiremos demasiado a los demás.

Para ello, estableceremos una variable de sesión lock_timeout (para versiones 9.3+) o/y statement_timeoutLo principal a recordar es que el valor de statement_timeout solo se aplica a la siguiente instrucción. Es decir, así en la concatenación — no funcionará:

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

Para no tener que restaurar posteriormente el valor "antiguo" de la variable, utilizamos la forma SET LOCAL, que limita el ámbito de la configuración a la transacción actual.

Recuerda que statement_timeout se aplica a todas las solicitudes posteriores, para que la transacción no pueda extenderse a tamaños inaceptables, si efectivamente hay muchos datos en la tabla.

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

Si la tabla no está completamente vacía, será necesario volver a guardar los datos 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, por lo que no es necesario eliminarla manualmente en el contexto de la conexión.

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

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

Recopilamos el script final

Utilizamos un "pseudo-python" así:

# собираем статистику с таблицы
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, sí, si no hay otras actividades ligadas al oid de la tabla por parte de BL o FK desde la base de datos:

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

Ejecuamos 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 vuelven a ejecutarse rápidamente.

Fuente: habr.com