El efecto de la inflación en tablas e índices es ampliamente conocido y está presente no solo en Postgres. Hay formas de solucionarlo de forma inmediata, como VACUUM FULL o CLUSTER, pero bloquean las mesas durante el funcionamiento y, por lo tanto, no siempre se pueden utilizar.
El artículo contendrá una pequeña teoría sobre cómo ocurre la hinchazón, cómo combatirla, sobre las restricciones diferidas y los problemas que traen al uso de la extensión pg_repack.
Este artículo está escrito en base a en PgConf.Rusia 2020.
¿Por qué ocurre la hinchazón?
Postgres se basa en un modelo de múltiples versiones (). Su esencia es que cada fila de la tabla puede tener varias versiones, mientras que las transacciones no ven más de una de estas versiones, pero no necesariamente la misma. Esto permite que varias transacciones funcionen simultáneamente y prácticamente no tengan impacto entre sí.
Obviamente, todas estas versiones deben almacenarse. Postgres trabaja con la memoria página por página y una página es la cantidad mínima de datos que se pueden leer o escribir desde el disco. Veamos un pequeño ejemplo para entender cómo sucede esto.
Digamos que tenemos una tabla a la que le hemos agregado varios registros. Han aparecido nuevos datos en la primera página del archivo donde está almacenada la tabla. Estas son versiones en vivo de filas que están disponibles para otras transacciones después de una confirmación (para simplificar, asumiremos que el nivel de aislamiento es Lectura confirmada).
Luego actualizamos una de las entradas, marcando así la versión anterior como ya no relevante.
Paso a paso, actualizando y eliminando versiones de filas, terminamos con una página en la que aproximadamente la mitad de los datos son "basura". Estos datos no son visibles para ninguna transacción.
Postgres tiene un mecanismo , que limpia las versiones obsoletas y deja espacio para nuevos datos. Pero si no está configurado de manera suficientemente agresiva o está ocupado trabajando en otras tablas, entonces quedan “datos basura” y tenemos que usar páginas adicionales para datos nuevos.
Entonces, en nuestro ejemplo, en algún momento la tabla constará de cuatro páginas, pero solo la mitad contendrá datos activos. Como resultado, al acceder a la tabla leeremos muchos más datos de los necesarios.
Incluso si VACUUM ahora elimina todas las versiones de filas irrelevantes, la situación no mejorará dramáticamente. Tendremos espacio libre en páginas o incluso páginas enteras para nuevas filas, pero seguiremos leyendo más datos de los necesarios.
Por cierto, si al final del archivo hubiera una página completamente en blanco (la segunda en nuestro ejemplo), VACUUM podría recortarla. Pero ahora ella está en el medio, así que no se puede hacer nada con ella.
Cuando el número de páginas vacías o muy escasas aumenta, lo que se denomina hinchazón, comienza a afectar el rendimiento.
Todo lo descrito anteriormente es la mecánica de la aparición de hinchazón en las mesas. En los índices esto sucede prácticamente de la misma manera.
¿Tengo hinchazón?
Hay varias formas de determinar si tienes hinchazón. La idea del primero es utilizar estadísticas internas de Postgres, que contienen información aproximada sobre el número de filas en las tablas, el número de filas "en vivo", etc. Puede encontrar muchas variaciones de scripts ya preparados en Internet. Tomamos como base de expertos de PostgreSQL, que pueden evaluar tablas hinchadas junto con índices de árbol tostado y btree hinchados. Según nuestra experiencia, su error es del 10-20%.
Otra forma es usar la extensión. , que le permite mirar dentro de las páginas y obtener un valor de inflación estimado y exacto. Pero en el segundo caso, tendrás que escanear toda la tabla.
Consideramos aceptable un pequeño valor de inflación, hasta el 20%. Puede considerarse como un análogo del factor de relleno para и . Al 50% o más, pueden comenzar los problemas de rendimiento.
Maneras de combatir la hinchazón
Postgres tiene varias formas de lidiar con la hinchazón listas para usar, pero no siempre son adecuadas para todos.
Configure AUTOVACUUM para que no se produzca hinchazón. O más precisamente, para mantenerlo en un nivel aceptable para usted. Esto parece un consejo de “capitán”, pero en realidad no siempre es fácil de lograr. Por ejemplo, tiene un desarrollo activo con cambios regulares en el esquema de datos o se está llevando a cabo algún tipo de migración de datos. Como resultado, su perfil de carga puede cambiar con frecuencia y normalmente variará de una tabla a otra. Esto significa que usted necesita trabajar constantemente un poco por delante y ajustar AUTOVACUUM al perfil cambiante de cada mesa. Pero obviamente esto no es fácil de hacer.
Otra razón común por la que AUTOVACUUM no puede mantenerse al día con las tablas es porque hay transacciones de larga duración que le impiden limpiar los datos que están disponibles para esas transacciones. La recomendación aquí también es obvia: deshacerse de las transacciones "colgantes" y minimizar el tiempo de las transacciones activas. Pero si la carga de su aplicación es un híbrido de OLAP y OLTP, entonces puede tener simultáneamente muchas actualizaciones frecuentes y consultas breves, así como operaciones a largo plazo, por ejemplo, crear un informe. En tal situación, vale la pena pensar en distribuir la carga entre diferentes bases, lo que permitirá un mayor ajuste de cada una de ellas.
Otro ejemplo: incluso si el perfil es homogéneo, pero la base de datos está bajo una carga muy alta, es posible que incluso el AUTOVACUUM más agresivo no funcione y se produzca una hinchazón. El escalado (vertical u horizontal) es la única solución.
Qué hacer en una situación en la que ha configurado AUTOVACUUM, pero la hinchazón sigue creciendo.
Equipo VACÍO LLENO Reconstruye el contenido de tablas e índices y deja solo datos relevantes en ellos. Para eliminar la hinchazón funciona perfectamente, pero durante su ejecución se captura un bloqueo exclusivo en la tabla (AccessExclusiveLock), que no permitirá ejecutar consultas en esta tabla, ni siquiera selecciones. Si puede permitirse el lujo de detener su servicio o parte de él durante algún tiempo (desde decenas de minutos hasta varias horas, dependiendo del tamaño de la base de datos y su hardware), entonces esta opción es la mejor. Desafortunadamente, no tenemos tiempo para ejecutar VACUUM FULL durante el mantenimiento programado, por lo que este método no es adecuado para nosotros.
Equipo GRUPO Reconstruye el contenido de las tablas de la misma manera que VACUUM FULL, pero le permite especificar un índice según el cual los datos se ordenarán físicamente en el disco (pero en el futuro el orden no está garantizado para nuevas filas). En determinadas situaciones, esta es una buena optimización para varias consultas, con la lectura de varios registros por índice. La desventaja del comando es la misma que la de VACUUM FULL: bloquea la mesa durante la operación.
Equipo REINDEXAR similar a los dos anteriores, pero reconstruye un índice específico o todos los índices de la tabla. Los bloqueos son ligeramente más débiles: ShareLock en la tabla (evita modificaciones, pero permite la selección) y AccessExclusiveLock en el índice que se está reconstruyendo (bloquea las consultas que utilizan este índice). Sin embargo, en la versión 12 de Postgres apareció un parámetro , que le permite reconstruir el índice sin bloquear la adición, modificación o eliminación simultánea de registros.
En versiones anteriores de Postgres, puede lograr un resultado similar a REINDEXAR CONCURRENTE usando . Le permite crear un índice sin bloqueo estricto (ShareUpdateExclusiveLock, que no interfiere con consultas paralelas), luego reemplazar el índice anterior por uno nuevo y eliminar el índice anterior. Esto le permite eliminar la hinchazón del índice sin interferir con su aplicación. Es importante tener en cuenta que al reconstruir índices habrá una carga adicional en el subsistema del disco.
Por lo tanto, si para los índices hay formas de eliminar la hinchazón "sobre la marcha", entonces no hay ninguna para las tablas. Aquí entran en juego varias extensiones externas: (anteriormente pg_reorg), , y otros. En este artículo no los compararé y solo hablaré de pg_repack, que, después de algunas modificaciones, usamos nosotros mismos.
Cómo funciona pg_repack
Digamos que tenemos una tabla completamente normal, con índices, restricciones y, desafortunadamente, hinchada. El primer paso de pg_repack es crear una tabla de registro para almacenar datos sobre todos los cambios mientras se ejecuta. El activador replicará estos cambios para cada inserción, actualización y eliminación. Luego se crea una tabla, similar a la original en estructura, pero sin índices ni restricciones, para no ralentizar el proceso de inserción de datos.
A continuación, pg_repack transfiere los datos de la tabla anterior a la nueva, filtra automáticamente todas las filas irrelevantes y luego crea índices para la nueva tabla. Durante la ejecución de todas estas operaciones, los cambios se acumulan en la tabla de registro.
El siguiente paso es transferir los cambios a la nueva tabla. La migración se realiza en varias iteraciones y cuando quedan menos de 20 entradas en la tabla de registro, pg_repack adquiere un bloqueo fuerte, migra los datos más recientes y reemplaza la tabla antigua por la nueva en las tablas del sistema Postgres. Este es el único y muy corto tiempo en el que no podrá trabajar con la mesa. Después de esto, la tabla anterior y la tabla con registros se eliminan y se libera espacio en el sistema de archivos. El proceso está completo.
Todo parece genial en teoría, pero ¿qué sucede en la práctica? Probamos pg_repack sin carga y bajo carga, y comprobamos su funcionamiento en caso de parada prematura (en otras palabras, usando Ctrl+C). Todas las pruebas fueron positivas.
Fuimos a la tienda de alimentos y luego no todo salió como esperábamos.
Primer panqueque a la venta
En el primer clúster recibimos un error sobre una violación de una restricción única:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.
Esta limitación tenía un nombre generado automáticamente index_16508; fue creado por pg_repack. A partir de los atributos incluidos en su composición determinamos “nuestra” restricción que le corresponde. El problema resultó ser que esta no es una limitación completamente ordinaria, sino diferida (), es decir. su verificación se realiza más tarde que el comando sql, lo que tiene consecuencias inesperadas.
Restricciones diferidas: por qué son necesarias y cómo funcionan
Una pequeña teoría sobre las restricciones diferidas.
Consideremos un ejemplo simple: tenemos un libro de referencia de automóviles con dos atributos: el nombre y el orden del automóvil en el directorio.
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique
);
Digamos que necesitábamos intercambiar el primer y segundo auto. La solución sencilla es actualizar el primer valor al segundo y el segundo al primero:
begin;
update cars set ord = 2 where name = 'audi';
update cars set ord = 1 where name = 'bmw';
commit;
Pero cuando ejecutamos este código, esperamos una violación de la restricción porque el orden de los valores en la tabla es único:
[23305] ERROR: duplicate key value violates unique constraint “uk_cars”
Detail: Key (ord)=(2) already exists.
¿Cómo puedo hacerlo de manera diferente? Opción uno: agregar un reemplazo de valor adicional a un pedido que se garantiza que no existirá en la tabla, por ejemplo "-1". En programación, esto se llama “intercambiar los valores de dos variables por una tercera”. El único inconveniente de este método es la actualización adicional.
Opción dos: rediseñar la tabla para utilizar un tipo de datos de punto flotante para el valor del pedido en lugar de números enteros. Luego, al actualizar el valor de 1, por ejemplo, a 2.5, la primera entrada automáticamente "se ubicará" entre la segunda y la tercera. Esta solución funciona, pero existen dos limitaciones. Primero, no funcionará si el valor se usa en algún lugar de la interfaz. En segundo lugar, dependiendo de la precisión del tipo de datos, tendrá un número limitado de inserciones posibles antes de volver a calcular los valores de todos los registros.
Opción tres: posponer la restricción para que se verifique solo en el momento de la confirmación:
create table cars
(
name text constraint pk_cars primary key,
ord integer not null constraint uk_cars unique deferrable initially deferred
);Dado que la lógica de nuestra solicitud inicial garantiza que todos los valores sean únicos en el momento de la confirmación, tendrá éxito.
El ejemplo discutido anteriormente es, por supuesto, muy sintético, pero revela la idea. En nuestra aplicación, utilizamos restricciones diferidas para implementar la lógica responsable de resolver conflictos cuando los usuarios trabajan simultáneamente con objetos de widget compartidos en el tablero. El uso de tales restricciones nos permite simplificar un poco el código de la aplicación.
En general, dependiendo del tipo de restricción, Postgres tiene tres niveles de granularidad para verificarlas: niveles de fila, transacción y expresión.
Fuente:
CHECK y NOT NULL siempre se verifican a nivel de fila, para otras restricciones, como se puede ver en la tabla, existen diferentes opciones. Puedes leer más .
Para resumir brevemente, las restricciones diferidas en varias situaciones proporcionan código más legible y menos comandos. Sin embargo, hay que pagar por esto complicando el proceso de depuración, ya que el momento en que ocurre el error y el momento en que te enteras del mismo están separados en el tiempo. Otro posible problema es que el planificador no siempre pueda construir un plan óptimo si la solicitud implica una restricción diferida.
Mejora de pg_repack
Hemos cubierto qué son las restricciones diferidas, pero ¿cómo se relacionan con nuestro problema? Recordemos el error que recibimos anteriormente:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.Ocurre cuando los datos se copian de una tabla de registro a una tabla nueva. Esto parece extraño porque... los datos de la tabla de registro se confirman junto con los datos de la tabla de origen. Si satisfacen las restricciones de la tabla original, ¿cómo pueden violar las mismas restricciones en la nueva?
Resulta que la raíz del problema radica en el paso anterior de pg_repack, que crea solo índices, pero no restricciones: la tabla anterior tenía una restricción única y la nueva creó un índice único en su lugar.
Es importante señalar aquí que si la restricción es normal y no diferida, entonces el índice único creado es equivalente a esta restricción, porque Las restricciones únicas en Postgres se implementan mediante la creación de un índice único. Pero en el caso de una restricción diferida, el comportamiento no es el mismo, porque el índice no se puede diferir y siempre se verifica en el momento en que se ejecuta el comando sql.
Por lo tanto, la esencia del problema radica en el "retraso" de la verificación: en la tabla original ocurre en el momento de la confirmación, y en la nueva tabla en el momento de ejecutar el comando SQL. Esto significa que debemos asegurarnos de que los controles se realicen de la misma manera en ambos casos: siempre con retraso o siempre de forma inmediata.
Entonces, ¿qué ideas teníamos?
Crear un índice similar al diferido
La primera idea es realizar ambas comprobaciones en modo inmediato. Esto puede generar varias restricciones de falsos positivos, pero si son pocas, esto no debería afectar el trabajo de los usuarios, ya que este tipo de conflictos son una situación normal para ellos. Ocurren, por ejemplo, cuando dos usuarios comienzan a editar el mismo widget al mismo tiempo y el cliente del segundo usuario no tiene tiempo de recibir información de que el widget ya está bloqueado para editarlo por parte del primer usuario. En tal situación, el servidor rechaza al segundo usuario y su cliente revierte los cambios y bloquea el widget. Un poco más tarde, cuando el primer usuario complete la edición, el segundo recibirá información de que el widget ya no está bloqueado y podrá repetir su acción.
Para garantizar que las comprobaciones estén siempre en modo no diferido, creamos un nuevo índice similar a la restricción diferida original:
CREATE UNIQUE INDEX CONCURRENTLY uk_tablename__immediate ON tablename (id, index);
-- run pg_repack
DROP INDEX CONCURRENTLY uk_tablename__immediate;En el entorno de prueba, recibimos sólo unos pocos errores esperados. ¡Éxito! Ejecutamos pg_repack nuevamente en producción y obtuvimos 5 errores en el primer clúster en una hora de trabajo. Este es un resultado aceptable. Sin embargo, ya en el segundo clúster el número de errores aumentó significativamente y tuvimos que detener pg_repack.
¿Por qué sucedió? La probabilidad de que se produzca un error depende de cuántos usuarios estén trabajando con los mismos widgets al mismo tiempo. Aparentemente, en ese momento hubo muchos menos cambios competitivos con los datos almacenados en el primer cluster que en los demás, es decir. simplemente tuvimos "suerte".
La idea no funcionó. En ese punto, vimos otras dos soluciones: reescribir el código de nuestra aplicación para prescindir de las restricciones diferidas, o "enseñar" a pg_repack a trabajar con ellas. Elegimos el segundo.
Reemplace los índices en la nueva tabla con restricciones diferidas de la tabla original
El propósito de la revisión era obvio: si la tabla original tiene una restricción diferida, entonces para la nueva es necesario crear dicha restricción, y no un índice.
Para probar nuestros cambios, escribimos una prueba simple:
- tabla con restricción diferida y un registro;
- insertar datos en un bucle que entre en conflicto con un registro existente;
- haga una actualización: los datos ya no entran en conflicto;
- confirmar los cambios.
create table test_table
(
id serial,
val int,
constraint uk_test_table__val unique (val) deferrable initially deferred
);
INSERT INTO test_table (val) VALUES (0);
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO test_table VALUES (0) RETURNING id INTO v_id;
UPDATE test_table set val = i where id = v_id;
COMMIT;
END;
END LOOP;La versión original de pg_repack siempre fallaba en la primera inserción, la versión modificada funcionó sin errores. Excelente.
Pasamos a producción y nuevamente obtenemos un error en la misma fase de copiar datos de la tabla de registro a una nueva:
$ ./pg_repack -t tablename -o id
INFO: repacking table "tablename"
ERROR: query failed:
ERROR: duplicate key value violates unique constraint "index_16508"
DETAIL: Key (id, index)=(100500, 42) already exists.Situación clásica: ¿todo funciona en entornos de prueba, pero no en producción?
APPLY_COUNT y la unión de dos lotes
Comenzamos a analizar el código literalmente línea por línea y descubrimos un punto importante: los datos se transfieren de la tabla de registro a una nueva en lotes, la constante APPLY_COUNT indicaba el tamaño del lote:
for (;;)
{
num = apply_log(connection, table, APPLY_COUNT);
if (num > MIN_TUPLES_BEFORE_SWITCH)
continue; /* there might be still some tuples, repeat. */
...
}El problema es que los datos de la transacción original, en la que varias operaciones podrían potencialmente violar la restricción, cuando se transfieren, pueden terminar en la unión de dos lotes: la mitad de los comandos se confirmarán en el primer lote y la otra mitad. en el segundo. Y aquí, dependiendo de tu suerte: si los equipos no violan nada en el primer lote, entonces todo está bien, pero si lo hacen, se produce un error.
APPLY_COUNT es igual a 1000 registros, lo que explica por qué nuestras pruebas tuvieron éxito: no cubrieron el caso de "unión por lotes". Usamos dos comandos: insertar y actualizar, por lo que siempre se colocaron exactamente 500 transacciones de dos comandos en un lote y no experimentamos ningún problema. Después de agregar la segunda actualización, nuestra edición dejó de funcionar:
FOR i IN 1..10000 LOOP
BEGIN
INSERT INTO test_table VALUES (1) RETURNING id INTO v_id;
UPDATE test_table set val = i where id = v_id;
UPDATE test_table set val = i where id = v_id; -- one more update
COMMIT;
END;
END LOOP;Entonces, la siguiente tarea es asegurarse de que los datos de la tabla original, que se cambiaron en una transacción, terminen en la nueva tabla también dentro de una transacción.
Rechazo de procesamiento por lotes
Y nuevamente teníamos dos soluciones. Primero: abandonemos por completo la partición en lotes y transfiramos datos en una sola transacción. La ventaja de esta solución fue su simplicidad: los cambios de código requeridos fueron mínimos (por cierto, en versiones anteriores pg_reorg funcionaba exactamente así). Pero hay un problema: estamos creando una transacción de larga duración y esto, como ya hemos dicho, amenaza con el surgimiento de una nueva inflación.
La segunda solución es más compleja, pero probablemente más correcta: crear una columna en la tabla de registro con el identificador de la transacción que agregó datos a la tabla. Luego, cuando copiamos datos, podemos agruparlos por este atributo y asegurarnos de que los cambios relacionados se transfieran juntos. El lote se formará a partir de varias transacciones (o una grande) y su tamaño variará según la cantidad de datos que se hayan modificado en estas transacciones. Es importante tener en cuenta que, dado que los datos de diferentes transacciones ingresan a la tabla de registro en orden aleatorio, ya no será posible leerlos secuencialmente, como era antes. seqscan para cada solicitud con filtrado por tx_id es demasiado caro, se necesita un índice, pero también ralentizará el método debido a la sobrecarga de actualizarlo. En general, como siempre, hay que sacrificar algo.
Entonces, decidimos comenzar con la primera opción, ya que es más sencilla. En primer lugar, era necesario entender si una transacción larga sería un problema real. Dado que la transferencia principal de datos de la tabla antigua a la nueva también ocurre en una transacción larga, la pregunta se transformó en "¿cuánto aumentaremos esta transacción?" La duración de la primera transacción depende principalmente del tamaño de la tabla. La duración de una nueva depende de cuántos cambios se acumulan en la tabla durante la transferencia de datos, es decir de la intensidad de la carga. La ejecución de pg_repack se produjo durante un momento de carga de servicio mínima y el volumen de cambios fue desproporcionadamente pequeño en comparación con el tamaño original de la tabla. Decidimos que podíamos descuidar el tiempo de una nueva transacción (en comparación, en promedio es de 1 hora y 2-3 minutos).
Los experimentos fueron positivos. Lanzamiento en producción también. Para mayor claridad, aquí hay una imagen con el tamaño de una de las bases de datos después de ejecutarla:
Como estábamos completamente satisfechos con esta solución, no intentamos implementar la segunda, pero estamos considerando la posibilidad de discutirla con los desarrolladores de la extensión. Desafortunadamente, nuestra revisión actual aún no está lista para su publicación, ya que solo resolvimos el problema con restricciones diferidas únicas y para un parche completo es necesario brindar soporte para otros tipos. Esperamos poder hacer esto en el futuro.
Quizás tenga una pregunta: ¿por qué nos involucramos en esta historia con la modificación de pg_repack y no utilizamos, por ejemplo, sus análogos? En algún momento también pensamos en esto, pero la experiencia positiva de usarlo antes, en tablas sin restricciones diferidas, nos motivó a intentar comprender la esencia del problema y solucionarlo. Además, el uso de otras soluciones también requiere tiempo para realizar pruebas, por lo que decidimos que primero intentaríamos solucionar el problema y, si nos dábamos cuenta de que no podíamos hacerlo en un tiempo razonable, comenzaríamos a buscar análogos. .
Hallazgos
Lo que podemos recomendar según nuestra propia experiencia:
- Controla tu hinchazón. Según los datos de monitoreo, puede comprender qué tan bien está configurado el vacío automático.
- Ajuste AUTOVACUUM para mantener la hinchazón en un nivel aceptable.
- Si la hinchazón sigue creciendo y no puede superarla con herramientas listas para usar, no tema usar extensiones externas. Lo principal es probarlo todo bien.
- No tenga miedo de modificar soluciones externas para adaptarlas a sus necesidades; a veces esto puede ser más efectivo e incluso más fácil que cambiar su propio código.
Fuente: habr.com