{"id":72875,"date":"2020-03-05T08:42:09","date_gmt":"2020-03-05T05:42:09","guid":{"rendered":"https:\/\/prohoster.info\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika"},"modified":"2020-03-05T08:42:09","modified_gmt":"2020-03-05T05:42:09","slug":"zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","status":"publish","type":"post","link":"https:\/\/prohoster.info\/sl\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","title":{"rendered":"Zdravje indeksov v PostgreSQL skozi o\u010di Java razvijalca","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\u017divjo.<\/p>\n

<\/p>\n

Moje ime je Vanya in sem Java razvijalec. Na \u017ealost sem veliko dela z PostgreSQL \u2013 ukvarjam se z nastavitvijo Baze podatkov, optimizacijo strukture, zmogljivosti in malo igram DBA ob koncih tedna.<\/p>\n

<\/p>\n

V zadnjem \u010dasu sem uredil nekaj baz podatkov v na\u0161ih mikroservisih in napisal java knji\u017enico pg-index-health<\/a><\/noindex>, ki olaj\u0161a to delo, prihrani moj \u010das in pomaga prepre\u010diti nekatere obi\u010dajne napake, ki jih razvijalci pogosto delajo. Danes bo govora prav o tej knji\u017enici.<\/p>\n

<\/p>\n

\"Zdravje<\/p>\n

<\/p>\n

Odricanje od odgovornosti<\/h3>\n

<\/p>\n

Osnovna razli\u010dica PostgreSQL, s katero delam, je 10. Vse SQL poizvedbe, ki jih uporabljam, so bile prav tako preverjene na 11. razli\u010dici. Najmanj podprta razli\u010dica je 9.6.<\/p>\n

<\/p>\n

Pozadina<\/h3>\n

<\/p>\n

Vse se je za\u010delo skoraj pred letom dni s \u010dudno situacijo zame: konkuren\u010dna ustvaritev indeksa na praznem mestu se je kon\u010dala z napako. Sam indeks, kot ponavadi, je ostal v neveljavnem stanju v bazi. Analiza dnevnikov je pokazala pomanjkanje temp_file_limit<\/a><\/noindex>. In potem je \u0161lo vse naprej... Ko sem se potopil globlje, sem odkril celo vrsto te\u017eav v konfiguraciji Baze podatkov, in z zavihanimi rokavi ter sijajem v o\u010deh sem se lotil njihovega popravila.<\/p>\n

<\/a><\/noindex><\/p>\n

Prva te\u017eava \u2013 privzeta konfiguracija<\/h3>\n

<\/p>\n

Verjetno je \u017ee postala metafora o Postgresu, ki ga lahko za\u017eene\u0161 na kavnih avtomatih, vsem dokaj nadle\u017ena, vendar\u2026 privzeta konfiguracija dejansko vzbuja vrsto vpra\u0161anj. Minimalno bi morali obrniti pozornost na maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> in lock_timeout<\/strong>.<\/p>\n

<\/p>\n

V na\u0161em primeru maintenance_work_mem<\/strong> je bil privzeto 64 MB, a temp_file_limit<\/strong> okoli 2 GB \u2013 preprosto nam je zmanjkalo pomnilnika za ustvarjanje indeksa na veliki tabeli.<\/p>\n

<\/p>\n

Zato sem v pg-index-health<\/strong> zbral vrsto klju\u010dnih<\/a><\/noindex>, po mojem mnenju, parametrov, ki jih je vredno nastaviti za vsako Bazo podatkov.<\/p>\n

<\/p>\n

Druga te\u017eava \u2013 podvojeni indeksi<\/h3>\n

<\/p>\n

Na\u0161e baze delujejo na SSD diskih, in uporabljamo HA<\/em>-konfiguracijo z ve\u010d podatkovnimi centri, glavno gostiteljsko napravo in n<\/em>-\u0161tevilom replik. Prostor na disku je za nas zelo dragocen vir; ni manj pomemben od zmogljivosti in porabe CPU. Zato, po eni strani, potrebujemo indekse za hitro branje, po drugi strani pa ne \u017eelimo videti odve\u010dnih indeksov v Bazi podatkov, saj ti zavzemajo prostor in upo\u010dasnjujejo posodobitve podatkov.<\/p>\n

<\/p>\n

In tako, ko sem obnovil vse neveljavne indekse<\/a><\/noindex> in se natan\u010dno ogledal poro\u010devalce Olega Bartunova<\/a><\/noindex>, odlo\u010dil sem se za 'veliko' \u010di\u0161\u010denje. Izkazalo se je, da razvijalci ne marajo brati dokumentacije za bazo podatkov. Zelo ne marajo. Zaradi tega nastaneta dve tipi\u010dni napaki \u2013 ro\u010dno ustvarjena indeksa na primarni klju\u010d in podoben 'ro\u010dni' indeks na unikatni stolpec. Gre za to, da niso potrebni \u2013 Postgres vse naredi sam. Te indekse lahko brez skrbi odstranite in za to je na voljo diagnostika. duplicirani_indeksi<\/a><\/noindex>.<\/p>\n

<\/p>\n

Tretja te\u017eava \u2013 prekrivni indeksi<\/h3>\n

<\/p>\n

Ve\u010dina za\u010detnih razvijalcev ustvarja indekse na en stolpec. S\u010dasoma, ko za\u010dnejo to zadevo okusiti, ljudje za\u010dnejo optimizirati svoje poizvedbe in dodajati bolj kompleksne indekse, ki vklju\u010dujejo ve\u010d stolpcev. Tako se pojavijo indeksi na stolpcih A<\/em>, A+B<\/em>, A+B+C<\/em> itd. Prva dva od teh indeksov lahko brez skrbi izbri\u0161ete, saj sta prefiksi tretjega. To prav tako precej prihrani prostor na disku in za to je na voljo diagnostika. prekrivni_indeksi<\/a><\/noindex>.<\/p>\n

<\/p>\n

\u010cetrta te\u017eava \u2013 zunanje klju\u010de brez indeksov<\/h3>\n

<\/p>\n

Postgres omogo\u010da ustvarjanje omejitev zunanjega klju\u010da brez navedbe podpornega indeksa. V mnogih situacijah to ni te\u017eava in se celo lahko ne poka\u017ee\u2026 Do dolo\u010dene mere\u2026<\/p>\n

<\/p>\n

Tako je bilo tudi pri nas: preprosto je v nekem trenutku job, ki se izvaja po razporedu in \u010disti bazo od testnih naro\u010dil, za\u010del 'zlagati' na\u0161 glavni stre\u017enik. CPU in IO sta letela v vi\u0161ave, poizvedbe so se upo\u010dasnile in prekinile zaradi \u010dasovne omejitve, storitev je izvajala napako 500. Hiter pregled pg_stat_activity<\/a><\/noindex> je pokazal, da so se ustavljale poizvedbe tipa:<\/p>\n

<\/p>\n

izbriši iz <table> kjer je id v (…)<\/code><\/pre>\n
<\/p>\n
Ob tem je bil indeks po id v ciljni tabeli, seveda, prisoten in zapisov je bilo odstranjevanih po pogojih \u010disto malo. Zdi se, da bi vse moralo delovati, a, \u017eal, ni delovalo.<\/p>\n
<\/p>\n
Na pomo\u010d je pri\u0161el \u010dude\u017eni explain analyze<\/strong> in povedal, da poleg odstranjevanja zapisov v ciljnosti tabeli poteka \u0161e preverjanje referen\u010dne celovitosti, in na eni izmed povezanih tabel ta preverjanje zru\u0161i v sekven\u010dno skeniranje<\/em> zaradi pomanjkanja ustreznega indeksa. Tako je nastala diagnostika zunanjih_klju\u010dev_brez_indeksov<\/a><\/noindex>.<\/p>\n
<\/p>\n
Peta te\u017eava \u2013 null vrednosti v indeksih<\/h3>\n
<\/p>\n
Po privzetku Postgres vklju\u010duje null vrednosti v btree-indekse, toda tam ponavadi niso potrebni. Zato si prizadevam te null vrednosti izlo\u010diti (diagnostika indeksi_z_null_vrednostmi<\/a><\/noindex>), ustvarjam delne indekse na nullable-stolpcih tipa where  is not null<\/code>. Na ta na\u010din sem uspel zmanj\u0161ati velikost ene na\u0161ih indeksov s 1877 MB na 16 kB. V eni od storitev se je skupna velikost Baze podatkov zmanj\u0161ala za 16% (za 4,3 GB v absolutnih \u0161tevilkah) zaradi izklju\u010ditve null vrednosti iz indeksov. Kolosalna ekonomija diskovnega prostora pri precej enostavnih prilagoditvah. \ud83d\ude42 <\/p>\n
<\/p>\n
Te\u017eava \u0161est \u2013 odsotnost primarnih klju\u010dev<\/h3>\n
<\/p>\n
Zaradi posebnosti mehanizma MVCC v Postgresu<\/a><\/noindex> je mo\u017ena tak\u0161na situacija, kot bloat<\/a><\/noindex>, ko se velikost va\u0161e tabele hitro pove\u010duje zaradi velikega \u0161tevila mrtvih zapisov. Naivno sem verjel, da nas to ne ogro\u017ea, in da se to z na\u0161o bazo ne bo zgodilo, saj smo, ojoj!!!, normalni razvijalci... Kako neumen in naiven sem bil...<\/p>\n
<\/p>\n
Nekega lepega dne se je ena \u010dudovita migracija odlo\u010dila in posodila vse zapise v veliki in aktivno uporabljani tabeli. Dobili smo +100 GB k velikosti tabele kar tako. Bilo je grozno \u017ealostno, a na\u0161a zlo\u017eitja se tu niso kon\u010dala. Po 15-ih urah je avto vakuum na tej tabeli kon\u010dal, jasno je postalo, da fizi\u010dno mesto ne bo vrnjeno. Nismo mogli ustaviti storitve in narediti VACUUM FULL, zato smo se odlo\u010dili uporabiti pg_repack<\/a><\/noindex>. In tukaj se je izkazalo, da pg_repack<\/em> ne zna obdelovati tabel brez primarnega klju\u010da ali drugega omejitev edinstvenosti, na\u0161o tabelo pa primarni klju\u010d ni imela. Tako je nastala diagnostika tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
V razli\u010dici knji\u017enice 0.1.5<\/strong> je bila dodana mo\u017enost zbiranja podatkov o bloatu tabel in indeksov in pravo\u010dasno reagirati nanj.<\/p>\n
<\/p>\n
Te\u017eave sedem in osem \u2013 pomanjkanje indeksov in neuporabni indeksi<\/h3>\n
<\/p>\n
Naslednji dve diagnostiki \u2013 tables_with_missing_indexes<\/a><\/noindex> in unused_indexes<\/a><\/noindex> \u2013 sta se v svoji kon\u010dni obliki pojavili relativno nedavno. Gre za to, da jih ni bilo mogo\u010de enostavno dodati.<\/p>\n
<\/p>\n
Kot sem \u017ee pisal, uporabljamo konfiguracijo z ve\u010d replikami, in bralna obremenitev na razli\u010dnih gostiteljih je na\u010deloma zelo razli\u010dna. Rezultat je situacija, da se nekatere tabele in indeksi na nekaterih gostiteljih prakti\u010dno ne uporabljajo, in za analizo je potrebno zbirati statistiko z vseh gostiteljev v grozdu. Statistiko je<\/a><\/noindex> tudi treba ponastaviti na vsakem gostitelju v grozdu, tega ni mogo\u010de storiti samo na masterju.<\/p>\n
<\/p>\n
Taka metoda nam je omogu\u0107ila da u\u0161tedimo nekoliko desetina gigabajta uklanjanjem indeksa koji nikada nisu kori\u0161\u0107eni, kao i dodavanjem nedostaju\u0107ih indeksa na retko kori\u0161\u0107ene tabele.<\/p>\n
<\/p>\n
\u0412 \u043a\u0430\u0447\u0435\u0441\u0442\u0432\u0435 \u0437\u0430\u043a\u043b\u044e\u0447\u0435\u043d\u0438\u044f<\/h3>\n
<\/p>\n
Naravno, prakti\u010dno za sve dijagnostike mo\u017ee se podesiti lista izuzetaka<\/a><\/noindex>. Na taj na\u010din se mogu brzo implementirati provere u va\u0161oj aplikaciji, spre\u010davaju\u0107i pojavu novih gre\u0161aka, a zatim postepeno ispravljaju\u0107i stare.<\/p>\n
<\/p>\n
Deo dijagnostika mo\u017ee se obaviti ve\u0107 u funkcionalnim testovima odmah nakon uvo\u0111enja migracija Baze podataka. I to je, verovatno, jedna od najmo\u0107nijih mogu\u0107nosti moje biblioteke. Primer kori\u0161\u0107enja mo\u017eete pogledati u demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
Provere za neiskori\u0161\u0107ene ili nedostaju\u0107e indekse, kao i za bloat, smisleno je izvoditi samo na stvarnoj Bazi podataka. Prikupljene vrednosti mogu biti zapisane u ClickHouse<\/a><\/noindex> ili poslati u sistem za monitoring.<\/p>\n
<\/p>\n
Veoma se nadam da pg-index-health<\/strong> \u0107e biti korisna i tra\u017eena. Tako\u0111e mo\u017eete doprineti razvoju biblioteke prijavljivanjem otkrivenih problema i predlaganjem novih dijagnostika.<\/p>\n
Izvor: habr.com<\/a> <\/p>","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"excerpt":{"rendered":"
\u041f\u0440\u0438\u0432\u0435\u0442. \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0412\u0430\u043d\u044f, \u0438 \u044f Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a. \u0422\u0430\u043a \u043f\u043e\u043b\u0443\u0447\u0438\u043b\u043e\u0441\u044c, \u0447\u0442\u043e \u044f \u043c\u043d\u043e\u0433\u043e \u0440\u0430\u0431\u043e\u0442\u0430\u044e \u0441 PostgreSQL \u2013 \u0437\u0430\u043d\u0438\u043c\u0430\u044e\u0441\u044c \u043d\u0430\u0441\u0442\u0440\u043e\u0439\u043a\u043e\u0439 \u0411\u0414, \u043e\u043f\u0442\u0438\u043c\u0438\u0437\u0430\u0446\u0438\u0435\u0439 \u0441\u0442\u0440\u0443\u043a\u0442\u0443\u0440\u044b, \u043f\u0440\u043e\u0438\u0437\u0432\u043e\u0434\u0438\u0442\u0435\u043b\u044c\u043d\u043e\u0441\u0442\u044c\u044e \u0438 \u043d\u0435\u043c\u043d\u043e\u0433\u043e \u0438\u0433\u0440\u0430\u044e \u0432 DBA \u043f\u043e \u0432\u044b\u0445\u043e\u0434\u043d\u044b\u043c. \u0417\u0430 \u043f\u043e\u0441\u043b\u0435\u0434\u043d\u0435\u0435 \u0432\u0440\u0435\u043c\u044f \u044f \u043f\u0440\u0438\u0432\u0451\u043b \u0432 \u043f\u043e\u0440\u044f\u0434\u043e\u043a \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e \u0431\u0430\u0437 \u0434\u0430\u043d\u043d\u044b\u0445 \u0432 \u043d\u0430\u0448\u0438\u0445 \u043c\u0438\u043a\u0440\u043e\u0441\u0435\u0440\u0432\u0438\u0441\u0430\u0445 \u0438 \u043d\u0430\u043f\u0438\u0441\u0430\u043b java-\u0431\u0438\u0431\u043b\u0438\u043e\u0442\u0435\u043a\u0443 pg-index-health, \u043a\u043e\u0442\u043e\u0440\u0430\u044f \u043e\u0431\u043b\u0435\u0433\u0447\u0430\u0435\u0442 \u044d\u0442\u0443 \u0440\u0430\u0431\u043e\u0442\u0443, \u044d\u043a\u043e\u043d\u043e\u043c\u0438\u0442 \u043c\u043e\u0451 \u0432\u0440\u0435\u043c\u044f \u0438 \u043f\u043e\u043c\u043e\u0433\u0430\u0435\u0442 […]<\/p>\n","protected":false,"gt_translate_keys":[{"key":"rendered","format":"html"}]},"author":1,"featured_media":72876,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[688],"tags":[],"class_list":["post-72875","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administrirovanie"],"aioseo_notices":[],"aioseo_head":"\n\t\t\n\t\n\t\n\t\n\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\t\t\n\n","aioseo_head_json":{"title":"\ud83e\udd47Zdravlje indeksa u PostgreSQL o\u010dima Java programera | ProHoster","description":"Zdravo. Zovem se Vanja i ja sam Java programer.","canonical_url":"https:\/\/prohoster.info\/sl\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sl_SI","og:site_name":"ProHoster | \u041a\u0443\u043f\u0438\u0442\u044c \u043d\u0430\u0434\u0435\u0436\u043d\u044b\u0439 \u0445\u043e\u0441\u0442\u0438\u043d\u0433 \u0434\u043b\u044f \u0441\u0430\u0439\u0442\u043e\u0432 \u0441 \u0437\u0430\u0449\u0438\u0442\u043e\u0439 \u043e\u0442 DDoS, VPS VDS \u0441\u0435\u0440\u0432\u0435\u0440\u044b","og:type":"article","og:title":"\ud83e\udd47\u0417\u0434\u043e\u0440\u043e\u0432\u044c\u0435 \u0438\u043d\u0434\u0435\u043a\u0441\u043e\u0432 \u0432 PostgreSQL \u0433\u043b\u0430\u0437\u0430\u043c\u0438 Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a\u0430 | ProHoster","og:description":"\u041f\u0440\u0438\u0432\u0435\u0442. \u041c\u0435\u043d\u044f \u0437\u043e\u0432\u0443\u0442 \u0412\u0430\u043d\u044f, \u0438 \u044f Java-\u0440\u0430\u0437\u0440\u0430\u0431\u043e\u0442\u0447\u0438\u043a.","og:url":"https:\/\/prohoster.info\/sl\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","og:image":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:secure_url":"https:\/\/prohoster.info\/wp-content\/uploads\/2021\/11\/logo-350.jpg","og:image:width":350,"og:image:height":350,"article:published_time":"2020-03-05T05:42:09+00:00","article:modified_time":"2020-03-05T05:42:09+00:00","article:publisher":"https:\/\/www.facebook.com\/prohoster","article:author":"https:\/\/www.facebook.com\/prohoster"},"aioseo_meta_data":{"post_id":"72875","title":null,"description":null,"keywords":null,"keyphrases":null,"primary_term":null,"canonical_url":null,"og_title":null,"og_description":null,"og_object_type":"default","og_image_type":"default","og_image_url":null,"og_image_width":null,"og_image_height":null,"og_image_custom_url":null,"og_image_custom_fields":null,"og_video":null,"og_custom_url":null,"og_article_section":null,"og_article_tags":null,"twitter_use_og":false,"twitter_card":"default","twitter_image_type":"default","twitter_image_url":null,"twitter_image_custom_url":null,"twitter_image_custom_fields":null,"twitter_title":null,"twitter_description":null,"schema":{"blockGraphs":[],"customGraphs":[],"default":{"data":{"Article":[],"Course":[],"Dataset":[],"FAQPage":[],"Movie":[],"Person":[],"Product":[],"ProductReview":[],"Car":[],"Recipe":[],"Service":[],"SoftwareApplication":[],"WebPage":[]},"graphName":"","isEnabled":true},"graphs":[]},"schema_type":null,"schema_type_options":null,"pillar_content":false,"robots_default":true,"robots_noindex":false,"robots_noarchive":false,"robots_nosnippet":false,"robots_nofollow":false,"robots_noimageindex":false,"robots_noodp":false,"robots_notranslate":false,"robots_max_snippet":null,"robots_max_videopreview":null,"robots_max_imagepreview":"large","priority":null,"frequency":null,"local_seo":null,"seo_analyzer_scan_date":null,"breadcrumb_settings":null,"limit_modified_date":false,"reviewed_by":null,"ai":null,"created":"2021-02-28 18:43:29","updated":"2022-09-27 23:10:08","focus_keyword":null,"additional_keywords":null,"truseo_locale":null},"gt_translate_keys":[{"key":"link","format":"url"}],"_links":{"self":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/posts\/72875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/comments?post=72875"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/posts\/72875\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/media\/72876"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/media?parent=72875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/categories?post=72875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sl\/wp-json\/wp\/v2\/tags?post=72875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}