બિલ્ડરો માટે B2B સેવાના ઉદાહરણનો ઉપયોગ કરીને ડેટાબેઝ ક્વેરીઝને ઑપ્ટિમાઇઝ કરવી

વધુ ઉત્પાદક સર્વર પર ગયા વિના ડેટાબેઝમાં ક્વેરીઝની સંખ્યા 10 ગણી કેવી રીતે વધારવી અને સિસ્ટમની કાર્યક્ષમતા જાળવવી? હું તમને કહીશ કે અમે અમારા ડેટાબેઝના પ્રદર્શનમાં થયેલા ઘટાડા સાથે કેવી રીતે વ્યવહાર કર્યો, અમે શક્ય તેટલા વપરાશકર્તાઓને સેવા આપવા અને કમ્પ્યુટિંગ સંસાધનોની કિંમતમાં વધારો ન કરવા માટે SQL ક્વેરીઝને કેવી રીતે ઑપ્ટિમાઇઝ કરી.

હું બાંધકામ કંપનીઓમાં વ્યવસાયિક પ્રક્રિયાઓનું સંચાલન કરવા માટે સેવા આપું છું. અમારી સાથે લગભગ 3 હજાર કંપનીઓ કામ કરે છે. અમારી સિસ્ટમ સાથે દરરોજ 10 હજારથી વધુ લોકો 4-10 કલાક કામ કરે છે. તે આયોજન, સૂચના, ચેતવણી, માન્યતાની વિવિધ સમસ્યાઓનું નિરાકરણ કરે છે... અમે PostgreSQL 9.6 નો ઉપયોગ કરીએ છીએ. અમારી પાસે ડેટાબેઝમાં લગભગ 300 કોષ્ટકો છે અને દરરોજ 200 મિલિયન પ્રશ્નો (10 હજાર અલગ અલગ) પ્રાપ્ત થાય છે. સરેરાશ અમારી પાસે પ્રતિ સેકન્ડ 3-4 હજાર વિનંતીઓ છે, સૌથી વધુ સક્રિય ક્ષણોમાં પ્રતિ સેકન્ડ 10 હજારથી વધુ વિનંતીઓ. મોટાભાગના પ્રશ્નો OLAP છે. તેમાં ઘણા ઓછા ઉમેરાઓ, ફેરફારો અને કાઢી નાખવામાં આવ્યા છે, એટલે કે OLTP લોડ પ્રમાણમાં હળવો છે. મેં આ બધા નંબરો આપ્યા છે જેથી કરીને તમે અમારા પ્રોજેક્ટના સ્કેલનું મૂલ્યાંકન કરી શકો અને સમજી શકો કે અમારો અનુભવ તમારા માટે કેટલો ઉપયોગી છે.

એક ચિત્ર. ગીતાત્મક

જ્યારે અમે વિકાસ શરૂ કર્યો, ત્યારે અમે ખરેખર વિચાર્યું ન હતું કે ડેટાબેઝ પર કયા પ્રકારનો ભાર આવશે અને જો સર્વર ખેંચવાનું બંધ કરશે તો અમે શું કરીશું. ડેટાબેઝ ડિઝાઇન કરતી વખતે, અમે સામાન્ય ભલામણોનું પાલન કર્યું અને પોતાને પગમાં ન મારવાનો પ્રયાસ કર્યો, પરંતુ સામાન્ય સલાહથી આગળ વધ્યા જેમ કે "પેટર્નનો ઉપયોગ કરશો નહીં. એન્ટિટી એટ્રિબ્યુટ મૂલ્યો અમે અંદર ગયા નથી. અમે ડેટા રીડન્ડન્સીને ટાળીને, નોર્મલાઇઝેશનના સિદ્ધાંતો પર આધારિત ડિઝાઇન કરી છે અને અમુક ક્વેરીઝને ઝડપી બનાવવાની કાળજી લીધી નથી. પ્રથમ વપરાશકર્તાઓ આવતાની સાથે જ, અમને પ્રદર્શન સમસ્યાનો સામનો કરવો પડ્યો. હંમેશની જેમ, અમે આ માટે સંપૂર્ણપણે તૈયાર ન હતા. પ્રથમ સમસ્યાઓ સરળ હોવાનું બહાર આવ્યું. એક નિયમ તરીકે, નવું અનુક્રમણિકા ઉમેરીને બધું હલ કરવામાં આવ્યું હતું. પરંતુ એક સમય એવો આવ્યો જ્યારે સરળ પેચો કામ કરવાનું બંધ કરી દીધું. અમારી પાસે અનુભવનો અભાવ છે અને સમસ્યાઓનું કારણ શું છે તે સમજવું અમારા માટે વધુને વધુ મુશ્કેલ બની રહ્યું છે તે સમજીને, અમે એવા નિષ્ણાતોને નોકરીએ રાખ્યા કે જેમણે અમને સર્વરને યોગ્ય રીતે સેટ કરવામાં મદદ કરી, મોનિટરિંગને કનેક્ટ કર્યું અને અમને ક્યાં શોધવું તે બતાવ્યું. આંકડા.

ચિત્ર બે. આંકડાકીય

તેથી અમારી પાસે લગભગ 10 હજાર અલગ-અલગ ક્વેરીઝ છે જે અમારા ડેટાબેઝ પર દરરોજ એક્ઝિક્યુટ કરવામાં આવે છે. આ 10 હજારમાંથી, એવા રાક્ષસો છે જે 2-3 એમએસના સરેરાશ એક્ઝેક્યુશન સમય સાથે 0.1-0.3 મિલિયન વખત ચલાવવામાં આવે છે, અને 30 સેકન્ડના સરેરાશ એક્ઝેક્યુશન સમય સાથે ક્વેરી છે જેને દિવસમાં 100 વખત કહેવામાં આવે છે.

તમામ 10 હજાર પ્રશ્નોને ઑપ્ટિમાઇઝ કરવું શક્ય ન હતું, તેથી અમે ડેટાબેઝની કામગીરીને યોગ્ય રીતે સુધારવા માટે અમારા પ્રયત્નોને ક્યાં નિર્દેશિત કરવા તે શોધવાનું નક્કી કર્યું. ઘણી પુનરાવર્તનો પછી, અમે વિનંતીઓને પ્રકારોમાં વિભાજીત કરવાનું શરૂ કર્યું.

ટોચની વિનંતીઓ

આ સૌથી ભારે પ્રશ્નો છે જે સૌથી વધુ સમય લે છે (કુલ સમય). આ એવી ક્વેરીઝ છે જેને ઘણી વાર કહેવામાં આવે છે અથવા ક્વેરીઝ કે જે ચલાવવામાં ઘણો લાંબો સમય લે છે (લાંબા અને વારંવારના પ્રશ્નો ઝડપ માટેની લડતના પ્રથમ પુનરાવર્તનમાં ઑપ્ટિમાઇઝ કરવામાં આવ્યા હતા). પરિણામે, સર્વર તેમના અમલ પર સૌથી વધુ સમય વિતાવે છે. વધુમાં, ટોચની વિનંતીઓને કુલ અમલના સમય દ્વારા અને IO સમય દ્વારા અલગથી અલગ કરવી મહત્વપૂર્ણ છે. આવા પ્રશ્નોને ઑપ્ટિમાઇઝ કરવાની પદ્ધતિઓ થોડી અલગ છે.

તમામ કંપનીઓની સામાન્ય પ્રથા ટોચની વિનંતીઓ સાથે કામ કરવાની છે. તેમાંના થોડા છે; એક ક્વેરી પણ ઑપ્ટિમાઇઝ કરવાથી 5-10% સંસાધનો ખાલી થઈ શકે છે. જો કે, જેમ જેમ પ્રોજેક્ટ પરિપક્વ થતો જાય છે તેમ, ટોપ ક્વેરીઝને ઑપ્ટિમાઇઝ કરવાનું વધુને વધુ બિન-તુચ્છ કાર્ય બની જાય છે. બધી સરળ પદ્ધતિઓ પહેલાથી જ તૈયાર કરવામાં આવી છે, અને સૌથી વધુ "ભારે" વિનંતી "માત્ર" 3-5% સંસાધનો લે છે. જો કુલ ટોપ ક્વેરીઝ 30-40% કરતા ઓછો સમય લે છે, તો સંભવતઃ તમે તેને ઝડપથી કામ કરવા માટે પહેલાથી જ પ્રયત્નો કરી લીધા છે અને હવે પછીના જૂથમાંથી ઑપ્ટિમાઇઝ ક્વેરીઝ પર આગળ વધવાનો સમય છે.
આ જૂથમાં કેટલા ટોચના પ્રશ્નોનો સમાવેશ થવો જોઈએ તે પ્રશ્નનો જવાબ આપવાનું બાકી છે. હું સામાન્ય રીતે ઓછામાં ઓછા 10 લઉં છું, પરંતુ 20 થી વધુ નહીં. હું એ સુનિશ્ચિત કરવાનો પ્રયાસ કરું છું કે TOP જૂથમાં પ્રથમ અને છેલ્લાનો સમય 10 કરતા વધુ વખત અલગ ન હોય. એટલે કે, જો ક્વેરી એક્ઝેક્યુશનનો સમય 1લા સ્થાનથી 10મા સ્થાને ઝડપથી ઘટી જાય, તો હું TOP-10 લઉં છું, જો ડ્રોપ વધુ ક્રમિક હોય, તો હું જૂથનું કદ વધારીને 15 અથવા 20 કરીશ.

મધ્યમ ખેડૂતો

આ બધી વિનંતીઓ છે જે TOP પછી તરત જ આવે છે, છેલ્લા 5-10% ના અપવાદ સિવાય. સામાન્ય રીતે, આ ક્વેરીઝને ઑપ્ટિમાઇઝ કરવામાં સર્વર પ્રભાવને મોટા પ્રમાણમાં વધારવાની તક રહેલી છે. આ વિનંતીઓનું વજન 80% સુધી હોઈ શકે છે. પરંતુ જો તેમનો હિસ્સો 50% થી વધી ગયો હોય, તો પણ તેમને વધુ કાળજીપૂર્વક જોવાનો સમય છે.

પૂંછડી

ઉલ્લેખ કર્યો છે તેમ, આ પ્રશ્નો અંતમાં આવે છે અને 5-10% સમય લે છે. તમે તેમના વિશે ફક્ત ત્યારે જ ભૂલી શકો છો જો તમે સ્વચાલિત ક્વેરી વિશ્લેષણ સાધનોનો ઉપયોગ ન કરો, તો પછી તેમને ઑપ્ટિમાઇઝ કરવું પણ સસ્તું હોઈ શકે છે.

દરેક જૂથનું મૂલ્યાંકન કેવી રીતે કરવું?

હું SQL ક્વેરીનો ઉપયોગ કરું છું જે PostgreSQL માટે આવા મૂલ્યાંકન કરવામાં મદદ કરે છે (મને ખાતરી છે કે અન્ય ઘણા 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 છે) જે એક જૂથની વિનંતીને બીજા જૂથથી અલગ કરે છે.

ઑપ્ટિમાઇઝેશન કાર્ય શરૂ થયું તે સમયે અને હાલમાં વિનંતીઓના શેરની તુલના આ રીતે થાય છે.

આકૃતિ દર્શાવે છે કે ટોચની વિનંતીઓનો હિસ્સો તીવ્ર ઘટાડો થયો છે, પરંતુ "મધ્યમ ખેડૂતો" વધ્યા છે.
શરૂઆતમાં, ટોચની વિનંતીઓમાં સ્પષ્ટ ભૂલોનો સમાવેશ થતો હતો. સમય જતાં, બાળપણના રોગો અદૃશ્ય થઈ ગયા, ટોચની વિનંતીઓનો હિસ્સો ઘટ્યો, અને મુશ્કેલ વિનંતીઓને ઝડપી બનાવવા માટે વધુને વધુ પ્રયત્નો કરવા પડ્યા.

વિનંતીઓનો ટેક્સ્ટ મેળવવા માટે અમે નીચેની વિનંતીનો ઉપયોગ કરીએ છીએ

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

અહીં સૌથી વધુ ઉપયોગમાં લેવાતી તકનીકોની સૂચિ છે જેણે અમને ટોચના પ્રશ્નોને ઝડપી બનાવવામાં મદદ કરી છે:

• સિસ્ટમની પુનઃ ડિઝાઇન, ઉદાહરણ તરીકે, ડેટાબેઝમાં સામયિક પ્રશ્નોને બદલે સંદેશ બ્રોકરનો ઉપયોગ કરીને સૂચના તર્કનું પુનઃકાર્ય કરવું
• અનુક્રમણિકાઓ ઉમેરવા અથવા બદલવી
• ORM ક્વેરીઝને શુદ્ધ SQL પર ફરીથી લખી રહ્યું છે
• આળસુ ડેટા લોડિંગ તર્કને ફરીથી લખવું
• ડેટા ડિનોર્મલાઈઝેશન દ્વારા કેશીંગ. ઉદાહરણ તરીકે, અમારી પાસે ટેબલ કનેક્શન છે ડિલિવરી -> ઇન્વોઇસ -> વિનંતી -> એપ્લિકેશન. એટલે કે, દરેક ડિલિવરી અન્ય કોષ્ટકો દ્વારા એપ્લિકેશન સાથે સંકળાયેલ છે. દરેક વિનંતીમાં તમામ કોષ્ટકોને લિંક ન કરવા માટે, અમે ડિલિવરી કોષ્ટકમાં વિનંતીની લિંકને ડુપ્લિકેટ કરી છે.
• સંદર્ભ પુસ્તકો સાથે સ્થિર કોષ્ટકોને કેશ કરવું અને પ્રોગ્રામ મેમરીમાં ભાગ્યે જ બદલાતા કોષ્ટકો.

કેટલીકવાર ફેરફારો પ્રભાવશાળી પુનઃડિઝાઇન સમાન હતા, પરંતુ તેઓ સિસ્ટમ લોડના 5-10% પ્રદાન કરે છે અને ન્યાયી હતા. સમય જતાં, એક્ઝોસ્ટ નાનો અને નાનો બન્યો, અને વધુને વધુ ગંભીર પુનઃડિઝાઇનની જરૂર પડી.

પછી અમે વિનંતીઓના બીજા જૂથ તરફ ધ્યાન આપ્યું - મધ્યમ ખેડૂતોના જૂથ. તેમાં બીજા ઘણા પ્રશ્નો છે અને એવું લાગતું હતું કે આખા જૂથનું વિશ્લેષણ કરવામાં ઘણો સમય લાગશે. જો કે, મોટાભાગની ક્વેરીઝ ઑપ્ટિમાઇઝ કરવા માટે ખૂબ જ સરળ હોવાનું બહાર આવ્યું છે, અને ઘણી સમસ્યાઓ વિવિધ ભિન્નતાઓમાં ડઝનેક વખત પુનરાવર્તિત થઈ હતી. અહીં કેટલાક લાક્ષણિક ઑપ્ટિમાઇઝેશનના ઉદાહરણો છે જે અમે ડઝનેક સમાન ક્વેરીઝ પર લાગુ કર્યા છે અને ઑપ્ટિમાઇઝ ક્વેરીઝના દરેક જૂથે ડેટાબેઝને 3-5% દ્વારા અનલોડ કર્યો છે.

• COUNT અને સંપૂર્ણ ટેબલ સ્કેનનો ઉપયોગ કરીને રેકોર્ડની હાજરી તપાસવાને બદલે, EXISTS નો ઉપયોગ થવા લાગ્યો
• DISTINCT થી છુટકારો મેળવ્યો (કોઈ સામાન્ય રેસીપી નથી, પરંતુ કેટલીકવાર તમે વિનંતીને 10-100 વખત ઝડપી કરીને સરળતાથી છુટકારો મેળવી શકો છો).

  ઉદાહરણ તરીકે, ડિલિવરીના મોટા ટેબલમાંથી બધા ડ્રાઇવરો પસંદ કરવા માટે ક્વેરી (ડિલિવરી) ને બદલે

  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 વખત ઝડપી કરવામાં આવી હતી. પ્રભાવશાળી પ્રદર્શન હોવા છતાં, શરૂઆતમાં અમને એવું લાગતું હતું કે ક્વેરી પૂર્ણ કરવા માટે 10 ms લે છે, જે 3જી સો સૌથી ભારે ક્વેરીમાંથી એક છે અને ડેટાબેઝ લોડ ટાઈમના એક ટકાનો સો ભાગ લે છે તે ઑપ્ટિમાઇઝ કરવાનો કોઈ અર્થ નથી. પરંતુ સમાન પ્રકારના પ્રશ્નોના જૂથમાં સમાન રેસીપી લાગુ કરીને, અમે થોડા ટકા પાછા જીત્યા. તમામ સેંકડો ક્વેરીઝની મેન્યુઅલી સમીક્ષા કરવામાં સમય ન બગાડે તે માટે, અમે ઘણી સરળ સ્ક્રિપ્ટો લખી છે જે સમાન પ્રકારની ક્વેરી શોધવા માટે રેગ્યુલર એક્સપ્રેશનનો ઉપયોગ કરે છે. પરિણામે, પ્રશ્નોના જૂથોને આપમેળે શોધવાથી અમને સાધારણ પ્રયત્નો સાથે અમારા પ્રદર્શનમાં વધુ સુધારો કરવાની મંજૂરી મળી.

પરિણામે, અમે ત્રણ વર્ષથી સમાન હાર્ડવેર પર કામ કરી રહ્યા છીએ. સરેરાશ દૈનિક ભાર લગભગ 30% છે, શિખરોમાં તે 70% સુધી પહોંચે છે. વિનંતીઓની સંખ્યા, તેમજ વપરાશકર્તાઓની સંખ્યામાં, આશરે 10 ગણો વધારો થયો છે. અને આ તમામ TOP-MEDIUM વિનંતીઓના સમાન જૂથોની સતત દેખરેખ માટે આભાર. TOP જૂથમાં નવી વિનંતી દેખાય કે તરત જ અમે તેનું વિશ્લેષણ કરીએ છીએ અને તેને ઝડપી બનાવવાનો પ્રયાસ કરીએ છીએ. અમે ક્વેરી એનાલિસિસ સ્ક્રિપ્ટ્સનો ઉપયોગ કરીને અઠવાડિયામાં એકવાર મધ્યમ જૂથની સમીક્ષા કરીએ છીએ. જો અમારી પાસે નવી ક્વેરી આવે છે કે જે અમે પહેલાથી જ જાણીએ છીએ કે કેવી રીતે ઑપ્ટિમાઇઝ કરવું, અમે તેને ઝડપથી બદલીએ છીએ. કેટલીકવાર આપણે નવી ઓપ્ટિમાઇઝેશન પદ્ધતિઓ શોધીએ છીએ જે એકસાથે અનેક પ્રશ્નો પર લાગુ કરી શકાય છે.

અમારી આગાહી મુજબ, વર્તમાન સર્વર વપરાશકર્તાઓની સંખ્યામાં બીજા 3-5 ગણા વધારાનો સામનો કરશે. સાચું, અમારી પાસે અમારી સ્લીવમાં વધુ એક પાસા છે - અમે હજુ પણ SELECT ક્વેરીઝને અરીસામાં સ્થાનાંતરિત કરી નથી, જેમ કે ભલામણ કરવામાં આવે છે. પરંતુ અમે આ સભાનપણે કરતા નથી, કારણ કે અમે "ભારે આર્ટિલરી" ચાલુ કરતા પહેલા "સ્માર્ટ" ઑપ્ટિમાઇઝેશનની શક્યતાઓને સંપૂર્ણપણે ખતમ કરવા માંગીએ છીએ.
કરવામાં આવેલ કાર્ય પર નિર્ણાયક દેખાવ વર્ટિકલ સ્કેલિંગનો ઉપયોગ કરવાનું સૂચન કરી શકે છે. નિષ્ણાતોનો સમય બગાડવાને બદલે વધુ શક્તિશાળી સર્વર ખરીદો. સર્વરનો ખર્ચ એટલો વધારે ન પણ હોઈ શકે, ખાસ કરીને કારણ કે અમે હજુ સુધી વર્ટિકલ સ્કેલિંગની મર્યાદાઓ પૂરી કરી નથી. જો કે, માત્ર વિનંતીઓની સંખ્યામાં 10 ગણો વધારો થયો છે. ઘણા વર્ષો દરમિયાન, સિસ્ટમની કાર્યક્ષમતા વધી છે અને હવે વધુ પ્રકારની વિનંતીઓ છે. કેશીંગ માટે આભાર, અસ્તિત્વમાં રહેલી કાર્યક્ષમતા ઓછી વિનંતીઓ અને વધુ કાર્યક્ષમ વિનંતીઓમાં કરવામાં આવે છે. આનો અર્થ એ છે કે વાસ્તવિક પ્રવેગ ગુણાંક મેળવવા માટે તમે સુરક્ષિત રીતે બીજા 5 વડે ગુણાકાર કરી શકો છો. તેથી, સૌથી રૂઢિચુસ્ત અંદાજો અનુસાર, અમે કહી શકીએ કે પ્રવેગક 50 ગણો અથવા વધુ હતો. સર્વરને વર્ટિકલી સ્વિંગ કરવા માટે 50 ગણો વધુ ખર્ચ થશે. ખાસ કરીને ધ્યાનમાં લેવું કે એકવાર ઑપ્ટિમાઇઝેશન હાથ ધરવામાં આવે તો તે હંમેશાં કામ કરે છે, અને ભાડે આપેલા સર્વરનું બિલ દર મહિને આવે છે.

સોર્સ: www.habr.com