Kako 10-krat povečati število poizvedb v bazo podatkov, ne da bi se preselili na bolj produktivni strežnik in ohraniti funkcionalnost sistema? Povedal vam bom, kako smo se spopadli z upadom zmogljivosti naše podatkovne baze, kako smo optimizirali SQL poizvedbe, da bi služile čim večjemu številu uporabnikov in ne bi povečale stroškov računalniških virov.
Izdelujem storitev za vodenje poslovnih procesov v gradbenih podjetjih. Približno 3 tisoč podjetij sodeluje z nami. Več kot 10 tisoč ljudi dela z našim sistemom vsak dan 4-10 ur. Rešuje različne probleme načrtovanja, obveščanja, opozarjanja, validacije... Uporabljamo PostgreSQL 9.6. V bazi imamo približno 300 tabel in vsak dan prejmemo do 200 milijonov poizvedb (10 tisoč različnih). V povprečju imamo 3-4 tisoč zahtevkov na sekundo, v najbolj aktivnih trenutkih več kot 10 tisoč zahtevkov na sekundo. Večina poizvedb je OLAP. Veliko manj je dodajanja, spreminjanja in brisanja, kar pomeni, da je obremenitev OLTP razmeroma majhna. Vse te številke sem posredoval, da lahko ocenite obseg našega projekta in razumete, kako koristne so lahko naše izkušnje za vas.
Slika ena. Lirična
Ko smo začeli z razvojem, nismo razmišljali o tem, kakšna bo obremenitev baze podatkov in kaj bomo storili, če strežnik neha vleči. Pri oblikovanju podatkovne baze smo upoštevali splošna priporočila in se trudili, da se ne bi ustrelili v nogo, ampak smo presegli splošne nasvete, kot je »ne uporabljajte vzorca nismo šli noter. Zasnovali smo po načelih normalizacije, izogibanja redundanci podatkov in nismo skrbeli za pospešitev določenih poizvedb. Takoj ko so prišli prvi uporabniki, smo naleteli na težavo pri delovanju. Kot ponavadi smo bili na to popolnoma nepripravljeni. Prve težave so se izkazale za preproste. Praviloma se je vse rešilo z dodajanjem novega indeksa. Toda prišel je čas, ko preprosti popravki niso več delovali. Ker se zavedamo, da nam primanjkuje izkušenj in čedalje težje razumemo, kaj povzroča težave, smo najeli strokovnjake, ki so nam pomagali pravilno nastaviti strežnik, priključiti nadzor in nam pokazali, kje iskati. .
Slika dve. Statistični
Tako imamo približno 10 tisoč različnih poizvedb, ki se izvajajo v naši bazi podatkov na dan. Od teh 10 tisoč so pošasti, ki se izvedejo 2-3 milijone krat s povprečnim časom izvajanja 0.1-0.3 ms, in obstajajo poizvedbe s povprečnim časom izvajanja 30 sekund, ki se kličejo 100-krat na dan.
Vseh 10 tisoč poizvedb ni bilo mogoče optimizirati, zato smo se odločili ugotoviti, kam usmeriti svoja prizadevanja, da bi pravilno izboljšali delovanje baze podatkov. Po več ponovitvah smo zahteve začeli deliti na vrste.
TOP zahteve
To so najtežje poizvedbe, ki vzamejo največ časa (skupni čas). To so poizvedbe, ki se zelo pogosto kličejo ali poizvedbe, ki se izvajajo zelo dolgo (dolge in pogoste poizvedbe so bile optimizirane v prvih iteracijah boja za hitrost). Posledično strežnik porabi največ časa za njihovo izvedbo. Poleg tega je pomembno ločiti najvišje zahteve po skupnem času izvajanja in ločeno po času IO. Metode za optimizacijo takih poizvedb so nekoliko drugačne.
Običajna praksa vseh podjetij je delo z TOP zahtevami. Malo jih je; optimizacija že ene poizvedbe lahko sprosti 5-10% virov. Vendar pa z dozorevanjem projekta optimizacija TOP poizvedb postaja vse bolj netrivialna naloga. Vse preproste metode so že izdelane, najbolj "težka" zahteva pa zahteva "le" 3-5% virov. Če TOP poizvedbe skupaj trajajo manj kot 30-40% časa, potem ste se najverjetneje že potrudili, da bi delovale hitro in je čas, da nadaljujete z optimizacijo poizvedb iz naslednje skupine.
Odgovoriti je treba še na vprašanje, koliko najpogostejših poizvedb je treba vključiti v to skupino. Običajno jih vzamem vsaj 10, vendar ne več kot 20. Trudim se, da se čas prvega in zadnjega v TOP skupini ne razlikujeta več kot 10-krat. To pomeni, da če čas izvajanja poizvedbe močno pade s 1. mesta na 10. mesto, potem vzamem TOP-10, če je padec bolj postopen, potem povečam velikost skupine na 15 ali 20.
Srednji kmetje
To so vse zahteve, ki pridejo takoj za TOP, z izjemo zadnjih 5-10%. Običajno se v optimizaciji teh poizvedb skriva priložnost za močno povečanje zmogljivosti strežnika. Te zahteve lahko tehtajo do 80 %. Toda tudi če je njihov delež presegel 50%, je čas, da jih pogledamo bolj natančno.
rep
Kot že omenjeno, te poizvedbe pridejo na koncu in trajajo 5-10 % časa. Nanje lahko pozabite le, če ne uporabljate orodij za samodejno analizo poizvedb, potem je lahko tudi njihova optimizacija poceni.
Kako oceniti posamezno skupino?
Uporabljam poizvedbo SQL, ki pomaga narediti takšno oceno za PostgreSQL (prepričan sem, da je podobno poizvedbo mogoče napisati za številne druge DBMS-je)
Poizvedba SQL za oceno velikosti skupin 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
Rezultat poizvedbe so trije stolpci, od katerih vsak vsebuje odstotek časa, ki je potreben za obdelavo poizvedb iz te skupine. Znotraj zahteve sta dve številki (v mojem primeru 20 in 800), ki ločujeta zahteve iz ene skupine od druge.
Tako se približno primerjata deleža povpraševanj ob začetku optimizacije in zdaj.
Diagram kaže, da se je delež zahtev TOP močno zmanjšal, "srednji kmetje" pa so se povečali.
Sprva so TOP zahteve vsebovale očitne napake. Sčasoma so otroške bolezni izginile, delež TOP prošenj se je zmanjšal, vse več je bilo treba vložiti truda v pospešitev zahtevnih zahtev.
Za pridobitev besedila zahtev uporabljamo naslednjo zahtevo
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
Tukaj je seznam najpogosteje uporabljenih tehnik, ki so nam pomagale pospešiti TOP poizvedbe:
- Preoblikovanje sistema, na primer predelava logike obveščanja z uporabo posrednika sporočil namesto občasnih poizvedb v bazi podatkov
- Dodajanje ali spreminjanje indeksov
- Prepisovanje poizvedb ORM v čisti SQL
- Ponovno pisanje logike lenega nalaganja podatkov
- Predpomnjenje z denormalizacijo podatkov. Na primer, imamo povezavo tabele Dostava -> Račun -> Zahtevek -> Vloga. To pomeni, da je vsaka dostava povezana z aplikacijo prek drugih tabel. Da ne bi povezali vseh tabel v vsaki zahtevi, smo podvojili povezavo do zahteve v tabeli Dostava.
- Predpomnjenje statičnih tabel z referenčnimi knjigami in redko spreminjajočimi se tabelami v programskem pomnilniku.
Včasih so spremembe pomenile impresivno preoblikovanje, vendar so zagotavljale 5–10 % obremenitve sistema in so bile upravičene. Sčasoma je bilo izpuha vse manj, zahtevala pa je vse bolj resno predelavo.
Nato smo se posvetili drugi skupini zahtev - skupini srednjih kmetov. V njej je veliko več poizvedb in zdelo se je, da bo analiza celotne skupine vzela veliko časa. Vendar se je izkazalo, da je večino poizvedb zelo enostavno optimizirati in številne težave so se ponovile več desetkrat v različnih različicah. Tu so primeri nekaj tipičnih optimizacij, ki smo jih uporabili za desetine podobnih poizvedb in vsaka skupina optimiziranih poizvedb je razbremenila bazo podatkov za 3-5 %.
- Namesto preverjanja prisotnosti zapisov z uporabo COUNT in skeniranja celotne tabele so se začeli uporabljati EXISTS
- Znebili DISTINCT (splošnega recepta ni, včasih pa se ga zlahka znebite tako, da zahtevo pospešite za 10-100 krat).
Na primer, namesto poizvedbe za izbiro vseh voznikov iz velike tabele dobav (DOSTAVA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDnaredil poizvedbo na razmeroma majhni tabeli 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)Zdi se, da smo uporabili korelirano podpoizvedbo, vendar omogoča več kot 10-kratno pospešitev.
- V mnogih primerih je bil COUNT popolnoma opuščen in
- namesto
UPPER(s) LIKE JOHN%’uporabo
s ILIKE “John%”
Vsaka posebna zahteva je bila včasih pospešena za 3-1000-krat. Kljub impresivni zmogljivosti se nam je sprva zdelo, da nima smisla optimizirati poizvedbe, ki traja 10 ms, je ena izmed 3. sto najtežjih poizvedb in zavzame stotinke odstotka celotnega časa nalaganja baze podatkov. Toda z uporabo istega recepta za skupino poizvedb iste vrste smo pridobili nekaj odstotkov nazaj. Da ne bi izgubljali časa z ročnim pregledovanjem vseh stotin poizvedb, smo napisali več preprostih skriptov, ki so uporabljali regularne izraze za iskanje poizvedb iste vrste. Posledično nam je samodejno iskanje po skupinah poizvedb omogočilo, da smo s skromnim trudom dodatno izboljšali našo učinkovitost.
Posledično že tri leta delamo na isti strojni opremi. Povprečna dnevna obremenitev je približno 30%, v konicah doseže 70%. Število povpraševanj in tudi število uporabnikov se je povečalo približno 10-krat. In vse to zahvaljujoč stalnemu spremljanju teh istih skupin TOP-MEDIUM zahtev. Takoj, ko se v skupini TOP pojavi nova zahteva, jo takoj analiziramo in poskušamo pospešiti. Skupino MEDIUM pregledamo enkrat tedensko z uporabo skript za analizo poizvedb. Če naletimo na nove poizvedbe, ki jih že znamo optimizirati, jih hitro spremenimo. Včasih najdemo nove metode optimizacije, ki jih je mogoče uporabiti za več poizvedb hkrati.
Po naših napovedih bo trenutni strežnik zdržal povečanje števila uporabnikov še za 3-5 krat. Res je, imamo še enega asa v rokavu - še vedno nismo prenesli SELECT poizvedb v zrcalo, kot je priporočljivo. Vendar tega ne počnemo zavestno, saj želimo najprej popolnoma izčrpati možnosti »pametne« optimizacije, preden vključimo »težko topništvo«.
Kritičen pogled na opravljeno delo lahko predlaga uporabo navpičnega skaliranja. Kupite zmogljivejši strežnik, namesto da izgubljate čas s strokovnjaki. Strežnik morda ne stane toliko, še posebej, ker še nismo izčrpali meja vertikalnega skaliranja. Vendar se je samo število zahtev povečalo za 10-krat. V nekaj letih se je funkcionalnost sistema povečala in zdaj je več vrst zahtevkov. Zahvaljujoč predpomnjenju se funkcionalnost, ki je obstajala, izvaja v manj zahtevah in bolj učinkovitih zahtevah. To pomeni, da lahko varno pomnožite še s 5, da dobite dejanski koeficient pospeška. Torej, po najbolj konservativnih ocenah lahko rečemo, da je bil pospešek 50-krat ali več. Navpično nihanje strežnika bi stalo 50-krat več. Sploh glede na to, da enkrat opravljena optimizacija deluje ves čas, račun za najeti strežnik pa pride vsak mesec.
Vir: www.habr.com