PostgreSQL-ի ինդեքսների առողջությունը Java ծրագրավորողի աչքերով

Hey.

Իմ անունը Վանյան է և ես Java-ի ծրագրավորող եմ: Պատահում է, որ ես շատ եմ աշխատում PostgreSQL-ի հետ՝ ստեղծելով տվյալների բազան, օպտիմալացնելով կառուցվածքը, կատարումը և հանգստյան օրերին մի փոքր DBA նվագարկում:

Վերջերս ես կարգի բերեցի մի քանի տվյալների շտեմարաններ մեր միկրոսերվիսներում և գրեցի java գրադարան pg-index-health, ինչը հեշտացնում է այս աշխատանքը, խնայում է ինձ ժամանակը և օգնում է խուսափել մշակողների կողմից թույլ տված որոշ սխալներից: Հենց այս գրադարանի մասին կխոսենք այսօր։

Հրաժարում պատասխանատվությունից

PostgreSQL-ի հիմնական տարբերակը, որի հետ ես աշխատում եմ, 10-ն է: Բոլոր SQL հարցումները, որոնք ես օգտագործում եմ, փորձարկվում են նաև 11 տարբերակով: Նվազագույն աջակցվող տարբերակը 9.6 է:

նախապատմությանը

Ամեն ինչ սկսվեց գրեթե մեկ տարի առաջ ինձ համար տարօրինակ իրավիճակից. անսպասելի ցուցանիշի մրցակցային ստեղծումն ավարտվեց սխալմամբ։ Ինդեքսն ինքնին, ինչպես միշտ, մնաց տվյալների բազայում անվավեր վիճակում: Տեղեկամատյանների վերլուծությունը ցույց տվեց պակասություն temp_file_limit. Եվ մենք գնում ենք... Ավելի խորը փորելով՝ ես հայտնաբերեցի տվյալների բազայի կազմաձևման մի ամբողջ փունջ և, թևերս վեր քաշելով, սկսեցի դրանք ուղղել աչքերիս փայլով:

Խնդիր առաջին - լռելյայն կազմաձևում

Հավանաբար բոլորը բավականին հոգնել են Postgres-ի մասին փոխաբերությունից, որը կարող է գործարկվել սրճեփի վրա, բայց… լռելյայն կոնֆիգուրացիան իսկապես մի շարք հարցեր է առաջացնում: Առնվազն, արժե ուշադրություն դարձնել maintenance_work_mem, temp_file_limit, statement_timeout и lock_timeout.

Մեր դեպքում maintenance_work_mem եղել է լռելյայն 64 ՄԲ, և temp_file_limit ինչ-որ բան մոտ 2 ԳԲ - մենք պարզապես բավարար հիշողություն չունեինք մեծ սեղանի վրա ինդեքս ստեղծելու համար:

Հետևաբար, մեջ pg-index-health Ես հավաքեցի մի շարք բանալի, իմ կարծիքով, այն պարամետրերը, որոնք պետք է կազմաձևվեն յուրաքանչյուր տվյալների բազայի համար:

Խնդիր երկրորդ՝ կրկնօրինակ ինդեքսներ

Մեր տվյալների բազաները ապրում են SSD կրիչներով, և մենք օգտագործում ենք HA- կոնֆիգուրացիա բազմաթիվ տվյալների կենտրոններով, գլխավոր հոսթով և n- կրկնօրինակների քանակը. Սկավառակի տարածքը մեզ համար շատ արժեքավոր ռեսուրս է. դա ոչ պակաս կարևոր է, քան կատարումը և պրոցեսորի սպառումը: Հետևաբար, մի կողմից մեզ անհրաժեշտ են ինդեքսներ արագ ընթերցման համար, իսկ մյուս կողմից՝ մենք չենք ուզում տվյալների բազայում տեսնել ավելորդ ինդեքսներ, քանի որ դրանք տարածք են խլում և դանդաղեցնում տվյալների թարմացումը։

Եվ հիմա, ամեն ինչ վերականգնելով անվավեր ինդեքսներ և բավականաչափ տեսած լինելով հաղորդում է Օլեգ Բարթունովը, որոշեցի կազմակերպել «մեծ» մաքրում։ Պարզվեց, որ մշակողները չեն սիրում կարդալ տվյալների բազայի փաստաթղթերը: Նրանք դա այնքան էլ չեն սիրում։ Դրա պատճառով առաջանում են երկու տիպիկ սխալներ՝ ձեռքով ստեղծված ինդեքս առաջնային բանալու վրա և նմանատիպ «ձեռնարկ» ինդեքս՝ եզակի սյունակում: Փաստն այն է, որ դրանք պետք չեն. Postgres-ը ամեն ինչ կանի ինքն իրեն: Նման ինդեքսները կարող են ապահով կերպով ջնջվել, և այդ նպատակով ախտորոշիչներ են հայտնվել duplicated_indexes.

Խնդիր երրորդ՝ հատվող ինդեքսներ

Սկսնակ ծրագրավորողներից շատերը ինդեքսներ են ստեղծում մեկ սյունակի վրա: Աստիճանաբար, այս բիզնեսը մանրակրկիտ փորձ ունենալով, մարդիկ սկսում են օպտիմալացնել իրենց հարցումները և ավելացնել ավելի բարդ ինդեքսներ, որոնք ներառում են մի քանի սյունակներ: Այսպես են հայտնվում սյունակների ինդեքսները A, A + B- ը, A+B+C եւ այլն։ Այս ցուցանիշներից առաջին երկուսը կարող են ապահով կերպով դուրս նետվել, քանի որ դրանք երրորդի նախածանցներն են: Սա նաև մեծ քանակությամբ սկավառակի տարածություն է խնայում, և դրա համար կան ախտորոշիչներ intersected_indexes.

Խնդիր չորրորդ - արտաքին բանալիներ առանց ինդեքսների

Postgres-ը թույլ է տալիս ստեղծել օտարերկրյա բանալիների սահմանափակումներ՝ առանց օժանդակ ինդեքս նշելու: Շատ իրավիճակներում դա խնդիր չէ, և կարող է նույնիսկ չդրսևորվել... Առայժմ...

Մեզ մոտ նույնն էր. պարզապես ժամանակի ինչ-որ պահի աշխատանքը, որն աշխատում էր ըստ ժամանակացույցի և մաքրում թեստային պատվերների բազան, սկսեց մեզ «ավելացնել» վարպետի կողմից: CPU-ն և IO-ն վատնվեցին, հարցումները դանդաղեցին և սպառվեցին, ծառայությունը հինգ հարյուր էր: Արագ վերլուծություն pg_stat_activity ցույց տվեց, որ հարցումները, ինչպիսիք են.

delete from <table> where id in (…)

Այս դեպքում, իհարկե, թիրախային աղյուսակում կար ինդեքս ըստ id-ի, և շատ քիչ գրառումներ են ջնջվել ըստ պայմանի։ Թվում էր, թե ամեն ինչ պետք է աշխատի, բայց, ավաղ, այդպես չեղավ:

Հրաշալին օգնության հասավ բացատրել վերլուծել և ասաց, որ բացի թիրախային աղյուսակում գրառումները ջնջելուց, կա նաև ուղղիչ ամբողջականության ստուգում, և հարակից աղյուսակներից մեկում այս ստուգումը ձախողվում է հաջորդական սկանավորում համապատասխան ցուցանիշի բացակայության պատճառով։ Այսպես ծնվեց ախտորոշումը օտար_բանալիներ_առանց_ինդեքսի.

Խնդիր հինգ – զրո արժեք ինդեքսներում

Լռելյայնորեն, Postgres-ը ներառում է զրոյական արժեքներ btree ինդեքսներում, բայց դրանք սովորաբար այնտեղ անհրաժեշտ չեն: Հետևաբար, ես ջանասիրաբար փորձում եմ դուրս նետել այս զրոյականները (ախտորոշում ինդեքսներ_null_արժեքներով), ստեղծելով մասնակի ինդեքսներ զրոյական սյունակների վրա՝ ըստ տեսակի where <A> is not null. Այս կերպ ես կարողացա մեր ինդեքսներից մեկի չափը 1877 ՄԲ-ից նվազեցնել մինչև 16 ԿԲ: Իսկ ծառայություններից մեկում տվյալների բազայի չափը նվազել է ընդհանուր առմամբ 16%-ով (բացարձակ թվերով 4.3 ԳԲ-ով)՝ ինդեքսներից զրոյական արժեքների բացառման պատճառով: Հսկայական խնայողություններ սկավառակի տարածության մեջ՝ շատ պարզ փոփոխություններով: 🙂

Խնդիր վեցերորդ – առաջնային բանալիների բացակայություն

Մեխանիզմի բնույթից ելնելով MVCC Պոստգրեսում հնարավոր է նման իրավիճակ փչելերբ ձեր սեղանի չափը արագորեն աճում է մեծ թվով մեռած գրառումների պատճառով: Ես միամտորեն հավատում էի, որ սա մեզ չի սպառնա, և որ դա չի պատահի մեր բազայի հետ, որովհետև մենք, վայ!!!, նորմալ ծրագրավորողներ ենք... Ինչ հիմար և միամիտ էի ես...

Մի օր, մի հրաշալի միգրացիա վերցրեց և թարմացրեց բոլոր գրառումները մեծ և ակտիվորեն օգտագործվող աղյուսակում: Սեղանի չափը դուրս եկավ +100 ԳԲ: Դա անիծյալ ամոթ էր, բայց մեր դժբախտությունները դրանով չավարտվեցին: Այն բանից հետո, երբ այս սեղանի վրա ավտովակուումն ավարտվեց 15 ժամ անց, պարզ դարձավ, որ ֆիզիկական գտնվելու վայրը չի վերադառնա: Մենք չկարողացանք դադարեցնել ծառայությունը և VACUUM FULL դարձնել, ուստի որոշեցինք օգտվել pg_repack. Եվ հետո պարզվեց, որ pg_repack չգիտի, թե ինչպես մշակել աղյուսակները առանց առաջնային բանալիի կամ այլ եզակիության սահմանափակման, և մեր աղյուսակը չուներ առաջնային բանալի: Այսպես ծնվեց ախտորոշումը աղյուսակներ_առանց_հիմնական_բանալու.

Գրադարանային տարբերակում 0.1.5 Ավելացվել է աղյուսակների և ինդեքսների փքվածությունից տվյալներ հավաքելու և դրանց ժամանակին արձագանքելու հնարավորությունը:

Յոթերորդ և ութերորդ խնդիրներ՝ անբավարար ինդեքսներ և չօգտագործված ինդեքսներ

Հետևյալ երկու ախտորոշիչները հետևյալն են. աղյուսակներ_բացակայող_ինդեքսներով и չօգտագործված_ինդեքսներ – համեմատաբար վերջերս են հայտնվել իրենց վերջնական տեսքով: Բանն այն է, որ դրանք պարզապես հնարավոր չէր վերցնել և ավելացնել:

Ինչպես արդեն գրել եմ, մենք օգտագործում ենք կոնֆիգուրացիա մի քանի կրկնօրինակներով, և տարբեր հոսթների վրա ընթերցման ծանրաբեռնվածությունը սկզբունքորեն տարբեր է: Արդյունքում պարզվում է, որ որոշ աղյուսակներ և ինդեքսներ որոշ հոստերի վրա գործնականում չեն օգտագործվում, և վերլուծության համար անհրաժեշտ է վիճակագրություն հավաքել կլաստերի բոլոր հոսթերից։ Վերականգնել վիճակագրությունը Սա անհրաժեշտ է նաև կլաստերի յուրաքանչյուր հոսթի համար, դուք չեք կարող դա անել միայն վարպետի վրա:

Այս մոտեցումը թույլ տվեց մեզ խնայել մի քանի տասնյակ գիգաբայթ՝ հեռացնելով երբեք չօգտագործված ինդեքսները, ինչպես նաև ավելացնելով բացակայող ինդեքսները հազվադեպ օգտագործվող աղյուսակներում։

Որպես եզրակացություն

Իհարկե, գրեթե բոլոր ախտորոշման համար կարող եք կարգավորել բացառման ցուցակ. Այսպիսով, դուք կարող եք արագորեն ստուգումներ իրականացնել ձեր հավելվածում՝ կանխելով նոր սխալների ի հայտ գալը, այնուհետև աստիճանաբար ուղղել հինները:

Որոշ ախտորոշումներ կարող են իրականացվել ֆունկցիոնալ թեստերում անմիջապես տվյալների բազայի միգրացիաները սկսելուց հետո: Եվ սա, թերեւս, իմ գրադարանի ամենահզոր հատկանիշներից մեկն է: Օգտագործման օրինակ կարելի է գտնել Demo.

Իմաստ ունի ստուգումներ կատարել չօգտագործված կամ բացակայող ինդեքսների, ինչպես նաև bloat-ի համար միայն իրական տվյալների բազայում: Հավաքված արժեքները կարող են գրանցվել clickhouse կամ ուղարկվել մոնիտորինգի համակարգ:

Ես իսկապես հույս ունեմ, որ pg-index-health օգտակար և պահանջված կլինի: Դուք կարող եք նաև նպաստել գրադարանի զարգացմանը՝ հաղորդելով ձեր գտած խնդիրների մասին և առաջարկելով նոր ախտորոշում:

Source: www.habr.com