Hola de nuevo El próximo martes, se inicia una nueva transmisión en el curso
"DBMS relacional" , por lo que seguimos publicando material útil sobre el tema. Vamos
La semana pasada escribí sobre el
acceso competitivo en Postgres , qué equipos se están bloqueando entre sí y cómo se pueden diagnosticar los equipos bloqueados. Por supuesto, después del diagnóstico, es posible que necesite tratamiento. Con Postgres, puedes dispararte en el pie, pero Postgres también te ofrece formas de no golpear la punta. Estos son algunos consejos importantes sobre cómo y cómo no hacerlo que encontramos útiles al trabajar con los usuarios al pasar de su única base de datos Postgres a
Citus o al crear nuevas aplicaciones de
análisis en tiempo real .
1. Nunca agregue una columna con un valor predeterminado
Regla Golden PostgreSQL: al agregar una columna a una tabla en un entorno de producción,
nunca especifique un valor predeterminado .
Agregar una columna requiere un bloqueo de tabla muy agresivo, que bloquea tanto la lectura como la escritura. Si agrega una columna con un valor predeterminado, PostgreSQL sobrescribirá la tabla completa para completar el valor predeterminado para cada fila, lo que puede tomar varias horas en tablas grandes. Al mismo tiempo, todas las solicitudes serán bloqueadas, por lo que su base de datos no estará disponible.
No hagas esto:
Hazlo mejor así:
O, mejor aún, evite actualizar y
delete bloqueos durante mucho tiempo, actualizando en pequeños lotes, por ejemplo:
do { numRowsUpdated = executeUpdate( "UPDATE items SET last_update = ? " + "WHERE ctid IN (SELECT ctid FROM items WHERE last_update IS NULL LIMIT 5000)", now); } while (numRowsUpdate > 0);
De esta forma, puede agregar y completar una nueva columna con una mínima interferencia para sus usuarios.
2. Tenga cuidado con las colas de bloqueo, use tiempos de espera
Cada bloqueo en PostgreSQL tiene una prioridad. Si la transacción B intenta apoderarse de un bloqueo que ya está retenido por la transacción A con un nivel de bloqueo en conflicto, la transacción B esperará en la cola de bloqueo. Ahora sucede algo interesante: si llega otra transacción C, tendrá que verificar no solo el conflicto con A, sino también con la transacción B y cualquier otra transacción en la cola de bloqueo.
Esto significa que, incluso si su comando DDL puede ejecutarse muy rápidamente, puede estar en la cola durante mucho tiempo, esperando que se completen
las solicitudes ,
y las solicitudes que se ejecutan después se bloquearán detrás de él .
Si puede encontrar largas consultas
SELECT en una tabla, no haga esto:
ALTER TABLE items ADD COLUMN last_update timestamptz;
Mejor haz esto:
SET lock_timeout TO '2s' ALTER TABLE items ADD COLUMN last_update timestamptz;
Si
lock_timeout establece
lock_timeout comando DDL no se ejecutará si está esperando un bloqueo y, por lo tanto, bloquea las solicitudes durante más de 2 segundos. La desventaja es que su
ALTER TABLE puede no ejecutarse, pero puede intentarlo más tarde. Puede consultar
pg_stat_activity para ver si tiene consultas largas antes de ejecutar el comando DDL.
3. Utilice la creación de índice sin bloqueo
Otra regla de oro de PostgreSQL: siempre use la creación de índice sin bloqueo.
Crear un índice para un conjunto de datos grande puede llevar horas o incluso días, y el comando
CREATE INDEX regular bloquea todos los registros durante la duración del comando. A pesar de que no bloquea los SELECT, sigue siendo bastante malo, y hay una mejor manera:
CREATE INDEX CONCURRENTLY .
No hagas esto:
En cambio, haz esto:
La creación de índices sin bloqueo tiene un inconveniente. Si algo sale mal, no retrocede y deja un índice incompleto ("no válido"). Si esto sucede, no te preocupes, solo corre
DROP INDEX CONCURRENTLY items_value_idx
e intenta crearlo de nuevo.
4. Use cerraduras agresivas lo más tarde posible
Cuando necesite ejecutar un comando que reciba bloqueos de tabla agresivos, intente hacerlo lo más tarde posible en la transacción para que las consultas puedan continuar el mayor tiempo posible.
Por ejemplo, si desea reemplazar completamente el contenido de la tabla. No hagas esto:
BEGIN;
En su lugar, cargue los datos en una nueva tabla y luego reemplace la anterior:
BEGIN; CREATE TABLE items_new (LIKE items INCLUDING ALL);
Hay un problema: no bloqueamos los registros desde el principio, y la vieja tabla de elementos podría haber cambiado para el momento en que la reiniciamos. Para evitar esto, podemos bloquear explícitamente la tabla para escribir, pero no para leer:
BEGIN; LOCK items IN EXCLUSIVE MODE; ...
A veces es mejor tomar el bloqueo en tus propias manos.
5. Agregar una clave primaria con un bloqueo mínimo
Agregar una clave principal a sus tablas suele ser una buena idea. Por ejemplo, si desea utilizar la replicación lógica o migrar una base de datos con
Citus Warp .
Postgres hace que sea muy fácil crear una clave primaria usando
ALTER TABLE , pero por ahora se crea un índice para la clave primaria, que puede llevar mucho tiempo si la tabla es grande, todas las solicitudes serán bloqueadas.
ALTER TABLE items ADD PRIMARY KEY (id);
Afortunadamente, puede hacer todo el trabajo duro primero utilizando
CREATE UNIQUE INDEX CONCURRENTLY , y luego usar el índice único como clave principal, que es una operación rápida.
CREATE UNIQUE INDEX CONCURRENTLY items_pk ON items (id);
La división de la creación de la clave primaria en dos etapas prácticamente no afecta al usuario.
6. Nunca use VACUUM FULL
La experiencia del usuario postgres a veces puede ser un poco increíble. Aunque
VACUUM FULL suena como lo que le gustaría hacer para limpiar el "polvo" de su base de datos, un comando más adecuado sería:
PLEASE FREEZE MY DATABASE FOR HOURS;
VACUUM FULL sobrescribe la tabla completa en el disco, lo que puede llevar horas o días, y al mismo tiempo bloquea todas las solicitudes. Aunque hay varios
VACUUM FULL uso válidos para
VACUUM FULL , como una tabla que solía ser grande, pero ahora es pequeña y todavía ocupa mucho espacio, pero probablemente esta no sea su opción.
Aunque debe esforzarse por configurar las opciones de limpieza automática y usar índices para acelerar las consultas, a veces puede ejecutar
VACUUM , pero NO
VACUUM FULL .
7. Evite los puntos muertos organizando comandos
Si ha estado usando PostgreSQL durante algún tiempo, lo más probable es que haya visto errores como:
ERROR: deadlock detected DETAIL: Process 13661 waits for ShareLock on transaction 45942; blocked by process 13483. Process 13483 waits for ShareLock on transaction 45937; blocked by process 13661.
Esto sucede cuando las transacciones concurrentes toman los mismos bloqueos en un orden diferente. Por ejemplo, una transacción ejecuta los siguientes comandos.
BEGIN; UPDATE items SET counter = counter + 1 WHERE key = 'hello';
Al mismo tiempo, otra transacción puede emitir los mismos comandos, pero en un orden diferente.
BEGIN UPDATE items SET counter = counter + 1 WHERE key = 'world';
Si estos bloques de transacciones se ejecutan al mismo tiempo, es probable que se queden atascados y nunca terminen. Postgres reconoce esta situación en aproximadamente un segundo y cancelará una de las transacciones para completar la otra. Cuando esto sucede, debe echar un vistazo a su aplicación para averiguar si puede asegurarse de que sus transacciones siempre se ejecuten en el mismo orden. Si ambas transacciones cambian
hello primero, luego
world , entonces la primera transacción bloqueará la segunda en
hello antes de que pueda capturar cualquier otra cerradura.
¡Comparte tus consejos!
Esperamos que encuentre útiles estas recomendaciones. Si tiene otros consejos, no dude en tuitear
@citusdata o nuestra comunidad activa de usuarios de Citus en
Slack .
Le recordamos que en unas horas habrá un
día abierto en el que hablaremos en detalle sobre el programa para el próximo curso.