Sante endèks nan PostgreSQL nan je yon pwomotè Java

Hi

Non mwen se Vanya e mwen se yon devlopè Java. Sa rive ke mwen travay anpil ak PostgreSQL - mete kanpe baz done a, optimize estrikti a, pèfòmans, ak jwe yon ti DBA nan wikenn yo.

Dènyèman mwen te ranje plizyè baz done nan mikwosèvis nou yo epi mwen te ekri yon bibliyotèk java pg-index-sante, ki fè travay sa a pi fasil, ekonomize m 'tan epi ede m' evite kèk erè komen fè pa devlopè yo. Se bibliyotèk sa n ap pale jodi a.

Avètisman

Vèsyon prensipal PostgreSQL mwen travay ak se 10. Tout demann SQL mwen itilize yo teste tou sou vèsyon 11. Vèsyon minimòm sipòte se 9.6.

pre-istwa

Li tout te kòmanse prèske yon ane de sa ak yon sitiyasyon ki te etranj pou mwen: kreyasyon an konpetitif nan yon endèks soti nan ble a te fini ak yon erè. Endèks nan tèt li, kòm dabitid, rete nan baz done a nan yon eta valab. Log analiz te montre yon mank temp_file_limit. Epi nou ale... Fouye pi fon, mwen dekouvri yon pakèt pwoblèm nan konfigirasyon baz done a epi, woule chemiz manch mwen, mwen te kòmanse ranje yo ak yon klere nan je m '.

Pwoblèm yon sèl - konfigirasyon default

Pwobableman tout moun se trè fatige ak metafò a sou Postgres, ki ka kouri sou yon Maker kafe, men ... konfigirasyon an default reyèlman ogmante yon kantite kesyon. Nan yon minimòm, li vo peye atansyon a antretyen_travay_mem, temp_file_limit, statement_timeout и lock_timeout.

Nan ka nou an antretyen_travay_mem te default 64 MB, ak temp_file_limit yon bagay alantou 2 GB - nou tou senpleman pa t 'gen ase memwa yo kreye yon endèks sou yon tab gwo.

Se poutèt sa, nan pg-index-sante Mwen kolekte yon seri kle, nan opinyon mwen, paramèt yo ki ta dwe configuré pou chak baz done.

Pwoblèm de - endis kopi

Baz done nou yo ap viv sou kondui SSD, epi nou itilize HA-konfigirasyon ak plizyè sant done, mèt lame ak n-kantite kopi. Espas disk se yon resous ki gen anpil valè pou nou; li pa mwens enpòtan pase pèfòmans ak konsomasyon CPU. Se poutèt sa, sou yon bò, nou bezwen endèks pou lekti rapid, ak sou lòt men an, nou pa vle wè endèks ki pa nesesè nan baz done a, paske yo manje espas ak ralanti aktyalizasyon done yo.

Epi kounyeya, li te retabli tout bagay endèks envalid epi li te wè ase rapò pa Oleg Bartunov, Mwen deside òganize yon "gwo" purge. Li te tounen soti ke devlopè pa renmen li dokiman baz done. Yo pa renmen li anpil. Poutèt sa, de erè tipik parèt - yon endèks ki te kreye manyèlman sou yon kle prensipal ak yon endèks "manyèl" menm jan an sou yon kolòn inik. Reyalite a se ke yo pa nesesè - Postgres pral fè tout bagay poukont li. Endis sa yo ka san danje efase, ak dyagnostik yo te parèt pou objektif sa a duplicated_indexes.

Pwoblèm twa - entèseksyon endis

Pifò devlopè inisyasyon kreye endèks sou yon kolòn sèl. Piti piti, yo te byen goute biznis sa a, moun yo kòmanse optimize demann yo epi ajoute endis pi konplèks ki gen ladan plizyè kolòn. Men ki jan endèks sou kolòn parèt A, A + B., A + B + C ak sou sa. De premye nan endis sa yo ka san danje jete deyò, paske yo se prefiks twazyèm lan. Sa a tou sove yon anpil nan espas ki gen kapasite ak gen dyagnostik pou sa a entèsekte_indexes.

Pwoblèm kat - kle etranje san endèks

Postgres pèmèt ou kreye kontrent kle etranje san yo pa espesifye yon endèks fè bak. Nan anpil sitiyasyon sa a pa yon pwoblèm, epi li ka pa menm manifeste tèt li... Pou le moman...

Se te menm bagay la avèk nou: li jis ke nan kèk pwen nan tan yon travay, kouri dapre yon orè ak netwaye baz done a nan lòd tès yo, te kòmanse "te ajoute" nan nou pa lame a mèt. CPU ak IO te ale nan fatra, demann yo ralanti epi yo te tan soti, sèvis la te senksan. Analiz rapid pg_stat_aktivite te montre ke demann tankou:

delete from <table> where id in (…)

Nan ka sa a, nan kou, te gen yon endèks pa id nan tablo sib la, ak anpil kèk dosye yo te efase dapre kondisyon an. Li te sanble ke tout bagay ta dwe travay, men, Ay, li pa t '.

Yon bèl bagay te vin pote sekou eksplike analize e li te di ke anplis efase dosye nan tab sib la, gen tou yon chèk entegrite referans, epi sou youn nan tab ki gen rapò chèk sa a echwe. eskanè sekans akòz mank de yon endèks apwopriye. Se konsa dyagnostik te fèt etranje_kle_san_endèks.

Pwoblèm senk - valè nil nan endèks

Pa default, Postgres gen ladan valè nil nan endèks btree, men yo anjeneral pa nesesè la. Se poutèt sa, mwen dilijans eseye jete sa yo nil (dyagnostik indexes_with_null_values), kreye endèks pasyèl sou kolòn nullable pa kalite where <A> is not null. Nan fason sa a mwen te kapab redwi gwosè a nan youn nan endis nou yo soti nan 1877 MB a 16 KB. Ak nan youn nan sèvis yo, gwosè baz done a diminye nan total pa 16% (pa 4.3 GB nan nimewo absoli) akòz esklizyon nan valè nil nan endèks yo. Enorme ekonomi nan espas ki gen kapasite ak modifikasyon trè senp. 🙂

Pwoblèm sis - mank de kle prensipal

Akòz nati mekanis lan MVCC nan Postgres yon sitiyasyon konsa posib gonflelè gwosè tab ou a ap grandi rapidman akòz yon gwo kantite dosye ki mouri. Mwen nayivman te kwè ke sa pa t ap menase nou, e ke sa a pa ta rive nan baz nou an, paske nou, wow!!!, se devlopè nòmal ... Ala sòt ak nayif mwen te ...

Yon jou, yon bèl migrasyon te pran ak mete ajou tout dosye yo nan yon tab gwo ak aktivman itilize. Nou te resevwa + 100 GB nan gwosè tab la soti nan ble a. Se te yon wont modi, men misadventures nou yo pa te fini la. Apre otovakyòm nan sou tab sa a te fini 15 èdtan pita, li te vin klè ke kote fizik la pa ta retounen. Nou pa t 'kapab sispann sèvis la ak fè VACUUM FULL, se konsa nou deside itilize pg_repack. Lè sa a, li te tounen soti sa pg_repack pa konnen ki jan yo trete tab san yon kle prensipal oswa lòt kontrent singularité, ak tab nou an pa t 'gen yon kle prensipal. Se konsa dyagnostik te fèt tables_without_primary_key.

Nan vèsyon bibliyotèk la 0.1.5 Kapasite pou kolekte done ki soti nan bloat nan tab ak endèks epi reponn a li nan yon fason apwopriye te ajoute.

Pwoblèm sèt ak uit - endèks ensifizan ak endèks rès

De dyagnostik sa yo se: tables_with_missing_indexes и unused_indexes – parèt nan fòm final yo relativman dènyèman. Pwen an se ke yo pa t 'kapab jis pran ak ajoute.

Kòm mwen te deja ekri, nou itilize yon konfigirasyon ak plizyè kopi, ak chaj la lekti sou diferan lame se fondamantalman diferan. Kòm yon rezilta, sitiyasyon an vire soti ke kèk tab ak endèks sou kèk lame yo pratikman pa itilize, epi pou analiz ou bezwen kolekte estatistik ki soti nan tout gen tout pouvwa a nan gwoup la. Reyajiste estatistik Sa nesesè tou sou chak lame nan gwoup la; ou pa ka fè sa sèlman sou mèt la.

Apwòch sa a te pèmèt nou sove plizyè dizèn jigokte lè nou retire endèks ki pa t janm itilize, ansanm ak ajoute endèks ki manke nan tab yo itilize raman.

Kòm yon konklizyon

Natirèlman, pou prèske tout dyagnostik ou ka konfigirasyon lis esklizyon. Nan fason sa a, ou ka byen vit aplike chèk nan aplikasyon w lan, anpeche nouvo erè parèt, epi apresa ranje ansyen erè piti piti.

Gen kèk dyagnostik ka fèt nan tès fonksyonèl imedyatman apre woule soti migrasyon baz done. Ak sa a se petèt youn nan karakteristik ki pi pwisan nan bibliyotèk mwen an. Ou ka jwenn yon egzanp itilizasyon nan Demo.

Li fè sans pou fè chèk pou endèks ki pa itilize oswa ki manke, osi byen ke pou gonfleman, sèlman sou yon baz done reyèl. Valè yo kolekte ka anrejistre nan Klike sou Kay oswa voye nan sistèm siveyans lan.

Mwen vrèman espere sa pg-index-sante pral itil ak nan demann. Ou kapab tou kontribye nan devlopman bibliyotèk la lè w rapòte pwoblèm ou jwenn epi sigjere nouvo dyagnostik.

Sous: www.habr.com