DBA: cuando pasa VACUUM, limpiamos la mesa manualmente

VACUUM puede "limpiar" de una tabla en PostgreSQL solo lo que nadie puede ver , es decir, no hay una sola consulta activa que se haya iniciado antes de que se cambiaran estos registros.

¿Pero si hay un tipo tan desagradable (carga OLAP a largo plazo en la base OLTP)? ¿Cómo limpiar una mesa que cambia activamente rodeada de largas consultas y no pisar un rastrillo?

Difundimos un rastrillo

Primero, determinamos qué es y cómo puede surgir el problema que queremos resolver.

Por lo general, esta situación ocurre en una mesa relativamente pequeña , pero en la que hay muchos cambios . Por lo general, estos son contadores / agregados / clasificaciones diferentes , en los que UPDATE se ejecuta a menudo, o una cola de búfer para procesar algún tipo de flujo de eventos en ejecución constante, registros sobre los cuales siempre son INSERT / DELETE.

Intentemos reproducir la opción con clasificaciones:

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 una conexión diferente, comienza una consulta larga y larga, que recopila algunas estadísticas complejas, pero que no afecta nuestra tabla :

 SELECT pg_sleep(10000); 

Ahora actualizamos el valor de uno de los contadores muchas, muchas veces. Para la pureza del experimento, haremos esto en transacciones separadas usando dblink , ya que esto sucederá en 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 

Que paso ¿Por qué, incluso para la ACTUALIZACIÓN más simple de un solo registro , el tiempo de ejecución se degradó 7 veces , de 0.524 ms a 3.808 ms? Y nuestra calificación se está construyendo más lentamente y más lentamente.

MVCC tiene la culpa

Se trata del mecanismo MVCC , que obliga a la solicitud a mirar todas las versiones anteriores del registro. Así que limpiemos nuestra tabla de las 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! Una consulta paralela nos está molestando : después de todo, un día puede querer referirse a estas versiones (¿y si?), Y deberían estar disponibles para él. Y así, incluso VACUUM FULL no nos ayudará.

"Sujetar" la mesa

Pero sabemos con certeza que nuestra tabla no necesita nuestra consulta. Por lo tanto, tratemos de devolver el rendimiento del sistema a un marco adecuado, después de haber eliminado todo lo superfluo de la tabla, al menos "manualmente", ya que VACUUM pasa.

Para hacerlo más claro, consideremos un ejemplo de una tabla de búfer. Es decir, hay una secuencia grande INSERT / DELETE, y a veces la tabla está completamente vacía. Pero si no está vacío allí, debemos guardar su contenido actual .

# 0: evalúa la situación

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

Formulamos los criterios: "es hora de actuar", si:

• VACUUM ha estado funcionando durante mucho tiempo
  Esperamos una gran carga, por lo tanto, sea 60 segundos desde el último [auto] VACÍO.
• tamaño de la tabla física mayor que el objetivo
  Lo definimos como el número duplicado de páginas (bloques de 8 KB) en relación con el tamaño mínimo - 1 negro por montón + 1 negro para cada uno de los índices - para una tabla potencialmente vacía. Si esperamos que cierta cantidad de datos permanezca siempre en el búfer "normalmente", es razonable ajustar esta fórmula.

# 1: VACÍO De todos modos

No podemos saber de antemano si la consulta paralela realmente nos está obstaculizando, exactamente cuántos registros han estado "desactualizados" desde su inicio. Por lo tanto, cuando decidimos procesar de alguna manera la tabla, en cualquier caso, primero debe ejecutar VACUUM en ella, ya que, a diferencia de VACUUM FULL, no interfiere con los procesos paralelos de lectura y escritura de datos.

Al mismo tiempo, puede limpiar de inmediato la mayor parte de lo que nos gustaría eliminar. Sí, y las solicitudes posteriores para esta tabla nos llegarán en un "caché activo" , lo que reducirá su duración y, por lo tanto, el tiempo total para bloquear a otros con nuestra transacción de publicación.

# 2: ¿Hay alguien en casa?

Vamos a ver si hay algo en la tabla:

 TABLE tbl LIMIT 1; 

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

Actúa de la misma manera que el comando DELETE incondicional para cada tabla, pero mucho más rápido, ya que en realidad no escanea tablas. Además, inmediatamente libera espacio en el disco, por lo que no es necesario realizar una operación de VACÍO después.

Si necesita restablecer el contador de la secuencia de la tabla (REINICIAR IDENTIDAD) al mismo tiempo, decida usted mismo.

# 3: ¡Todo a su vez!

Dado que trabajamos en condiciones de alta competitividad, mientras verificamos aquí la ausencia de entradas en la tabla, alguien ya podría escribir algo allí. No debemos perder esta información, ¿y qué? Así es, debe hacerse para que nadie pueda grabar con seguridad.

Para hacer esto, debemos habilitar el aislamiento SERIALIZABLE para nuestra transacción (sí, aquí comenzamos la transacción) y bloquear la tabla "firmemente":

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

Este nivel de bloqueo se debe a las operaciones que queremos realizar en él.

# 4: conflicto de intereses

Venimos aquí y queremos "bloquear" la tableta, y si alguien estaba activo en ella en ese momento, por ejemplo, ¿leer de ella? Nos "colgaremos" en anticipación del lanzamiento de este bloque, mientras que otros que deseen leer ya estarán enterrados en nosotros ...

Para evitar que esto suceda, "sacrifíquese": si todavía no pudimos obtener el bloqueo durante un cierto tiempo (permitido pequeño), obtendremos una excepción de la base de datos, pero al menos no nos molestaremos en el resto.

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

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

Para no tratar con la restauración posterior del valor "antiguo" de la variable, utilizamos el formulario SET LOCAL , que limita el alcance de la configuración a la transacción actual.

Recuerde que Statement_timeout se aplica a todas las solicitudes posteriores para que la transacción no se extienda a valores inaceptables si hay muchos datos en la tabla.

# 5: copiar datos

Si la tabla resultó no estar completamente vacía, los datos deberán volverse a guardar a través de la etiqueta temporal auxiliar:

 CREATE TEMPORARY TABLE _tmp_swap ON COMMIT DROP AS TABLE tbl; 

La firma ON COMMIT DROP 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 la conexión.

Dado que suponemos que no hay muchos datos "en vivo", esta operación debería realizarse lo suficientemente rápido.

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

Recopilamos el guión final

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

Ejecutemos el script en la tabla de origen 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.