Տվյալների բազայի հարցումների օպտիմիզացում՝ օգտագործելով 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 հազար հարցումները, ուստի մենք որոշեցինք պարզել, թե ուր ուղղել մեր ջանքերը՝ տվյալների բազայի արդյունավետությունը ճիշտ բարելավելու համար: Մի քանի կրկնություններից հետո մենք սկսեցինք հարցումները բաժանել տեսակների:

TOP հարցումներ

Սրանք ամենածանր հարցումներն են, որոնք ամենաշատ ժամանակն են խլում (ընդհանուր ժամանակ): Սրանք հարցումներ են, որոնք կա՛մ շատ հաճախ են կոչվում, կա՛մ հարցումներ են, որոնց կատարումը շատ երկար է տևում (երկար և հաճախակի հարցումները օպտիմիզացվել են արագության համար պայքարի առաջին կրկնություններում): Արդյունքում սերվերը ամենաշատ ժամանակն է ծախսում դրանց կատարման վրա։ Ավելին, կարևոր է հիմնական հարցումները առանձնացնել ըստ կատարման ընդհանուր ժամանակի և առանձին՝ ըստ IO ժամանակի: Նման հարցումների օպտիմալացման մեթոդները մի փոքր տարբեր են:

Բոլոր ընկերությունների սովորական պրակտիկան դա TOP հարցումների հետ աշխատելն է: Դրանցից մի քանիսը կան, նույնիսկ մեկ հարցման օպտիմալացումը կարող է ազատել ռեսուրսների 5-10%-ը: Այնուամենայնիվ, քանի որ նախագիծը հասունանում է, TOP հարցումների օպտիմալացումը դառնում է ավելի ու ավելի աննշան խնդիր: Բոլոր պարզ մեթոդներն արդեն մշակված են, և «ամենածանր» հարցումը վերցնում է ռեսուրսների «ընդամենը» 3-5%-ը։ Եթե ​​TOP հարցումներն ընդհանուր առմամբ խլում են ժամանակի 30-40%-ից պակաս, ապա, ամենայն հավանականությամբ, դուք արդեն ջանքեր եք գործադրել դրանք արագ աշխատելու համար, և ժամանակն է անցնելու հաջորդ խմբի հարցումների օպտիմալացմանը:
Մնում է պատասխանել այն հարցին, թե քանի թոփ հարցումներ պետք է ներառվեն այս խմբում: Ես սովորաբար վերցնում եմ առնվազն 10, բայց ոչ ավելի, քան 20: Ես փորձում եմ ապահովել, որ TOP խմբում առաջինի և վերջինի ժամանակը տարբերվի ոչ ավելի, քան 10 անգամ: Այսինքն, եթե հարցման կատարման ժամանակը կտրուկ իջնում ​​է 1-ին տեղից մինչև 10-րդ, ապա ես վերցնում եմ TOP-10-ը, եթե անկումն ավելի աստիճանական է, ապա ես խմբի չափը մեծացնում եմ մինչև 15 կամ 20:

Միջին գյուղացիներ

Սրանք բոլոր հարցումներն են, որոնք գալիս են TOP-ից անմիջապես հետո, բացառությամբ վերջին 5-10%-ի: Սովորաբար, այս հարցումների օպտիմալացման ժամանակ հնարավորություն է ընձեռվում մեծապես բարձրացնել սերվերի աշխատանքը: Այս հարցումները կարող են կշռել մինչև 80%: Բայց նույնիսկ եթե նրանց մասնաբաժինը գերազանցել է 50%-ը, ապա ժամանակն է նրանց ավելի ուշադիր նայել:

Պոչ

Ինչպես նշվեց, այս հարցումները գալիս են վերջում և խլում են ժամանակի 5-10%-ը: Դուք կարող եք մոռանալ դրանց մասին միայն այն դեպքում, եթե չեք օգտագործում ավտոմատ հարցումների վերլուծության գործիքներ, ապա դրանց օպտիմալացումը կարող է նաև էժան լինել:

Ինչպե՞ս գնահատել յուրաքանչյուր խումբ:

Ես օգտագործում եմ SQL հարցում, որն օգնում է նման գնահատական ​​տալ PostgreSQL-ի համար (վստահ եմ, որ նմանատիպ հարցում կարելի է գրել շատ այլ DBMS-ների համար)

SQL հարցում՝ 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

Հարցման արդյունքը երեք սյունակ է, որոնցից յուրաքանչյուրը պարունակում է այս խմբի հարցումները մշակելու համար պահանջվող ժամանակի տոկոսը: Հարցման ներսում կա երկու թիվ (իմ դեպքում դա 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-ին
• Ծույլ տվյալների բեռնման տրամաբանության վերագրանցում
• Քեշավորում տվյալների ապանորմալացման միջոցով: Օրինակ, մենք ունենք սեղանի միացում Առաքում -> Հաշիվ -> Հարցում -> Դիմում: Այսինքն՝ յուրաքանչյուր առաքում այլ աղյուսակների միջոցով կապված է հավելվածի հետ։ Որպեսզի չկապվեն բոլոր աղյուսակները յուրաքանչյուր հարցում, մենք կրկնօրինակեցինք հարցումը հղումը Առաքման աղյուսակում:
• Ստատիկ աղյուսակների քեշավորում տեղեկատու գրքերով և հազվադեպ փոփոխվող աղյուսակներ ծրագրի հիշողության մեջ:

Երբեմն փոփոխությունները հասնում էին տպավորիչ վերանախագծման, սակայն դրանք ապահովում էին համակարգի ծանրաբեռնվածության 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 մվ, որը 3-րդ հարյուր ամենածանր հարցումներից մեկն է և զբաղեցնում է տվյալների բազայի բեռնման ընդհանուր ժամանակի հարյուրերորդական մասը: Բայց կիրառելով նույն բաղադրատոմսը նույն տեսակի հարցումների խմբին, մենք մի քանի տոկոս հետ շահեցինք: Որպեսզի ժամանակ չկորցնենք՝ ձեռքով վերանայելով բոլոր հարյուրավոր հարցումները, մենք գրեցինք մի քանի պարզ սկրիպտներ, որոնք օգտագործում էին կանոնավոր արտահայտություններ՝ նույն տեսակի հարցումներ գտնելու համար: Արդյունքում, հարցումների խմբերի ինքնաբերաբար որոնումը թույլ տվեց մեզ էլ ավելի բարելավել մեր կատարողականությունը համեստ ջանքերով:

Արդյունքում՝ մենք արդեն երեք տարի է, ինչ աշխատում ենք նույն սարքավորման վրա։ Միջին օրական ծանրաբեռնվածությունը կազմում է մոտ 30%, պիկերում այն ​​հասնում է 70%-ի: Հարցումների, ինչպես նաև օգտատերերի թիվը աճել է մոտ 10 անգամ։ Եվ այս ամենը TOP-MEDIUM հարցումների այս նույն խմբերի մշտական ​​մոնիտորինգի շնորհիվ։ Հենց նոր հարցում է հայտնվում TOP խմբում, մենք անմիջապես վերլուծում ենք այն և փորձում արագացնել այն։ Մենք վերանայում ենք MEDIUM խումբը շաբաթը մեկ անգամ՝ օգտագործելով հարցումների վերլուծության սցենարները: Եթե ​​մենք հանդիպենք նոր հարցումների, որոնք մենք արդեն գիտենք, թե ինչպես կարելի է օպտիմալացնել, մենք արագ փոխում ենք դրանք: Երբեմն մենք գտնում ենք օպտիմալացման նոր մեթոդներ, որոնք կարող են կիրառվել միանգամից մի քանի հարցումների համար:

Մեր կանխատեսումների համաձայն՝ ներկայիս սերվերը կդիմանա օգտվողների թվի ավելացմանը եւս 3-5 անգամ։ Ճիշտ է, մենք ևս մեկ ace մեր ձեռքում ունենք. մենք դեռ չենք փոխանցել SELECT հարցումները հայելու մեջ, ինչպես խորհուրդ է տրվում: Բայց մենք դա գիտակցաբար չենք անում, քանի որ նախքան «ծանր հրետանին» միացնելը մենք նախ ուզում ենք ամբողջությամբ սպառել «խելացի» օպտիմալացման հնարավորությունները։
Կատարված աշխատանքի քննադատական ​​հայացքը կարող է առաջարկել օգտագործել ուղղահայաց մասշտաբավորում: Գնե՛ք ավելի հզոր սերվեր՝ մասնագետների ժամանակը վատնելու փոխարեն։ Սերվերը կարող է այդքան թանկ չգնալ, մանավանդ որ մենք դեռ չենք սպառել ուղղահայաց մասշտաբավորման սահմանները։ Սակայն միայն հարցումների թիվն է ավելացել 10 անգամ։ Մի քանի տարիների ընթացքում համակարգի ֆունկցիոնալությունը մեծացել է, և այժմ կան ավելի շատ տեսակի հարցումներ: Քեշավորման շնորհիվ գոյություն ունեցող ֆունկցիոնալությունը կատարվում է ավելի քիչ հարցումներով և ավելի արդյունավետ հարցումներով: Սա նշանակում է, որ դուք կարող եք ապահով կերպով բազմապատկել ևս 5-ով, որպեսզի ստանաք իրական արագացման գործակիցը: Այսպիսով, ըստ ամենապահպանողական գնահատականների, կարելի է ասել, որ արագացումը եղել է 50 անգամ կամ ավելի։ Սերվերի ուղղահայաց ճոճումը կարժենա 50 անգամ ավելի թանկ: Հատկապես հաշվի առնելով, որ երբ օպտիմալացումն իրականացվում է, այն աշխատում է անընդհատ, իսկ վարձակալած սերվերի հաշիվը գալիս է ամեն ամիս:

Source: www.habr.com