Permítame recordarle que comenzamos con problemas relacionados con el aislamiento , hicimos una digresión sobre la organización de datos en un nivel bajo , hablamos en detalle sobre las versiones de fila y cómo se obtienen instantáneas de las versiones.

Luego analizamos la limpieza en la página (y las actualizaciones HOT), la limpieza regular , pero hoy nos fijamos en la limpieza automática.

Auto limpieza (autovacuum)

Ya hemos dicho que la limpieza ordinaria en condiciones normales (cuando nadie mantiene el horizonte de transacciones durante mucho tiempo) debe hacer frente a su trabajo. La pregunta es con qué frecuencia llamarlo.

Si limpia una mesa para cambiar pañales muy raramente, crecerá más de lo que quisiera. Además, para la próxima limpieza, puede tomar varios pasos a través de los índices si se han acumulado demasiados cambios.

Si borra la tabla con demasiada frecuencia, en lugar de un trabajo útil, el servidor se dedicará constantemente al mantenimiento, lo que tampoco es bueno.

Tenga en cuenta que comenzar una limpieza regular programada no resuelve el problema, porque la carga puede cambiar con el tiempo. Si la tabla comenzó a actualizarse más activamente, debería limpiarse con más frecuencia.

La limpieza automática es solo el mecanismo que le permite comenzar a limpiar, dependiendo de la actividad de los cambios en las tablas.

Cuando la limpieza automática está habilitada (parámetro de configuración de vacío automático), el proceso del iniciador de vacío automático siempre está presente en el sistema, que planea funcionar, y los flujos de trabajo de los trabajadores de vacío automático están involucrados en la limpieza real, varias de las cuales pueden funcionar en paralelo.

El proceso del iniciador de autovacuum compila una lista de bases de datos en las que hay alguna actividad. La actividad está determinada por las estadísticas, y para que se recopile, se debe establecer el parámetro track_counts . Nunca apague autovacuum y track_counts , de lo contrario, la limpieza automática no funcionará.

Una vez en autovacuum_naptime, se inicia el proceso del iniciador de autovacuum (utilizando el proceso postmaster) un flujo de trabajo para cada base de datos en la lista. En otras palabras, si hay alguna actividad en la base de datos, los flujos de trabajo entrarán en ella con un intervalo autovacuum_naptime . Para hacer esto, si hay varias bases de datos activas (N piezas), los procesos de trabajo se inician N veces más a menudo que autovacuum_naptime . Pero al mismo tiempo, el número total de flujos de trabajo que trabajan simultáneamente está limitado por el parámetro autovacuum_max_workers .

Una vez iniciado, el flujo de trabajo se conecta a la base de datos especificada por él y comienza construyendo la lista:

• todas las tablas, vistas materializadas y tablas tostadas que deben borrarse,
• todas las tablas y representaciones materializadas que requieren análisis (no se analizan las tablas tostadas, porque siempre se accede a ellas por índice).

Además, el flujo de trabajo a su vez limpia y / o analiza los objetos seleccionados y finaliza cuando se completa la limpieza.

Si el proceso no ha completado todo el trabajo previsto para autovacuum_naptime , el proceso del iniciador de autovacuum enviará otro flujo de trabajo a la misma base de datos y trabajarán juntos. "Juntos" simplemente significa que el segundo proceso construirá su lista de tablas y la seguirá. Por lo tanto, se procesarán diferentes tablas en paralelo, pero a nivel de una tabla no hay paralelismo: si uno de los procesos de trabajo ya está trabajando en la tabla, el otro lo omitirá y continuará.

Se ha debatido la necesidad de un procesamiento paralelo durante mucho tiempo, pero el parche aún no se ha adoptado.

Ahora echemos un vistazo más de cerca a lo que "requiere limpieza" y "requiere análisis".

¿Qué mesas necesitan limpieza?

Se cree que la limpieza es necesaria si el número de versiones "muertas", es decir, irrelevantes, de cadenas supera un valor umbral establecido. El recopilador de estadísticas recopila constantemente el número de versiones muertas y se almacena en la tabla pg_stat_all_tables. Y el umbral se establece mediante dos parámetros:

• autovacuum_vacuum_threshold define el valor absoluto (en piezas),
• autovacuum_vacuum_scale_factor determina la proporción de filas en una tabla.

La fórmula final es: la limpieza es necesaria si pg_stat_all_tables.n_dead_tup> = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltupes.

La configuración predeterminada establece autovacuum_vacuum_threshold = 50 y
autovacuum_vacuum_scale_factor = 0.2. El parámetro principal aquí, por supuesto, es autovacuum_vacuum_scale_factor : es lo que es importante para las tablas grandes (es decir, los posibles problemas están asociados con ellas). El valor del 20% parece estar muy sobreestimado, lo más probable es que deba reducirse significativamente.

Los valores óptimos de los parámetros pueden variar para diferentes tablas según su tamaño y la naturaleza de los cambios. Tiene sentido establecer, en general, valores adecuados y, si es necesario, configurar especialmente los parámetros a nivel de algunas tablas utilizando los parámetros de almacenamiento:

• autovacuum_vacuum_threshold y toast.autovacuum_vacuum_threshold ,
• autovacuum_vacuum_scale_factor y toast.autovacuum_vacuum_scale_factor .

Para no confundirse, esto debe hacerse solo para un pequeño número de tablas que se destacan entre otras por el volumen o la intensidad de los cambios, y solo si los valores establecidos globalmente no son adecuados.

Además, la limpieza automática se puede desactivar en el nivel de la mesa (aunque es difícil pensar en una razón por la cual esto sería necesario):

• autovacuum_enabled y toast.autovacuum_enabled .

Por ejemplo, la última vez que creamos una mesa de vacío con la limpieza automática desactivada, para, con fines de demostración, gestionar la limpieza manual. El parámetro de almacenamiento se puede cambiar de la siguiente manera:

=> ALTER TABLE vac SET (autovacuum_enabled = off); 

Para formalizar todo lo anterior, crearemos una vista que muestre qué tablas necesitan limpiarse actualmente. Utilizará una función que devuelve el valor actual del parámetro, dado que puede anularse a nivel de tabla:

 => CREATE FUNCTION get_value(param text, reloptions text[], relkind "char") RETURNS float AS $$ SELECT coalesce( --    ,    (SELECT option_value FROM pg_options_to_table(reloptions) WHERE option_name = CASE --  toast-    WHEN relkind = 't' THEN 'toast.' ELSE '' END || param ), --      current_setting(param) )::float; $$ LANGUAGE sql; 

Y aquí está la vista:

 => CREATE VIEW need_vacuum AS SELECT st.schemaname || '.' || st.relname tablename, st.n_dead_tup dead_tup, get_value('autovacuum_vacuum_threshold', c.reloptions, c.relkind) + get_value('autovacuum_vacuum_scale_factor', c.reloptions, c.relkind) * c.reltuples max_dead_tup, st.last_autovacuum FROM pg_stat_all_tables st, pg_class c WHERE c.oid = st.relid AND c.relkind IN ('r','m','t'); 

¿Qué tablas necesitan análisis?

Con el autoanálisis, la situación es aproximadamente la misma. Se cree que el análisis es necesario para aquellas tablas para las cuales el número de versiones cambiadas (desde el último análisis) de las filas excede el valor umbral especificado por dos parámetros similares: pg_stat_all_tables.n_mod_since_analyze> = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltu.

La configuración predeterminada de análisis automático es ligeramente diferente: autovacuum_analyze_threshold = 50 y autovacuum_analyze_scale_factor = 0.1. También se pueden definir a nivel de parámetros de almacenamiento para tablas individuales:

• autovacuum_analyze_threshold ,
• autovacuum_analyze_scale_factor

Como las tablas de tostadas no se analizan, no hay parámetros correspondientes para ellas.

Creemos una vista para el análisis:

 => CREATE VIEW need_analyze AS SELECT st.schemaname || '.' || st.relname tablename, st.n_mod_since_analyze mod_tup, get_value('autovacuum_analyze_threshold', c.reloptions, c.relkind) + get_value('autovacuum_analyze_scale_factor', c.reloptions, c.relkind) * c.reltuples max_mod_tup, st.last_autoanalyze FROM pg_stat_all_tables st, pg_class c WHERE c.oid = st.relid AND c.relkind IN ('r','m'); 

Ejemplo

Para los experimentos, establecemos los siguientes valores de parámetros:

 => ALTER SYSTEM SET autovacuum_naptime = '1s'; --     => ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03; -- 3% => ALTER SYSTEM SET autovacuum_vacuum_threshold = 0; => ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02; -- 2% => ALTER SYSTEM SET autovacuum_analyze_threshold = 0; 

 => SELECT pg_reload_conf(); 

  pg_reload_conf ---------------- t (1 row) 

Ahora cree una tabla similar a la que usamos la última vez e inserte mil filas en ella. La limpieza automática está deshabilitada en el nivel de la mesa, y la activaremos nosotros mismos. Si esto no se hace, los ejemplos no serán reproducibles, ya que la limpieza automática puede funcionar en el momento equivocado.

 => CREATE TABLE autovac( id serial, s char(100) ) WITH (autovacuum_enabled = off); => INSERT INTO autovac SELECT g.id,'A' FROM generate_series(1,1000) g(id); 

Esto es lo que mostrará nuestra vista de limpieza:

 => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 0 | 0 | (1 row) 

Hay dos puntos a los que debe prestar atención. En primer lugar, max_dead_tup = 0, aunque el 3% de 1000 líneas son 30 líneas. El hecho es que todavía no tenemos estadísticas sobre la mesa, ya que INSERT no la actualiza. Hasta que se analice nuestra tabla, los ceros permanecerán, ya que pg_class.reltuples = 0. Sin embargo, echemos un vistazo a la segunda vista para el análisis:

 => SELECT * FROM need_analyze WHERE tablename = 'public.autovac'; 

  tablename | mod_tup | max_mod_tup | last_autoanalyze ----------------+---------+-------------+------------------ public.autovac | 1000 | 0 | (1 row) 

Como la tabla ha cambiado (agregado) 1000 filas, y esto es más que cero, el análisis automático debería funcionar. Mira esto:

 => ALTER TABLE autovac SET (autovacuum_enabled = on); 

Después de una breve pausa, vemos que la tabla se analiza y en lugar de ceros en max_mod_tup vemos las 20 líneas correctas:

 => SELECT * FROM need_analyze WHERE tablename = 'public.autovac'; 

  tablename | mod_tup | max_mod_tup | last_autoanalyze ----------------+---------+-------------+------------------------------- public.autovac | 0 | 20 | 2019-05-21 11:59:48.465987+03 (1 row) 

 => SELECT reltuples, relpages FROM pg_class WHERE relname = 'autovac'; 

  reltuples | relpages -----------+---------- 1000 | 17 (1 row) 

Volvamos a la limpieza automática:

 => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 0 | 30 | (1 row) 

Max_dead_tup, como vemos, ya se ha solucionado. El segundo punto al que debe prestar atención es dead_tup = 0. Las estadísticas muestran que no hay versiones muertas de filas en la tabla ... y esto es cierto. Todavía no hay nada que limpiar en nuestra mesa. Por lo tanto, cualquier tabla que se use solo en el modo de solo agregar no se borrará y, por lo tanto, el mapa de visibilidad no se actualizará. Y esto hace que sea imposible utilizar exclusivamente el escaneo de índice (escaneo de solo índice).

(La próxima vez veremos que la limpieza tarde o temprano llegará a la tabla de solo agregar, pero esto sucederá muy raramente).

Conclusión práctica: si es importante utilizar solo el escaneo de índice, es posible que deba solicitar la limpieza manual.

Ahora apague la limpieza automática nuevamente y actualice la línea 31, una más que el valor umbral.

 => ALTER TABLE autovac SET (autovacuum_enabled = off); => UPDATE autovac SET s = 'B' WHERE id <= 31; => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 31 | 30 | (1 row) 

Ahora se cumple la condición para activar la limpieza automática. Active la limpieza automática y después de una breve pausa veremos que la tabla ha sido procesada:

 => ALTER TABLE autovac SET (autovacuum_enabled = on); => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+------------------------------- public.autovac | 0 | 30 | 2019-05-21 11:59:52.554571+03 (1 row) 

Regulación de la carga

La limpieza no bloquea otros procesos, ya que funciona página por página, pero crea una carga en el sistema y puede tener un efecto notable en el rendimiento.

Regulación para limpieza regular

Para poder controlar la intensidad de la limpieza y, en consecuencia, su efecto en el sistema, el proceso alterna entre el trabajo y las expectativas. La limpieza realiza aproximadamente vacío_cost_limit unidades de trabajo convencionales, y luego se queda dormido en vacuum_cost_delay ms.

La configuración predeterminada establece vacuum_cost_limit = 200, vacuum_cost_delay = 0. El último cero en realidad significa que la limpieza (normal) no se duerme, por lo que el valor específico de vacuum_cost_limit no juega ningún papel. Esto se hace porque si el administrador tuvo que iniciar VACUUM manualmente, probablemente quiera realizar la limpieza lo más rápido posible.

Sin embargo, si todavía configura el tiempo de suspensión, la cantidad de trabajo especificada en vacuum_cost_limit consistirá en el costo de trabajar con páginas en la memoria caché del búfer. Cada acceso a la página se evalúa de la siguiente manera:

• si la página se encontró en la memoria caché del búfer, entonces vacuum_cost_page_hit = 1;
• si no se encuentra, entonces vacuum_cost_page_miss = 10;
• si no pudo encontrarlo y tuvo que sacar la página sucia del búfer, entonces vacuum_cost_page_dirty = 20.

Es decir, con la configuración del vacío_cost_limit por defecto, 200 páginas del caché, o 20 páginas del disco, o 10 páginas con extrusión se pueden procesar de una vez. Está claro que se trata de números bastante arbitrarios, pero no tiene sentido seleccionarlos con mayor precisión.

Regulación para auto limpieza

El control de carga durante la limpieza automática funciona igual que para la limpieza regular. Pero para que la limpieza manual y la limpieza automática puedan funcionar con diferentes intensidades, los procesos automáticos tienen sus propios parámetros: autovacuum_vacuum_cost_limit y autovacuum_vacuum_cost_delay . Si estos parámetros toman el valor -1, se utiliza el valor de vacuum_cost_limit y / o vacuum_cost_delay .

Por defecto, autovacuum_vacuum_cost_limit = -1 (es decir, se utiliza el valor vacuum_cost_limit = 200) y autovacuum_vacuum_cost_delay = 20ms. En equipos modernos con estos números, la limpieza automática funcionará muy, muy lentamente.

En la versión 12, el valor de autovacuum_vacuum_cost_delay se reducirá a 2 ms, lo que puede considerarse una primera aproximación más apropiada.

Además, debe tenerse en cuenta que el límite establecido por estos parámetros es común a todos los procesos de trabajo. En otras palabras, a medida que cambia el número de procesos de trabajo concurrentes, la carga total permanecerá constante. Por lo tanto, si la tarea es aumentar el rendimiento de la limpieza automática , al agregar flujos de trabajo, vale la pena aumentar autovacuum_vacuum_cost_limit .

Uso de memoria y monitoreo

La última vez, vimos cómo la limpieza utiliza la memoria RAM del tamaño maintenance_work_mem para almacenar los identificadores de versión de las filas que se van a limpiar.

La limpieza automática hace exactamente lo mismo. Pero puede haber muchos procesos concurrentes si configura autovacuum_max_workers en un valor grande. Además, toda la memoria se asigna de forma inmediata y completa, y no por necesidad. Por lo tanto, para el flujo de trabajo de limpieza automática , puede establecer su propia restricción utilizando el parámetro autovacuum_work_mem . Por defecto, este parámetro es -1, es decir, no se usa.

Como ya se mencionó, la limpieza puede funcionar con una cantidad mínima de memoria. Pero si se crean índices en la tabla, entonces un pequeño valor de maintenance_work_mem puede conducir a escaneos de índice repetidos. Lo mismo es cierto para la limpieza automática. Idealmente, debe seleccionar un valor mínimo de autovacuum_work_mem en el que no se realicen exploraciones repetidas.

Vimos que para monitorear la limpieza puede usar el parámetro VERBOSE (pero no se puede especificar para la limpieza automática) o la vista pg_stat_progress_vacuum (pero solo muestra la información actual). Por lo tanto, la forma principal de supervisar la limpieza automática es el parámetro log_autovacuum_min_duration , que muestra información en el registro de mensajes del servidor. Por defecto está desactivado (establecido en -1). Hay una razón para habilitar este parámetro (con un valor de 0, se mostrará información sobre todos los inicios de limpieza automática) y observar los números.

Así es como se ve la salida:

 => ALTER SYSTEM SET log_autovacuum_min_duration = 0; => SELECT pg_reload_conf(); 

  pg_reload_conf ---------------- t (1 row) 

 => UPDATE autovac SET s = 'C' WHERE id <= 31; 

 student$ tail -n 7 /var/log/postgresql/postgresql-11-main.log 

 2019-05-21 11:59:55.675 MSK [9737] LOG: automatic vacuum of table "test.public.autovac": index scans: 0 pages: 0 removed, 18 remain, 0 skipped due to pins, 0 skipped frozen tuples: 31 removed, 1000 remain, 0 are dead but not yet removable, oldest xmin: 4040 buffer usage: 78 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 2019-05-21 11:59:55.676 MSK [9737] LOG: automatic analyze of table "test.public.autovac" system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 

Toda la información necesaria está presente aquí.

Recuerde que a menudo no debe aumentar el tamaño de la memoria, sino disminuir el umbral de limpieza para que se procesen menos datos a la vez.

También puede tener sentido controlar la longitud de la lista de tablas que deben limpiarse utilizando las vistas anteriores. Un aumento en la longitud de la lista indicará que la limpieza automática no tiene tiempo para hacer su trabajo y que es necesario cambiar la configuración.

Continuará