වඩා ඵලදායී සේවාදායකයක් වෙත නොගොස් දත්ත සමුදාය වෙත විමසුම් ගණන මෙන් 10 ගුණයක් වර්ධනය කර පද්ධති ක්රියාකාරිත්වය පවත්වා ගන්නේ කෙසේද? අපගේ දත්ත සමුදායේ ක්රියාකාරීත්වය අඩුවීම සම්බන්ධයෙන් අප කටයුතු කළ ආකාරය, හැකිතාක් පරිශීලකයින්ට සේවය කිරීමට සහ පරිගණක සම්පත්වල පිරිවැය වැඩි නොකිරීමට SQL විමසුම් ප්රශස්ත කළ ආකාරය මම ඔබට කියමි.
ඉදිකිරීම් සමාගම්වල ව්යාපාර ක්රියාවලි කළමනාකරණය සඳහා මම සේවාවක් කරන්නෙමි. සමාගම් තුන්දහසක් පමණ අප සමඟ වැඩ කරයි. 3 කට වැඩි පිරිසක් සෑම දිනකම පැය 10-4 ක් අපගේ පද්ධතිය සමඟ වැඩ කරති. එය සැලසුම් කිරීම, දැනුම්දීම, අනතුරු ඇඟවීම, වලංගු කිරීම වැනි විවිධ ගැටළු විසඳයි... අපි PostgreSQL 10 භාවිතා කරමු. අපට දත්ත ගබඩාවේ වගු 9.6 ක් පමණ ඇති අතර දිනකට විමසුම් මිලියන 300 ක් (විවිධ ඒවා 200 දහසක්) ලැබේ. සාමාන්යයෙන් අපට තත්පරයකට ඉල්ලීම් 10-3 දහසක් ඇත, වඩාත් ක්රියාකාරී අවස්ථාවන්හිදී තත්පරයකට ඉල්ලීම් 4 දහසකට වඩා. බොහෝ විමසුම් OLAP වේ. බොහෝ අඩු එකතු කිරීම්, වෙනස් කිරීම් සහ මකාදැමීම් ඇත, එනම් OLTP භාරය සාපේක්ෂව සැහැල්ලු ය. ඔබට අපගේ ව්යාපෘතියේ පරිමාණය තක්සේරු කිරීමට සහ අපගේ අත්දැකීම ඔබට කෙතරම් ප්රයෝජනවත් විය හැකි දැයි තේරුම් ගැනීමට හැකි වන පරිදි මම මෙම සියලු අංක ලබා දුන්නෙමි.
පින්තුර එක. ගීතමය
අපි සංවර්ධනය ආරම්භ කරන විට, දත්ත සමුදාය මත කුමන ආකාරයේ බරක් වැටෙනු ඇත්ද සහ සේවාදායකය ඇදීම නැවැත්වූ විට අප කරන්නේ කුමක්ද යන්න ගැන අපි සැබවින්ම සිතුවේ නැත. දත්ත සමුදාය සැලසුම් කිරීමේදී, අපි සාමාන්ය නිර්දේශ අනුගමනය කළ අතර පාදයට වෙඩි නොතැබීමට උත්සාහ කළෙමු, නමුත් “රටාව භාවිතා නොකරන්න” වැනි සාමාන්ය උපදෙස් ඉක්මවා ගියෙමු.
පින්තූරය දෙක. සංඛ්යානමය
එබැවින් අපගේ දත්ත ගබඩාවේ දිනකට විවිධ විමසුම් 10 ක් පමණ ක්රියාත්මක වේ. මෙම 10 න්, සාමාන්ය ක්රියාත්මක කිරීමේ කාලය 2-3 ms සමඟ මිලියන 0.1-0.3 වාරයක් ක්රියාත්මක කරන රාක්ෂයන් සිටින අතර, දිනකට 30 වතාවක් හැඳින්වෙන තත්පර 100 ක සාමාන්ය ක්රියාත්මක කිරීමේ කාලය සහිත විමසුම් තිබේ.
සියලුම විමසුම් 10 ප්රශස්ත කිරීමට නොහැකි විය, එබැවින් දත්ත සමුදායේ ක්රියාකාරිත්වය නිවැරදිව වැඩිදියුණු කිරීම සඳහා අපගේ උත්සාහයන් යොමු කළ යුත්තේ කොතැනටදැයි සොයා ගැනීමට අපි තීරණය කළෙමු. පුනරාවර්තන කිහිපයකට පසු, අපි ඉල්ලීම් වර්ග වලට බෙදීමට පටන් ගත්තෙමු.
ඉහළම ඉල්ලීම්
මේවා වැඩිම කාලයක් ගතවන (මුළු කාලය) බරම විමසුම් වේ. මේවා බොහෝ විට හැඳින්වෙන විමසුම් හෝ ක්රියාත්මක කිරීමට ඉතා දිගු කාලයක් ගත වන විමසුම් වේ (වේගය සඳහා වන සටනේ පළමු පුනරාවර්තන වලදී දිගු සහ නිතර විමසුම් ප්රශස්ත කර ඇත). එහි ප්රතිඵලයක් වශයෙන්, සේවාදායකය ඔවුන්ගේ ක්රියාත්මක කිරීම සඳහා වැඩිම කාලයක් ගත කරයි. එපමනක් නොව, ඉහල ඉල්ලීම් සම්පූර්ණ ක්රියාත්මක කිරීමේ කාලය සහ IO වේලාවෙන් වෙන වෙනම වෙන් කිරීම වැදගත් වේ. එවැනි විමසුම් ප්රශස්ත කිරීම සඳහා ක්රම තරමක් වෙනස් වේ.
සියලුම සමාගම්වල සාමාන්ය පුරුද්ද වන්නේ TOP ඉල්ලීම් සමඟ වැඩ කිරීමයි. ඒවායින් කිහිපයක් තිබේ; එක් විමසුමක් පවා ප්රශස්ත කිරීමෙන් සම්පත් වලින් 5-10% ක් නිදහස් කළ හැකිය. කෙසේ වෙතත්, ව්යාපෘතිය පරිණත වන විට, TOP විමසුම් ප්රශස්ත කිරීම වඩ වඩාත් සුළු නොවන කාර්යයක් බවට පත්වේ. සියලුම සරල ක්රම දැනටමත් සකස් කර ඇති අතර, වඩාත්ම "බර" ඉල්ලීම සඳහා සම්පත් වලින් 3-5% "පමණක්" ගත වේ. TOP විමසුම් සඳහා ගතවන කාලය 30-40% ට වඩා අඩු නම්, බොහෝ විට ඔබ ඒවා ඉක්මනින් ක්රියාත්මක කිරීමට උත්සාහ කර ඇති අතර ඊළඟ කණ්ඩායමෙන් විමසුම් ප්රශස්ත කිරීම වෙත යාමට කාලයයි.
මෙම කණ්ඩායමට ඉහළ විමසුම් කීයක් ඇතුළත් කළ යුතුද යන ප්රශ්නයට පිළිතුරු දීමට එය ඉතිරිව ඇත. මම සාමාන්යයෙන් අවම වශයෙන් 10ක් ගන්නවා, නමුත් 20කට වඩා වැඩි නොවේ. TOP කාණ්ඩයේ පළමු සහ අවසාන කාලය 10 ගුණයකට වඩා වෙනස් නොවන බව සහතික කිරීමට මම උත්සාහ කරමි. එනම්, විමසුම් ක්රියාත්මක කිරීමේ කාලය 1 වන ස්ථානයේ සිට 10 වන ස්ථානය දක්වා තියුනු ලෙස පහත වැටේ නම්, මම TOP-10 ගන්නවා, පහත වැටීම වඩා ක්රමානුකූල නම්, මම කණ්ඩායම් ප්රමාණය 15 හෝ 20 දක්වා වැඩි කරමි.
මධ්යම ගොවීන්
මේ සියල්ල අවසන් 5-10% හැර, TOP ට පසු වහාම පැමිණෙන ඉල්ලීම් වේ. සාමාන්යයෙන්, මෙම විමසුම් ප්රශස්ත කිරීමේදී සේවාදායක කාර්ය සාධනය විශාල ලෙස වැඩි කිරීමට අවස්ථාව තිබේ. මෙම ඉල්ලීම් 80% දක්වා බර විය හැක. නමුත් ඔවුන්ගේ කොටස 50% ඉක්මවා ඇතත්, ඔවුන් වඩාත් ප්රවේශමෙන් බැලීමට කාලයයි.
වලිගය
සඳහන් කළ පරිදි, මෙම විමසුම් අවසානයේ පැමිණෙන අතර කාලයෙන් 5-10% ක් ගතවේ. ඔබට ඒවා අමතක කළ හැක්කේ ඔබ ස්වයංක්රීය විමසුම් විශ්ලේෂණ මෙවලම් භාවිතා නොකරන්නේ නම් පමණි, එවිට ඒවා ප්රශස්ත කිරීම ද ලාභදායී විය හැකිය.
එක් එක් කණ්ඩායම ඇගයීමට ලක් කරන්නේ කෙසේද?
මම PostgreSQL සඳහා එවැනි තක්සේරුවක් කිරීමට උපකාර වන SQL විමසුමක් භාවිතා කරමි (වෙනත් බොහෝ DBMS සඳහා සමාන විමසුමක් ලිවිය හැකි බව මට විශ්වාසයි)
TOP-MEDIUM-TAIL කණ්ඩායම්වල ප්රමාණය තක්සේරු කිරීමට SQL විමසුම
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
විමසුමේ ප්රතිඵලය තීරු තුනකි, ඒ සෑම එකකම මෙම සමූහයෙන් විමසුම් සැකසීමට ගතවන කාලය ප්රතිශතය අඩංගු වේ. ඉල්ලීම ඇතුළත එක් කණ්ඩායමකින් තවත් කණ්ඩායමකින් ඉල්ලීම් වෙන් කරන අංක දෙකක් (මගේ නඩුවේ එය 20 සහ 800 වේ) ඇත.
ප්රශස්තිකරණ කටයුතු ආරම්භ වූ අවස්ථාවේ සහ දැන් ඉල්ලීම්වල කොටස් දළ වශයෙන් සංසන්දනය කරන්නේ එලෙසයි.
රූප සටහනෙන් පෙන්නුම් කරන්නේ TOP ඉල්ලීම්වල කොටස තියුනු ලෙස අඩු වී ඇති නමුත් "මැද ගොවීන්" වැඩි වී ඇති බවයි.
මුලදී, TOP ඉල්ලීම්වලට අමූලික වැරදි ඇතුළත් විය. කාලයාගේ ඇවෑමෙන්, ළමා රෝග අතුරුදහන් විය, TOP ඉල්ලීම්වල කොටස අඩු විය, දුෂ්කර ඉල්ලීම් වේගවත් කිරීම සඳහා වැඩි වැඩියෙන් උත්සාහ කිරීමට සිදු විය.
ඉල්ලීම් පෙළ ලබා ගැනීමට අපි පහත ඉල්ලීම භාවිතා කරමු
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
TOP විමසුම් වේගවත් කිරීමට අපට උපකාර වූ බහුලව භාවිතා වන තාක්ෂණික ක්රම ලැයිස්තුවක් මෙන්න:
- පද්ධතිය ප්රතිනිර්මාණය කිරීම, උදාහරණයක් ලෙස, දත්ත සමුදාය වෙත වරින් වර කෙරෙන විමසුම් වෙනුවට පණිවිඩ තැරැව්කරුවෙකු භාවිතයෙන් දැනුම්දීම් තර්කනය නැවත සකස් කිරීම
- දර්ශක එකතු කිරීම හෝ වෙනස් කිරීම
- ORM විමසුම් පිරිසිදු SQL වෙත නැවත ලිවීම
- කම්මැලි දත්ත පැටවීමේ තර්කනය නැවත ලිවීම
- දත්ත denormalization හරහා හැඹිලිගත කිරීම. උදාහරණයක් ලෙස, අපට වගු සම්බන්ධතාවක් ඇත බෙදාහැරීම -> ඉන්වොයිසිය -> ඉල්ලීම -> අයදුම්පත. එනම්, එක් එක් බෙදාහැරීම වෙනත් වගු හරහා යෙදුමක් සමඟ සම්බන්ධ වේ. එක් එක් ඉල්ලීමේ සියලුම වගු සම්බන්ධ නොකිරීමට, අපි බෙදාහැරීමේ වගුවේ ඇති ඉල්ලීමට සබැඳිය අනුපිටපත් කළෙමු.
- විමර්ශන පොත් සමඟ ස්ථිතික වගු හැඹිලි කිරීම සහ වැඩසටහන් මතකයේ කලාතුරකින් වෙනස් වන වගු.
සමහර විට වෙනස්කම් සිත් ඇදගන්නාසුළු ප්රතිනිර්මාණයකට සමාන වූ නමුත් ඒවා පද්ධති භාරයෙන් 5-10% ක් ලබා දුන් අතර ඒවා යුක්ති සහගත විය. කාලයාගේ ඇවෑමෙන්, පිටාර ගැලීම කුඩා හා කුඩා වූ අතර, වඩ වඩාත් බරපතල නැවත සැලසුම් කිරීම අවශ්ය විය.
ඉන්පසු අපි අපගේ අවධානය යොමු කළේ දෙවන ඉල්ලීම් කණ්ඩායම වෙත - මධ්යම ගොවීන් කණ්ඩායම වෙත ය. එහි තවත් බොහෝ විමසුම් ඇති අතර මුළු කණ්ඩායමම විශ්ලේෂණය කිරීමට බොහෝ කාලයක් ගතවනු ඇති බව පෙනෙන්නට තිබුණි. කෙසේ වෙතත්, බොහෝ විමසුම් ප්රශස්ත කිරීම ඉතා සරල වූ අතර, බොහෝ ගැටලු විවිධ වෙනස්කම් වලින් දුසිම් ගනනක් නැවත නැවතත් සිදු විය. මෙන්න අපි සමාන විමසුම් දුසිම් ගණනකට යෙදූ සමහර සාමාන්ය ප්රශස්තිකරණය සඳහා උදාහරණ වන අතර ප්රශස්ත කළ විමසුම් සමූහයක් 3-5% කින් දත්ත සමුදාය මුදා හරින ලදී.
- COUNT සහ සම්පූර්ණ වගු පරිලෝකනය භාවිතා කරමින් වාර්තා තිබේදැයි පරීක්ෂා කිරීම වෙනුවට, EXISTS භාවිතා කිරීමට පටන් ගත්තේය
- DISTINCT ඉවත් කර ඇත (සාමාන්ය වට්ටෝරුවක් නොමැත, නමුත් සමහර විට ඉල්ලීම 10-100 ගුණයකින් වේගවත් කිරීමෙන් ඔබට එය පහසුවෙන් ඉවත් කළ හැකිය).
උදාහරණයක් ලෙස, විශාල බෙදාහැරීමේ වගුවකින් (DELIVERY) සියලුම රියදුරන් තේරීමට විමසුමක් වෙනුවට
SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
සාපේක්ෂ වශයෙන් කුඩා මේසයක් මත විමසුමක් කළේය 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)
අපි සහසම්බන්ධ උප විමසුමක් භාවිතා කළ බව පෙනේ, නමුත් එය 10 ගුණයකට වඩා වැඩි වේගයක් ලබා දෙයි.
- බොහෝ අවස්ථාවලදී, COUNT සම්පූර්ණයෙන්ම අත්හැර දමා ඇත
ආසන්න අගය ගණනය කිරීම මගින් ප්රතිස්ථාපනය වේ - වෙනුවට
UPPER(s) LIKE JOHN%’
භාවිත
s ILIKE “John%”
එක් එක් නිශ්චිත ඉල්ලීම සමහර විට 3-1000 ගුණයකින් වේගවත් විය. ආකර්ෂණීය කාර්ය සාධනය තිබියදීත්, මුලදී අපට පෙනුනේ, සම්පූර්ණ කිරීමට ms 10 ක් ගත වන, 3 වන බරම විමසුම් සියයෙන් එකක් වන සහ සමස්ත දත්ත සමුදාය පැටවීමේ කාලයෙන් සියයට සියයෙන් සියයක් ගන්නා විමසුමක් ප්රශස්ත කිරීමෙහි තේරුමක් නැති බවයි. නමුත් එකම වට්ටෝරුව එකම වර්ගයේ විමසුම් සමූහයකට යෙදීමෙන් අපි සියයට කිහිපයක් ආපසු ලබා ගත්තෙමු. සියගණනක් විමසුම් හස්තීයව සමාලෝචනය කිරීමට කාලය නාස්ති නොකිරීමට, අපි එකම වර්ගයේ විමසුම් සොයා ගැනීමට සාමාන්ය ප්රකාශන භාවිතා කරන සරල ස්ක්රිප්ට් කිහිපයක් ලිව්වෙමු. එහි ප්රතිඵලයක් වශයෙන්, ස්වයංක්රීයව විමසුම් කණ්ඩායම් සෙවීම, නිහතමානී උත්සාහයකින් අපගේ කාර්ය සාධනය තව දුරටත් වැඩිදියුණු කිරීමට අපට ඉඩ සැලසේ.
එහි ප්රතිඵලයක් ලෙස අපි දැනට වසර තුනක සිට එකම දෘඪාංගයේ වැඩ කරනවා. සාමාන්ය දෛනික බර 30% ක් පමණ වන අතර, උපරිම වලදී එය 70% දක්වා ළඟා වේ. ඉල්ලීම් ගණන මෙන්ම පරිශීලකයින් සංඛ්යාව ද ආසන්න වශයෙන් 10 ගුණයකින් වැඩි වී ඇත. මේ සියල්ලටම ස්තූතිවන්ත වන්නේ මෙම TOP-MEDIUM ඉල්ලීම් කණ්ඩායම්වල නිරන්තර අධීක්ෂණයට ය. TOP කණ්ඩායම තුළ නව ඉල්ලීමක් දිස් වූ වහාම, අපි එය වහාම විශ්ලේෂණය කර එය වේගවත් කිරීමට උත්සාහ කරමු. අපි විමසුම් විශ්ලේෂණ ස්ක්රිප්ට් භාවිතයෙන් සතියකට වරක් MEDIUM සමූහය සමාලෝචනය කරන්නෙමු. ප්රශස්තකරණය කරන්නේ කෙසේදැයි අප දැනටමත් දන්නා නව විමසුම් අපට හමු වුවහොත්, අපි ඒවා ඉක්මනින් වෙනස් කරන්නෙමු. සමහර විට අපි එකවර විමසුම් කිහිපයකට යෙදිය හැකි නව ප්රශස්තිකරණ ක්රම සොයා ගනිමු.
අපගේ අනාවැකි වලට අනුව, වත්මන් සේවාදායකය තවත් 3-5 ගුණයකින් පරිශීලකයින් සංඛ්යාව වැඩිවීමට ඔරොත්තු දෙනු ඇත. සත්ය, අපට අපේ අත් ඉහළට තවත් එක් ace up එකක් ඇත - නිර්දේශ කර ඇති පරිදි අපි තවමත් SELECT විමසුම් කැඩපත වෙත මාරු කර නැත. නමුත් අපි මෙය දැනුවත්ව නොකරමු, මන්ද අපට "බර කාලතුවක්කු" සක්රිය කිරීමට පෙර "ස්මාර්ට්" ප්රශස්තිකරණයේ හැකියාවන් මුලුමනින්ම අවසන් කිරීමට අවශ්ය බැවිනි.
සිදු කරන ලද කාර්යය පිළිබඳ විවේචනාත්මක බැල්මක් සිරස් පරිමාණය භාවිතා කිරීම යෝජනා කළ හැකිය. විශේෂඥයින්ගේ කාලය නාස්ති කිරීම වෙනුවට වඩා බලවත් සේවාදායකයක් මිලදී ගන්න. සේවාදායකයට එතරම් මුදලක් වැය නොවනු ඇත, විශේෂයෙන් අපි තවමත් සිරස් පරිමාණයේ සීමාවන් අවසන් කර නොමැති නිසා. කෙසේ වෙතත්, ඉල්ලීම් ගණන පමණක් 10 ගුණයකින් වැඩි විය. වසර ගණනාවක් පුරා, පද්ධතියේ ක්රියාකාරිත්වය වැඩි වී ඇති අතර දැන් තවත් ඉල්ලීම් වර්ග තිබේ. හැඹිලිගත කිරීමට ස්තූතියි, පැවති ක්රියාකාරීත්වය අඩු ඉල්ලීම් සහ වඩාත් කාර්යක්ෂම ඉල්ලීම් වලින් සිදු කෙරේ. මෙයින් අදහස් කරන්නේ සැබෑ ත්වරණ සංගුණකය ලබා ගැනීම සඳහා ඔබට ආරක්ෂිතව තවත් 5 කින් ගුණ කළ හැකි බවයි. එබැවින්, වඩාත්ම ගතානුගතික ඇස්තමේන්තු වලට අනුව, ත්වරණය 50 ගුණයක් හෝ ඊට වැඩි බව අපට පැවසිය හැකිය. සේවාදායකයක් සිරස් අතට පැද්දීමට 50 ගුණයකින් වැඩි මුදලක් වැය වේ. විශේෂයෙන් සලකා බැලීමේදී ප්රශස්තිකරණය සිදු කළ පසු එය සෑම විටම ක්රියාත්මක වන අතර කුලියට ගත් සේවාදායකයේ බිල්පත සෑම මසකම පැමිණේ.
මූලාශ්රය: www.habr.com