Como aumentar 10 veces o número de consultas á base de datos sen pasar a un servidor máis produtivo e manter a funcionalidade do sistema? Contareivos como afrontamos o descenso do rendemento da nosa base de datos, como optimizamos as consultas SQL para atender ao maior número posible de usuarios e non aumentar o custo dos recursos informáticos.
Fago un servizo de xestión de procesos comerciais en construtoras. Unhas 3 mil empresas traballan connosco. Máis de 10 mil persoas traballan co noso sistema todos os días durante 4-10 horas. Resolve diversos problemas de planificación, notificación, aviso, validación... Usamos PostgreSQL 9.6. Contamos cunhas 300 táboas na base de datos e cada día recíbense ata 200 millóns de consultas (10 mil diferentes). De media temos entre 3 e 4 mil solicitudes por segundo, nos momentos máis activos máis de 10 mil solicitudes por segundo. A maioría das consultas son OLAP. Hai moito menos engadidos, modificacións e eliminacións, o que significa que a carga OLTP é relativamente lixeira. Acheguei todos estes números para que poidas avaliar a escala do noso proxecto e comprender o útil que pode ser para ti a nosa experiencia.
Imaxe un. Lírico
Cando comezamos o desenvolvemento, non pensamos realmente en que tipo de carga caería sobre a base de datos e que fariamos se o servidor deixase de tirar. Ao deseñar a base de datos, seguimos recomendacións xerais e intentamos non dispararnos no pé, pero fomos máis aló de consellos xerais como "non use o patrón". non entramos. Deseñamos partindo dos principios da normalización, evitando a redundancia de datos e non nos preocupamos por axilizar determinadas consultas. En canto chegaron os primeiros usuarios, atopamos un problema de rendemento. Como de costume, non estabamos completamente preparados para iso. Os primeiros problemas resultaron sinxelos. Como regra xeral, todo resolveuse engadindo un novo índice. Pero chegou un momento no que os parches simples deixaron de funcionar. Dándonos conta de que carecemos de experiencia e que cada vez é máis difícil entender o que está a causar os problemas, contratamos especialistas que nos axudaron a configurar correctamente o servidor, a conectar a monitorización e a mostraron onde buscar para conseguir. .
Imaxe dúas. estatística
Polo tanto, temos preto de 10 mil consultas diferentes que se executan na nosa base de datos ao día. Destes 10 mil, hai monstros que se executan 2-3 millóns de veces cun tempo medio de execución de 0.1-0.3 ms, e hai consultas cun tempo medio de execución de 30 segundos que se chaman 100 veces ao día.
Non foi posible optimizar as 10 mil consultas, polo que decidimos descubrir onde dirixir os nosos esforzos para mellorar o rendemento da base de datos correctamente. Despois de varias iteracións, comezamos a dividir as solicitudes en tipos.
TOP solicitudes
Estas son as consultas máis pesadas que levan máis tempo (tempo total). Son consultas que se chaman con moita frecuencia ou consultas que tardan moito en executarse (as consultas longas e frecuentes optimizáronse nas primeiras iteracións da loita pola velocidade). Como resultado, o servidor dedica máis tempo á súa execución. Ademais, é importante separar as solicitudes principais polo tempo de execución total e por separado polo tempo de E/S. Os métodos para optimizar tales consultas son lixeiramente diferentes.
A práctica habitual de todas as empresas é traballar con solicitudes TOP. Hai poucos; optimizar incluso unha consulta pode liberar un 5-10% dos recursos. Non obstante, a medida que o proxecto madura, optimizar as consultas TOP convértese nunha tarefa cada vez máis non trivial. Xa se elaboraron todos os métodos sinxelos e a solicitude máis "pesada" leva "só" o 3-5% dos recursos. Se as consultas TOP en total ocupan menos do 30-40 % do tempo, é probable que xa fixeches esforzos para facelos funcionar rapidamente e é hora de pasar á optimización das consultas do seguinte grupo.
Queda por responder á pregunta de cantas consultas principais deberían incluírse neste grupo. Normalmente levo polo menos 10, pero non máis de 20. Intento asegurarme de que o tempo do primeiro e o último do grupo TOP difiran non máis de 10 veces. É dicir, se o tempo de execución da consulta cae drasticamente do 1º ao 10º lugar, entón tomo TOP-10, se a caída é máis gradual, entón aumento o tamaño do grupo a 15 ou 20.
Campesiños medios
Todas estas son solicitudes que veñen inmediatamente despois de TOP, coa excepción do último 5-10%. Normalmente, na optimización destas consultas reside a oportunidade de aumentar moito o rendemento do servidor. Estas solicitudes poden pesar ata o 80%. Pero aínda que a súa participación supere o 50%, entón é hora de miralos con máis atención.
Rabo
Como se mencionou, estas consultas chegan ao final e levan un 5-10% do tempo. Só se pode esquecer deles se non utiliza ferramentas automáticas de análise de consultas, entón optimizalas tamén pode ser barata.
Como avaliar cada grupo?
Eu uso unha consulta SQL que axuda a facer esa avaliación para PostgreSQL (estou seguro de que se pode escribir unha consulta similar para moitos outros DBMS)
Consulta SQL para estimar o tamaño dos 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
O resultado da consulta son tres columnas, cada unha das cales contén a porcentaxe de tempo que leva procesar as consultas deste grupo. Dentro da solicitude hai dous números (no meu caso é o 20 e o 800) que separan as solicitudes dun grupo doutro.
Así é como se comparan aproximadamente as cotas de solicitudes no momento en que comezou o traballo de optimización e agora.
O diagrama mostra que a proporción de solicitudes TOP diminuíu drasticamente, pero os "campesiños medios" aumentaron.
Nun principio, as solicitudes TOP incluían erros flagrantes. Co paso do tempo, as enfermidades da infancia desapareceron, a proporción de solicitudes TOP diminuíu e houbo que facer cada vez máis esforzos para acelerar as solicitudes difíciles.
Para obter o texto das solicitudes utilizamos a seguinte solicitude
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í tes unha lista das técnicas máis utilizadas que nos axudaron a acelerar as consultas TOP:
- Redeseño do sistema, por exemplo, reelaborando a lóxica de notificación usando un corredor de mensaxes en lugar de consultas periódicas á base de datos
- Engadir ou cambiar índices
- Reescribindo consultas ORM a SQL puro
- Reescribindo a lóxica de carga de datos preguiceiro
- Almacenamento en caché mediante a desnormalización de datos. Por exemplo, temos unha conexión de mesa Entrega -> Factura -> Solicitude -> Solicitude. É dicir, cada entrega está asociada a unha aplicación a través doutras táboas. Para non vincular todas as táboas de cada solicitude, duplicamos a ligazón á solicitude na táboa de Entrega.
- Almacenamento en caché de táboas estáticas con libros de consulta e raramente cambian as táboas na memoria do programa.
Ás veces, os cambios supuxeron un redeseño impresionante, pero proporcionaban un 5-10% da carga do sistema e estaban xustificados. Co paso do tempo, o tubo de escape fíxose cada vez máis pequeno e requiríase un redeseño cada vez máis serio.
Despois diriximos a nosa atención ao segundo grupo de solicitudes: o grupo de campesiños medios. Hai moitas máis consultas nel e parecía que levaría moito tempo analizar todo o grupo. Non obstante, a maioría das consultas resultaron moi sinxelas de optimizar e moitos problemas repetíanse decenas de veces en diferentes variacións. Aquí tes exemplos dalgunhas optimizacións típicas que aplicamos a decenas de consultas similares e cada grupo de consultas optimizadas descargou a base de datos nun 3-5%.
- En lugar de comprobar a presenza de rexistros mediante COUNT e unha exploración da táboa completa, empezou a usar EXISTS
- Desfíxose de DISTINCT (non hai unha receita xeral, pero ás veces podes desfacerte facilmente del acelerando a solicitude entre 10 e 100 veces).
Por exemplo, en lugar dunha consulta para seleccionar todos os controladores dunha gran táboa de entregas (ENTREGA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDrealizou unha consulta nunha táboa relativamente pequena 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)Parece que usamos unha subconsulta correlacionada, pero dá unha aceleración de máis de 10 veces.
- En moitos casos, COUNT abandonouse por completo e
- en vez de
UPPER(s) LIKE JOHN%’uso
s ILIKE “John%”
Cada solicitude específica ás veces acelerábase entre 3 e 1000 veces. A pesar do impresionante rendemento, ao principio pareceunos que non tiña sentido optimizar unha consulta que tarda 10 ms en completarse, é unha das 3.ª cen consultas máis pesadas e ocupa centésimas de por cento do tempo total de carga da base de datos. Pero ao aplicar a mesma receita a un grupo de consultas do mesmo tipo, recuperamos un pouco por cento. Para non perder o tempo revisando manualmente os centos de consultas, escribimos varios scripts sinxelos que usaban expresións regulares para atopar consultas do mesmo tipo. Como resultado, a busca automática de grupos de consultas permitiunos mellorar aínda máis o noso rendemento cun esforzo modesto.
Como resultado, levamos tres anos traballando no mesmo hardware. A carga diaria media é dun 30%, nos picos alcanza o 70%. O número de solicitudes, así como o número de usuarios, aumentou aproximadamente 10 veces. E todo isto grazas ao seguimento constante destes mesmos grupos de solicitudes TOP-MEDIUM. En canto aparece unha nova solicitude no grupo TOP, analizámola inmediatamente e tentamos acelerala. Revisamos o grupo MEDIO unha vez á semana mediante scripts de análise de consultas. Se nos atopamos con novas consultas que xa sabemos optimizar, cambiámolas rapidamente. Ás veces atopamos novos métodos de optimización que se poden aplicar a varias consultas á vez.
Segundo as nosas previsións, o servidor actual soportará un aumento do número de usuarios outras 3-5 veces. É certo, temos un as máis na manga: aínda non transferimos consultas SELECT ao espello, como se recomenda. Pero non o facemos conscientemente, porque primeiro queremos esgotar completamente as posibilidades de optimización "intelixente" antes de activar a "artillaría pesada".
Unha mirada crítica ao traballo realizado pode suxerir o uso de escala vertical. Compre un servidor máis potente en lugar de perder o tempo dos especialistas. É posible que o servidor non custe tanto, sobre todo porque aínda non esgotamos os límites da escala vertical. Non obstante, só o número de solicitudes aumentou 10 veces. Ao longo de varios anos, a funcionalidade do sistema aumentou e agora hai máis tipos de solicitudes. A funcionalidade que existía, debido ao caché, realízase en menos solicitudes, e tamén en solicitudes máis eficientes. Isto significa que podes multiplicar con seguridade por outros 5 para obter o coeficiente de aceleración real. Entón, segundo as estimacións máis conservadoras, podemos dicir que a aceleración foi 50 veces ou máis. Balancear verticalmente un servidor custaría 50 veces máis. Sobre todo tendo en conta que unha vez realizada a optimización funciona todo o tempo, e a factura do servidor alugado chega cada mes.
Fonte: www.habr.com