Hola.
Me llamo Vanya y soy desarrollador Java. Por casualidad, he trabajado mucho con PostgreSQL: estoy encargado de configurar bases de datos, optimizar estructuras, mejorar el rendimiento y un poco de DBA los fines de semana.
Últimamente, he ordenado varias bases de datos en nuestros microservicios y he escrito una biblioteca de Java , que facilita este trabajo, ahorra mi tiempo y ayuda a evitar algunos errores comunes que cometen los desarrolladores. De eso es de lo que hablaremos hoy.
Descargo de responsabilidad
La versión principal de PostgreSQL con la que trabajo es la 10. Todas las consultas SQL que utilizo también han sido probadas en la versión 11. La versión mínima compatible es la 9.6.
Antecedentes
Todo comenzó hace casi un año con una situación extraña para mí: la creación concurrente de un índice de repente falló. El índice, como suele suceder, quedó en un estado no válido en la base de datos. El análisis de los registros mostró la falta de . Y comenzó… Al profundizar, descubrí un montón de problemas en la configuración de la base de datos y, arremangándome, me puse a solucionarlos con entusiasmo.
El primer problema - la configuración por defecto
Probablemente, la metáfora de un Postgres que se puede ejecutar en una cafetera ya ha cansado a muchos, pero… la configuración por defecto realmente plantea varias preguntas. Como mínimo, vale la pena prestar atención a maintenance_work_mem, temp_file_limit, statement_timeout y lock_timeout.
En nuestro caso maintenance_work_mem era por defecto de 64 megabytes, y temp_file_limit algo alrededor de 2 gigabytes – simplemente no teníamos suficiente memoria para crear un índice en una tabla grande.
Por eso, en pg-index-health reuní una serie de , que en mi opinión, vale la pena ajustar para cada base de datos.
El segundo problema - índices duplicados
Nuestras bases viven en discos SSD, y utilizamos HA-configuraciones con múltiples centros de datos, un host maestro y n-un número de réplicas. El espacio en disco es un recurso muy valioso para nosotros; es tan importante como el rendimiento y el consumo de CPU. Por lo tanto, por un lado, necesitamos índices para una lectura rápida, pero por otro lado, no queremos ver índices innecesarios en la base de datos, ya que consumen espacio y ralentizan la actualización de datos.
Y así, después de restaurar todos los y tras haber visto , decidí hacer una «gran» limpieza. Resultó que a los desarrolladores no les gusta leer la documentación de la base de datos. No les gusta en absoluto. Por esta razón, surgen dos errores comunes: un índice creado manualmente en la clave primaria y un índice similar “manual” en una columna única. La cuestión es que no son necesarios; Postgres se encarga de ello. Estos índices se pueden eliminar sin problema, y para esto se ha creado un diagnóstico. .
El tercer problema: índices superpuestos
La mayoría de los desarrolladores principiantes crea índices en una sola columna. Gradualmente, al experimentar y probar esta tarea, las personas comienzan a optimizar sus consultas y agregar índices más complejos que incluyen varias columnas. Así surgen índices en columnas. A, A+B, A+B+C y así sucesivamente. Los dos primeros de estos índices se pueden eliminar sin problema, ya que son prefijos del tercero. Esto también ahorra espacio en disco, y para esto hay un diagnóstico. .
El cuarto problema: claves externas sin índices
Postgres permite crear restricciones de clave externa sin especificar un índice de soporte. En muchas situaciones, esto no es un problema y ni siquiera se manifiesta... Hasta que llega el momento...
Así nos pasó a nosotros: en un momento dado, un trabajo programado que limpiaba la base de datos de pedidos de prueba comenzó a ‘acumular’ nuestro servidor maestro. El CPU y el IO se dispararon, las consultas se ralentizaron y fueron interrumpidas por tiempo de espera, el servicio ofrecía errores 500. Un análisis rápido mostró que las consultas del tipo:
eliminar de <table> donde id está en (…)Mientras tanto, el índice por id en la tabla de destino, por supuesto, estaba presente, y se eliminaban muy pocas entradas según la condición. Parecía que todo debería funcionar, pero, lamentablemente, no funcionaba.
El maravilloso explain analyze vino al rescate y reveló que, además de eliminar registros en la tabla de destino, también se estaba llevando a cabo una verificación de la integridad referencial, y en una de las tablas relacionadas esta verificación se caía en un escaneo secuencial debido a la falta de un índice adecuado. Así nació el diagnóstico. .
El quinto problema: valor null en los índices
Por defecto, Postgres incluye valores null en los índices btree, pero generalmente no son necesarios allí. Por lo tanto, me esfuerzo por eliminar estos nulls (diagnóstico ), creando índices parciales en columnas que admiten null con la condición donde no es null. De este modo, 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 total de la base de datos se redujo en un 16% (en 4.3 GB en números absolutos) al eliminar valores nulos de los índices. Un ahorro colosal de espacio en disco con mejoras bastante simples. 🙂
El sexto problema: falta de claves primarias
Debido a las peculiaridades del mecanismo puede darse la situación en la que , el tamaño de su tabla crece rápidamente debido a una gran cantidad de registros muertos. Naïvamente pensé que eso no nos afectaría, y que nuestra base no debería tener ese problema, ya que somos, ¡vaya!, desarrolladores normales... Qué tonto y ingenuo fui...
Un buen día, una maravillosa migración se encargó de actualizar todos los registros en una tabla grande que se utiliza activamente. Obtuvimos +100 GB en el tamaño de la tabla de la nada. Fue realmente frustrante, pero nuestras desventuras no terminaron ahí. Después de 15 horas, el autovacuum en esta tabla terminó, y quedó claro que el espacio físico no regresaría. No podíamos detener el servicio para realizar un VACUUM FULL, así que se decidió usar . Y aquí resultó que pg_repack no puede manejar tablas sin llave primaria o algún otro tipo de restricción de unicidad, y nuestra tabla no tenía una llave primaria. Así nació el diagnóstico .
En la versión de la biblioteca 0.1.5 se agregó la posibilidad de recopilar datos sobre el bloat de las tablas e índices y reaccionar a tiempo.
Los problemas siete y ocho: falta de índices e índices no utilizados
Los siguientes dos diagnósticos son y – en su forma final aparecieron relativamente recientemente. La cuestión es que no se podían añadir así como así.
Como ya mencioné, utilizamos una configuración con múltiples réplicas, y la carga de lectura en diferentes hosts varía significativamente. Como resultado, existen tablas e índices en algunos hosts que prácticamente no se utilizan, y para el análisis es necesario recopilar estadísticas de todos los hosts en el clúster. también es necesario en cada host del clúster, no se puede hacer solo en el maestro.
Este enfoque nos permitió ahorrar varios decenas de gigabytes al eliminar índices que nunca se usaron, así como agregar los índices faltantes en tablas poco utilizadas.
En conclusión
Por supuesto, prácticamente todas las diagnósticas se pueden configurar . De este modo, se pueden implementar rápidamente las comprobaciones en su aplicación, evitando la aparición de nuevos errores y luego corrigiendo gradualmente los antiguos.
Algunas diagnósticas pueden ejecutarse ya en pruebas funcionales justo después de haber realizado las migraciones de la base de datos. Y esta es, sin duda, una de las características más potentes de mi biblioteca. Puede ver un ejemplo de uso en .
Las comprobaciones de índices no utilizados o faltantes, así como el bloat, solo tienen sentido realizarse sobre una base de datos real. Los valores recopilados pueden ser registrados en o enviados al sistema de monitoreo.
Espero sinceramente que pg-index-health sea útil y demandada. También puede contribuir al desarrollo de la biblioteca informando sobre problemas encontrados y sugiriendo nuevas diagnósticas.
Fuente: habr.com
