¿Cómo aumentar 10 veces el número de consultas a la base de datos sin pasar a un servidor más productivo y mantener la funcionalidad del sistema? Le contaré cómo lidiamos con la disminución en el 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. Cerca de 3 mil empresas trabajan con nosotros. Más de 10 mil personas trabajan con nuestro sistema todos los días durante 4 a 10 horas. Resuelve diversos problemas de planificación, notificación, aviso, validación... Usamos PostgreSQL 9.6. Tenemos alrededor de 300 tablas en la base de datos y cada día se reciben hasta 200 millones de consultas (10 mil diferentes). En promedio tenemos entre 3 y 4 mil solicitudes por segundo, en los momentos más activos más de 10 mil solicitudes por segundo. La mayoría de las consultas son OLAP. Hay muchas menos adiciones, modificaciones y eliminaciones, lo que significa que la carga OLTP es relativamente ligera. Proporcioné todos estos números para que pueda evaluar la escala de nuestro proyecto y comprender lo útil que puede ser nuestra experiencia para usted.
Imagen uno. Lírico
Cuando comenzamos el desarrollo, realmente no pensamos en qué tipo de carga recaería sobre la base de datos y qué haríamos si el servidor dejara de funcionar. Al diseñar la base de datos, seguimos recomendaciones generales y tratamos de no dispararnos en el pie, pero fuimos más allá de consejos generales como "no uses el patrón". no entramos. Diseñamos basándonos en los principios de normalización, evitando la redundancia de datos y no nos preocupamos por acelerar ciertas consultas. Tan pronto como llegaron los primeros usuarios, nos encontramos con un problema de rendimiento. Como siempre, no estábamos preparados para esto. Los primeros problemas resultaron sencillos. Como regla general, todo se resolvió agregando un nuevo índice. Pero llegó un momento en que los parches simples dejaron de funcionar. Al darnos cuenta de que nos falta experiencia y cada vez nos resulta más difícil entender qué está causando los problemas, contratamos especialistas que nos ayudaron a configurar el servidor correctamente, conectar el monitoreo y nos mostraron dónde buscar para obtener .
Imagen dos. Estadístico
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 se ejecutan entre 2 y 3 millones de veces con un tiempo de ejecución promedio de 0.1 a 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 nuestros esfuerzos para mejorar correctamente el rendimiento de la base de datos. Después de varias iteraciones, comenzamos a dividir las solicitudes en tipos.
Solicitudes principales
Estas son las consultas más pesadas y que toman más tiempo (tiempo total). Estas son consultas que se llaman con mucha frecuencia o consultas que tardan mucho tiempo en ejecutarse (las consultas largas y frecuentes se optimizaron en las primeras iteraciones de la lucha por la velocidad). Como resultado, el servidor dedica la mayor parte del tiempo a su ejecución. Además, es importante separar las solicitudes principales por tiempo total de ejecución y por separado por tiempo de IO. Los métodos para optimizar este tipo de consultas son ligeramente diferentes.
La práctica habitual de todas las empresas es trabajar con solicitudes TOP. Hay pocos; optimizar incluso una consulta puede liberar entre un 5 y un 10 % de los recursos. Sin embargo, a medida que el proyecto madura, optimizar las consultas TOP se convierte en una tarea cada vez más no trivial. Todos los métodos simples ya se han desarrollado y la solicitud más "pesada" requiere "sólo" entre el 3 y el 5% de los recursos. Si las consultas TOP en total toman menos del 30-40% del tiempo, 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 de cuántas consultas principales deberían incluirse en este grupo. Normalmente tomo al menos 10, pero no más de 20. Intento que el tiempo del primero y del último del grupo TOP difieran no más de 10 veces. Es decir, si el tiempo de ejecución de la consulta cae bruscamente del 1er lugar al 10mo, entonces tomo el TOP-10, si la caída es más gradual, entonces aumento el tamaño del grupo a 15 o 20.
campesinos medios
Todas estas son solicitudes que vienen inmediatamente después de TOP, con la excepción del último 5-10%. Por lo general, al optimizar estas consultas existe la oportunidad de aumentar considerablemente el rendimiento del servidor. Estas solicitudes pueden tener un peso de hasta el 80%. Pero incluso si su participación ha superado el 50%, entonces es hora de examinarlos más detenidamente.
Cola
Como se mencionó, estas consultas aparecen al final y toman entre el 5% y el 10% del tiempo. Puede olvidarse de ellos solo si no utiliza herramientas automáticas de análisis de consultas, entonces optimizarlos también puede resultar económico.
¿Cómo evaluar a cada grupo?
Utilizo una consulta SQL que ayuda a realizar dicha evaluación para PostgreSQL (estoy seguro de que se puede escribir una consulta similar para muchos otros DBMS)
Consulta SQL para estimar el tamaño de los grupos TOP-MEDIUM-TAIL
SELECT 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 el porcentaje de tiempo que lleva procesar las consultas de este grupo. Dentro de la solicitud hay dos números (en mi caso es 20 y 800) que separan las solicitudes de un grupo de otro.
Así es como se comparan aproximadamente los porcentajes de solicitudes en el momento en que comenzaron los trabajos de optimización y en la actualidad.
El diagrama muestra que la proporción de solicitudes TOP ha disminuido drásticamente, pero los "campesinos medios" han aumentado.
Al principio, las solicitudes TOP incluían errores flagrantes. Con el tiempo, las enfermedades infantiles desaparecieron, la proporción de solicitudes TOP disminuyó y hubo que hacer cada vez más esfuerzos para acelerar las solicitudes difíciles.
Para obtener el texto de las solicitudes utilizamos la siguiente 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 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800 -- TAIL
Aquí hay una lista de las técnicas más utilizadas que nos ayudaron a acelerar las consultas TOP:
- Rediseño del sistema, por ejemplo, reelaboración de la lógica de notificación utilizando un intermediario de mensajes en lugar de consultas periódicas a la base de datos.
- Agregar o cambiar índices
- Reescribir consultas ORM a SQL puro
- Reescribiendo la lógica de carga de datos diferida
- Almacenamiento en caché mediante desnormalización de datos. Por ejemplo, tenemos una conexión de tabla Entrega -> Factura -> Solicitud -> Solicitud. Es decir, cada entrega se asocia a una aplicación a través de otras tablas. Para no vincular todas las tablas en cada solicitud, duplicamos el enlace a la solicitud en la tabla Entrega.
- Almacenamiento en caché de tablas estáticas con libros de referencia y rara vez cambios de tablas en la memoria del programa.
A veces los cambios equivalían a un rediseño impresionante, pero proporcionaban entre el 5 y el 10% de la carga del sistema y estaban justificados. Con el tiempo, el escape se hizo cada vez más pequeño y se requirió un rediseño cada vez más serio.
Luego nos centramos en el segundo grupo de peticiones: el grupo de los campesinos medios. Hay muchas más consultas en él y parecía que llevaría mucho tiempo analizar todo el grupo. Sin embargo, la mayoría de las consultas resultaron ser muy sencillas de optimizar y muchos problemas se repitieron decenas de veces en diferentes variaciones. A continuación se muestran ejemplos de algunas optimizaciones típicas que aplicamos a docenas de consultas similares y cada grupo de consultas optimizadas descargó la base de datos entre un 3% y un 5%.
- En lugar de verificar la presencia de registros usando COUNT y un escaneo completo de la tabla, se comenzó a usar EXISTS
- Se deshizo de DISTINCT (no existe una receta general, pero a veces puede deshacerse de él fácilmente acelerando la solicitud entre 10 y 100 veces).
Por ejemplo, en lugar de una consulta para seleccionar todos los conductores de una gran tabla de entregas (ENTREGA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDhizo una consulta en una tabla relativamente pequeña PERSONA
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Parecería que utilizamos una subconsulta correlacionada, pero proporciona una aceleración de más de 10 veces.
- En muchos casos, COUNT fue abandonado por completo y
- en lugar de
UPPER(s) LIKE JOHN%’utilizar
s ILIKE “John%”
Cada solicitud específica a veces se aceleraba entre 3 y 1000 veces. A pesar del impresionante rendimiento, al principio nos pareció que no tenía sentido optimizar una consulta que tarda 10 ms en completarse, es una de las cien consultas más pesadas y ocupa centésimas de porcentaje del tiempo total de carga de la base de datos. Pero al aplicar la misma receta a un grupo de consultas del mismo tipo, recuperamos un pequeño porcentaje. Para no perder tiempo revisando manualmente los cientos de consultas, escribimos varios scripts simples que usaban expresiones regulares para encontrar consultas del mismo tipo. Como resultado, la búsqueda automática de grupos de consultas nos permitió mejorar aún más nuestro rendimiento con un esfuerzo modesto.
Por eso llevamos tres años trabajando en el mismo hardware. La carga diaria promedio es de alrededor del 30%, en picos alcanza el 70%. El número de solicitudes, así como el número de usuarios, se ha multiplicado aproximadamente por 10. Y todo ello gracias al seguimiento constante de estos mismos grupos de solicitudes 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 nos encontramos con nuevas consultas que ya sabemos optimizar, las cambiamos rápidamente. En ocasiones nos encontramos con 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 del número de usuarios de 3 a 5 veces más. Es cierto que tenemos un as más bajo la manga: todavía no hemos transferido las consultas SELECT al espejo, como se recomienda. Pero no lo hacemos conscientemente, porque 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 hacer perder el tiempo a los especialistas. Es posible que el servidor no cueste tanto, especialmente porque aún no hemos agotado los límites del escalamiento vertical. Sin embargo, sólo el número de solicitudes aumentó 10 veces. A lo largo de varios años, la funcionalidad del sistema ha aumentado y ahora hay más tipos de solicitudes. Gracias al almacenamiento en caché, la funcionalidad que existía se realiza en menos solicitudes y en solicitudes más eficientes. Esto significa que puedes multiplicar con seguridad 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 50 veces o más. Girar verticalmente un servidor costaría 50 veces más. Sobre todo teniendo en cuenta que una vez realizada la optimización funciona todo el tiempo, y la factura del servidor alquilado llega todos los meses.
Fuente: habr.com