¿Cómo crecer 10 veces por debajo del número de consultas de la base de datos sin pasar a un servidor más eficiente y mantener el sistema funcionando? Le diré cómo luchamos con la degradación del rendimiento de nuestra base de datos, cómo optimizamos las consultas SQL para atender a la mayor cantidad de usuarios posible y no aumentar el costo de los recursos informáticos.
Realizo un servicio de gestión de procesos de negocio en empresas constructoras. Alrededor de 3 mil empresas trabajan con nosotros. Más de 10 mil personas trabajan con nuestro sistema durante 4-10 horas todos los días. Resuelve varias tareas de planificación, alertas, advertencias, validaciones ... Utilizamos PostgreSQL 9.6. Tenemos alrededor de 300 tablas en la base de datos y todos los días se envían hasta 200 millones de solicitudes (10 mil diferentes). En promedio, tenemos 3-4 mil solicitudes por segundo, en los momentos más activos más de 10 mil solicitudes por segundo. La mayoría de las solicitudes son OLAP. Hay muchas menos adiciones, modificaciones y eliminaciones, es decir, la carga OLTP es relativamente pequeña. Le di todas estas cifras para que pueda evaluar el alcance de nuestro proyecto y comprender cómo nuestra experiencia puede serle útil.
La primera foto. Lírico
Cuando comenzamos el desarrollo, realmente no pensamos en qué tipo de carga habrá en la base de datos y qué haremos si el servidor deja de funcionar. Al diseñar la base de datos, seguimos las recomendaciones generales e intentamos no dispararnos en el pie, pero más allá de consejos generales como “no use el patrón de
Valores de Atributo de Entidad , no fuimos. Diseñado en base a los principios de normalización, evitando la redundancia de datos y no le importaba acelerar ciertas consultas. Tan pronto como llegaron los primeros usuarios, nos encontramos con un problema de rendimiento. Como de costumbre, no estábamos preparados para esto. Los primeros problemas fueron simples. Como regla general, todo se decidió agregando un nuevo índice. Pero llegó un momento en que los parches simples dejaron de funcionar. Al darnos cuenta de que no hay suficiente experiencia y que cada vez es más difícil entender la causa de los problemas, contratamos especialistas que nos ayudaron a configurar el servidor correctamente, conectar el monitoreo y mostrar dónde buscar para obtener
estadísticas .
La segunda foto. Estadística
Entonces, tenemos alrededor de 10 mil consultas diferentes que se ejecutan en nuestra base de datos por día. De estos 10 mil, hay monstruos que corren 2-3 millones de veces con un tiempo de ejecución promedio de 0.1-0.3 ms y hay consultas con un tiempo de ejecución promedio de 30 segundos que se llaman 100 veces al día.
No fue posible optimizar las 10 mil consultas, por lo que decidimos averiguar hacia dónde dirigir los esfuerzos para mejorar el rendimiento de la base de datos correctamente. Después de varias iteraciones, comenzamos a dividir las solicitudes en tipos.
TOP consultas
Estas son las consultas más difíciles que toman más tiempo (tiempo total). Estas son consultas que se llaman con mucha frecuencia o consultas que toman mucho tiempo (las consultas largas y frecuentes se optimizaron incluso en las primeras iteraciones de la lucha por la velocidad). Como resultado, el servidor pasa la mayor parte del tiempo en su ejecución en total. Además, es importante separar las solicitudes principales por el tiempo de ejecución total y por separado por tiempo de E / S. Las formas de optimizar tales consultas son ligeramente diferentes.
La práctica habitual de todas las empresas es trabajar con solicitudes TOP. Hay pocos de ellos, la optimización de incluso una solicitud puede liberar del 5 al 10% de los recursos. Sin embargo, a medida que el proyecto envejece, la optimización de las consultas TOP se convierte en una tarea cada vez más trivial. Todos los métodos simples ya se han resuelto, e incluso la solicitud más "difícil" quita "solo" el 3-5% de los recursos. Si las consultas TOP en total toman menos del 30-40% del tiempo, entonces lo más probable es que ya haya hecho esfuerzos para que funcionen rápidamente y es hora de pasar a optimizar las consultas del siguiente grupo.
Queda por responder la pregunta cuántas consultas principales incluir en este grupo. Por lo general, tomo no menos de 10, pero no más de 20. Intento asegurarme de que el tiempo del primero y el último en el grupo TOP no difiera más de 10 veces. Es decir, si el tiempo de ejecución de la consulta cae bruscamente de 1 lugar a 10, entonces tomo TOP-10, si la caída es más suave, entonces aumento el tamaño del grupo a 15 o 20.
Los campesinos medios (medianos)
Estas son todas las solicitudes que van inmediatamente después de TOP, con la excepción del último 5-10%. Por lo general, en la optimización de estas solicitudes particulares reside la capacidad de aumentar considerablemente el rendimiento del servidor. Estas consultas pueden "pesar" hasta un 80%. Pero incluso si su participación ha excedido el 50%, entonces es hora de mirarlos más de cerca.
Cola
Como se dijo, estas solicitudes se envían al final y toman del 5 al 10% del tiempo. Puede olvidarse de ellos solo si no utiliza herramientas de análisis de consultas automáticas, entonces su optimización también puede ser barata.
¿Cómo evaluar cada grupo?
Utilizo una consulta SQL que ayuda a hacer una evaluación de este tipo para PostgreSQL (estoy seguro de que para muchos otros DBMS puede escribir una consulta similar)
Consulta SQL para estimar el tamaño de los grupos TOP-MEDIUM-TAILSELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail FROM ( SELECT CASE WHEN rn <= 20 THEN tt_percent ELSE 0 END AS time_top, CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium, CASE WHEN rn > 800 THEN tt_percent ELSE 0 END AS time_tail FROM ( SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query, ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn FROM pg_stat_statements ORDER BY total_time DESC ) AS t ) AS ts
El resultado de la consulta son tres columnas, cada una de las cuales contiene un porcentaje del tiempo que se dedica a procesar las solicitudes de este grupo. Dentro de la consulta, hay dos números (en mi caso, 20 y 800) que separan las solicitudes de un grupo de otro.
Así es como las proporciones de solicitudes en el momento del inicio del trabajo de optimización ahora se correlacionan aproximadamente.
El diagrama muestra que la proporción de solicitudes TOP ha disminuido drásticamente, pero los "campesinos medios" han crecido.
Inicialmente, los errores TOP golpearon las consultas TOP. Con el tiempo, las enfermedades infantiles desaparecieron, la proporción de solicitudes TOP se redujo y se tuvieron que hacer más esfuerzos para acelerar las solicitudes difíciles.
Para obtener el texto de las solicitudes, usamos dicha solicitud SELECT * FROM ( SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query FROM pg_stat_statements ORDER BY total_time DESC ) AS T WHERE rn <= 20
Aquí hay una lista de los trucos más utilizados que nos ayudaron a acelerar las consultas TOP:
- Rediseñar sistemas, por ejemplo, procesar la lógica de notificación en el intermediario de mensajes en lugar de consultas periódicas
- Agregar o modificar índices
- Reescribir consultas ORM en SQL puro
- Reescribir lógica de carga de datos diferida
- Almacenamiento en caché a través de la desnormalización de datos. Por ejemplo, tenemos un enlace entre las tablas Entrega -> Factura -> Solicitud -> Solicitud. Es decir, cada entrega se asocia con la aplicación a través de otras tablas. Para no vincular todas las tablas en cada solicitud, duplicamos el enlace a la aplicación en la tabla Entrega.
- Almacenamiento en caché de tablas estáticas con directorios y rara vez cambio de tablas en la memoria del programa.
A veces, los cambios arrastraron un rediseño impresionante, pero dieron el 5-10% de la descarga del sistema y se justificaron. Con el tiempo, el escape se hizo cada vez menos, y el rediseño se hizo cada vez más serio.
Luego llamamos la atención sobre el segundo grupo de solicitudes: el grupo de campesinos medios. Tiene muchas más solicitudes y parecía que tomaría mucho tiempo analizar a todo el grupo. Sin embargo, la mayoría de las consultas resultaron ser muy simples para la optimización, y muchos problemas se repitieron docenas de veces en diferentes variaciones. Aquí hay ejemplos de algunas optimizaciones típicas que aplicamos a docenas de consultas similares y cada grupo de consultas optimizadas descargó la base de datos en un 3-5%.
- En lugar de verificar la presencia de registros con COUNT y un escaneo completo de la tabla, EXISTE
- Nos deshicimos de DISTINCT (no hay una receta general, pero a veces puedes deshacerte de ella fácilmente acelerando la solicitud 10-100 veces).
Por ejemplo, en lugar de consultar para seleccionar todos los controladores en una tabla de entrega grande (ENTREGA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
hizo una solicitud para una tabla PERSON relativamente pequeña
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
Parece que usamos una subconsulta de correlación, pero da una aceleración de más de 10 veces.
- En muchos casos, COUNT y
reemplazado por el cálculo del valor aproximado
- en lugar de
UPPER(s) LIKE JOHN%'
usar
s ILIKE “John%”
Cada solicitud específica a veces se aceleró 3-1000 veces. A pesar del rendimiento impresionante, al principio nos pareció que no tenía sentido optimizar la consulta, que se ejecutó durante 10 ms, incluida en el tercer centenar de las consultas más pesadas y en el tiempo total de carga de la base de datos tomó centésimas de porcentaje. Pero aplicando la misma receta a un grupo de solicitudes similares, recuperamos varios por ciento. Para no perder el tiempo visualizando manualmente los cientos de consultas, escribimos varios scripts simples que, utilizando expresiones regulares, encontraron consultas similares. Como resultado, la búsqueda automática de grupos de consulta nos permitió mejorar aún más nuestro rendimiento al realizar esfuerzos modestos.
Como resultado, hemos estado trabajando en el mismo hardware durante tres años. La carga diaria promedio es de aproximadamente el 30%, en los picos alcanza el 70%. La cantidad de solicitudes, así como la cantidad de usuarios, ha aumentado aproximadamente 10 veces. Y todo esto gracias al monitoreo constante de estos mismos grupos de consultas TOP-MEDIUM. Tan pronto como aparece una nueva solicitud en el grupo TOP, la analizamos inmediatamente e intentamos acelerarla. Revisamos el grupo MEDIO una vez por semana utilizando scripts de análisis de consultas. Si encuentra nuevas solicitudes que ya sabemos cómo optimizar, las cambiamos rápidamente. A veces encontramos nuevos métodos de optimización que se pueden aplicar a varias consultas a la vez.
Según nuestras previsiones, el servidor actual resistirá un aumento en el número de usuarios en otras 3-5 veces. Es cierto, tenemos una carta de triunfo más en la manga; todavía no hemos traducido las consultas SELECT al espejo, como se recomienda. Pero no lo hacemos conscientemente, ya que primero queremos agotar por completo las posibilidades de optimización "inteligente" antes de activar la "artillería pesada".
Una mirada crítica al trabajo realizado puede sugerir el uso de escala vertical. Compre un servidor más potente, en lugar de perder el tiempo de los especialistas. Es posible que el servidor no cueste tanto, especialmente porque los límites de escala vertical aún no se han agotado. Sin embargo, solo el número de solicitudes aumentó 10 veces. Durante varios años, la funcionalidad del sistema ha aumentado y ahora hay más variedades de solicitudes. La funcionalidad que se debió al almacenamiento en caché se realiza mediante menos solicitudes, además, solicitudes más eficientes. Por lo tanto, puede multiplicar de forma segura por otros 5 para obtener el coeficiente de aceleración real. Entonces, según las estimaciones más conservadoras, podemos decir que la aceleración fue de 50 o más veces. Agitar verticalmente el servidor 50 veces costaría más. Especialmente teniendo en cuenta que una vez que la optimización se lleva a cabo todo el tiempo, y cada mes llega una factura por un servidor alquilado.