Com augmentar 10 vegades el nombre de consultes a la base de dades sense passar a un servidor més productiu i mantenir la funcionalitat del sistema? Us explicaré com vam afrontar la disminució del rendiment de la nostra base de dades, com vam optimitzar les consultes SQL per atendre el màxim d'usuaris possible i no augmentar el cost dels recursos informàtics.
Realitzo un servei de gestió de processos de negoci en empreses constructores. Unes 3 mil empreses treballen amb nosaltres. Més de 10 mil persones treballen amb el nostre sistema cada dia durant 4-10 hores. Soluciona diversos problemes de planificació, notificació, avís, validació... Utilitzem PostgreSQL 9.6. Tenim unes 300 taules a la base de dades i cada dia es reben fins a 200 milions de consultes (10 mil diferents). De mitjana tenim entre 3 i 4 mil peticions per segon, en els moments més actius més de 10 mil peticions per segon. La majoria de les consultes són OLAP. Hi ha moltes menys addicions, modificacions i supressions, el que significa que la càrrega OLTP és relativament lleugera. He proporcionat tots aquests números perquè pugueu avaluar l'envergadura del nostre projecte i comprendre com d'utilitat pot ser per a vosaltres la nostra experiència.
Imatge una. Líric
Quan vam començar el desenvolupament, no ens vam plantejar realment quin tipus de càrrega cauria a la base de dades i què faríem si el servidor deixés de tirar. Quan vam dissenyar la base de dades, vam seguir les recomanacions generals i vam intentar no disparar-nos al peu, però vam anar més enllà dels consells generals com "no utilitzeu el patró". no vam entrar. Vam dissenyar basant-nos en els principis de normalització, evitant la redundància de dades i no ens importava accelerar determinades consultes. Tan bon punt van arribar els primers usuaris, vam trobar un problema de rendiment. Com de costum, no estàvem completament preparats per a això. Els primers problemes van resultar ser senzills. Per regla general, tot es va resoldre afegint un nou índex. Però va arribar un moment en què els pedaços simples van deixar de funcionar. En adonar-nos que ens falta experiència i que cada cop ens és més difícil entendre què és el que causa els problemes, vam contractar especialistes que ens van ajudar a configurar correctament el servidor, connectar el monitoratge i ens van mostrar on buscar-lo. .
Imatge dos. Estadístic
Així, tenim unes 10 mil consultes diferents que s'executen a la nostra base de dades al dia. D'aquests 10 mil, hi ha monstres que s'executen 2-3 milions de vegades amb un temps d'execució mitjà de 0.1-0.3 ms, i hi ha consultes amb un temps d'execució mitjà de 30 segons que es criden 100 vegades al dia.
No va ser possible optimitzar les 10 mil consultes, així que vam decidir esbrinar cap a on dirigir els nostres esforços per millorar el rendiment de la base de dades correctament. Després de diverses iteracions, vam començar a dividir les sol·licituds en tipus.
TOP sol·licituds
Aquestes són les consultes més pesades que triguen més temps (temps total). Es tracta de consultes que s'anomenen molt sovint o que triguen molt a executar-se (les consultes llargues i freqüents es van optimitzar en les primeres iteracions de la lluita per la velocitat). Com a resultat, el servidor dedica més temps a la seva execució. A més, és important separar les sol·licituds principals pel temps d'execució total i per separat pel temps d'E/S. Els mètodes per optimitzar aquestes consultes són lleugerament diferents.
La pràctica habitual de totes les empreses és treballar amb peticions TOP. N'hi ha pocs; optimitzar fins i tot una sola consulta pot alliberar un 5-10% de recursos. Tanmateix, a mesura que el projecte madura, optimitzar les consultes TOP esdevé una tasca cada cop més no trivial. Tots els mètodes senzills ja s'han elaborat, i la sol·licitud més "pesada" requereix "només" el 3-5% dels recursos. Si les consultes TOP en total triguen menys del 30-40% del temps, és molt probable que ja hagis fet esforços per fer-les funcionar ràpidament i és hora de passar a optimitzar les consultes del grup següent.
Queda per respondre a la pregunta de quantes consultes principals s'han d'incloure en aquest grup. Normalment en prenc almenys 10, però no més de 20. Intento assegurar-me que el temps del primer i l'últim del grup TOP no difereixin més de 10 vegades. És a dir, si el temps d'execució de la consulta baixa bruscament del primer lloc al 1è, prenc TOP-10, si la caiguda és més gradual, augmento la mida del grup a 10 o 15.
Camperols mitjans
Totes aquestes són sol·licituds que arriben immediatament després de TOP, amb l'excepció de l'últim 5-10%. Normalment, en l'optimització d'aquestes consultes rau l'oportunitat d'augmentar molt el rendiment del servidor. Aquestes peticions poden arribar al 80%. Però fins i tot si la seva quota ha superat el 50%, llavors és hora de mirar-los amb més atenció.
Cua
Com s'ha esmentat, aquestes consultes arriben al final i triguen entre un 5 i un 10% del temps. Només us podeu oblidar si no feu servir eines d'anàlisi de consultes automàtiques, aleshores optimitzar-les també pot ser barat.
Com avaluar cada grup?
Utilitzo una consulta SQL que ajuda a fer aquesta avaluació per a PostgreSQL (estic segur que es pot escriure una consulta similar per a molts altres SGBD)
Consulta SQL per estimar la mida dels grups 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 resultat de la consulta són tres columnes, cadascuna de les quals conté el percentatge de temps que triga a processar les consultes d'aquest grup. Dins de la sol·licitud hi ha dos números (en el meu cas és el 20 i el 800) que separen les peticions d'un grup d'un altre.
Així és com es comparen aproximadament les quotes de sol·licituds en el moment en què es va iniciar el treball d'optimització i ara.
El diagrama mostra que la quota de sol·licituds TOP ha disminuït dràsticament, però els "camperols mitjans" han augmentat.
Al principi, les peticions TOP incloïen errors flagrants. Amb el temps, les malalties infantils van desaparèixer, la quota de sol·licituds TOP va disminuir i s'havien de fer cada cop més esforços per accelerar les peticions difícils.
Per obtenir el text de les sol·licituds utilitzem la següent sol·licitud
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í teniu una llista de les tècniques més utilitzades que ens van ajudar a accelerar les consultes TOP:
- Redisseny del sistema, per exemple, reelaborant la lògica de notificació mitjançant un agent de missatges en lloc de consultes periòdiques a la base de dades
- Afegir o canviar índexs
- Reescriptura de consultes ORM a SQL pur
- Reescriptura de la lògica de càrrega de dades mandrosa
- Emmagatzematge a la memòria cau mitjançant la desnormalització de dades. Per exemple, tenim una connexió de taula Lliurament -> Factura -> Sol·licitud -> Sol·licitud. És a dir, cada lliurament s'associa a una aplicació a través d'altres taules. Per no enllaçar totes les taules de cada sol·licitud, vam duplicar l'enllaç a la sol·licitud a la taula de lliurament.
- Emmagatzematge a la memòria cau taules estàtiques amb llibres de referència i taules canviant poques vegades a la memòria del programa.
De vegades, els canvis van suposar un redisseny impressionant, però proporcionaven un 5-10% de la càrrega del sistema i estaven justificats. Amb el temps, l'escapament es va fer cada cop més petit i es va requerir un redisseny cada cop més seriós.
Després vam centrar la nostra atenció en el segon grup de peticions: el grup de pagesos mitjans. Hi ha moltes més consultes i semblava que caldria molt de temps analitzar tot el grup. Tanmateix, la majoria de consultes van resultar ser molt senzilles d'optimitzar i molts problemes es van repetir desenes de vegades en diferents variacions. Aquests són exemples d'algunes optimitzacions típiques que hem aplicat a desenes de consultes similars i cada grup de consultes optimitzades va descarregar la base de dades en un 3-5%.
- En lloc de comprovar la presència de registres mitjançant COUNT i una exploració de taula completa, es va començar a utilitzar EXISTS
- Em vaig desfer de DISTINCT (no hi ha cap recepta general, però de vegades us podeu desfer fàcilment accelerant la sol·licitud entre 10 i 100 vegades).
Per exemple, en lloc d'una consulta per seleccionar tots els conductors d'una taula gran de lliuraments (ENTREGA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDva fer una consulta en una taula relativament petita PERSON
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Sembla que hem utilitzat una subconsulta correlacionada, però dóna una velocitat de més de 10 vegades.
- En molts casos, COUNT es va abandonar completament i
- en comptes de
UPPER(s) LIKE JOHN%’utilitzar
s ILIKE “John%”
Cada sol·licitud específica es va accelerar de vegades entre 3 i 1000 vegades. Malgrat el rendiment impressionant, al principi ens va semblar que no tenia sentit optimitzar una consulta que triga 10 ms a completar-se, és una de les 3r cent consultes més pesades i ocupa centèsimes d'un per cent del temps total de càrrega de la base de dades. Però aplicant la mateixa recepta a un grup de consultes del mateix tipus, hem recuperat un percentatge. Per no perdre el temps revisant manualment els centenars de consultes, vam escriure diversos scripts senzills que utilitzaven expressions regulars per trobar consultes del mateix tipus. Com a resultat, la cerca automàtica de grups de consultes ens va permetre millorar encara més el nostre rendiment amb un esforç modest.
Com a resultat, ja fa tres anys que treballem en el mateix maquinari. La càrrega diària mitjana és d'un 30%, en pics arriba al 70%. El nombre de peticions, així com el nombre d'usuaris, ha augmentat aproximadament 10 vegades. I tot això gràcies al seguiment constant d'aquests mateixos grups de peticions TOP-MEDIUM. Tan bon punt apareix una nova sol·licitud al grup TOP, l'analitzem immediatament i intentem accelerar-la. Revisem el grup MITJÀ un cop per setmana mitjançant scripts d'anàlisi de consultes. Si ens trobem amb noves consultes que ja sabem optimitzar, les canviem ràpidament. De vegades trobem nous mètodes d'optimització que es poden aplicar a diverses consultes alhora.
Segons les nostres previsions, el servidor actual suportarà un augment del nombre d'usuaris en 3-5 vegades més. És cert que tenim un as més a la màniga: encara no hem transferit les consultes SELECT al mirall, com es recomana. Però no ho fem conscientment, perquè primer volem esgotar completament les possibilitats d'optimització "intel·ligent" abans d'encendre l'"artilleria pesada".
Una mirada crítica al treball realitzat pot suggerir l'ús de l'escala vertical. Compra un servidor més potent en lloc de perdre el temps dels especialistes. El servidor pot no costar tant, sobretot perquè encara no hem esgotat els límits de l'escala vertical. Tanmateix, només el nombre de sol·licituds va augmentar 10 vegades. Al llarg de diversos anys, la funcionalitat del sistema ha augmentat i ara hi ha més tipus de peticions. La funcionalitat que existia, a causa de la memòria cau, es realitza en menys peticions, i també en peticions més eficients. Això significa que podeu multiplicar amb seguretat per 5 més per obtenir el coeficient d'acceleració real. Així doncs, segons les estimacions més conservadores, podem dir que l'acceleració va ser 50 vegades o més. Girar verticalment un servidor costaria 50 vegades més. Sobretot tenint en compte que un cop realitzada l'optimització funciona tot el temps, i la factura del servidor llogat arriba cada mes.
Font: www.habr.com