Hey.
Mi nombre es Vanya y soy desarrollador de Java. Sucede que trabajo mucho con PostgreSQL: configuro la base de datos, optimizo la estructura, el rendimiento y juego un poco de DBA los fines de semana.
Recientemente ordené varias bases de datos en nuestros microservicios y escribí una biblioteca Java. , lo que facilita este trabajo, me ahorra tiempo y me ayuda a evitar algunos errores comunes cometidos por los desarrolladores. Es esta biblioteca de la que hablaremos hoy.
Observación
La versión principal de PostgreSQL con la que trabajo es la 10. Todas las consultas SQL que uso también se prueban en la versión 11. La versión mínima admitida es 9.6.
Prehistoria
Todo empezó hace casi un año con una situación que me resultó extraña: la creación competitiva de un índice de la nada terminó con un error. El índice en sí, como de costumbre, permaneció en la base de datos en un estado no válido. El análisis de registros mostró una escasez . Y listo... Profundizando más, descubrí un montón de problemas en la configuración de la base de datos y, arremangándome, comencé a solucionarlos con un brillo en los ojos.
Problema uno: configuración predeterminada
Probablemente todo el mundo esté bastante cansado de la metáfora sobre Postgres, que se puede ejecutar en una cafetera, pero... la configuración predeterminada realmente plantea una serie de preguntas. Como mínimo, vale la pena prestar atención a mantenimiento_trabajo_mem, límite_archivo_temp, declaración_tiempo de espera и lock_timeout.
En este caso, mantenimiento_trabajo_mem era el valor predeterminado de 64 MB, y límite_archivo_temp algo alrededor de 2 GB; simplemente no teníamos suficiente memoria para crear un índice en una tabla grande.
Por lo tanto, en pg-indice-salud Coleccioné una serie , en mi opinión, los parámetros que se deben configurar para cada base de datos.
Problema dos: índices duplicados
Nuestras bases de datos residen en unidades SSD y utilizamos HA-configuración con múltiples centros de datos, host maestro y n-número de réplicas. El espacio en disco es un recurso muy valioso para nosotros; no es menos importante que el rendimiento y el consumo de CPU. Por lo tanto, por un lado, necesitamos índices para una lectura rápida y, por otro lado, no queremos ver índices innecesarios en la base de datos, ya que consumen espacio y ralentizan la actualización de los datos.
Y ahora, habiendo restaurado todo. y habiendo visto suficiente , decidí organizar una “gran” purga. Resultó que a los desarrolladores no les gusta leer la documentación de la base de datos. No les gusta mucho. Debido a esto, surgen dos errores típicos: un índice creado manualmente en una clave primaria y un índice "manual" similar en una columna única. El hecho es que no son necesarios: Postgres hará todo por sí solo. Estos índices se pueden eliminar de forma segura y para ello han aparecido diagnósticos. .
Problema tres: índices que se cruzan
La mayoría de los desarrolladores novatos crean índices en una sola columna. Poco a poco, habiendo experimentado a fondo este negocio, las personas comienzan a optimizar sus consultas y a agregar índices más complejos que incluyen varias columnas. Así aparecen los índices en las columnas. A, A + B, A + B + C etcétera. Los dos primeros de estos índices se pueden descartar con seguridad, ya que son prefijos del tercero. Esto también ahorra mucho espacio en disco y existen diagnósticos para esto. .
Problema cuatro: claves foráneas sin índices
Postgres le permite crear restricciones de clave externa sin especificar un índice de respaldo. En muchas situaciones esto no es un problema y puede que ni siquiera se manifieste... Por el momento...
A nosotros nos pasó lo mismo: solo que en algún momento el host maestro comenzó a "agregar" a nosotros un trabajo que se ejecutaba según un cronograma y limpiaba la base de datos de pedidos de prueba. La CPU y el IO se desperdiciaron, las solicitudes se ralentizaron y se agotó el tiempo de espera, el servicio fue de quinientos. Análisis rápido mostró que consultas como:
delete from <table> where id in (…)En este caso, por supuesto, había un índice por identificación en la tabla de destino y se eliminaron muy pocos registros según la condición. Parecía que todo debería funcionar, pero, lamentablemente, no fue así.
El maravilloso vino al rescate. explicar analizar y dijo que además de eliminar registros en la tabla de destino, también hay una verificación de integridad referencial, y en una de las tablas relacionadas esta verificación falla escaneo secuencial debido a la falta de un índice adecuado. Así nació el diagnóstico .
Problema cinco: valor nulo en índices
De forma predeterminada, Postgres incluye valores nulos en los índices btree, pero generalmente no son necesarios allí. Por lo tanto, trato diligentemente de descartar estos valores nulos (diagnósticos ), creando índices parciales en columnas que aceptan valores NULL por tipo where <A> is not null. De esta manera pude reducir el tamaño de uno de nuestros índices de 1877 MB a 16 KB. Y en uno de los servicios, el tamaño de la base de datos disminuyó en total un 16% (4.3 GB en números absolutos) debido a la exclusión de valores nulos de los índices. Enormes ahorros de espacio en disco con modificaciones muy sencillas. 🙂
Problema seis: falta de claves primarias
Por la naturaleza del mecanismo. una situación como esta es posible cuando el tamaño de su tabla crece rápidamente debido a una gran cantidad de registros muertos. Ingenuamente creí que esto no nos amenazaría, y que esto no le pasaría a nuestra base, porque nosotros, ¡¡¡guau!!!, somos desarrolladores normales... Qué estúpido e ingenuo fui...
Un día, una maravillosa migración tomó y actualizó todos los registros en una tabla grande y utilizada activamente. Obtuvimos +100 GB en el tamaño de la tabla de la nada. Fue una lástima, pero nuestras desventuras no terminaron ahí. Después de que el autovacío en esta mesa terminó 15 horas después, quedó claro que la ubicación física no regresaría. No pudimos detener el servicio y hacer que VACUUM FULL, así que decidimos usar . Y luego resultó que pg_reempacar no sabe cómo procesar tablas sin una clave principal u otra restricción de unicidad, y nuestra tabla no tenía una clave principal. Así nació el diagnóstico .
En la versión de la biblioteca. 0.1.5 Se ha agregado la capacidad de recopilar datos de una gran cantidad de tablas e índices y responder a ellos de manera oportuna.
Problemas siete y ocho: índices insuficientes e índices no utilizados
Los dos diagnósticos siguientes son: и – apareció en su forma final hace relativamente poco tiempo. La cuestión es que no se pueden simplemente tomar y agregar.
Como ya escribí, utilizamos una configuración con varias réplicas y la carga de lectura en diferentes hosts es fundamentalmente diferente. Como resultado, resulta que algunas tablas e índices en algunos hosts prácticamente no se utilizan y, para el análisis, es necesario recopilar estadísticas de todos los hosts del clúster. Esto también es necesario en cada host del clúster; no puede hacerlo solo en el maestro.
Este enfoque nos permitió ahorrar varias decenas de gigabytes al eliminar índices que nunca se usaron, además de agregar índices faltantes a tablas que rara vez se usan.
Como conclusión
Por supuesto, para casi todos los diagnósticos puedes configurar . De esta manera, puede implementar rápidamente comprobaciones en su aplicación, evitando que aparezcan nuevos errores y luego corregir gradualmente los antiguos.
Algunos diagnósticos se pueden realizar en pruebas funcionales inmediatamente después de implementar las migraciones de bases de datos. Y esta es quizás una de las características más poderosas de mi biblioteca. Un ejemplo de uso se puede encontrar en .
Tiene sentido realizar comprobaciones de índices no utilizados o faltantes, así como de índices hinchados, sólo en una base de datos real. Los valores recopilados se pueden registrar en o enviado al sistema de seguimiento.
Realmente espero que pg-indice-salud será útil y tendrá demanda. También puede contribuir al desarrollo de la biblioteca informando los problemas que encuentre y sugiriendo nuevos diagnósticos.
Fuente: habr.com