Hoe kinne jo 10 kear it oantal fragen nei de databank groeie sûnder te ferpleatsen nei in mear produktive server en systeemfunksjonaliteit te behâlden? Ik sil jo fertelle hoe't wy omgean mei de delgong yn 'e prestaasjes fan ús databank, hoe't wy SQL-fragen optimalisearre om safolle mooglik brûkers te tsjinjen en de kosten fan komputerboarnen net te ferheegjen.
Ik meitsje in tsjinst foar it behearen fan saaklike prosessen yn boubedriuwen. Sa'n 3 tûzen bedriuwen wurkje mei ús. Mear dan 10 tûzen minsken wurkje elke dei 4-10 oeren mei ús systeem. It lost ferskate problemen op fan planning, notifikaasje, warskôging, falidaasje ... Wy brûke PostgreSQL 9.6. Wy hawwe sawat 300 tabellen yn 'e database en oant 200 miljoen queries (10 tûzen ferskillende) wurde elke dei ûntfongen. Gemiddeld hawwe wy 3-4 tûzen oanfragen per sekonde, op de meast aktive mominten mear as 10 tûzen oanfragen per sekonde. De measte fragen binne OLAP. D'r binne folle minder tafoegings, wizigingen en wiskjen, wat betsjuttet dat de OLTP-lading relatyf licht is. Ik haw al dizze sifers levere sadat jo de skaal fan ús projekt kinne beoardielje en begripe hoe brûkber ús ûnderfining foar jo kin wêze.
Foto ien. Lyrysk
Doe't wy begûn ûntwikkeling, wy net echt tinke oer hokker soarte fan lading soe falle op de databank en wat wy soene dwaan as de tsjinner stoppe te lûken. By it ûntwerpen fan de databank folgen wy algemiene oanbefellings en besochten ússels net yn 'e foet te sjitten, mar gongen fierder as algemien advys lykas "brûk it patroan net wy gongen net yn. Wy ûntworpen op basis fan 'e prinsipes fan normalisaasje, it foarkommen fan oerstalligens fan gegevens en makken ús net oer it fersnellen fan bepaalde fragen. Sadree't de earste brûkers oankamen, tsjinkaam wy in prestaasjeprobleem. As gewoanlik wiene wy hjir hielendal net klear foar. De earste problemen bliken ienfâldich te wêzen. As regel waard alles oplost troch it tafoegjen fan in nije yndeks. Mar der kaam in tiid dat ienfâldige patches ophâlde te wurkjen. It realisearjen dat wy ûnderfining misse en it foar ús hieltyd dreger wurdt om te begripen wat de problemen feroarsake, hawwe wy spesjalisten ynhierd dy't ús holpen de tsjinner goed yn te stellen, tafersjoch te ferbinen en ús lieten sjen wêr't wy moatte sykje om te krijen .
Foto twa. Statistical
Sa hawwe wy sawat 10 tûzen ferskillende fragen dy't per dei wurde útfierd op ús database. Fan dizze 10 tûzen binne der meunsters dy't 2-3 miljoen kear útfierd wurde mei in gemiddelde útfiertiid fan 0.1-0.3 ms, en der binne fragen mei in gemiddelde útfiertiid fan 30 sekonden dy't 100 kear deis wurde neamd.
It wie net mooglik om alle 10 tûzen queries te optimalisearjen, dus besleaten wy út te finen wêr't wy ús ynspanningen rjochtsje om de prestaasjes fan 'e database korrekt te ferbetterjen. Nei ferskate iteraasjes begûnen wy oanfragen te ferdielen yn soarten.
TOP fersiken
Dit binne de swierste fragen dy't de measte tiid nimme (totale tiid). Dit binne queries dy't òf hiel faak neamd wurde òf queries dy't in hiel lange tiid nimme om út te fieren (lange en faak queries waarden optimalisearre yn 'e earste iteraasjes fan 'e striid foar snelheid). As gefolch, de tsjinner besteget de measte tiid oan har útfiering. Boppedat is it wichtich om topoanfragen te skieden troch totale útfieringstiid en apart troch IO-tiid. De metoaden foar it optimalisearjen fan sokke fragen binne wat oars.
De gewoane praktyk fan alle bedriuwen is om te wurkjen mei TOP-oanfragen. D'r binne in pear fan har; Sels ien query optimalisearje kin 5-10% fan boarnen frijmeitsje. As it projekt lykwols groeit, wurdt it optimalisearjen fan TOP-fragen in hieltyd mear net-triviale taak. Alle ienfâldige metoaden binne al útwurke, en it meast "swiere" fersyk nimt "mar" 3-5% fan middels. As TOP-fragen yn totaal minder dan 30-40% fan 'e tiid nimme, dan hawwe jo wierskynlik al ynspanningen dien om se fluch te wurkjen en it is tiid om troch te gean nei it optimalisearjen fan fragen fan 'e folgjende groep.
It bliuwt om de fraach te beantwurdzjen hoefolle topfragen moatte wurde opnommen yn dizze groep. Ik nim meastentiids op syn minst 10, mar net mear as 20. Ik besykje te soargjen dat de tiid fan 'e earste en lêste yn' e TOP-groep net mear as 10 kear ferskilt. Dat is, as de útfieringstiid fan 'e query skerp falt fan it 1e plak nei it 10e, dan nim ik TOP-10, as de daling stadichoan is, dan ferheegje ik de groepgrutte nei 15 of 20.
Middelboeren
Dit binne allegear oanfragen dy't direkt nei TOP komme, mei útsûndering fan 'e lêste 5-10%. Gewoanlik leit by it optimalisearjen fan dizze fragen de kâns om de serverprestaasjes sterk te ferheegjen. Dizze oanfragen kinne oant 80% weagje. Mar sels as har oandiel mear as 50% hat, dan is it tiid om se foarsichtiger te besjen.
Sturt
Lykas neamd, komme dizze fragen oan 'e ein en nimme 5-10% fan' e tiid. Jo kinne har allinich ferjitte as jo gjin automatyske ark foar query-analyze brûke, dan kin it optimalisearjen fan har ek goedkeap wêze.
Hoe elke groep te evaluearjen?
Ik brûk in SQL-query dy't helpt om sa'n beoardieling foar PostgreSQL te meitsjen (ik bin der wis fan dat in ferlykbere fraach kin wurde skreaun foar in protte oare DBMS's)
SQL-fraach om de grutte fan TOP-MEDIUM-TAIL-groepen te skatten
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
It resultaat fan 'e fraach is trije kolommen, wêrfan elk it persintaazje tiid befettet dat it nimt om fragen út dizze groep te ferwurkjen. Binnen it fersyk binne d'r twa nûmers (yn myn gefal is it 20 en 800) dy't fersiken fan ien groep fan in oare skiede.
Dit is hoe't de oandielen fan oanfragen rûchwei fergelykje op 'e tiid dat optimalisaasjewurk begon en no.
It diagram lit sjen dat it oandiel fan TOP-oanfragen sterk fermindere is, mar de "middenboeren" binne tanommen.
Yn it earstoan omfetten de TOP-oanfragen blatante flaters. Yn 'e rin fan' e tiid ferdwûnen bernesykten, it oandiel TOP-oanfragen fermindere, en der moasten hieltyd mear ynspanningen dien wurde om drege oanfragen te fersnellen.
Om de tekst fan oanfragen te krijen brûke wy it folgjende fersyk
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
Hjir is in list mei de meast brûkte techniken dy't ús holpen hawwe om TOP-fragen te fersnellen:
- Opnij ûntwerp fan it systeem, bygelyks it werwurkjen fan de notifikaasjelogika mei in berjochtmakelaar ynstee fan periodike fragen nei de databank
- Yndeksen tafoegje of feroarje
- ORM-fragen oerskriuwe nei pure SQL
- Rewriting loai data laden logika
- Caching fia gegevensdenormalisaasje. Bygelyks, wy hawwe in tabel ferbining Levering -> Invoice -> Fersyk -> Applikaasje. Dat is, elke levering is ferbûn mei in applikaasje fia oare tabellen. Om net alle tabellen yn elk fersyk te keppeljen, hawwe wy de keppeling nei it fersyk duplisearre yn 'e tabel Levering.
- Caching statyske tabellen mei referinsje boeken en selden feroarjende tabellen yn programma ûnthâld.
Soms bedroegen de feroaringen in yndrukwekkende werynrjochting, mar se levere 5-10% fan 'e systeemlading en wiene rjochtfeardige. Nei ferrin fan tiid, de exhaust waard lytser en lytser, en mear en mear serieuze werynrjochting nedich.
Doe draaiden wy ús oandacht nei de twadde groep fersiken - de groep middenboeren. Der sitte noch folle mear fragen yn en it like derop dat it in soad tiid duorje soe om de hiele groep te analysearjen. De measte fragen bliken lykwols heul ienfâldich te optimalisearjen, en in protte problemen waarden tsientallen kearen werhelle yn ferskate farianten. Hjir binne foarbylden fan wat typyske optimisaasjes dy't wy tapast hawwe op tsientallen ferlykbere fragen en elke groep optimalisearre queries ûntladen de database mei 3-5%.
- Ynstee fan te kontrolearjen op de oanwêzigens fan records mei COUNT en in folsleine tabelscan, begon EXISTS te brûken
- Krij DISTINCT kwyt (d'r is gjin algemien resept, mar soms kinne jo it maklik kwytreitsje troch it fersyk 10-100 kear te fersnellen).
Bygelyks, ynstee fan in query om alle bestjoerders te selektearjen út in grutte tabel mei leveringen (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDmakke in query op in relatyf lytse tabel PERSOAN
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)It soe lykje dat wy in korrelearre subquery hawwe brûkt, mar it jout in fersnelling fan mear dan 10 kear.
- Yn in protte gefallen waard COUNT hielendal ferlitten en
- вместо
UPPER(s) LIKE JOHN%’brûke
s ILIKE “John%”
Elk spesifyk fersyk waard soms fersneld troch 3-1000 kear. Nettsjinsteande de yndrukwekkende prestaasjes like it ús earst dat d'r gjin punt wie om in query te optimalisearjen dy't 10 ms duorret om te foltôgjen, ien fan 'e 3e hûndert swierste fragen is en hûndertste fan in persint fan' e totale databanklaadtiid nimt. Mar troch it tapassen fan itselde resept op in groep fragen fan itselde type, wûnen wy in pear prosint werom. Om gjin tiid te fergrieme mei it hânmjittich besjen fan alle hûnderten fragen, hawwe wy ferskate ienfâldige skripts skreaun dy't reguliere útdrukkingen brûkten om fragen fan itselde type te finen. As resultaat koe it automatysk sykjen fan groepen fan queries ús ús prestaasjes mei beskieden ynspanning fierder ferbetterje.
As gefolch hawwe wy no trije jier oan deselde hardware wurke. De gemiddelde deistige lading is sa'n 30%, yn peaks berikt it 70%. It oantal oanfragen, lykas it oantal brûkers, is sawat 10 kear ferhege. En dit alles te tankjen oan it konstante tafersjoch fan deselde groepen fan TOP-MEDIUM oanfragen. Sadree't in nij fersyk ferskynt yn 'e TOP-groep, analysearje wy it fuortendaliks en besykje it te rapperjen. Wy beoardielje de MEDIUM-groep ien kear yn 'e wike mei help fan query-analyze-skripts. As wy nije fragen tsjinkomme dy't wy al witte hoe te optimalisearjen, feroarje wy se fluch. Soms fine wy nije optimisaasjemetoaden dy't kinne wurde tapast op ferskate fragen tagelyk.
Neffens ús prognoazes sil de hjoeddeistige tsjinner in ferheging fan it oantal brûkers mei noch 3-5 kear ferneare. Wier, wy hawwe noch ien ace yn 'e mouw - wy hawwe noch gjin SELECT-fragen oerbrocht nei de spegel, lykas oanrikkemandearre. Mar wy dogge dit net bewust, om't wy earst de mooglikheden fan "tûke" optimalisaasje folslein útputje wolle foardat wy de "swiere artillery" oansette.
In krityske blik op it dien wurk kin suggerearje it brûken fan fertikale skaalfergrutting. Keapje in machtiger tsjinner ynstee fan fergriemen fan de tiid fan spesjalisten. De server kin net sa folle kostje, foaral om't wy de grinzen fan fertikale skaalfergrutting noch net hawwe útput. Lykwols, allinnich it oantal oanfragen tanommen 10 kear. Yn 'e rin fan ferskate jierren is de funksjonaliteit fan it systeem ferhege en no binne d'r mear soarten oanfragen. Mei tank oan caching wurdt de funksjonaliteit dy't bestie útfierd yn minder oanfragen, en effisjinter fersiken. Dit betsjut dat jo feilich fermannichfâldigje kinne mei in oare 5 om de echte fersnellingskoëffisjint te krijen. Sa, neffens de meast konservative rûzingen, kinne wy sizze dat de fersnelling wie 50 kear of mear. Fertikaal swingen fan in tsjinner soe 50 kear mear kostje. Benammen yn betinken nommen dat ienris optimalisaasje wurdt útfierd, it wurket de hiele tiid, en de rekken foar de hierde tsjinner komt elke moanne.
Boarne: www.habr.com