Bii o ṣe le dagba awọn akoko 10 nọmba awọn ibeere si ibi ipamọ data laisi gbigbe si olupin ti o ni iṣelọpọ diẹ sii ati ṣetọju iṣẹ ṣiṣe eto? Emi yoo sọ fun ọ bii a ṣe ṣe pẹlu idinku ninu iṣẹ ṣiṣe data data wa, bawo ni a ṣe ṣe iṣapeye awọn ibeere SQL lati ṣe iranṣẹ bi ọpọlọpọ awọn olumulo bi o ti ṣee ṣe kii ṣe alekun idiyele ti awọn orisun iširo.
Mo ṣe iṣẹ kan fun ṣiṣakoso awọn ilana iṣowo ni awọn ile-iṣẹ ikole. Nipa awọn ile-iṣẹ 3 ẹgbẹrun ṣiṣẹ pẹlu wa. Diẹ sii ju awọn eniyan 10 ẹgbẹrun ṣiṣẹ pẹlu eto wa lojoojumọ fun awọn wakati 4-10. O yanju awọn iṣoro oriṣiriṣi ti igbero, iwifunni, ikilọ, afọwọsi… A lo PostgreSQL 9.6. A ni nipa awọn tabili 300 ni ibi ipamọ data ati pe o to awọn ibeere 200 milionu (10 ẹgbẹrun oriṣiriṣi oriṣiriṣi) ni a gba ni gbogbo ọjọ. Ni apapọ a ni awọn ibeere 3-4 ẹgbẹrun fun iṣẹju-aaya, ni awọn akoko ti nṣiṣe lọwọ julọ ju awọn ibeere 10 ẹgbẹrun fun iṣẹju keji. Pupọ julọ awọn ibeere ni OLAP. Awọn afikun diẹ ni o wa, awọn iyipada ati awọn piparẹ, afipamo pe ẹru OLTP jẹ ina diẹ. Mo pese gbogbo awọn nọmba wọnyi ki o le ṣe ayẹwo iwọn ti iṣẹ akanṣe wa ki o loye bi iriri wa ṣe wulo fun ọ.
Aworan ọkan. Oriki
Nigba ti a bẹrẹ idagbasoke, a ko ronu nipa iru iru fifuye ti yoo ṣubu lori ibi ipamọ data ati ohun ti a yoo ṣe ti olupin naa ba dẹkun fifa. Nigbati o ba n ṣe apẹrẹ data, a tẹle awọn iṣeduro gbogbogbo ati gbiyanju lati ma ṣe iyaworan ara wa ni ẹsẹ, ṣugbọn o kọja imọran gbogbogbo bi “maṣe lo ilana naa a ko wọle. A ṣe apẹrẹ ti o da lori awọn ipilẹ ti isọdọtun, yago fun apọju data ati pe ko bikita nipa iyara awọn ibeere kan. Ni kete ti awọn olumulo akọkọ ti de, a pade iṣoro iṣẹ kan. Gẹgẹbi igbagbogbo, a ko mura patapata fun eyi. Awọn iṣoro akọkọ ti jade lati rọrun. Gẹgẹbi ofin, ohun gbogbo ti yanju nipasẹ fifi itọka tuntun kun. Ṣugbọn akoko kan wa nigbati awọn abulẹ ti o rọrun duro ṣiṣẹ. Ni mimọ pe a ko ni iriri ati pe o n nira pupọ fun wa lati loye ohun ti o nfa awọn iṣoro naa, a bẹwẹ awọn alamọja ti o ṣe iranlọwọ fun wa lati ṣeto olupin naa ni deede, so ibojuwo, ati ṣafihan ibiti a le wa lati gba. .
Aworan meji. Iṣiro
Nitorinaa a ni nipa awọn ibeere oriṣiriṣi 10 ẹgbẹrun ti a ṣe lori ibi ipamọ data wa fun ọjọ kan. Ninu awọn 10 ẹgbẹrun wọnyi, awọn ohun ibanilẹru titobi wa ti a ṣe ni awọn akoko 2-3 miliọnu pẹlu akoko ipaniyan apapọ ti 0.1-0.3 ms, ati pe awọn ibeere wa pẹlu akoko ipaniyan apapọ ti awọn aaya 30 ti a pe ni awọn akoko 100 ni ọjọ kan.
Ko ṣee ṣe lati mu gbogbo awọn ibeere 10 ẹgbẹrun lọ, nitorinaa a pinnu lati wa ibiti a yoo ṣe itọsọna awọn akitiyan wa lati le mu iṣẹ ṣiṣe ti data daradara dara. Lẹhin ọpọlọpọ awọn iterations, a bẹrẹ lati pin awọn ibeere si awọn oriṣi.
TOP ibeere
Iwọnyi jẹ awọn ibeere ti o wuwo julọ ti o gba akoko pupọ julọ (akoko lapapọ). Iwọnyi jẹ awọn ibeere ti a pe ni igbagbogbo tabi awọn ibeere ti o gba akoko pipẹ pupọ lati ṣiṣẹ (awọn ibeere gigun ati loorekoore ni iṣapeye ni awọn iterations akọkọ ti ija fun iyara). Bi abajade, olupin naa lo akoko pupọ julọ lori ipaniyan wọn. Pẹlupẹlu, o ṣe pataki lati ya awọn ibeere oke nipasẹ akoko ipaniyan lapapọ ati lọtọ nipasẹ akoko IO. Awọn ọna fun iṣapeye iru awọn ibeere jẹ iyatọ diẹ.
Iwa deede ti gbogbo awọn ile-iṣẹ ni lati ṣiṣẹ pẹlu awọn ibeere TOP. Diẹ ninu wọn wa; iṣapeye paapaa ibeere kan le ṣe ominira 5-10% ti awọn orisun. Bibẹẹkọ, bi iṣẹ akanṣe naa ti dagba, iṣapeye awọn ibeere TOP di iṣẹ-ṣiṣe ti kii ṣe pataki. Gbogbo awọn ọna ti o rọrun ti ṣiṣẹ tẹlẹ, ati pe ibeere “eru” julọ gba “nikan” 3-5% ti awọn orisun. Ti awọn ibeere TOP lapapọ gba kere ju 30-40% ti akoko naa, lẹhinna o ṣee ṣe pe o ti ṣe awọn igbiyanju tẹlẹ lati jẹ ki wọn ṣiṣẹ ni iyara ati pe o to akoko lati lọ siwaju si iṣapeye awọn ibeere lati ẹgbẹ atẹle.
O wa lati dahun ibeere melo ni awọn ibeere oke yẹ ki o wa ninu ẹgbẹ yii. Mo maa n gba o kere ju 10, ṣugbọn kii ṣe ju 20. Mo gbiyanju lati rii daju pe akoko ti akọkọ ati ti o kẹhin ni ẹgbẹ TOP yatọ nipasẹ ko ju awọn akoko 10 lọ. Iyẹn ni, ti akoko ipaniyan ibeere ba lọ silẹ ni kiakia lati aaye 1st si 10th, lẹhinna Mo mu TOP-10, ti isubu naa ba jẹ diẹ sii diẹ sii, lẹhinna Mo mu iwọn ẹgbẹ pọ si 15 tabi 20.
Awọn agbedemeji agbedemeji
Iwọnyi jẹ gbogbo awọn ibeere ti o wa lẹsẹkẹsẹ lẹhin TOP, pẹlu ayafi ti 5-10% to kẹhin. Nigbagbogbo, ni jijẹ awọn ibeere wọnyi wa ni aye lati mu iṣẹ ṣiṣe olupin pọ si. Awọn ibeere wọnyi le ṣe iwọn to 80%. Ṣugbọn paapaa ti ipin wọn ba ti kọja 50%, lẹhinna o to akoko lati wo wọn ni pẹkipẹki.
Ìrù
Gẹgẹbi a ti sọ, awọn ibeere wọnyi wa ni ipari ati gba 5-10% ti akoko naa. O le gbagbe nipa wọn nikan ti o ko ba lo awọn irinṣẹ itupalẹ ibeere adaṣe, lẹhinna iṣapeye wọn tun le jẹ olowo poku.
Bawo ni lati ṣe iṣiro ẹgbẹ kọọkan?
Mo lo ibeere SQL kan ti o ṣe iranlọwọ lati ṣe iru igbelewọn fun PostgreSQL (Mo da mi loju pe iru ibeere kan le kọ fun ọpọlọpọ awọn DBMS miiran)
Ibeere SQL lati ṣe iṣiro iwọn awọn ẹgbẹ 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
Abajade ibeere naa jẹ awọn ọwọn mẹta, ọkọọkan ninu eyiti o ni ipin ogorun akoko ti o gba lati ṣe ilana awọn ibeere lati ẹgbẹ yii. Ninu ibeere naa awọn nọmba meji wa (ninu ọran mi o jẹ 20 ati 800) ti o ya awọn ibeere lati ẹgbẹ kan si ekeji.
Eyi ni bii awọn ipin ti awọn ibeere ṣe afiwe ni aijọju ni akoko iṣapeye iṣẹ bẹrẹ ati ni bayi.
Aworan naa fihan pe ipin ti awọn ibeere TOP ti dinku pupọ, ṣugbọn “awọn alagbede aarin” ti pọ si.
Ni akọkọ, awọn ibeere TOP pẹlu awọn aibuku gbangba. Ni akoko pupọ, awọn arun ọmọde padanu, ipin ti awọn ibeere TOP dinku, ati siwaju ati siwaju sii awọn igbiyanju ni lati ṣe lati yara awọn ibeere ti o nira.
Lati gba ọrọ ti awọn ibeere a lo ibeere atẹle
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
Eyi ni atokọ ti awọn ilana ti o wọpọ julọ ti o ṣe iranlọwọ fun wa ni iyara awọn ibeere TOP:
- Atunse eto naa, fun apẹẹrẹ, tun ṣiṣẹ ọgbọn iwifunni nipa lilo alagbata ifiranṣẹ dipo awọn ibeere igbakọọkan si ibi ipamọ data
- Fifi tabi yiyipada awọn atọka
- Atunkọ awọn ibeere ORM si SQL mimọ
- Atunkọ ọlẹ data ikojọpọ kannaa
- Caching nipasẹ data deormalization. Fun apẹẹrẹ, a ni Ifijiṣẹ asopọ tabili -> Invoice -> Ibere -> Ohun elo. Iyẹn ni, ifijiṣẹ kọọkan ni nkan ṣe pẹlu ohun elo nipasẹ awọn tabili miiran. Ni ibere ki o má ba sopọ gbogbo awọn tabili ni ibeere kọọkan, a ṣe ẹda ọna asopọ si ibeere ni tabili Ifijiṣẹ.
- Caching aimi tabili pẹlu awọn iwe itọkasi ati ṣọwọn iyipada tabili ni iranti eto.
Nigba miiran awọn iyipada jẹ atunṣe ti o yanilenu, ṣugbọn wọn pese 5-10% ti fifuye eto ati pe o jẹ idalare. Ni akoko pupọ, eefi naa di kekere ati kere, ati siwaju ati siwaju sii pataki atunṣe ti a nilo.
Lẹhinna a yi ifojusi wa si ẹgbẹ keji ti awọn ibeere - ẹgbẹ ti awọn alarogbe aarin. Ọpọlọpọ awọn ibeere diẹ sii wa ninu rẹ ati pe o dabi pe yoo gba akoko pupọ lati ṣe itupalẹ gbogbo ẹgbẹ naa. Sibẹsibẹ, ọpọlọpọ awọn ibeere ti jade lati jẹ irọrun pupọ lati mu dara, ati pe ọpọlọpọ awọn iṣoro ni a tun ṣe ni ọpọlọpọ igba ni awọn iyatọ oriṣiriṣi. Eyi ni awọn apẹẹrẹ ti diẹ ninu awọn iṣapeye aṣoju ti a lo si awọn dosinni ti awọn ibeere ti o jọra ati ẹgbẹ kọọkan ti awọn ibeere iṣapeye ti gbe data data silẹ nipasẹ 3-5%.
- Dipo ti ṣayẹwo fun wiwa awọn igbasilẹ nipa lilo COUNT ati ọlọjẹ tabili ni kikun, EXISTS bẹrẹ lati ṣee lo
- Pa DISTINCT kuro (ko si ohunelo gbogbogbo, ṣugbọn nigbami o le ni rọọrun yọ kuro nipa titẹ ibeere naa ni awọn akoko 10-100).
Fun apẹẹrẹ, dipo ibeere lati yan gbogbo awọn awakọ lati inu tabili nla ti awọn ifijiṣẹ (Ifiranṣẹ)
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.IDṣe ibeere lori tabili kekere kan ENIYAN
SELECT P.ID, P.FIRST_NAME, P.LAST_NAME FROM PERSON WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)Yoo dabi pe a lo ipilẹ ti o ni ibatan, ṣugbọn o funni ni iyara diẹ sii ju awọn akoko 10 lọ.
- Ni ọpọlọpọ awọn ọran, COUNT ti kọ silẹ lapapọ ati
- dipo
UPPER(s) LIKE JOHN%’lilo
s ILIKE “John%”
Ibere kan pato kọọkan jẹ iyara nigbakan nipasẹ awọn akoko 3-1000. Laibikita iṣẹ iwunilori, ni akọkọ o dabi fun wa pe ko si aaye ni iṣapeye ibeere kan ti o gba 10 ms lati pari, jẹ ọkan ninu awọn ibeere 3rd ọgọrun ti o wuwo julọ, ati pe o gba awọn ọgọọgọrun ti ida kan ti akoko fifuye data gbogbogbo. Ṣugbọn nipa lilo ohunelo kanna si ẹgbẹ kan ti awọn ibeere ti iru kanna, a gba pada diẹ ninu ogorun. Ni ibere ki o ma ṣe padanu akoko pẹlu ọwọ atunwo gbogbo awọn ọgọọgọrun awọn ibeere, a kowe ọpọlọpọ awọn iwe afọwọkọ ti o rọrun ti o lo awọn ikosile deede lati wa awọn ibeere ti iru kanna. Bi abajade, awọn ẹgbẹ wiwa laifọwọyi ti awọn ibeere gba wa laaye lati ni ilọsiwaju siwaju si iṣẹ wa pẹlu igbiyanju iwọntunwọnsi.
Bi abajade, a ti n ṣiṣẹ lori ohun elo kanna fun ọdun mẹta ni bayi. Iwọn apapọ ojoojumọ jẹ nipa 30%, ni awọn oke giga o de 70%. Nọmba awọn ibeere, bakanna bi nọmba awọn olumulo, ti pọ si isunmọ awọn akoko 10. Ati pe gbogbo eyi ṣe ọpẹ si ibojuwo igbagbogbo ti awọn ẹgbẹ kanna ti awọn ibeere TOP-MEDIUM. Ni kete ti ibeere tuntun ba han ninu ẹgbẹ TOP, a ṣe itupalẹ lẹsẹkẹsẹ ati gbiyanju lati yara. A ṣe atunyẹwo ẹgbẹ MEDIUM lẹẹkan ni ọsẹ kan nipa lilo awọn iwe afọwọkọ itupalẹ ibeere. Ti a ba pade awọn ibeere tuntun ti a ti mọ tẹlẹ bi a ṣe le mu wọn pọ si, a yara yi wọn pada. Nigba miiran a wa awọn ọna iṣapeye tuntun ti o le lo si awọn ibeere pupọ ni ẹẹkan.
Gẹgẹbi awọn asọtẹlẹ wa, olupin lọwọlọwọ yoo koju ilosoke ninu nọmba awọn olumulo nipasẹ awọn akoko 3-5 miiran. Lootọ, a ni ace kan diẹ si apa ọwọ wa - a ko tun gbe awọn ibeere Yan si digi, bi a ti ṣeduro. Ṣugbọn a ko ṣe eyi ni mimọ, nitori a fẹ lati kọkọ pari patapata awọn iṣeeṣe ti iṣapeye “ọlọgbọn” ṣaaju ki o to tan-an “awọn ohun ija nla”.
Wiwo to ṣe pataki ni iṣẹ ti o ṣe le daba ni lilo igbewọn inaro. Ra olupin ti o lagbara diẹ sii dipo jafara akoko ti awọn alamọja. Olupin naa le ma jẹ iye owo yẹn, paapaa niwọn igba ti a ko tii ti pari awọn opin ti iwọn inaro. Sibẹsibẹ, nikan awọn nọmba ti awọn ibeere pọ 10 igba. Ni akoko ti awọn ọdun pupọ, iṣẹ ṣiṣe ti eto naa ti pọ si ati bayi awọn oriṣi awọn ibeere diẹ sii wa. Ṣeun si caching, iṣẹ ṣiṣe ti o wa ni a ṣe ni awọn ibeere diẹ, ati awọn ibeere ti o munadoko diẹ sii. Eyi tumọ si pe o le ni isodipupo lailewu nipasẹ 5 miiran lati gba olùsọdipúpọ isare gidi. Nitorinaa, ni ibamu si awọn iṣiro Konsafetifu julọ, a le sọ pe isare naa jẹ awọn akoko 50 tabi diẹ sii. Lilọ kiri ni inaro olupin yoo jẹ iye igba 50 diẹ sii. Paapa ni akiyesi pe ni kete ti iṣapeye ti gbe jade o ṣiṣẹ ni gbogbo igba, ati pe owo fun olupin iyalo wa ni gbogbo oṣu.
orisun: www.habr.com