ProHoster > Blogs > Administrācija > PostgreSQL indeksu stāvoklis Java izstrādātāja skatījumā

PostgreSQL indeksu stāvoklis Java izstrādātāja skatījumā

Sveicieni.

Mani sauc Vanja, un es esmu Java izstrādātājs. Gadās, ka es daudz strādāju ar PostgreSQL - izveidoju datubāzi, optimizēju struktūru, veiktspēju un nedēļas nogalēs spēlēju nedaudz DBA.

Nesen esmu sakārtojis vairākas datu bāzes mÅ«su mikropakalpojumos un uzrakstÄ«jis java bibliotēku pg-index-veselÄ«ba, kas atvieglo Å”o darbu, ietaupa man laiku un palÄ«dz izvairÄ«ties no dažām izplatÄ«tām kļūdām, ko pieļauj izstrādātāji. TieÅ”i par Å”o bibliotēku mēs Å”odien runāsim.

PostgreSQL indeksu stāvoklis Java izstrādātāja skatījumā

Atbildības noraidīŔana

Galvenā PostgreSQL versija, ar kuru es strādāju, ir 10. Visi manis izmantotie SQL vaicājumi ir arÄ« pārbaudÄ«ti 11. versijā. Minimālā atbalstÄ«tā versija ir 9.6.

Aizvēsture

Viss sākās gandrīz pirms gada ar situāciju, kas man bija dīvaina: konkurētspējīga indeksa izveide no zila gaisa beidzās ar kļūdu. Pats rādītājs, kā parasti, palika datubāzē nederīgā stāvoklī. Baļķu analīze parādīja trūkumu temp_file_limit. Un dodamies ceļā... Rakoties dziļāk, es atklāju veselu kaudzi problēmu datu bāzes konfigurācijā un, atrotījis piedurknes, ar dzirksti acīs sāku tās labot.

Pirmā problēma - noklusējuma konfigurācija

Laikam jau visiem ir diezgan apnikusi metafora par Postgres, ko var darbināt uz kafijas automāta, bet... noklusējuma konfigurācija tieŔām rada virkni jautājumu. Vismaz ir vērts pievērst uzmanÄ«bu apkopes_darba_atmiņa, temp_file_limit, paziņojums_noildze Šø lock_timeout.

MÅ«su gadÄ«jumā apkopes_darba_atmiņa bija noklusējuma 64 MB, un temp_file_limit kaut kas ap 2 GB - mums vienkārÅ”i nebija pietiekami daudz atmiņas, lai izveidotu indeksu uz lielas tabulas.

Tāpēc iekŔā pg-index-veselÄ«ba Es savācu sēriju taustiņu, manuprāt, parametri, kas bÅ«tu jākonfigurē katrai datubāzei.

Otrā problēma ā€“ dublēti indeksi

MÅ«su datu bāzes darbojas uz SSD diskdziņiem, un mēs to izmantojam HA-konfigurācija ar vairākiem datu centriem, galveno resursdatoru un n-reprodukciju skaits. Diska vieta mums ir ļoti vērtÄ«gs resurss; tas ir ne mazāk svarÄ«gi kā veiktspēja un CPU patēriņŔ. Tāpēc, no vienas puses, mums ir nepiecieÅ”ami indeksi ātrai lasÄ«Å”anai, un, no otras puses, mēs nevēlamies datu bāzē redzēt nevajadzÄ«gus indeksus, jo tie aizņem vietu un palēnina datu atjaunināŔanu.

Un tagad, visu atjaunojis nederÄ«gi indeksi un pietiekami daudz redzējis ziņo Oļegs Bartunovs, es nolēmu noorganizēt ā€œlieloā€ tÄ«rÄ«Å”anu. IzrādÄ«jās, ka izstrādātājiem nepatÄ«k lasÄ«t datu bāzes dokumentāciju. Viņiem tas ļoti nepatÄ«k. Å Ä« iemesla dēļ rodas divas tipiskas kļūdas - manuāli izveidots indekss primārajā atslēgā un lÄ«dzÄ«gs ā€œmanuālsā€ indekss unikālā kolonnā. Fakts ir tāds, ka tie nav vajadzÄ«gi - Postgres visu izdarÄ«s pats. Šādus indeksus var droÅ”i dzēst, un Å”im nolÅ«kam ir parādÄ«jusies diagnostika dublēti_indeksi.

TreŔā problēma ā€“ krustojoÅ”ie indeksi

Lielākā daļa iesācēju izstrādātāju veido indeksus vienā kolonnā. Pamazām, pamatÄ«gi pieredzējuÅ”i Å”o biznesu, cilvēki sāk optimizēt savus vaicājumus un pievienot sarežģītākus indeksus, kas ietver vairākas kolonnas. Šādi parādās indeksi kolonnās A, A + B, A+B+C. un tā tālāk. Pirmos divus no Å”iem indeksiem var droÅ”i izmest, jo tie ir treŔā prefiksi. Tas arÄ« ietaupa daudz vietas diskā, un tam ir diagnostika krustoti_indeksi.

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

Postgres ļauj izveidot ārējās atslēgas ierobežojumus, nenorādot atbalsta indeksu. Daudzās situācijās tā nav problēma un var pat neizpausties... Pagaidām...

Ar mums bija tāpat: tikai kādā brÄ«dÄ« galvenais saimnieks mums sāka ā€œpievienotā€ darbu, kas darbojas pēc grafika un notÄ«ra testa pasÅ«tÄ«jumu datubāzi. CPU un IO aizgāja velti, pieprasÄ«jumi palēninājās un tika beidzies, pakalpojums bija pieci simti. Ātra analÄ«ze pg_stat_activity parādÄ«ja, ka tādi vaicājumi kā:

delete from <table> where id in (ā€¦)

Šajā gadījumā, protams, mērķa tabulā bija indekss pēc id, un saskaņā ar nosacījumu tika izdzēsti ļoti maz ierakstu. Šķita, ka visam vajadzētu darboties, bet diemžēl tas nenotika.

BrÄ«niŔķīgais nāca palÄ«gā izskaidrot analÄ«zi un teica, ka papildus ierakstu dzÄ“Å”anai mērÄ·a tabulā ir arÄ« atsauces integritātes pārbaude, un vienā no saistÄ«tajām tabulām Ŕī pārbaude neizdodas. secÄ«ga skenÄ“Å”ana piemērota indeksa trÅ«kuma dēļ. Tā radās diagnostika sveÅ”as_atslēgas_bez_indeksa.

Piektā problēma ā€“ nulles vērtÄ«ba indeksos

Pēc noklusējuma Postgres btree indeksos iekļauj nulles vērtÄ«bas, taču tās parasti tur nav vajadzÄ«gas. Tāpēc es cÄ«tÄ«gi cenÅ”os izmest Ŕīs nulles (diagnostika indeksi_ar_null_vērtÄ«bām), izveidojot daļējus indeksus nullējamām kolonnām pēc veida where <A> is not null. Tādā veidā es varēju samazināt viena mÅ«su indeksa izmēru no 1877 MB lÄ«dz 16 KB. Un vienā no pakalpojumiem datu bāzes lielums kopumā samazinājās par 16% (absolÅ«tos skaitļos par 4.3 GB), jo no indeksiem tika izslēgtas nulles. MilzÄ«gs diska vietas ietaupÄ«jums ar ļoti vienkārŔām modifikācijām. šŸ™‚

Sestā problēma ā€“ primāro atslēgu trÅ«kums

Mehānisma rakstura dēļ MVCC Postgresā Ŕāda situācija ir iespējama uzpÅ«stieskad jÅ«su tabulas lielums strauji pieaug lielā miruÅ”o ierakstu skaita dēļ. Es naivi ticēju, ka tas mums nedraudēs, un ar mÅ«su bāzi tas nenotiks, jo mēs, wow!!!, esam normāli izstrādātāji... Cik es biju stulba un naiva...

Kādu dienu viena brÄ«niŔķīga migrācija paņēma un atjaunināja visus ierakstus lielā un aktÄ«vi izmantotā tabulā. Mēs saņēmām +100 GB lÄ«dz galda izmēram no zila gaisa. Tas bija sasodÄ«ti kauns, bet ar to mÅ«su nelaimes nebeidzās. Pēc tam, kad 15 stundas vēlāk beidzās autovakuums uz Ŕī galda, kļuva skaidrs, ka fiziskā atraÅ”anās vieta neatgriezÄ«sies. Mēs nevarējām pārtraukt pakalpojumu un padarÄ«t VACUUM FULL, tāpēc mēs nolēmām izmantot pg_repack. Un tad izrādÄ«jās, ka pg_repack nezina, kā apstrādāt tabulas bez primārās atslēgas vai cita unikalitātes ierobežojuma, un mÅ«su tabulai nebija primārās atslēgas. Tā radās diagnostika tabulas_bez_primārās_atslēgas.

Bibliotēkas versijā 0.1.5 Ir pievienota iespēja apkopot datus no uzpūstām tabulām un indeksiem un laicīgi uz tiem reaģēt.

Septītā un astotā problēma - nepietiekami indeksi un neizmantoti indeksi

Å Ä«s divas diagnostikas metodes ir: tabulas_ar_trÅ«kstoÅ”iem_indeksiem Šø neizmantotie_indeksi ā€“ savā galÄ«gajā formā parādÄ«jās salÄ«dzinoÅ”i nesen. Lieta ir tāda, ka tos nevarēja vienkārÅ”i paņemt un pievienot.

Kā jau rakstÄ«ju, mēs izmantojam konfigurāciju ar vairākām replikām, un lasÄ«Å”anas slodze uz dažādiem saimniekiem bÅ«tiski atŔķiras. Rezultātā situācija izrādās, ka dažas tabulas un indeksi dažos saimniekdatoros praktiski netiek izmantotas, un analÄ«zei ir jāapkopo statistika no visiem klastera resursdatoriem. AtiestatÄ«t statistiku Tas ir nepiecieÅ”ams arÄ« katrā klastera saimniekdatorā; to nevar izdarÄ«t tikai galvenajā resursdatorā.

Å Ä« pieeja ļāva mums ietaupÄ«t vairākus desmitus gigabaitu, noņemot indeksus, kas nekad netika izmantoti, kā arÄ« pievienojot trÅ«kstoÅ”os indeksus reti izmantotajām tabulām.

Kā secinājums

Protams, gandrÄ«z visu diagnostiku varat konfigurēt izslēgÅ”anas saraksts. Tādā veidā jÅ«s varat ātri ieviest pārbaudes savā lietojumprogrammā, novērÅ”ot jaunu kļūdu parādÄ«Å”anos, un pēc tam pakāpeniski novērst vecās.

Dažu diagnostiku var veikt funkcionālās pārbaudēs tÅ«lÄ«t pēc datu bāzes migrācijas ievieÅ”anas. Un Ŕī, iespējams, ir viena no manas bibliotēkas jaudÄ«gākajām funkcijām. LietoÅ”anas piemēru var atrast demo.

Ir lietderÄ«gi pārbaudÄ«t neizmantotos vai trÅ«kstoÅ”os indeksus, kā arÄ« uzpÅ«sties, tikai reālā datu bāzē. Savāktās vērtÄ«bas var ierakstÄ«t NoklikŔķiniet uz Māja vai nosÅ«tÄ«ts uz uzraudzÄ«bas sistēmu.

Es to ļoti ceru pg-index-veselība būs noderīgi un pieprasīti. Varat arī dot ieguldījumu bibliotēkas attīstībā, ziņojot par atrastajām problēmām un iesakot jaunas diagnostikas metodes.

Avots: www.habr.com

Pievieno komentāru