PostgreSQL indeksu veselība Java izstrādātāja skatījumā

Sveiki.

Mani sauc Vanya, un esmu Java izstrādātājs. Tā ir iznācis, ka es daudz strādāju ar PostgreSQL – nodarbojos ar datu bāzu iestatīšanu, struktūras optimizēšanu, veiktspēju un nedaudz spēlēju DBA brīvdienās.

Pēdējā laikā es esmu sakārtojis vairākas datu bāzes mūsu mikroservisos un uzrakstījis java bibliotēku pg-index-health, kas atvieglo šo darbu, ietaupa manu laiku un palīdz izvairīties no dažām tipiskām kļūdām, ko pieļauj izstrādātāji. Par šo bibliotēku šodien arī būs runa.

Atsauksme

Galvenā PostgreSQL versija, ar kurā es strādāju, ir 10. Visas SQL vaicājumi, kurus izmantoju, ir pārbaudīti arī 11. versijā. Minimālā atbalstītā versija ir 9.6.

Iepriekšējā pieredze

Viss sākās pirms gandrīz gada ar dīvainu situāciju man: konkurējoša indeksa izveide beidzās ar kļūdu. Indekss, kā pierasts, palika nevalīdotajā stāvoklī datu bāzē. Analizējot žurnālus, izrādījās, ka trūkst temp_file_limit. Un sākās... Iedziļinoties, es atklāju veselu čupiņu problēmu datu bāzes konfigurācijā un, sasliekot piedurknes, ar spožumu acīs ķēros pie labošanas.

Pirmā problēma – noklusējuma konfigurācija.

Droši vien metafora par Postgres, kuru var uzsākt uz kafijas automāta, visiem jau ir apnikusi, bet... noklusējuma konfigurācija tiešām rada virkni jautājumu. Vismaz jāpievērš uzmanība maintenance_work_mem, temp_file_limit, statement_timeout un lock_timeout.

Mūsu gadījumā maintenance_work_mem bija noklusējuma 64 MB, bet temp_file_limit apmēram 2 GB – mums vienkārši trūka atmiņas, lai izveidotu indeksu lielā tabulā.

Tāpēc es pg-index-health saglabāju virkni atslēgas, manuprāt, parametru, kurus vērts konfigurēt katrai datu bāzei.

Otrā problēma – dublējošie indeksi.

Mūsu datu bāzes dzīvo uz SSD diskiem, un mēs izmantojam HA-konfigurāciju ar vairākiem datu centriem, galveno resursu un n-skaitu replikāciju. Vieta diskā – ļoti vērtīgs resurss mūsuprāt; tas ir ne mazāk svarīgs par veiktspēju un CPU patēriņu. Tāpēc, no vienas puses, mums ir nepieciešami indeksi ātrai lasīšanai, bet no otras puses, mēs nevēlamies redzēt datu bāzē liekus indeksus, jo tie patērē vietu un palēnina datu atjaunināšanu.

Un nu, atjaunojot visus nevalītiskos indeksus un noskatoties Olega Bartunova ziņojumus, es nolēmu veikt “lielu” tīrīšanu. Izrādījās, ka izstrādātāji nepatīk lasīt datu bāzes dokumentāciju. Viņi patiešām nepatīk. Tāpēc rodas divas tipiskas kļūdas – manuāli izveidots indekss uz primāro atslēgu un līdzīgs “rokām” izveidots indekss uz unikālo kolonnu. Problēma tāda, ka tie nav nepieciešami – Postgres visu izdarīs pats. Šādus indeksus var droši dzēst, un tāpēc ir parādījusies diagnostika duplicated_indexes.

Trešā problēma – pārklājoši indeksi

Lielākā daļa iesācēju izstrādātāju izveido indeksus uz vienu kolonnu. Pakāpeniski, iepazīstoties ar šo procesu, cilvēki sāk optimizēt savus vaicājumus un pievienot sarežģītākus indeksus, kas ietver vairākas kolonnas. Tā tiek izveidoti indeksi uz kolonnām A, A+B, A+B+C utt. Pirmajos divos no šiem indeksiem var droši izmest, jo tie ir trešā prefiksi. Tas arī ievērojami ietaupa vietu diskā, un tam ir diagnostika intersected_indexes.

Ceturtā problēma – ārējās atslēgas bez indeksiem

Postgres ļauj izveidot ārējo atslēgu ierobežojumus bez atbalsta indeksa norādīšanas. Daudzās situācijās tas nav problēma un pat neizpaužas… Līdz kādam brīdim…

Tā bija arī pie mums: vienkārši kādā brīdī nedēļas darbs, kas tika veikts pēc grafika un attīrīja datu bāzi no testēšanas pasūtījumiem, sāka “uzkrāt” mums mājas hostu. CPU un IO uzlēca debesīs, vaicājumi palēninājās un tika pārtraukta laika ierobežojuma dēļ, pakalpojums sniedza pieci simti. Ātra analīze pg_stat_activity parādīja, ka iestrēgusi vaicājumi veidā:

dzēst no <table> kur id ir (…)

Kā jau bija gaidīts, indekss pēc id mērķa tabulā, protams, bija, un ieraksti tika izdzēsti pēc nosacījuma pavisam maz. Izskatījās, ka viss jāstrādā, bet, diemžēl, nestrādāja.

Palīdzi nāca brīnišķīgā explain analyze un pastāstīja, ka papildus ierakstu dzēšanai mērķa tabulā notiek arī atsauces integritātes pārbaude, un kādā no saistītajām tabulām šī pārbaude pārvēršas par sequential scan sakarā ar piemērotā indeksa trūkumu. Tā radās diagnostika foreign_keys_without_index.

Piecā problēma – null vērtība indeksos

Pēc noklusējuma Postgres iekļauj null vērtības btree-indeksos, bet parasti tās tur nav nepieciešamas. Tāpēc es centīgi cenšos šos null iznīcināt (diagnostika indexes_with_null_values), izveidojot daļējos indeksus uz nullable-kolonām, piemēram where is not null. Šādā veidā man izdevās samazināt viena no mūsu indeksiem lielumu no 1877 MB līdz 16 KB. Turklāt vienā no pakalpojumiem datu bāzes lielums kopumā samazinājās par 16% (par 4.3 GB absolūtajās skaitļos) izslēdzot null vērtības no indeksiem. Kolosāla diska vietas ekonomija, veicot diezgan vienkāršas izmaiņas. 🙂

Sestā problēma – primāro atslēgu trūkums

Nepieciešamību MVCC Postgres’ā var rasties tāda situācija kā bloat, kad jūsu tabulas lielums strauji pieaug liela skaita mirušo ierakstu dēļ. Es naivi uzskatīju, ka tas mūs neskars, un ka ar mūsu datu bāzi tā nenotiks, jo mēs, ak vai!!!, esam normāli izstrādātāji… Cik muļķīgi un naivi es biju…

Vienā jaukā dienā viena brīnišķīga migrācija paņēma un atjaunoja visus ierakstus lielā un aktīvi izmantotā tabulā. Mēs ieguvām +100 GB tabulas lielumam no nekurienes. Tas bija ārkārtīgi nomācoši, bet mūsu likteņi uz to nebeidzās. Pēc tam, kad pēc 15 stundām automātiskā vakuuma process šajā tabulā beidzās, kļuva skaidrs, ka fiziskā vieta neatgriezīsies. Apturēt pakalpojumu un veikt VACUUM FULL mēs nevarējām, tāpēc tika pieņemts lēmums izmantot pg_repack. Un te izrādījās, ka pg_repack nezina, kā apstrādāt tabulas bez primārās atslēgas vai citas unikalitātes ierobežojuma, un mūsu tabulā nebija primārās atslēgas. Tā radās diagnostika tables_without_primary_key.

Bibliotēkas versijā 0.1.5 tika iekļauta iespēja vākt datus par tabulu un indeksu bloat un savlaicīgi reaģēt uz to.

Septītā un astotā problēma – indeksu trūkums un neizmantotie indeksi

Divas nākamās diagnostikas — tables_with_missing_indexes un unused_indexes – savā galīgajā formā radās salīdzinoši nesen. Lieta tāda, ka tās nevarēja vienkārši tā ņemt un pievienot.

Kā jau rakstīju, mēs izmantojam konfigurāciju ar vairākiem replikātiem, un lasīšanas slodze dažādos hostos ir principiāli atšķirīga. Rezultātā rodas situācija, ka dažas tabulas un indeksi dažos hostos praktiski nav izmantoti, un analīzei ir nepieciešams vākt statistiku no visiem hostiem klasterī. Statistiku arī ir jāsamaksā katrā hostā klasterī, nevar to izdarīt tikai uz meistaru.

Šāds piegājiens ļāva mums ietaupīt vairākus desmitus gigabaitu, noņemot indeksus, kas nekad netika izmantoti, kā arī pievienojot nepietiekamos indeksus retāk izmantotajām tabulām.

Noslēgumā

Protams, praktiski visām diagnostikām var iestatīt izņēmumu sarakstu. Tādā veidā var ātri ieviest pārbaudes jūsu lietojumprogrammā, novēršot jaunu kļūdu parādīšanos, un pēc tam pakāpeniski labot vecās.

Daļa diagnostiku var tikt veikta jau funkcionālajos testos tūlīt pēc datu bāzes migrāciju uzlikšanas. Un, iespējams, tā ir viena no spēcīgākajām manas bibliotēkas iespējām. Piemēru var apskatīt demo.

Pārbaudes par neizmantotiem vai trūkstošiem indeksiem, kā arī par bloat, ir jēgpilns veikt tikai uz reālas datu bāzes. Savāktās vērtības var ierakstīt ClickHouse vai nosūtīt uz monitoringa sistēmu.

Es ļoti ceru, ka pg-index-health būs noderīga un pieprasīta. Jūs arī varat veicināt bibliotēkas attīstību, ziņojot par atklātajām problēmām un piedāvājot jaunas diagnostikas.

Avots: habr.com