Para recordarle, comenzamos con problemas relacionados con el aislamiento , hicimos una digresión sobre la estructura de datos de bajo nivel , discutimos las versiones de fila en detalle y observamos cómo se obtienen las instantáneas de datos de las versiones de fila.

Luego exploramos el vacío en la página (y las actualizaciones HOT) y el vacío . Ahora analizaremos el vacío automático.

Autovacuum

Ya hemos mencionado que normalmente (es decir, cuando nada retiene el horizonte de transacciones durante mucho tiempo) VACUUM generalmente hace su trabajo. El problema es con qué frecuencia llamarlo.

Si aspiramos una mesa para cambiar pañales muy raramente, su tamaño crecerá más de lo deseado. Además, una próxima operación de vacío puede requerir varios pases a través de índices si se realizaron demasiados cambios.

Si aspiramos la mesa con demasiada frecuencia, el servidor realizará constantemente tareas de mantenimiento en lugar de trabajo útil, y esto tampoco es bueno.

Tenga en cuenta que el lanzamiento de VACUUM en el horario de ninguna manera resuelve el problema porque la carga de trabajo puede cambiar con el tiempo. Si la tabla comienza a cambiar más intensamente, debe aspirarse con más frecuencia.

Autovacuum es exactamente la técnica que nos permite lanzar la aspiradora dependiendo de cuán intensivos sean los cambios en la tabla.

Cuando autovacuum está activado (el conjunto de parámetros de configuración de autovacuum ), se inicia el proceso del demonio del iniciador de autovacuum , que planifica el trabajo. La aspiración en sí se realiza mediante procesos de trabajo de aspiración automática , varios de los cuales pueden ejecutarse en paralelo.

El proceso del iniciador de autovacuum compone una lista de bases de datos donde se realiza cualquier actividad. La actividad se determina a partir de estadísticas y, para recopilarla, se debe establecer el parámetro track_counts . Nunca apague autovacuum y track_counts , de lo contrario, la función autovacuum no funcionará.

Una vez cada segundo de autovacuum_naptime segundos, el iniciador de autovacuum inicia (utilizando el proceso postmaster ) un proceso de trabajo para cada base de datos en la lista. En otras palabras, si hay alguna actividad en una base de datos, los procesos de trabajo se enviarán a ella en un intervalo de autovacuum_naptime segundos. Con este fin, si hay algunas (N) bases de datos activas disponibles, los procesos de trabajo se inician N veces más que cada segundo de autovacuum_naptime segundos. Pero el número total de procesos de trabajo que se ejecutan simultáneamente está limitado por el parámetro autovacuum_max_workers .

Cuando se inicia, un proceso de trabajo se conecta a la base de datos asignada y comienza con la composición de una lista de:

• Todas las tablas, vistas materializadas y tablas TOAST que requieren pasar la aspiradora.
• Todas las tablas y vistas materializadas que requieren análisis (las tablas TOAST no se analizan ya que siempre se alcanzan con acceso al índice).

Luego, el proceso de trabajo pasa la aspiradora y / o analiza los objetos de la lista uno a la vez y se completa cuando finaliza la aspiración.

Si el proceso no pudo hacer todo el trabajo planificado en segundos de autovacuum_naptime , el proceso de inicio de autovacuum enviará un proceso de trabajo más a esta base de datos, y trabajarán juntos. “Juntos” solo significa que el segundo proceso creará su propia lista y la superará. Por lo tanto, solo se procesarán tablas diferentes en paralelo, pero no hay paralelismo a nivel de una tabla: si uno de los procesos de trabajo ya está manejando una tabla, otro proceso lo omitirá y continuará.

Ahora aclaremos con más detalle qué se entiende por "requiere aspiración" y "requiere análisis".

Recientemente se comprometió el parche que permite que el vacío procese índices en paralelo con los trabajadores en segundo plano.

¿Qué mesas requieren pasar la aspiradora?

Se considera que se requiere aspirar si el número de tuplas muertas (es decir, desactualizadas) excede el umbral especificado. El recopilador de estadísticas realiza un seguimiento permanente del número de tuplas muertas, que se almacena en la tabla pg_stat_all_tables . Y dos parámetros especifican el umbral:

• autovacuum_vacuum_threshold define un valor absoluto (el número de tuplas).
• autovacuum_vacuum_scale_factor define el reparto de filas en la tabla.

En resumen: se requiere pasar la aspiradora si pg_stat_all_tables.n_dead_tup > = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltupes .

Con la configuración predeterminada, autovacuum_vacuum_threshold = 50 y autovacuum_vacuum_scale_factor = 0.2. autovacuum_vacuum_scale_factor es, sin duda, lo más importante aquí: es este parámetro el que es crítico para las tablas grandes (y es a ellas a las que se asocian posibles problemas). El valor del 20% parece excesivamente alto, y lo más probable es que deba reducirse considerablemente.

Los valores óptimos de los parámetros pueden variar para diferentes tablas y dependen de los tamaños de tabla y los detalles de los cambios. Tiene sentido establecer valores generalmente adecuados y, si surge la necesidad, hacer ajustes especiales de los parámetros a nivel de ciertas tablas mediante parámetros de almacenamiento:

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

Para evitar confundirse, es razonable hacerlo solo para unas pocas tablas que se distinguen entre el resto por la cantidad e intensidad de los cambios y solo cuando los valores establecidos globalmente no funcionan bien.

Además, puede desactivar el vacío automático en el nivel de la mesa (aunque difícilmente podemos pensar en una razón por la que podría ser necesario):

• autovacuum_enabled y toast.autovacuum_enabled .

Por ejemplo, la última vez que creamos la mesa de aspiración con autovacío desactivado para controlar manualmente la aspiración con fines de demostración. El parámetro de almacenamiento se puede cambiar de la siguiente manera:

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

Para formalizar todo lo anterior, creemos una vista que muestre qué tablas necesitan pasar la aspiradora en este momento. Utilizará la función que devuelve el valor actual del parámetro y tiene en cuenta que el valor se puede redefinir a nivel de tabla:

 => CREATE FUNCTION get_value(param text, reloptions text[], relkind "char") RETURNS float AS $$ SELECT coalesce( -- if the storage parameter is set, we take its value (SELECT option_value FROM pg_options_to_table(reloptions) WHERE option_name = CASE -- for TOAST tables, the parameter name differs WHEN relkind = 't' THEN 'toast.' ELSE '' END || param ), -- otherwise, we take the value of the configuration parameter current_setting(param) )::float; $$ LANGUAGE sql; 

Y esta es 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 requieren análisis?

La situación con el análisis automático es similar. Se considera que esas tablas requieren un análisis cuyo número de tuplas actualizadas (desde el último análisis) excede el umbral especificado por dos parámetros similares: pg_stat_all_tables.n_mod_since_analyze > = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltupes .

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

• autovacuum_analyze_threshold
• autovacuum_analyze_scale_factor

Como las tablas TOAST no se analizan, no tienen tales parámetros.

También 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

Establezcamos los siguientes valores de parámetros para experimentos:

 => ALTER SYSTEM SET autovacuum_naptime = '1s'; -- to aviod waiting long => 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 creemos una tabla similar a la utilizada la última vez e inserte mil filas en ella. Autovacuum está desactivado en el nivel de la mesa, y lo activaremos nosotros mismos. Sin esto, los ejemplos no serán reproducibles ya que el autovacuuming puede activarse en un mal momento.

 => 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 nuestra visión para aspirar mostrará:

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

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

Aquí se debe prestar atención a dos cosas. Primero, max_dead_tup = 0, aunque el 3% de 1000 filas forman 30 filas. Lo que pasa es que todavía no tenemos estadísticas sobre la mesa ya que INSERT no la actualiza por sí sola. Hasta que se analice la tabla, los ceros permanecerán desde pg_class.reltuples = 0. Pero veamos 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 se han cambiado (agregado) 1000 filas en la tabla, que es mayor que cero, se debe activar el análisis automático. Vamos a ver esto:

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

Después de una breve pausa, podemos ver que la tabla ha sido analizada y se muestran 20 filas correctas en max_dead_tup en lugar de ceros:

 => 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 al autovacuuming:

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

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

Como podemos ver, max_dead_tup ya se ha solucionado. Otra cosa a la que debe prestar atención es que dead_tup = 0. Las estadísticas muestran que la tabla no tiene tuplas muertas ..., y esto es cierto. Todavía no hay nada que aspirar en la mesa. Cualquier tabla utilizada exclusivamente en el modo de solo agregar no se pasará al vacío y, por lo tanto, el mapa de visibilidad no se actualizará. Pero esto hace que el uso de escaneo de solo índice sea imposible.

(La próxima vez veremos que pasar la aspiradora tarde o temprano alcanzará una tabla de solo agregar, pero esto sucederá muy raramente).

Una lección aprendida: si el escaneo de solo índice es crítico, puede ser necesario llamar manualmente a un proceso de vacío.

Ahora apaguemos el vacío automático nuevamente y actualicemos 31 líneas, que es una línea mayor que el 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 de activación por vacío. Activemos el vacío automático 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) 

Estrangulamiento de carga

VACUUM no bloquea otros procesos ya que funciona página por página, pero produce una carga adicional en el sistema y puede afectar significativamente el rendimiento.

Estrangulamiento por vacío

Para poder controlar la intensidad del vacío y, por lo tanto, su efecto en el sistema, el proceso alterna el trabajo y la espera. El proceso hará alrededor de vacuum_cost_limit unidades convencionales de trabajo y luego dormirá por vacuum_cost_delay ms.

Las configuraciones predeterminadas son vacuum_cost_limit = 200 y vacuum_cost_delay = 0. El último cero en realidad significa que VACUUM no duerme, por lo que un valor específico de vacuum_cost_limit no importa en absoluto. El razonamiento detrás de esto es que si un administrador tuvo que iniciar VACUUM manualmente, es probable que desee que la aspiradora se realice lo más rápido posible.

Sin embargo, si establecemos el tiempo de sueño, la cantidad de trabajo especificada en vacuum_cost_limit estará compuesta por los costos de trabajo con páginas en la memoria caché del búfer. Cada acceso a la página se estima de la siguiente manera:

• Si la página se encuentra en la memoria caché del búfer, vacuum_cost_page_hit = 1.
• Si no se encontró, vacuum_cost_page_miss = 10.
• Si no se encontró y una página sucia tuvo que ser desalojada adicionalmente de la memoria caché del búfer, vacuum_cost_page_dirty = 20.

Es decir, con la configuración predeterminada de vacuum_cost_limit , se pueden procesar de una sola vez 200 páginas de caché o 20 páginas de disco o 10 páginas con desalojo. Está claro que estas cifras son bastante provisionales, pero no tiene sentido seleccionar las más precisas.

Estrangulamiento para autovacuuming

Para procesos de vacío, el estrangulador de carga funciona de la misma manera que para VACUUM. Pero para los procesos de autovacuum y VACUUM lanzado manualmente para trabajar con diferente intensidad, autovacuum tiene sus propios parámetros: autovacuum_vacuum_cost_limit y autovacuum_vacuum_cost_delay . Si estos parámetros tienen el valor de -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 de vacuum_cost_limit = 200) y autovacuum_vacuum_cost_delay = 20 ms. En hardware moderno, el vacío automático será realmente lento.

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

Además, debemos tener en cuenta que el límite especificado por esta configuración es común para todos los procesos de trabajo. En otras palabras, cuando se cambia el número de procesos de trabajo simultáneos, la carga general permanece sin cambios. Por lo tanto, para aumentar el rendimiento de autovacuum, al agregar procesos de trabajo, tiene sentido aumentar también autovacuum_vacuum_cost_limit .

Uso de memoria y monitoreo.

La última vez observamos cómo VACUUM usaba RAM de tamaño maintenance_work_mem para almacenar los tid que se aspirarían.

Autovacuum hace absolutamente lo mismo. Pero puede haber muchos procesos de trabajo simultáneos si autovacuum_max_workers se establece en un valor grande. Además, toda la memoria se asigna a la vez en lugar de cuando surja la necesidad. Por lo tanto, para un proceso de trabajo, su propia limitación se puede establecer mediante el parámetro autovacuum_work_mem . El valor predeterminado de este parámetro es -1, es decir, no se usa.

Como ya se mencionó, VACUUM también puede funcionar con un tamaño mínimo de memoria. Pero si se crean índices en la tabla, un pequeño valor de maintenance_work_mem puede implicar escaneos de índice repetidos. Lo mismo es cierto para autovacuum. Idealmente, autovacuum_work_mem debería tener un valor mínimo tal que no ocurran escaneos repetidos.

Hemos visto que para monitorear VACUUM, se puede usar la opción VERBOSE (que no se puede especificar para autovacuum) o la vista pg_stat_progress_vacuum (que, sin embargo, solo muestra la información actual). Por lo tanto, el principal medio para monitorear el autovacuuming es usar el parámetro log_autovacuum_min_duration , que envía la información al registro de mensajes del servidor. Está desactivado de forma predeterminada (establecido en -1). Es razonable activar este parámetro (con el valor de 0, se generará información sobre todas las ejecuciones de vacío automático) y ver las cifras.

Así es como se ve la información de 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á disponible aquí.

Para recordarle, a menudo tiene sentido reducir el umbral de activación por vacío para procesar menos datos a la vez en lugar de aumentar el tamaño de la memoria.

También puede ser razonable usar las vistas anteriores para controlar la longitud de la lista de tablas que requieren pasar la aspiradora. El aumento de la longitud de la lista indicará que los procesos de vacío automático carecen de tiempo para hacer su trabajo y la configuración debe cambiarse.

Continuará