Nola hazi datu-baserako kontsulta kopurua 10 aldiz zerbitzari produktiboago batera mugitu gabe eta sistemaren funtzionaltasuna mantendu? Gure datu-basearen errendimenduaren beherakadari nola aurre egin diogun kontatuko dizuet, nola optimizatu ditugun SQL kontsultak ahalik eta erabiltzaile gehienei zerbitzatzeko eta baliabide informatikoen kostua ez handitzeko.
Eraikuntza enpresetan negozio-prozesuak kudeatzeko zerbitzu bat egiten dut. 3 mila enpresa inguruk lan egiten dute gurekin. 10 mila pertsona baino gehiagok gure sistemarekin lan egiten dute egunero 4-10 orduz. Hainbat arazo konpontzen ditu plangintza, jakinarazpena, abisua, balioztatzea... PostgreSQL 9.6 erabiltzen dugu. Datu-basean 300 taula inguru ditugu eta egunero 200 milioi kontsulta (10 mila ezberdin) jasotzen dira. Batez beste 3-4 mila eskaera izaten ditugu segundoko, momentu aktiboenetan 10 mila eskaera baino gehiago segundoko. Kontsulta gehienak OLAP dira. Askoz gehikuntza, aldaketa eta ezabaketa gutxiago daude, hau da, OLTP karga nahiko arina da. Zenbaki hauek guztiak eman nituen, gure proiektuaren tamaina ebaluatu eta gure esperientzia zuretzako zein erabilgarria izan daitekeen ulertzeko.
Irudi bat. Lirikoa
Garatzen hasi ginenean, ez genuen benetan pentsatu zer-nolako karga eroriko zen datu-basean eta zer egingo genukeen zerbitzariak tiratzeari utziko balu. Datu-basea diseinatzerakoan, gomendio orokorrak jarraitu genituen eta oinetan tirorik ez egiten saiatu ginen, baina aholku orokorretik harago joan ginen "ez erabili eredua". ez ginen sartu. Normalizazioaren printzipioetan oinarrituta diseinatu genuen, datuen erredundantzia saihestuz eta ez zitzaigun axola zenbait kontsulta bizkortzea. Lehen erabiltzaileak iritsi bezain laster, errendimendu arazo bat aurkitu dugu. Ohi bezala, guztiz prestatu gabe geunden horretarako. Lehen arazoak sinpleak izan ziren. Oro har, dena indize berri bat gehituz konpondu zen. Baina iritsi zen garai bat non adabaki sinpleak funtzionatzeari utzi zion. Esperientzia falta zaigula eta arazoak zerk eragiten dituen ulertzea gero eta zailagoa zaigula konturatuta, zerbitzaria behar bezala konfiguratzen lagundu diguten espezialistak kontratatu ditugu, monitorizazioa konektatu eta non bilatu behar dugun erakutsi digute. .
Bigarren irudia. Estatistika
Beraz, egunero gure datu-basean exekutatzen diren 10 mila kontsulta ezberdin inguru ditugu. 10 mila horietatik 2-3 milioi aldiz exekutatzen diren munstroak daude 0.1-0.3 ms-ko batez besteko exekuzio-denbora batekin, eta 30 segundoko batez besteko exekuzio-denbora duten kontsultak daude, egunean 100 aldiz deitzen direnak.
Ezin izan zen 10 mila kontsulta guztiak optimizatu, beraz, gure ahaleginak nora bideratu asmatzea erabaki genuen datu-basearen errendimendua behar bezala hobetzeko. Hainbat errepikapenen ondoren, eskaerak motatan banatzen hasi ginen.
TOP eskaerak
Hauek dira denbora gehien hartzen duten kontsultarik astunenak (denbora osoa). Oso sarri deitzen diren kontsultak edo exekutatzeko oso denbora luzea behar duten kontsultak dira (kontsulta luzeak eta maiztasunak optimizatu ziren abiaduraren aldeko borrokaren lehen iterazioetan). Ondorioz, zerbitzariak denbora gehien ematen du haien exekuzioan. Gainera, garrantzitsua da eskaera nagusiak exekuzio denbora osoaren arabera eta IO denboraren arabera bereiztea. Kontsultak optimizatzeko metodoak zertxobait desberdinak dira.
Enpresa guztien ohiko praktika TOP eskaerak lantzea da. Horietako gutxi daude; kontsulta bakarra optimizatzeak baliabideen %5-10 askatu dezake. Hala ere, proiektua heltzen doan heinean, TOP kontsultak optimizatzea gero eta zeregin ez-trivialagoa bihurtzen da. Metodo sinple guztiak landu dira dagoeneko, eta eskaera "astunenak" baliabideen % 3-5 "soilik" hartzen du. Guztira, TOP kontsultek denboraren % 30-40 baino gutxiago hartzen badute, ziurrenik dagoeneko ahaleginak egin dituzu azkar funtzionatzeko eta hurrengo taldeko kontsultak optimizatzera pasatzeko garaia da.
Talde honetan zenbat kontsulta nagusi sartu behar diren galderari erantzutea geratzen da. Normalean 10 hartzen ditut gutxienez, baina 20 baino gehiago ez. TOP taldeko lehenengoaren eta azkenaren denbora 10 aldiz baino gehiago ez dela ziurtatzen saiatzen naiz. Hau da, kontsultaren exekuzio-denbora nabarmen jaisten bada 1. tokitik 10era, orduan TOP-10 hartzen dut, jaitsiera pixkanaka-pixkanaka bada, taldeen tamaina 15 edo 20ra handitzen dut.
Erdiko nekazariak
TOParen ondoren berehala datozen eskaerak dira guztiak, azken %5-10a izan ezik. Normalean, kontsulta hauek optimizatzean zerbitzariaren errendimendua asko handitzeko aukera dago. Eskaera horiek %80ra arteko pisua izan dezakete. Baina haien kuota %50etik gorakoa izan arren, orduan arreta gehiagoz aztertzeko garaia da.
Isatsa
Esan bezala, kontsulta hauek amaieran etortzen dira eta denboraren %5-10 hartzen dute. Kontsulten analisi automatikoko tresnak erabiltzen ez badituzu bakarrik ahaztu ditzakezu, orduan optimizatzea ere merkea izan daiteke.
Nola baloratu talde bakoitza?
PostgreSQL-ren ebaluazioa egiten laguntzen duen SQL kontsulta bat erabiltzen dut (ziur nago antzeko kontsulta bat beste DBMS askotarako idatzi daitekeela)
SQL kontsulta TOP-MEDIUM-TAIL taldeen tamaina kalkulatzeko
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
Kontsultaren emaitza hiru zutabe da, eta bakoitzak talde honetako kontsultak prozesatzeko behar duen denbora-portzentajea dauka. Eskaeraren barruan bi zenbaki daude (nire kasuan 20 eta 800 da) eskaerak talde batetik bestetik bereizten dituztenak.
Horrela konparatzen dira gutxi gorabehera eskaeren akzioak optimizazio lanak hasi ziren garaian eta orain.
Diagramak erakusten du TOP eskaeren kuota asko murriztu dela, baina "erdiko nekazariak" handitu egin direla.
Hasieran, TOP eskaerak akats nabarmenak barne hartzen zituen. Denborarekin, haurtzaroko gaixotasunak desagertu egin ziren, TOP eskaeren zatia gutxitu egin zen eta gero eta ahalegin gehiago egin behar izan ziren eskaera zailak azkartzeko.
Eskaeren testua lortzeko honako eskaera hau erabiltzen dugu
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
Hona hemen TOP kontsultak bizkortzen lagundu diguten teknika erabilienen zerrenda:
- Sistema birdiseinatzea, adibidez, jakinarazpen-logika birlantzea mezu-artekari bat erabiliz datu-baseari aldizkako kontsultak egin beharrean.
- Indizeak gehitzea edo aldatzea
- ORM kontsultak SQL hutsean berridaztea
- Datu alferrak kargatzeko logika berridaztea
- Datuen desnormalizazioaren bidez gordetzea. Adibidez, mahai-konexio bat dugu Entrega -> Faktura -> Eskaera -> Aplikazioa. Hau da, entrega bakoitza aplikazio batekin lotzen da beste taulen bidez. Eskaera bakoitzean taula guztiak ez lotzeko, Bidalketa taulan eskaeraren esteka bikoiztu dugu.
- Taula estatikoak cachean gordetzea erreferentzia-liburuekin eta oso gutxitan aldatzen dira taulak programaren memorian.
Batzuetan, aldaketek birdiseinu ikusgarria izan zuten, baina sistemaren kargaren % 5-10 ematen zuten eta justifikatuta zeuden. Denborarekin, ihesa gero eta txikiagoa zen, eta gero eta birmoldaketa serioagoa behar zen.
Ondoren, bigarren eskaera taldean jarri genuen arreta: erdiko nekazarien taldea. Askoz kontsulta gehiago daude bertan eta talde osoa aztertzeko denbora asko beharko litzatekeela zirudien. Hala ere, kontsulta gehienak oso errazak izan ziren optimizatzeko, eta arazo asko dozenaka aldiz errepikatu ziren aldaera ezberdinetan. Hona hemen antzeko dozenaka kontsultatan aplikatu ditugun optimizazio tipiko batzuen adibideak eta optimizatutako kontsulta talde bakoitzak datu-basea % 3-5 deskargatu zuen.
- COUNT eta taula osoa eskaneatzea erabiliz erregistroen presentzia egiaztatu beharrean, EXISTS erabiltzen hasi zen
- DISTINCT kendu dut (ez dago errezeta orokorrik, baina batzuetan erraz ken dezakezu eskaera 10-100 aldiz bizkortuz).
Adibidez, bidalketen taula handi batetik gidari guztiak hautatzeko kontsultaren ordez (ENTREGA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDkontsulta bat egin zuen taula txiki samarrean 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)Badirudi korrelazionatutako azpikontsulta bat erabili dugula, baina 10 aldiz baino gehiagoko abiadura ematen du.
- Kasu askotan, COUNT guztiz abandonatu zen eta
- ordez
UPPER(s) LIKE JOHN%βerabilera
s ILIKE βJohn%β
Eskaera zehatz bakoitza 3-1000 aldiz bizkortu zen batzuetan. Errendimendu ikusgarria izan arren, hasiera batean iruditu zitzaigun ez zegoela 10 ms betetzeko behar dituen kontsulta bat optimizatzea, 3. ehunen kontsulta astunenetako bat dela eta datu-basearen karga-denboraren ehuneko ehuneneko ehunenetan hartzen duela. Baina mota bereko kontsulta talde bati errezeta bera aplikatuz, ehuneko batzuk irabazi genituen. Ehunka kontsulta guztiak eskuz berrikusten denbora ez galtzeko, mota bereko kontsultak aurkitzeko adierazpen erregularrak erabiltzen zituzten hainbat script sinple idatzi genituen. Ondorioz, kontsulta-taldeak automatikoki bilatzeari esker, gure errendimendua gehiago hobetu genuen ahalegin xumearekin.
Ondorioz, hiru urte daramatzagu hardware berdinarekin lanean. Eguneko batez besteko karga %30 ingurukoa da, gailurretan %70era iristen da. Eskaeren kopurua, baita erabiltzaile kopurua ere, gutxi gorabehera 10 aldiz handitu da. Eta hori guztia GOI-ERTAINAko eskaera talde hauen etengabeko jarraipenari esker. TOP taldean eskaera berri bat agertu bezain laster, berehala aztertu eta bizkortzen saiatzen gara. MEDIUM taldea astean behin berrikusten dugu kontsultak aztertzeko gidoiak erabiliz. Dagoeneko optimizatzen dakigun kontsulta berriekin topo egiten badugu, azkar aldatzen ditugu. Batzuetan, hainbat kontsultatan aldi berean aplika daitezkeen optimizazio-metodo berriak aurkitzen ditugu.
Gure aurreikuspenen arabera, egungo zerbitzariak erabiltzaile kopurua beste 3-5 aldiz gehiago jasango du. Egia da, aseko bat gehiago daukagu ββmahukan - oraindik ez ditugu SELECT kontsultak ispilura transferitu, gomendatzen den bezala. Baina hori ez dugu kontzienteki egiten, lehenik eta behin optimizazio "adimentsua" aukerak guztiz agortu nahi ditugulako "artilleria astuna" piztu aurretik.
Egindako lanari begirada kritiko batek eskala bertikala erabiltzea iradoki dezake. Erosi zerbitzari indartsuago bat espezialisten denbora galdu beharrean. Zerbitzariak agian ez du horrenbeste kostatuko, batez ere eskalatze bertikalaren mugak oraindik agortu ez ditugulako. Hala ere, eskaera kopurua 10 aldiz baino ez da handitu. Hainbat urtetan, sistemaren funtzionaltasuna handitu egin da eta orain eskaera mota gehiago daude. Cacheari esker, zegoen funtzionaltasuna eskaera gutxiagotan egiten da, eta eskaera eraginkorragoetan. Horrek esan nahi du segurtasunez beste 5 batez biderkatu dezakezula benetako azelerazio-koefizientea lortzeko. Beraz, estimazio kontserbadoreenen arabera, azelerazioa 50 aldiz edo gehiago izan zela esan dezakegu. Zerbitzari bat bertikalki kulunkatzea 50 aldiz gehiago kostatuko litzateke. Batez ere kontuan izanda optimizazioa egin ondoren uneoro funtzionatzen duela, eta alokatutako zerbitzariaren faktura hilero etortzen dela.
Iturria: www.habr.com