Kiel kreski 10 fojojn la nombron da demandoj al la datumbazo sen moviĝi al pli produktiva servilo kaj konservi sisteman funkciojn? Mi rakontos al vi kiel ni traktis la malkreskon en la rendimento de nia datumbazo, kiel ni optimumigis SQL-demandojn por servi kiel eble plej multajn uzantojn kaj ne pliigi la koston de komputikaj rimedoj.
Mi faras servon por administri komercajn procezojn en konstruentreprenoj. Ĉirkaŭ 3 mil kompanioj laboras kun ni. Pli ol 10 mil homoj laboras kun nia sistemo ĉiutage dum 4-10 horoj. Ĝi solvas diversajn problemojn de planado, sciigo, averto, validigo... Ni uzas PostgreSQL 9.6. Ni havas ĉirkaŭ 300 tabelojn en la datumbazo kaj ĉiutage ricevas ĝis 200 milionoj da demandoj (10 mil diversaj). Averaĝe ni havas 3-4 mil petojn sekundo, en la plej aktivaj momentoj pli ol 10 mil petojn sekundo. La plej multaj el la demandoj estas OLAP. Estas multe malpli da aldonoj, modifoj kaj forigoj, kio signifas, ke la OLTP-ŝarĝo estas relative malpeza. Mi provizis ĉiujn ĉi tiujn nombrojn por ke vi povu taksi la amplekson de nia projekto kaj kompreni kiom utila nia sperto povas esti por vi.
Bildo unu. Lirika
Kiam ni komencis disvolviĝon, ni ne vere pensis pri kia ŝarĝo falus sur la datumbazon kaj kion ni farus se la servilo ĉesos tiri. Dum desegnado de la datumbazo, ni sekvis ĝeneralajn rekomendojn kaj provis ne pafi nin en la piedon, sed preterpasis ĝeneralajn konsilojn kiel "ne uzu la ŝablonon. ni ne eniris. Ni desegnis surbaze de la principoj de normaligo, evitante datuman redundon kaj ne zorgis pri rapidigo de certaj demandoj. Tuj kiam la unuaj uzantoj alvenis, ni renkontis rendimentan problemon. Kiel kutime, ni estis tute nepreparitaj por tio. La unuaj problemoj montriĝis simplaj. Kiel regulo, ĉio estis solvita aldonante novan indekson. Sed venis tempo, kiam simplaj flikiloj ĉesis funkcii. Rimarkinte, ke mankas al ni sperto kaj fariĝas ĉiam pli malfacile por ni kompreni, kio kaŭzas la problemojn, ni dungis specialistojn, kiuj helpis nin agordi la servilon ĝuste, konekti monitoradon kaj montris al ni kien serĉi por akiri. .
Bildo du. Statistika
Do ni havas ĉirkaŭ 10 mil malsamajn demandojn, kiuj estas ekzekutitaj en nia datumbazo ĉiutage. El ĉi tiuj 10 mil, estas monstroj, kiuj estas ekzekutitaj 2-3 milionojn da fojoj kun averaĝa ekzekuttempo de 0.1-0.3 ms, kaj estas demandoj kun averaĝa ekzekuttempo de 30 sekundoj, kiuj estas nomitaj 100 fojojn tage.
Ne eblis optimumigi ĉiujn 10 mil demandojn, do ni decidis eltrovi kien direkti niajn klopodojn por plibonigi la rendimenton de la datumbazo ĝuste. Post pluraj ripetoj, ni komencis dividi petojn en tipojn.
TOP petoj
Ĉi tiuj estas la plej pezaj demandoj, kiuj prenas la plej grandan tempon (totala tempo). Ĉi tiuj estas demandoj, kiuj estas aŭ tre ofte nomataj aŭ demandoj, kiuj postulas tre longan tempon por plenumi (longaj kaj oftaj demandoj estis optimumigitaj en la unuaj ripetoj de la batalo por rapideco). Kiel rezulto, la servilo pasigas la plej grandan tempon por ilia ekzekuto. Plie, gravas apartigi ĉefajn petojn laŭ tuta ekzekuttempo kaj aparte laŭ IO-tempo. La metodoj por optimumigi tiajn demandojn estas iomete malsamaj.
La kutima praktiko de ĉiuj kompanioj estas labori kun TOP-petoj. Estas malmultaj el ili; optimumigi eĉ unu demandon povas liberigi 5-10% da rimedoj. Tamen, dum la projekto maturiĝas, optimumigi TOP-demandojn fariĝas ĉiam pli ne-triviala tasko. Ĉiuj simplaj metodoj jam estas ellaboritaj, kaj la plej "peza" peto prenas "nur" 3-5% de rimedoj. Se TOP-demandoj entute daŭras malpli ol 30-40% de la tempo, tiam plej verŝajne vi jam klopodis por rapide funkcii ilin kaj estas tempo pluiri al optimumigo de demandoj de la sekva grupo.
Restas respondi la demandon pri kiom da ĉefaj demandoj estu inkluzivitaj en ĉi tiu grupo. Mi kutime prenas almenaŭ 10, sed ne pli ol 20. Mi provas certigi, ke la tempo de la unua kaj lasta en la TOP-grupo diferencas ne pli ol 10 fojojn. Tio estas, se la tempo de ekzekuto de la demandoj malpliiĝas de la 1-a loko al la 10-a, tiam mi prenas TOP-10, se la guto estas pli laŭgrada, tiam mi pliigas la grupan grandecon al 15 aŭ 20.
Mezaj kamparanoj
Ĉi tiuj estas ĉiuj petoj, kiuj venas tuj post TOP, escepte de la lastaj 5-10%. Kutime, en optimumigo de ĉi tiuj demandoj kuŝas la ŝanco ege pliigi servilan rendimenton. Ĉi tiuj petoj povas pezi ĝis 80%. Sed eĉ se ilia parto superis 50%, tiam estas tempo rigardi ilin pli atente.
Vosto
Kiel menciite, ĉi tiuj demandoj venas ĉe la fino kaj prenas 5-10% de la tempo. Vi povas forgesi pri ili nur se vi ne uzas aŭtomatajn konsultajn analizojn, tiam optimumigi ilin ankaŭ povas esti malmultekosta.
Kiel taksi ĉiun grupon?
Mi uzas SQL-demandon, kiu helpas fari tian takson por PostgreSQL (mi certas, ke simila demando povas esti skribita por multaj aliaj DBMSoj)
SQL-demando por taksi la grandecon de TOP-MEDIUM-TAIL grupoj
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
La rezulto de la demando estas tri kolumnoj, ĉiu el kiuj enhavas la procenton de tempo necesa por prilabori demandojn de ĉi tiu grupo. Ene de la peto estas du ciferoj (en mia kazo estas 20 kaj 800) kiuj apartigas petojn de unu grupo de alia.
Jen kiel la akcioj de petoj proksimume komparas en la tempo, kiam la optimumigo laboro komenciĝis kaj nun.
La diagramo montras, ke la parto de TOP-petoj akre malpliiĝis, sed la "mezaj kamparanoj" pliiĝis.
Komence, la TOP-petoj inkludis evidentajn erarojn. Kun la tempo, infanaj malsanoj malaperis, la parto de TOP-petoj malpliiĝis, kaj pli kaj pli da klopodoj devis esti faritaj por akceli malfacilajn petojn.
Por ricevi la tekston de petoj ni uzas la jenan peton
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
Jen listo de la plej ofte uzataj teknikoj, kiuj helpis nin akceli TOP-demandojn:
- Restrukturi la sistemon, ekzemple, reverkante la sciigan logikon uzante mesaĝmakleriston anstataŭ periodajn demandojn al la datumbazo
- Aldonante aŭ ŝanĝante indeksojn
- Reverkante ORM-demandojn al pura SQL
- Reverkado de maldiligenta datuma ŝarĝo-logiko
- Kaŝmemoro per datummalnormaligo. Ekzemple, ni havas tabelkonekton Transdono -> Fakturo -> Peto -> Apliko. Tio estas, ĉiu livero estas asociita kun aplikaĵo tra aliaj tabloj. Por ne ligi ĉiujn tabelojn en ĉiu peto, ni duobligis la ligon al la peto en la Livero-tabelo.
- Kaŝmemoro senmovaj tabeloj kun referenclibroj kaj malofte ŝanĝante tabelojn en programmemoro.
Kelkfoje la ŝanĝoj sumiĝis al impona restrukturado, sed ili disponigis 5-10% de la sistema ŝarĝo kaj estis pravigitaj. Kun la tempo, la degaso iĝis pli kaj pli malgranda, kaj pli kaj pli serioza restrukturado estis postulata.
Poste ni turnis nian atenton al la dua grupo de petoj - la grupo de mezaj kamparanoj. Estas multaj pliaj demandoj en ĝi kaj ŝajnis ke necesus multe da tempo por analizi la tutan grupon. Tamen, la plej multaj demandoj montriĝis tre simplaj por optimumigi, kaj multaj problemoj ripetiĝis dekoj da fojoj en malsamaj varioj. Jen ekzemploj de iuj tipaj optimumigoj, kiujn ni aplikis al dekoj da similaj demandoj kaj ĉiu grupo de optimumigitaj demandoj malŝarĝis la datumbazon je 3-5%.
- Anstataŭ kontroli la ĉeeston de rekordoj uzante COUNT kaj plenan tabelskanadon, EKZISTAS komencis esti uzata
- Foriĝis DISTINCT (ne ekzistas ĝenerala recepto, sed foje vi povas facile forigi ĝin plirapidigante la peton 10-100 fojojn).
Ekzemple, anstataŭ demando por elekti ĉiujn ŝoforojn el granda tabelo de liveraĵoj (LIVERO)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDfaris demandon sur relative malgranda tablo PERSONO
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Ŝajnus, ke ni uzis korelacian subdemandon, sed ĝi donas plirapidigon de pli ol 10 fojojn.
- En multaj kazoj, COUNT estis forlasita entute kaj
- anstataŭ
UPPER(s) LIKE JOHN%’uzi
s ILIKE “John%”
Ĉiu specifa peto foje estis plirapidigita je 3-1000 fojojn. Malgraŭ la impona agado, komence ŝajnis al ni, ke ne utilas optimumigi demandon, kiu bezonas 10 ms por kompletigi, estas unu el la 3-a cent plej pezaj demandoj kaj okupas centonojn de procento de la ĝenerala datumbaza ŝarĝotempo. Sed aplikante la saman recepton al grupo de demandoj de la sama tipo, ni regajnas kelkajn procentojn. Por ne perdi tempon mane reviziante ĉiujn centojn da demandoj, ni skribis plurajn simplajn skriptojn, kiuj uzis regulajn esprimojn por trovi demandojn de la sama tipo. Rezulte, aŭtomate serĉado de grupoj de demandoj permesis al ni plu plibonigi nian agadon per modesta peno.
Kiel rezulto, ni laboras pri la sama aparataro jam de tri jaroj. La averaĝa ĉiutaga ŝarĝo estas ĉirkaŭ 30%, en pintoj ĝi atingas 70%. La nombro da petoj, same kiel la nombro da uzantoj, pliiĝis proksimume 10 fojojn. Kaj ĉio ĉi danke al la konstanta monitorado de ĉi tiuj mem grupoj de TOP-MEZAJ petoj. Tuj kiam nova peto aperas en la TOP-grupo, ni tuj analizas ĝin kaj provas akceli ĝin. Ni revizias la MEZAN-grupon unufoje semajne uzante demandajn analizajn skriptojn. Se ni renkontas novajn demandojn, kiujn ni jam scias kiel optimumigi, ni rapide ŝanĝas ilin. Kelkfoje ni trovas novajn optimumigajn metodojn, kiuj povas esti aplikitaj al pluraj demandoj samtempe.
Laŭ niaj antaŭvidoj, la nuna servilo eltenos plialtiĝon de la nombro da uzantoj je pliaj 3-5 fojojn. Vere, ni havas ankoraŭ unu ason en la maniko - ni ankoraŭ ne transdonis SELECT-demandojn al la spegulo, kiel rekomendite. Sed ni ne faras ĉi tion konscie, ĉar ni volas unue tute elĉerpi la eblecojn de "inteligenta" optimumigo antaŭ ol ŝalti la "pezan artilerion".
Kritika rigardo al la farita laboro povas sugesti uzi vertikalan skaladon. Aĉetu pli potencan servilon anstataŭ malŝpari la tempon de specialistoj. La servilo eble ne kostas tiom multe, precipe ĉar ni ankoraŭ ne elĉerpis la limojn de vertikala skalo. Tamen nur la nombro da petoj pliiĝis 10 fojojn. Dum pluraj jaroj, la funkcieco de la sistemo pliiĝis kaj nun ekzistas pli da specoj de petoj. Danke al kaŝmemoro, la ekzistinta funkcio estas plenumita en malpli da petoj, kaj pli efikaj petoj. Ĉi tio signifas, ke vi povas sekure multobligi per alia 5 por akiri la realan akcelan koeficienton. Do, laŭ la plej konservativaj taksoj, ni povas diri, ke la akcelo estis 50 fojojn aŭ pli. Vertikale svingi servilon kostus 50 fojojn pli. Precipe konsiderante, ke post kiam la optimumigo estas efektivigita, ĝi funkcias la tutan tempon, kaj la fakturo por la luita servilo venas ĉiumonate.
fonto: www.habr.com