Kodi mungakulire bwanji 10 kuchuluka kwa mafunso ku database osasunthira ku seva yopindulitsa kwambiri ndikusunga magwiridwe antchito? Ndikuuzani momwe tinachitira ndi kuchepa kwa kagwiritsidwe ntchito ka database yathu, momwe tidakonzera mafunso a SQL kuti tigwiritse ntchito ogwiritsa ntchito ambiri momwe tingathere komanso osawonjezera mtengo wazinthu zamakompyuta.
Ndimapanga ntchito yoyang'anira mabizinesi m'makampani omanga. Pafupifupi makampani 3 zikwizikwi amagwira nafe ntchito. Anthu opitilira 10 sauzande amagwira ntchito ndi dongosolo lathu tsiku lililonse kwa maola 4-10. Imathetsa mavuto osiyanasiyana akukonzekera, zidziwitso, chenjezo, kutsimikizira ... Timagwiritsa ntchito PostgreSQL 9.6. Tili ndi magome pafupifupi 300 m'nkhokwe ndipo mafunso ofikira 200 miliyoni (10 zikwi zosiyanasiyana) amalandiridwa tsiku lililonse. Pafupifupi tili ndi zopempha 3-4 zikwi pa sekondi iliyonse, panthawi yogwira ntchito kwambiri kuposa zopempha 10 zikwi pamphindi. Ambiri mwa mafunso ndi OLAP. Pali zowonjezera zochepa, zosinthidwa ndi zochotsa, kutanthauza kuti katundu wa OLTP ndiwopepuka. Ndapereka manambala onsewa kuti muwone kukula kwa polojekiti yathu ndikumvetsetsa momwe zomwe takumana nazo zingakuthandizireni.
Chithunzi choyamba. Nyimbo zanyimbo
Pamene tinayamba chitukuko, sitinaganizire kwenikweni za mtundu wa katundu umene ungagwere pa database ndi zomwe tingachite ngati seva itasiya kukoka. Popanga nkhokwe, tidatsatira malingaliro anthawi zonse ndikuyesera kuti tisadziwombere pamapazi, koma zidapitilira upangiri wamba ngati "musagwiritse ntchito sitinalowemo. Tidapanga motengera mfundo za normalization, kupewa kufutukuka kwa data ndipo sitinasamale kufulumizitsa mafunso ena. Ogwiritsa ntchito oyamba atangofika, tidakumana ndi vuto la magwiridwe antchito. Monga mwa nthawi zonse, tinali osakonzekera konse. Mavuto oyamba adakhala osavuta. Monga lamulo, zonse zidathetsedwa powonjezera index yatsopano. Koma inafika nthawi imene zigamba zosavuta zinasiya kugwira ntchito. Pozindikira kuti tilibe chidziwitso ndipo zikuchulukirachulukira kuti timvetsetse chomwe chikuyambitsa mavuto, tidalemba akatswiri omwe adatithandiza kukhazikitsa seva molondola, kulumikiza kuwunika, ndikutiwonetsa komwe tingapeze. .
Chithunzi chachiwiri. Zowerengera
Chifukwa chake tili ndi mafunso opitilira 10 omwe amaperekedwa patsamba lathu patsiku. Mwa 10 zikwizikwi, pali zilombo zomwe zimaphedwa nthawi 2-3 miliyoni ndi nthawi yapakati ya 0.1-0.3 ms, ndipo pali mafunso omwe amakhala ndi nthawi yapakati ya masekondi 30 omwe amatchedwa nthawi 100 patsiku.
Sizinali zotheka kukhathamiritsa mafunso onse 10, chifukwa chake tidaganiza zopeza komwe tingatsogolere zoyesayesa zathu kuti tikwaniritse bwino ntchito ya database. Titabwereza kangapo, tinayamba kugawa zopempha m'njira zosiyanasiyana.
Zopempha zapamwamba
Awa ndi mafunso olemera kwambiri omwe amatenga nthawi yambiri (nthawi yonse). Awa ndi mafunso omwe amatchedwa nthawi zambiri kapena mafunso omwe amatenga nthawi yayitali kuti achite (mafunso ataliatali komanso pafupipafupi adakonzedwa pakubwereza koyambirira komenyera liwiro). Zotsatira zake, seva imathera nthawi yambiri pakuchita kwawo. Kuphatikiza apo, ndikofunikira kulekanitsa zopempha zapamwamba ndi nthawi yonse yophatikizika komanso mosiyana ndi nthawi ya IO. Njira zokwaniritsira mafunso otere ndizosiyana pang'ono.
Mchitidwe wanthawi zonse wamakampani onse ndikugwira ntchito ndi zopempha za TOP. Pali ochepa aiwo; kukhathamiritsa ngakhale funso limodzi kumatha kumasula 5-10% yazinthu. Komabe, pulojekitiyo ikakhwima, kukhathamiritsa mafunso a TOP kumakhala ntchito yovuta kwambiri. Njira zonse zosavuta zapangidwa kale, ndipo pempho "lolemera" kwambiri limatenga "3-5% yokha" yazinthu. Ngati mafunso a TOP onse amatenga zosakwana 30-40% ya nthawiyo, ndiye kuti mwayesetsa kale kuti agwire ntchito mwachangu ndipo ndi nthawi yoti mupitilize kuwongolera mafunso kuchokera kugulu lotsatira.
Yatsala kuyankha funso la mafunso angati apamwamba omwe akuyenera kuphatikizidwa mugululi. Nthawi zambiri ndimatenga 10, koma osapitirira 20. Ndimayesetsa kuonetsetsa kuti nthawi yoyamba ndi yomaliza mu gulu la TOP imasiyana ndi nthawi zosaposa 10. Ndiye kuti, ngati nthawi yofunsa mafunso itsika kwambiri kuchokera pa 1st kupita pa 10, ndiye ndimatenga TOP-10, ngati dontho likuyenda pang'onopang'ono, ndiye kuti ndikuwonjezera kukula kwa gulu mpaka 15 kapena 20.
Anthu apakati
Izi ndizo zopempha zomwe zimabwera mwamsanga pambuyo pa TOP, kupatulapo 5-10% yomaliza. Nthawi zambiri, pakuwongolera mafunsowa mumakhala mwayi wowonjezera kwambiri magwiridwe antchito a seva. Zopempha izi zimatha kulemera mpaka 80%. Koma ngakhale gawo lawo litadutsa 50%, ndiye nthawi yoti muwayang'ane mosamala.
Mchira
Monga tafotokozera, mafunsowa amabwera kumapeto ndipo amatenga 5-10% ya nthawiyo. Mutha kuyiwala za iwo pokhapokha ngati simugwiritsa ntchito zida zowunikira mafunso, ndiye kuti kuwongolera kungakhalenso kotsika mtengo.
Momwe mungawunikire gulu lililonse?
Ndimagwiritsa ntchito funso la SQL lomwe limathandiza kuwunika kwa PostgreSQL (ndikutsimikiza kuti funso lofananalo likhoza kulembedwa kwa ma DBMS ena ambiri)
SQL funso kuti muyerekeze kukula kwa magulu a 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
Zotsatira za funsoli ndi mizati itatu, iliyonse ili ndi kuchuluka kwa nthawi yomwe zimafunika kuti tiyankhe mafunso agululi. Mkati mwa pempholi muli manambala awiri (kwa ine ndi 20 ndi 800) omwe amalekanitsa zopempha kuchokera ku gulu lina kuchokera ku lina.
Umu ndi momwe magawo amafunsira amafananizira nthawi yomwe ntchito yokhathamiritsa idayamba komanso pano.
Chithunzichi chikuwonetsa kuti gawo la zopempha za TOP latsika kwambiri, koma "anthu apakati" awonjezeka.
Poyamba, zopempha za TOP zidaphatikizapo zolakwika zowonekera. M'kupita kwa nthawi, matenda a ubwana adatha, gawo la zopempha za TOP linachepa, ndipo kuyesetsa kwakukulu kunayenera kuchitidwa kuti afulumizitse zopempha zovuta.
Kuti tipeze malemba a zopempha timagwiritsa ntchito zotsatirazi
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
Nawu mndandanda wanjira zomwe zimagwiritsidwa ntchito kwambiri zomwe zidatithandizira kufulumizitsa mafunso a TOP:
- Kukonzanso dongosolo, mwachitsanzo, kukonzanso malingaliro a zidziwitso pogwiritsa ntchito meseji broker m'malo mofunsa nthawi ndi nthawi ku database.
- Kuwonjezera kapena kusintha indexes
- Kulembanso mafunso a ORM ku SQL yoyera
- Kulembanso mfundo zaulesi zotsegula
- Kusunga kudzera mu data denormalization. Mwachitsanzo, tili ndi kugwirizana kwa tebulo Kutumiza -> Invoice -> Pempho -> Ntchito. Ndiye kuti, kutumiza kulikonse kumalumikizidwa ndi pulogalamu kudzera pamagome ena. Kuti tisalumikizane ndi matebulo onse pa pempho lililonse, tidabwereza ulalo wa pempholo pa Delivery table.
- Kusunga matebulo osasunthika okhala ndi mabuku ofotokozera komanso osasintha ma tebulo pamakumbukiro a pulogalamu.
Nthawi zina zosinthazo zidakhala kukonzanso kochititsa chidwi, koma zidapereka 5-10% yazinthu zamakina ndipo zidali zomveka. M’kupita kwa nthaŵi, utsiwo unakhala wawung’ono ndi wocheperako, ndipo kukonzanso kwakukulu kunafunikira.
Kenako tinatembenukira ku gulu lachiwiri la zopempha - gulu la alimi apakati. Pali mafunso ena ambiri momwemo ndipo zikuwoneka kuti zingatenge nthawi yochuluka kuti tiwunike gulu lonselo. Komabe, mafunso ambiri adakhala osavuta kukhathamiritsa, ndipo mavuto ambiri amabwerezedwa kangapo mosiyanasiyana. Nazi zitsanzo za kukhathamiritsa komwe tidagwiritsa ntchito kumafunso ambiri ofanana ndipo gulu lililonse lamafunso okometsedwa lidatsitsa nkhokwe ndi 3-5%.
- M'malo moyang'ana kukhalapo kwa zolemba pogwiritsa ntchito COUNT ndi kusanthula tebulo lonse, EXISTS inayamba kugwiritsidwa ntchito
- Chotsani DISTINCT (palibe njira yanthawi zonse, koma nthawi zina mutha kuyichotsa mosavuta ndikufulumizitsa pempholi nthawi 10-100).
Mwachitsanzo, m'malo mwa funso loti musankhe madalaivala onse patebulo lalikulu la zotumizira (DELIVERY)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDadafunsa patebulo laling'ono 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)Zikuwoneka kuti tidagwiritsa ntchito gawo lolumikizana, koma limapereka liwiro lopitilira nthawi 10.
- Nthawi zambiri, COUNT idasiyidwa palimodzi
- mmalo mwa
UPPER(s) LIKE JOHN%’gwiritsani ntchito
s ILIKE “John%”
Pempho lililonse lapadera nthawi zina lidafulumizitsidwa ndi nthawi 3-1000. Ngakhale ntchito yochititsa chidwiyi, poyamba zinkawoneka kwa ife kuti palibe chifukwa chokonzekera funso lomwe limatenga 10 ms kuti limalize, ndilo limodzi mwa mafunso olemera kwambiri a 3rd, ndipo limatenga zana limodzi mwa magawo zana a nthawi yonse yosungiramo deta. Koma pogwiritsa ntchito njira yomweyi ku gulu la mafunso amtundu womwewo, tapindula pang'ono peresenti. Kuti tisataye nthawi ndikuwunikanso mazana a mafunso onse, tidalemba zolemba zosavuta zingapo zomwe zimagwiritsa ntchito mawu okhazikika kuti tipeze mafunso amtundu womwewo. Zotsatira zake, kufufuza magulu a mafunso kunatithandiza kupititsa patsogolo kagwiridwe kathu ndi khama lochepa.
Zotsatira zake, takhala tikugwira ntchito pa hardware yomweyo kwa zaka zitatu tsopano. Pafupifupi tsiku lililonse katundu ndi pafupifupi 30%, pachimake amafika 70%. Chiwerengero cha zopempha, komanso chiwerengero cha ogwiritsa ntchito, chawonjezeka pafupifupi ka 10. Ndipo zonsezi zikomo chifukwa chowunika mosalekeza magulu omwewa a pempho la TOP-MEDIUM. Pempho latsopano likangowonekera mu gulu la TOP, timasanthula nthawi yomweyo ndikuyesera kufulumizitsa. Timawunikanso gulu la MEDIUM kamodzi pa sabata pogwiritsa ntchito zolemba zowunikira mafunso. Tikakumana ndi mafunso atsopano omwe tikudziwa kale kuwongolera, timawasintha mwachangu. Nthawi zina timapeza njira zatsopano zowonjezeretsa zomwe zitha kugwiritsidwa ntchito pamafunso angapo nthawi imodzi.
Malinga ndi zolosera zathu, seva yamakono idzapirira kuwonjezeka kwa chiwerengero cha ogwiritsa ntchito nthawi zina 3-5. Zowona, tili ndi imodzi yowonjezereka m'manja mwathu - sitinasamutsire mafunso OSAKHALA pagalasi, monga momwe tikulimbikitsidwa. Koma sitichita izi mwachidwi, chifukwa tikufuna kuti tiyambe kutaya mwayi wa kukhathamiritsa kwa "nzeru" tisanayatse "zojambula zolemera".
Kuyang'ana mozama pa ntchito yomwe yachitika kungafune kugwiritsa ntchito makulitsidwe oyima. Gulani seva yamphamvu kwambiri m'malo mowononga nthawi ya akatswiri. Seva ikhoza kukhala yopanda mtengo wotere, makamaka popeza sitinathebe malire okweza molunjika. Komabe, chiwerengero chokha cha zopempha chinawonjezeka ka 10. Pazaka zingapo, magwiridwe antchito a dongosololi awonjezeka ndipo tsopano pali mitundu yambiri ya zopempha. Chifukwa cha caching, magwiridwe antchito omwe analipo amachitidwa pazopempha zochepa, komanso zopempha zogwira mtima. Izi zikutanthauza kuti mutha kuchulukitsa mosatetezeka ndi 5 ina kuti mupeze mathamangitsidwe enieni. Choncho, malinga ndi kuyerekezera kwambiri ndiwofatsa, tinganene kuti mathamangitsidwe anali 50 nthawi kapena kuposa. Kusuntha molunjika seva kungawononge nthawi 50 kuposa. Makamaka poganizira kuti kukhathamiritsa kukachitika kumagwira ntchito nthawi zonse, ndipo bilu ya seva yobwereka imabwera mwezi uliwonse.
Source: www.habr.com