Ahoana no hampitomboana in-10 ny isan'ny fanontaniana ao amin'ny angon-drakitra nefa tsy mifindra amin'ny mpizara mamokatra kokoa ary mitazona ny fiasan'ny rafitra? Holazaiko aminao ny fomba niatrehanay ny fihenan'ny fahombiazan'ny angon-drakitray, ny fomba nanatsaranay ny fangatahana SQL mba hanompoana mpampiasa betsaka araka izay tratra ary tsy hampitombo ny vidin'ny loharanon-karena.
Manao serivisy aho amin'ny fitantanana ny fizotran'ny raharaham-barotra amin'ny orinasa mpanao fanorenana. Orinasa 3 arivo eo ho eo no miara-miasa aminay. Olona maherin'ny 10 arivo no miasa isan'andro amin'ny rafitra misy antsika mandritra ny 4-10 ora. Mamaha olana isan-karazany amin'ny drafitra, fampahafantarana, fampitandremana, fanamarinana... Mampiasa PostgreSQL 9.6 izahay. Manana tabilao 300 eo ho eo ao amin'ny angon-drakitra izahay ary hatramin'ny 200 tapitrisa fanontaniana (10 arivo isan-karazany) voaray isan'andro. Amin'ny ankapobeny dia manana fangatahana 3-4 arivo isan-tsegondra izahay, amin'ny fotoana mavitrika indrindra dia mihoatra ny fangatahana 10 arivo isan-tsegondra. Ny ankamaroan'ny fanontaniana dia OLAP. Vitsy kokoa ny fanampiana, fanovana ary famafana, midika izany fa maivana ny entana OLTP. Nomeko ireo isa rehetra ireo mba hahafahanao manombana ny haavon'ny tetikasanay sy hahatakatra ny maha-zava-dehibe ny traikefanay ho anao.
Sary voalohany. tonon-
Rehefa nanomboka ny fampandrosoana izahay, dia tsy nieritreritra ny karazana enta-mavesatra hianjera amin'ny angon-drakitra ary inona no hataonay raha tsy misintona ny mpizara. Rehefa namolavola ny angon-drakitra dia nanaraka ny tolo-kevitra ankapobeny izahay ary niezaka ny tsy hitifitra ny tenanay, fa nihoatra ny toro-hevitra ankapobeny toy ny hoe "aza mampiasa ny lamina." tsy niditra izahay. Namboarinay mifototra amin'ny fitsipiky ny normalization, misoroka ny tsy fahampian'ny data ary tsy miraharaha ny fanafainganana ny fanontaniana sasany. Vantany vao tonga ireo mpampiasa voalohany dia nisedra olana momba ny fampisehoana izahay. Toy ny mahazatra, tsy niomana tanteraka taminβizany izahay. Ny olana voalohany dia nivadika ho tsotra. Amin'ny maha-fitsipika, ny zava-drehetra dia voavaha tamin'ny alalan'ny fampidirana index vaovao. Saingy tonga ny fotoana izay nijanonan'ny patch tsotra. Noho ny fahatsapanay fa tsy ampy traikefa izahay ary mihasarotra ho anay ny mahatakatra ny antony mahatonga ny olana, dia nanakarama manam-pahaizana manokana izahay izay nanampy anay hametraka ny lohamilina araka ny tokony ho izy, mampifandray ny fanaraha-maso, ary naneho anay ny toerana tokony halehanay. .
Sary roa. Antontan'isa
Noho izany dia manana fanontaniana 10 arivo eo ho eo izahay izay tanterahina amin'ny angon-drakitray isan'andro. Amin'ireo 10 arivo ireo dia misy monsters izay novonoina in-2-3 tapitrisa miaraka amin'ny salan'isan'ny fotoana famonoana 0.1-0.3 ms, ary misy fanontaniana miaraka amin'ny salan'isa 30 segondra izay antsoina in-100 isan'andro.
Tsy azo natao ny nanatsara ny fanontaniana 10 arivo rehetra, ka nanapa-kevitra izahay ny hamantatra hoe aiza no hitarihana ny ezaka ataonay mba hanatsarana ny fahombiazan'ny angon-drakitra. Taorian'ny famerimberenana maromaro, dia nanomboka nizara ny fangatahana ho karazany izahay.
TOP fangatahana
Ireo no fanontaniana mavesatra indrindra izay maka fotoana be indrindra (fotoana manontolo). Ireo dia fanontaniana izay antsoina matetika matetika na fanontaniana mila fotoana ela hanaovana izany (fanontaniana lava sy matetika no nohatsaraina tamin'ny famerenana voalohany ny ady ho an'ny hafainganam-pandeha). Vokatr'izany dia mandany fotoana betsaka indrindra amin'ny famonoana azy ny mpizara. Ankoatr'izay, zava-dehibe ny manasaraka ny fangatahana ambony amin'ny fotoanan'ny fanatanterahana tanteraka ary misaraka amin'ny fotoana IO. Hafa kely ny fomba fanatsarana ny fanontaniana toy izany.
Ny fanaon'ny orinasa rehetra dia ny miasa amin'ny fangatahana TOP. Vitsy izy ireo; Ny fanatsarana na dia ny fangatahana iray aza dia afaka manafaka 5-10% amin'ny loharano. Na izany aza, rehefa mihamatotra ny tetikasa, dia lasa asa tsy misy dikany ny fanatsarana ny fanontaniana TOP. Ny fomba tsotra rehetra dia efa niasa, ary ny fangatahana "mavesatra" indrindra dia maka "ihany" 3-5% amin'ny loharano. Raha toa ka maka latsaky ny 30-40% amin'ny fitambarany ny fangatahana TOP, dia azo inoana fa efa nanao ezaka ianao mba hampahomby azy ireo ary tonga ny fotoana hirosoana amin'ny fanatsarana ny fanontaniana avy amin'ny vondrona manaraka.
Mbola mitoetra ny mamaly ny fanontaniana hoe firy ny fanontaniana ambony tokony hampidirina ao amin'ity vondrona ity. Matetika aho no maka farafahakeliny 10, fa tsy mihoatra ny 20. Miezaka ny miantoka aho fa ny fotoana voalohany sy farany ao amin'ny vondrona TOP dia tsy mitovy amin'ny in-10. Izany hoe, raha mihena be ny fotoana fanatanterahana ny fangatahana avy amin'ny toerana voalohany ka hatramin'ny faha-1, dia maka TOP-10 aho, raha miandalana kokoa ny fidina, dia ampitomboiko ho 10 na 15 ny haben'ny vondrona.
Tantsaha afovoany
Ireo rehetra ireo dia fangatahana tonga avy hatrany aorian'ny TOP, ankoatra ny 5-10% farany. Matetika, amin'ny fanatsarana ireo fanontaniana ireo dia misy ny fahafahana mampitombo be ny fahombiazan'ny mpizara. Ireo fangatahana ireo dia mety milanja hatramin'ny 80%. Saingy na dia mihoatra ny 50% aza ny anjarany, dia izao no fotoana hijerena azy ireo tsara kokoa.
Rambo
Araka ny voalaza, ireo fanontaniana ireo dia tonga amin'ny farany ary maka 5-10% amin'ny fotoana. Afaka manadino azy ireo ianao raha tsy mampiasa fitaovana famakafakana fanontaniana mandeha ho azy, dia mety ho mora ihany koa ny fanatsarana azy ireo.
Ahoana ny fanombanana ny vondrona tsirairay?
Mampiasa fanontaniana SQL aho izay manampy amin'ny fanaovana tombana toy izany ho an'ny PostgreSQL (Azoko antoka fa azo soratana amin'ny DBMS maro hafa ny fangatahana mitovy amin'izany)
Fanontaniana SQL hanombanana ny haben'ny vondrona 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
Ny valin'ny fanontaniana dia tsanganana telo, izay misy ny isan-jaton'ny fotoana ilaina amin'ny fikarakarana fanontaniana avy amin'ity vondrona ity. Ao anatin'ilay fangatahana dia misy isa roa (raha ny ahy dia 20 sy 800) izay manasaraka ny fangatahana amin'ny vondrona iray amin'ny iray hafa.
Toy izao ny fampitahana ny ampahany amin'ny fangatahana tamin'ny fotoana nanombohan'ny asa fanatsarana sy ankehitriny.
Ny kisary dia mampiseho fa ny ampahany amin'ny fangatahana TOP dia nihena be, saingy nitombo ny "mpamboly afovoany".
Tamin'ny voalohany, ny fangatahana TOP dia nahitana fahadisoana mibaribary. Rehefa nandeha ny fotoana, dia nanjavona ny aretina fahazazana, nihena ny anjaran'ny fangatahana TOP, ary tsy maintsy nisy ezaka bebe kokoa mba hanafainganana ny fangatahana sarotra.
Mba hahazoana ny lahatsoratry ny fangatahana dia mampiasa ity fangatahana manaraka ity izahay
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
Ity misy lisitry ny teknika fampiasa matetika indrindra nanampy anay hanafaingana ny fanontaniana TOP:
- Famolavolana indray ny rafitra, ohatra, mamerina ny lojika fampahafantarana amin'ny alΓ lan'ny mpanelanelana hafatra fa tsy fanontaniana miverimberina amin'ny angon-drakitra
- Manampy na manova fanondroana
- Manoratra indray ny fanontaniana ORM amin'ny SQL madio
- Famerenana ny lojika fametahana data malaina
- Caching amin'ny alΓ lan'ny denormalization data. Ohatra, manana fifandraisana latabatra Delivery -> Invoice -> Request -> Application. Izany hoe, ny fanaterana tsirairay dia mifandray amin'ny fampiharana amin'ny alΓ lan'ny tabilao hafa. Mba tsy hampifandray ny latabatra rehetra amin'ny fangatahana tsirairay, dia naverinay duplicate ny rohy mankany amin'ny fangatahana ao amin'ny latabatra fanaterana.
- Fametahana latabatra static miaraka amin'ny boky fanondroana ary zara raha manova latabatra ao amin'ny fitadidian'ny programa.
Indraindray ny fanovana dia nisy fanavaozana mahavariana, saingy nanome 5-10% ny enta-mavesatry ny rafitra izy ireo ary nohamarinina. Rehefa nandeha ny fotoana, dia nihena sy nihena ny setroka, ary ilaina ny fanavaozana bebe kokoa.
Avy eo dia nitodika tany amin'ny vondrona faharoa fangatahana izahay - ny vondron'ny tantsaha antonony. Misy fanontaniana maro hafa ao ary toa mila fotoana be ny famakafakana ny vondrona manontolo. Na izany aza, ny ankamaroan'ny fanontaniana dia nivadika ho tena tsotra ny fanatsarana, ary olana maro no naverina im-polo amin'ny fiovaovana samihafa. Ireto misy ohatra amin'ny fanatsarana mahazatra sasany izay nampiharinay tamin'ny fanontaniana am-polony mitovy ary ny vondrona fanontaniana nohatsaraina tsirairay dia namoaka ny angon-drakitra tamin'ny 3-5%.
- Raha tokony hanamarina ny fisian'ny firaketana amin'ny alΓ lan'ny COUNT sy ny scan table feno, dia nanomboka nampiasaina ny EXISTS
- Esory ny DISTINCT (tsy misy fomba fanamboarana ankapobeny, fa indraindray dia azonao atao ny manala azy mora foana amin'ny fanafainganana ny fangatahana in-10-100).
Ohatra, ho solon'ny fangatahana hifidianana ny mpamily rehetra avy amin'ny latabatra lehibe misy fanaterana (FANATERANA)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDnanao fanontaniana teo amin'ny PERSON latabatra somary kely
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Hoatran'ny hoe nampiasa subquery mifamatotra izahay, saingy manome hafainganam-pandeha mihoatra ny in-10 izany.
- Amin'ny tranga maro, COUNT dia nilaozana tanteraka ary
- raha tokony
UPPER(s) LIKE JOHN%βfampiasana
s ILIKE βJohn%β
Ny fangatahana manokana tsirairay dia nohafainganina in-3-1000. Na dia teo aza ny zava-bita mahavariana, tamin'ny voalohany dia toa tsy nisy dikany ny fanatsarana ny fangatahana mila 10 ms vao vita, dia iray amin'ireo fanontaniana mavesatra faha-3 zato indrindra, ary mandany am-jatony isan-jaton'ny fotoana famenoana ny angona manontolo. Saingy tamin'ny fampiharana ny fomba fanamboarana mitovy amin'ny vondrona fanontaniana mitovy karazana, dia nahazo indray isan-jato izahay. Mba tsy handany fotoana hamerenana amin'ny tanana ny fanontaniana an-jatony rehetra, dia nanoratra sora-baventy tsotra maromaro izay nampiasa fitenenana mahazatra hitadiavana fanontaniana mitovy karazana. Vokatr'izany, ny fikarohana ho azy ireo vondrona fanontaniana dia namela anay hanatsara bebe kokoa ny zava-bitanay tamin'ny ezaka kely.
Vokatr'izany dia efa niasa tamin'ny fitaovana mitovy izahay nandritra ny telo taona izao. Ny enta-mavesatra isan'andro dia manodidina ny 30%, amin'ny tampony dia mahatratra 70%. Nitombo avo in-10 eo ho eo ny isan'ny fangatahana, ary koa ny isan'ny mpampiasa. Ary izany rehetra izany dia noho ny fanaraha-maso tsy tapaka ireo vondrona fangatahana TOP-MEDIUM ireo. Raha vao misy fangatahana vaovao miseho ao amin'ny vondrona TOP dia mamakafaka izany avy hatrany izahay ary manandrana manafaingana azy. Mamerina indray mandeha isan-kerinandro ny vondrona MEDIUM amin'ny fampiasana sora-baventy famakafakana fanontaniana. Raha sendra misy fanontaniana vaovao efa hain'ny optimizer izahay dia ovainay haingana izy ireo. Indraindray izahay dia mahita fomba fanatsarana vaovao azo ampiharina amin'ny fanontaniana maromaro indray mandeha.
Araka ny vinavinay, ny mpizara amin'izao fotoana izao dia hahatohitra ny fitomboan'ny isan'ny mpampiasa in-3-5. Marina fa manana ace iray hafa izahay - mbola tsy nafindranay tamin'ny fitaratra ny fanontaniana SELECT, araka ny soso-kevitra. Saingy tsy manao izany amin'ny saina izahay, satria te-hanafoana tanteraka ny fahafahan'ny fanatsarana "smart" aloha izahay alohan'ny hanodina ny "tafika mavesatra".
Ny fijerena mitsikera ny asa vita dia mety hanoro hevitra ny fampiasana scaling mitsangana. Mividiana mpizara matanjaka kokoa fa tsy mandany fotoana amin'ny manam-pahaizana manokana. Mety tsy lafo be ny mpizara, indrindra fa tsy mbola lany ny fetran'ny scaling mitsangana. Na izany aza, ny isan'ny fangatahana ihany no nitombo in-10. Nandritra ny taona maromaro, nitombo ny fiasan'ny rafitra ary ankehitriny dia misy karazana fangatahana maro kokoa. Noho ny caching, ny fampiasa efa nisy dia vita amin'ny fangatahana vitsy kokoa, ary fangatahana mahomby kokoa. Midika izany fa afaka mihamaro soa aman-tsara amin'ny 5 hafa ianao mba hahazoana ny coefficient acceleration tena izy. Noho izany, araka ny tombantomban'ny mpandala ny nentin-drazana indrindra, dia afaka milaza isika fa ny hafainganam-pandeha dia in-50 na mihoatra. Mitentina avo 50 heny ny sandan'ny mpizara mitsangana. Indrindra fa rehefa vita ny fanatsarana dia miasa foana, ary ny volavolan-dalΓ na ho an'ny mpizara hofaina dia tonga isam-bolana.
Source: www.habr.com