🥇Cuando falla VACUUM, limpiamos la mesa manualmente

VACÍO Puede “limpiar” de una tabla en PostgreSQL solo lo que nadie puede ver - es decir, no hay una sola solicitud activa que haya comenzado antes de que se cambiaran estos registros.

Pero, ¿qué pasa si todavía existe un tipo tan desagradable (carga OLAP a largo plazo en una base de datos OLTP)? Cómo Limpiar y cambiar activamente la mesa. rodeado de largas consultas y no pisar un rastrillo?

Colocando el rastrillo

Primero, determinemos cuál es el problema que queremos solucionar y cómo puede surgir.

Generalmente esta situación sucede en una mesa relativamente pequeña, pero en el que ocurre muchos cambios. Generalmente esto o diferente metros/agregados/clasificaciones, en el que a menudo se ejecuta UPDATE, o cola de buffer para procesar un flujo constante de eventos, cuyos registros se INSERTAN/ELIMINAN constantemente.

Intentemos reproducir la opción con calificaciones:

CREATE TABLE tbl(k text PRIMARY KEY, v integer);
CREATE INDEX ON tbl(v DESC); -- по этому индексу будем строить рейтинг

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

Y en paralelo, en otra conexión, comienza una solicitud muy, muy larga, que recopila algunas estadísticas complejas, pero no afecta nuestra mesa:

SELECT pg_sleep(10000);

Ahora actualizamos el valor de uno de los contadores muchas, muchas veces. Para la pureza del experimento, hagamos esto. en transacciones separadas usando dblinkcómo sucederá 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é pasó? ¿Por qué incluso para la ACTUALIZACIÓN más simple de un solo registro? tiempo de ejecución degradado 7 veces — ¿De 0.524 ms a 3.808 ms? Y nuestra calificación está aumentando cada vez más lentamente.

Todo es culpa de MVCC.

Se trata de Mecanismo MVCC, lo que hace que la consulta revise todas las versiones anteriores de la entrada. Entonces, limpiemos nuestra mesa de versiones "muertas":

VACUUM VERBOSE tbl;

INFO:  vacuuming "public.tbl"
INFO:  "tbl": found 0 removable, 10026 nonremovable row versions in 45 out of 45 pages
DETAIL:  10000 dead row versions cannot be removed yet, oldest xmin: 597439602

¡Oh, no hay nada que limpiar! Paralelo La solicitud en ejecución está interfiriendo con nosotros. - después de todo, es posible que algún día quiera recurrir a estas versiones (¿y si?), y deberían estar disponibles para él. Y por lo tanto ni siquiera VACUUM FULL nos ayudará.

“Colapsar” la mesa

Pero sabemos con certeza que esa consulta no necesita nuestra tabla. Por lo tanto, seguiremos intentando devolver el rendimiento del sistema a límites adecuados eliminando todo lo innecesario de la mesa, al menos "manualmente", ya que VACUUM se rinde.

Para que quede más claro, veamos el ejemplo del caso de una tabla de buffer. Es decir, hay un gran flujo de INSERT/DELETE y, a veces, la tabla está completamente vacía. Pero si no está vacío, debemos guardar su contenido actual.

#0: Evaluar la situación

Está claro que se puede intentar hacer algo con la tabla incluso después de cada operación, pero esto no tiene mucho sentido: la sobrecarga de mantenimiento será claramente mayor que el rendimiento de las consultas de destino.

Formulemos los criterios: "es hora de actuar" si:

VACUUM se lanzó hace bastante tiempo
Esperamos una carga pesada, así que déjalo así. segundos 60 desde el último [auto]VACUUM.
El tamaño de la tabla física es mayor que el objetivo.
Definámoslo como el doble del número de páginas (bloques de 8 KB) en relación con el tamaño mínimo: 1 bloque para montón + 1 bloque para cada índice - para una mesa potencialmente vacía. Si esperamos que una cierta cantidad de datos siempre permanezca en el búfer "normalmente", es razonable modificar esta fórmula.

Solicitud de verificación

SELECT
  relpages
, ((
    SELECT
      count(*)
    FROM
      pg_index
    WHERE
      indrelid = cl.oid
  ) + 1) << 13 size_norm -- тут правильнее делать * current_setting('block_size')::bigint, но кто меняет размер блока?..
, 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: Todavía VACÍO

No podemos saber de antemano si una consulta paralela nos está interfiriendo significativamente: exactamente cuántos registros han quedado "obsoletos" desde que comenzó. Por lo tanto, cuando decidimos procesar de alguna manera la tabla, en cualquier caso, primero debemos ejecutar sobre ella. VACÍO - a diferencia de VACUUM FULL, no interfiere con los procesos paralelos que trabajan con datos de lectura y escritura.

Al mismo tiempo, puede limpiar inmediatamente la mayor parte de lo que nos gustaría eliminar. Sí, y las consultas posteriores sobre esta mesa nos llegarán a nosotros. por "caché caliente", lo que reducirá su duración y, por lo tanto, el tiempo total de bloqueo de otros mediante nuestra transacción de servicio.

#2: ¿Hay alguien en casa?

Comprobemos si hay algo en la tabla:

TABLE tbl LIMIT 1;

Si no queda ni un solo registro, entonces podemos ahorrar mucho en el procesamiento simplemente haciendo TRUNCAR:

Actúa igual que un comando DELETE incondicional para cada tabla, pero es mucho más rápido ya que en realidad no escanea las tablas. Además, libera inmediatamente espacio en el disco, por lo que no es necesario realizar una operación VACUUM posteriormente.

Depende de usted decidir si necesita restablecer el contador de secuencia de la tabla (REINICIAR IDENTIDAD).

#3: ¡Todos, tomen turnos!

Como trabajamos en un entorno altamente competitivo, mientras estamos aquí comprobando que no hay entradas en la tabla, es posible que alguien ya haya escrito algo allí. No deberíamos perder esta información, ¿y qué? Así es, debemos asegurarnos de que nadie pueda escribirlo con seguridad.

Para hacer esto necesitamos habilitar SERIALIZABLE-aislamiento de nuestra transacción (sí, aquí iniciamos una transacción) y bloqueamos la tabla “firmemente”:

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

Este nivel de bloqueo viene determinado por las operaciones que queramos realizar sobre él.

#4: Conflicto de intereses

Venimos aquí y queremos "bloquear" el letrero. ¿Qué pasaría si alguien estuviera activo en él en ese momento, por ejemplo, leyendo? Nos “colgaremos” esperando que se libere este bloque, y otros que quieran leer se toparán con nosotros...

Para evitar que esto suceda, nos "sacrificaremos": si no pudimos obtener un bloqueo dentro de un tiempo determinado (aceptablemente corto), recibiremos una excepción de la base, pero al menos no interferiremos demasiado con otros.

Para hacer esto, configure la variable de sesión. lock_timeout (para versiones 9.3+) o/y declaración_tiempo de espera. Lo principal que hay que recordar es que el valor de state_timeout solo se aplica a partir de la siguiente declaración. Es decir, así al pegar - no funcionará:

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

Para no tener que lidiar con la restauración del valor "antiguo" de la variable más adelante, utilizamos el formulario ESTABLECER LOCALES, que limita el alcance de la configuración a la transacción actual.

Recordamos que state_timeout se aplica a todas las solicitudes posteriores, por lo que la transacción no puede extenderse a valores inaceptables si hay muchos datos en la tabla.

#5: Copiar datos

Si la tabla no está completamente vacía, habrá que volver a guardar los datos utilizando una tabla temporal auxiliar:

CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl;

Firma EN CAÍDA DE COMPROMISO Significa que en el momento en que finaliza la transacción, la tabla temporal dejará de existir y no es necesario eliminarla manualmente en el contexto de conexión.

Como suponemos que no hay muchos datos "en vivo", esta operación debería realizarse con bastante rapidez.

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

Armando el guión final

Usamos 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;

¿Es posible no copiar los datos por segunda vez?En principio, esto es posible si el oid de la tabla en sí no está vinculado a ninguna otra actividad del lado BL o FK del lado DB:

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

Ejecutemos el script en la tabla fuente y verifiquemos 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 ha reducido 50 veces y todas las ACTUALIZACIONES se ejecutan rápidamente nuevamente.

Fuente: habr.com

Cuando falla VACUUM, limpiamos la mesa manualmente