Paano lalago ng 10 beses ang bilang ng mga query sa database nang hindi lumilipat sa isang mas produktibong server at nagpapanatili ng paggana ng system? Sasabihin ko sa iyo kung paano namin hinarap ang pagbaba sa pagganap ng aming database, kung paano namin na-optimize ang mga query sa SQL upang maghatid ng maraming user hangga't maaari at hindi mapataas ang halaga ng mga mapagkukunan sa pag-compute.
Gumagawa ako ng serbisyo para sa pamamahala ng mga proseso ng negosyo sa mga kumpanya ng konstruksiyon. Humigit-kumulang 3 libong kumpanya ang nagtatrabaho sa amin. Mahigit sa 10 libong tao ang nagtatrabaho sa aming system araw-araw sa loob ng 4-10 oras. Nilulutas nito ang iba't ibang problema sa pagpaplano, abiso, babala, pagpapatunay... Ginagamit namin ang PostgreSQL 9.6. Mayroon kaming humigit-kumulang 300 mga talahanayan sa database at hanggang sa 200 milyong mga query (10 libong iba't ibang mga) ay natatanggap araw-araw. Sa karaniwan, mayroon kaming 3-4 na libong kahilingan sa bawat segundo, sa mga pinakaaktibong sandali, higit sa 10 libong kahilingan bawat segundo. Karamihan sa mga query ay OLAP. Mayroong mas kaunting mga karagdagan, pagbabago at pagtanggal, ibig sabihin, ang OLTP load ay medyo magaan. Ibinigay ko ang lahat ng mga numerong ito upang masuri mo ang sukat ng aming proyekto at maunawaan kung gaano kapaki-pakinabang ang aming karanasan para sa iyo.
Larawan isa. Liriko
Noong sinimulan namin ang pag-develop, hindi namin naisip kung anong uri ng pag-load ang mahuhulog sa database at kung ano ang gagawin namin kung huminto ang server sa paghila. Kapag nagdidisenyo ng database, sinunod namin ang mga pangkalahatang rekomendasyon at sinubukan naming huwag i-shoot ang aming sarili sa paa, ngunit lumampas sa pangkalahatang payo tulad ng "huwag gamitin ang pattern hindi kami pumasok. Nagdisenyo kami batay sa mga prinsipyo ng normalisasyon, pag-iwas sa redundancy ng data at walang pakialam sa pagpapabilis ng ilang query. Sa sandaling dumating ang mga unang user, nakatagpo kami ng problema sa pagganap. Gaya ng dati, hindi kami handa para dito. Ang mga unang problema ay naging simple. Bilang isang patakaran, ang lahat ay nalutas sa pamamagitan ng pagdaragdag ng isang bagong index. Ngunit dumating ang oras na ang mga simpleng patch ay tumigil sa paggana. Napagtatanto na kulang kami ng karanasan at lalong nagiging mahirap para sa amin na maunawaan kung ano ang nagiging sanhi ng mga problema, kumuha kami ng mga espesyalista na tumulong sa aming i-set up nang tama ang server, ikonekta ang pagsubaybay, at ipinakita sa amin kung saan hahanapin .
Larawan dalawa. Istatistika
Kaya mayroon kaming humigit-kumulang 10 libong iba't ibang mga query na isinasagawa sa aming database bawat araw. Sa 10 libo na ito, mayroong mga halimaw na pinaandar ng 2-3 milyong beses na may average na oras ng pagpapatupad na 0.1-0.3 ms, at may mga query na may average na oras ng pagpapatupad na 30 segundo na tinatawag na 100 beses sa isang araw.
Hindi posible na i-optimize ang lahat ng 10 libong query, kaya nagpasya kaming malaman kung saan ididirekta ang aming mga pagsisikap upang mapabuti ang pagganap ng database nang tama. Pagkatapos ng ilang pag-ulit, sinimulan naming hatiin ang mga kahilingan sa mga uri.
NANGUNGUNANG mga kahilingan
Ito ang mga pinakamabibigat na query na tumatagal ng pinakamaraming oras (kabuuang oras). Ito ay mga query na maaaring tinatawag na napakadalas o mga query na tumatagal ng napakatagal na oras upang maisagawa (matagal at madalas na mga query ay na-optimize sa mga unang pag-ulit ng paglaban para sa bilis). Bilang resulta, ang server ay gumugugol ng pinakamaraming oras sa kanilang pagpapatupad. Bukod dito, mahalagang paghiwalayin ang mga nangungunang kahilingan ayon sa kabuuang oras ng pagpapatupad at hiwalay sa oras ng IO. Ang mga pamamaraan para sa pag-optimize ng mga naturang query ay bahagyang naiiba.
Ang karaniwang kasanayan ng lahat ng mga kumpanya ay magtrabaho kasama ang mga TOP na kahilingan. May iilan sa mga ito; ang pag-optimize ng kahit isang query ay maaaring magbakante ng 5-10% ng mga mapagkukunan. Gayunpaman, habang tumatanda ang proyekto, ang pag-optimize sa mga TOP na query ay nagiging isang lalong hindi maliit na gawain. Ang lahat ng mga simpleng pamamaraan ay nagawa na, at ang pinaka "mabigat" na kahilingan ay "lamang" 3-5% ng mga mapagkukunan. Kung ang mga NANGUNGUNANG query sa kabuuan ay tumatagal ng mas mababa sa 30-40% ng oras, malamang na nagsumikap ka na upang gumana ang mga ito nang mabilis at oras na para magpatuloy sa pag-optimize ng mga query mula sa susunod na pangkat.
Ito ay nananatiling sagutin ang tanong kung gaano karaming mga nangungunang query ang dapat isama sa pangkat na ito. Karaniwan akong kumukuha ng hindi bababa sa 10, ngunit hindi hihigit sa 20. Sinisikap kong tiyakin na ang oras ng una at huling sa TOP na grupo ay naiiba nang hindi hihigit sa 10 beses. Iyon ay, kung ang oras ng pagpapatupad ng query ay bumaba nang husto mula sa unang lugar hanggang ika-1, pagkatapos ay kukuha ako ng TOP-10, kung ang pagbaba ay mas unti-unti, pagkatapos ay dinadagdagan ko ang laki ng grupo sa 10 o 15.
Gitnang magsasaka
Ito ang lahat ng mga kahilingan na dumarating kaagad pagkatapos ng TOP, maliban sa huling 5-10%. Karaniwan, sa pag-optimize ng mga query na ito ay namamalagi ang pagkakataon upang lubos na mapataas ang pagganap ng server. Ang mga kahilingang ito ay maaaring tumimbang ng hanggang 80%. Ngunit kahit na ang kanilang bahagi ay lumampas sa 50%, pagkatapos ay oras na upang tingnan ang mga ito nang mas mabuti.
buntot
Tulad ng nabanggit, ang mga query na ito ay darating sa dulo at tumatagal ng 5-10% ng oras. Maaari mo lamang kalimutan ang tungkol sa mga ito kung hindi ka gumagamit ng mga awtomatikong tool sa pagsusuri ng query, kung gayon ang pag-optimize sa mga ito ay maaari ding mura.
Paano suriin ang bawat pangkat?
Gumagamit ako ng SQL query na tumutulong sa paggawa ng ganoong pagtatasa para sa PostgreSQL (Sigurado ako na ang isang katulad na query ay maaaring isulat para sa maraming iba pang mga DBMS)
SQL query upang tantyahin ang laki ng mga pangkat na 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
Ang resulta ng query ay tatlong column, na ang bawat isa ay naglalaman ng porsyento ng oras na kinakailangan upang maproseso ang mga query mula sa pangkat na ito. Sa loob ng kahilingan mayroong dalawang numero (sa aking kaso ito ay 20 at 800) na naghihiwalay ng mga kahilingan mula sa isang grupo mula sa isa pa.
Ito ay kung paano halos ihambing ang mga bahagi ng mga kahilingan sa oras na nagsimula ang trabaho sa pag-optimize at ngayon.
Ang diagram ay nagpapakita na ang bahagi ng mga kahilingan ng TOP ay bumaba nang husto, ngunit ang mga "gitnang magsasaka" ay tumaas.
Noong una, kasama sa mga TOP na kahilingan ang tahasang mga pagkakamali. Sa paglipas ng panahon, nawala ang mga sakit sa pagkabata, nabawasan ang bahagi ng mga kahilingan sa TOP, at higit pang mga pagsisikap ang kailangang gawin upang mapabilis ang mahihirap na kahilingan.
Upang makuha ang text ng mga kahilingan, ginagamit namin ang sumusunod na kahilingan
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
Narito ang isang listahan ng mga pinakakaraniwang ginagamit na diskarte na nakatulong sa amin na mapabilis ang NANGUNGUNANG mga query:
- Muling disenyo ng system, halimbawa, muling paggawa ng lohika ng notification gamit ang isang message broker sa halip na mga pana-panahong query sa database
- Pagdaragdag o pagbabago ng mga index
- Muling pagsusulat ng mga query sa ORM sa purong SQL
- Muling pagsusulat ng tamad na lohika sa paglo-load ng data
- Pag-cache sa pamamagitan ng data denormalization. Halimbawa, mayroon kaming table connection Delivery -> Invoice -> Request -> Application. Iyon ay, ang bawat paghahatid ay nauugnay sa isang aplikasyon sa pamamagitan ng iba pang mga talahanayan. Upang hindi ma-link ang lahat ng mga talahanayan sa bawat kahilingan, na-duplicate namin ang link sa kahilingan sa talahanayan ng Paghahatid.
- Pag-cache ng mga static na talahanayan na may mga reference na libro at bihirang baguhin ang mga talahanayan sa memorya ng programa.
Minsan ang mga pagbabago ay katumbas ng isang kahanga-hangang muling pagdidisenyo, ngunit nagbigay sila ng 5-10% ng pag-load ng system at nabigyang-katwiran. Sa paglipas ng panahon, ang tambutso ay naging mas maliit at mas maliit, at higit pa at mas seryosong muling pagdidisenyo ay kinakailangan.
Pagkatapos ay ibinaling namin ang aming atensyon sa pangalawang grupo ng mga kahilingan - ang grupo ng mga panggitnang magsasaka. Marami pang query dito at mukhang magtatagal para pag-aralan ang buong grupo. Gayunpaman, ang karamihan sa mga query ay naging napakasimpleng i-optimize, at maraming mga problema ang naulit nang dose-dosenang beses sa iba't ibang mga pagkakaiba-iba. Narito ang mga halimbawa ng ilang karaniwang pag-optimize na inilapat namin sa dose-dosenang mga katulad na query at ang bawat pangkat ng mga na-optimize na query ay nag-unload sa database ng 3-5%.
- Sa halip na suriin ang pagkakaroon ng mga talaan gamit ang COUNT at isang buong pag-scan ng talahanayan, nagsimulang gamitin ang EXISTS
- Inalis ang DISTINCT (walang pangkalahatang recipe, ngunit kung minsan madali mong mapupuksa ito sa pamamagitan ng pagpapabilis ng kahilingan ng 10-100 beses).
Halimbawa, sa halip na isang query upang piliin ang lahat ng mga driver mula sa isang malaking talahanayan ng mga paghahatid (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDgumawa ng query sa isang medyo maliit na table na 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)Mukhang gumamit kami ng isang nauugnay na subquery, ngunit nagbibigay ito ng bilis ng higit sa 10 beses.
- Sa maraming pagkakataon, ang COUNT ay ganap na inabandona at
- sa halip ng
UPPER(s) LIKE JOHN%βgamitin
s ILIKE βJohn%β
Ang bawat partikular na kahilingan ay minsan ay pinabilis ng 3-1000 beses. Sa kabila ng kahanga-hangang pagganap, sa una ay tila sa amin na walang punto sa pag-optimize ng isang query na tumatagal ng 10 ms upang makumpleto, ay isa sa ika-3 daang pinakamabigat na query, at tumatagal ng daan-daang porsyento ng kabuuang oras ng pag-load ng database. Ngunit sa pamamagitan ng paglalapat ng parehong recipe sa isang pangkat ng mga query ng parehong uri, nanalo kami ng ilang porsyento. Upang hindi mag-aksaya ng oras sa manu-manong pagsusuri sa lahat ng daan-daang query, sumulat kami ng ilang simpleng script na gumamit ng mga regular na expression upang maghanap ng mga query na may parehong uri. Bilang resulta, ang awtomatikong paghahanap sa mga pangkat ng mga query ay nagbigay-daan sa amin upang higit pang pagbutihin ang aming pagganap sa katamtamang pagsisikap.
Bilang resulta, tatlong taon na kaming nagtatrabaho sa parehong hardware. Ang average na pang-araw-araw na pagkarga ay halos 30%, sa mga taluktok umabot ito sa 70%. Ang bilang ng mga kahilingan, pati na rin ang bilang ng mga user, ay tumaas nang humigit-kumulang 10 beses. At lahat ng ito salamat sa patuloy na pagsubaybay sa parehong mga grupo ng mga TOP-MEDIUM na kahilingan. Sa sandaling lumitaw ang isang bagong kahilingan sa TOP na pangkat, agad naming sinusuri ito at sinusubukang pabilisin ito. Sinusuri namin ang pangkat na MEDIUM isang beses sa isang linggo gamit ang mga script ng pagsusuri ng query. Kung makatagpo kami ng mga bagong query na alam na namin kung paano i-optimize, mabilis naming binabago ang mga ito. Minsan nakakahanap kami ng mga bagong paraan ng pag-optimize na maaaring ilapat sa ilang query nang sabay-sabay.
Ayon sa aming mga pagtataya, ang kasalukuyang server ay makatiis ng pagtaas sa bilang ng mga gumagamit ng isa pang 3-5 beses. Totoo, mayroon pa kaming isa pang ace - hindi pa rin namin inilipat ang SELECT query sa salamin, gaya ng inirerekomenda. Ngunit hindi namin ito sinasadya, dahil gusto muna naming ganap na maubos ang mga posibilidad ng "matalinong" pag-optimize bago i-on ang "mabigat na artilerya".
Ang isang kritikal na pagtingin sa gawaing ginawa ay maaaring magmungkahi ng paggamit ng vertical scaling. Bumili ng mas malakas na server sa halip na mag-aksaya ng oras ng mga espesyalista. Maaaring hindi ganoon kalaki ang halaga ng server, lalo na dahil hindi pa namin nauubos ang mga limitasyon ng vertical scaling. Gayunpaman, ang bilang lamang ng mga kahilingan ay tumaas ng 10 beses. Sa paglipas ng ilang taon, ang pag-andar ng system ay tumaas at ngayon ay may higit pang mga uri ng mga kahilingan. Salamat sa pag-cache, ang functionality na umiiral ay ginagawa sa mas kaunting mga kahilingan, at mas mahusay na mga kahilingan. Nangangahulugan ito na maaari mong ligtas na i-multiply ng isa pang 5 upang makuha ang tunay na koepisyent ng acceleration. Kaya, ayon sa pinakakonserbatibong pagtatantya, masasabi nating ang acceleration ay 50 beses o higit pa. Ang patayong pag-indayog ng isang server ay nagkakahalaga ng 50 beses na mas mataas. Lalo na kung isasaalang-alang na kapag naisagawa ang pag-optimize ay gumagana ito sa lahat ng oras, at ang bayarin para sa inuupahang server ay darating bawat buwan.
Pinagmulan: www.habr.com