{"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\/et\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","title":{"rendered":"PostgreSQL indeksite tervis Java arendaja silmade l\u00e4bi","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Tere.<\/p>\n

<\/p>\n

Minu nimi on Vanya ja ma olen Java-arendaja. Olen t\u00f6\u00f6tanud palju PostgreSQL-iga \u2013 seadistades andmebaase, optimeerides struktuuri, t\u00f5husust ja m\u00e4ngides natuke DBA-d n\u00e4dalavahetustel.<\/p>\n

<\/p>\n

Viimase aja jooksul olen korrastanud mitmeid andmebaase meie mikroteenustes ja kirjutanud Java-raamatukogu pg-index-health<\/a><\/noindex>, mis lihtsustab seda t\u00f6\u00f6d, s\u00e4\u00e4stab minu aega ja aitab v\u00e4ltida m\u00f5ningaid t\u00fc\u00fcpilisi vigu, mida arendajad teevad. Just sellest raamatukogust r\u00e4\u00e4gime t\u00e4na.<\/p>\n

<\/p>\n

\"PostgreSQL<\/p>\n

<\/p>\n

M\u00e4rkus<\/h3>\n

<\/p>\n

Peamine PostgreSQL versioon, millega ma t\u00f6\u00f6tan, on 10. Olen kontrollinud ka k\u00f5iki kasutatavaid SQL-p\u00e4ringuid 11. versioonis. Minimumn\u00f5udlik versioon on 9.6.<\/p>\n

<\/p>\n

Eelalugu<\/h3>\n

<\/p>\n

K\u00f5ik algas peaaegu aasta tagasi kummalise olukorraga minu jaoks: konkurentsiv\u00f5imeline indekseerimise loomine t\u00fchjal kohal l\u00f5ppes veaga. Ise indeks, nagu ikka, j\u00e4i vale olekusse andmebaasis. Logide anal\u00fc\u00fcs n\u00e4itas, et puudus temp_file_limit<\/a><\/noindex>. Ja l\u00e4ks lahti... S\u00fcgavamale kaevudes leidsin hulga probleeme andmebaasi konfiguratsioonis ja \u00fcles\u00e4ratatud k\u00e4tega, s\u00e4demetega silmades, asusin neid parandama.<\/p>\n

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

Esimene probleem \u2013 vaikimisi konfiguratsioon<\/h3>\n

<\/p>\n

T\u00f5en\u00e4oliselt on Postgres'e kohviaparatuuriga k\u00e4ivitusmeetanid juba paljudele k\u00f5vasti k\u00f5rva j\u00e4\u00e4nud, kuid\u2026 vaikeseaded t\u00f5epoolest tekitavad mitmeid k\u00fcsimusi. Esiteks tasub t\u00e4helepanu p\u00f6\u00f6rata maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> ja lock_timeout<\/strong>.<\/p>\n

<\/p>\n

Meie puhul maintenance_work_mem<\/strong> oli vaikimisi 64 MB, kuid temp_file_limit<\/strong> umbes 2 GB \u2013 meil lihtsalt ei j\u00e4tkunud m\u00e4lu suure tabeli indeksi loomiseks.<\/p>\n

<\/p>\n

Seega pg-index-health<\/strong> koondasin m\u00f5ned olulised<\/a><\/noindex>, minu arvates, parameetrid, mida tuleks iga DB jaoks seadistada.<\/p>\n

<\/p>\n

Teine probleem \u2013 dubleeritud indeksid<\/h3>\n

<\/p>\n

Meie andmebaasid asuvad SSD kettadel ja kasutame HA<\/em>- konfiguratsiooni mitmete andmekeskustega, peahostiga ja n<\/em>- arvukate koopiatega. Kettaruumi on meie jaoks v\u00e4ga v\u00e4\u00e4rtuslik ressurss; see on sama oluline kui j\u00f5udlus ja CPU tarbimine. Seet\u00f5ttu vajame, \u00fchelt poolt, kiire lugemise jaoks indekseid, kuid teisalt ei soovi me DBs n\u00e4ha liigseid indekseid, kuna need kulutavad ruumi ja aeglustavad andmete v\u00e4rskendamist.<\/p>\n

<\/p>\n

Ja nii, taastades k\u00f5ik kehtetud indeksid<\/a><\/noindex> ja vaadates Oleg Bartunovi ettekandeid<\/a><\/noindex>, ma otsustasin korraldada \u00absuure\u00bb puhastuse. Selgus, et arendajad ei armasta andmebaasi dokumentatsiooni lugeda. Nad ei armasta seda \u00fcldse. Selle t\u00f5ttu tekivad kaks t\u00fc\u00fcpilist viga \u2013 k\u00e4sitsi loodud indeks primaarv\u00f5tmele ja sarnane \u00abk\u00e4sitsi\u00bb indeks unikaalsele veerule. Asi on selles, et need ei ole vajalikud \u2013 Postgres teeb k\u00f5ik ise. Selliseid indekse saab julgelt eemaldada ja selleks on v\u00e4lja t\u00f6\u00f6tatud diagnoos. duplicated_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Kolmas probleem \u2013 kattuvad indeksid<\/h3>\n

<\/p>\n

Enamik algajaid arendajaid loob indekseid \u00fche veeru p\u00f5hjal. Aja jooksul, peale selle ettev\u00f5tteks proovimist, hakkavad inimesed oma p\u00e4ringute optimeerimist laiendama ja lisama keerulisemaid indekseid, mis h\u00f5lmavad mitut veergu. Nii tekivad indeksid veergudele A<\/em>, A+B<\/em>, A+B+C<\/em> jne. Esimese kahe indeksi v\u00f5ib julgelt k\u00f5rvaldada, kuna need on kolmanda prefiksid. See s\u00e4\u00e4stab ka korralikult kettaruumi ja selle jaoks on v\u00e4lja kujundatud diagnoos. intersected_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Neljandaks probleemiks on v\u00e4lisv\u00f5tmed ilma indeksiteta.<\/h3>\n

<\/p>\n

Postgres v\u00f5imaldab luua v\u00e4lisv\u00f5tme piiranguid ilma toetava indeksita. Paljudes olukordades ei ole see probleem, ega pruugi isegi v\u00e4lja tulla... Kuni teatud hetkeni...<\/p>\n

<\/p>\n

Nii juhtus ka meiega: j\u00e4rsku hakkas ajastatud job, mis eemaldas testimisj\u00e4rgud andmebaasist, meil \u201eladuma\u201d peahostit. CPU ja IO t\u00f5usid lakke, p\u00e4ringud aeglustusid ja katkestasid aja\u00fclesande t\u00f5ttu, teenus andis 500 viga. Kiire anal\u00fc\u00fcs pg_stat_activity<\/a><\/noindex> n\u00e4itas, et seisma j\u00e4id p\u00e4ringud, mis n\u00e4gid v\u00e4lja nagu:<\/p>\n

<\/p>\n

kustuta <table> kus id on (…)<\/code><\/pre>\n
<\/p>\n
Samuti oli siht tabelis loomulikult indeks id j\u00e4rgi, ja kirjeid kustutati tingimuse alusel \u00fcsna v\u00e4he. Tundus, et k\u00f5ik peaks t\u00f6\u00f6tama, aga kahjuks ei t\u00f6\u00f6tanud.<\/p>\n
<\/p>\n
Appi tuli imeline explain analyze<\/strong> ja n\u00e4itas, et lisaks kirje kustutamisele siht tabelis tehakse veel viidete terviklikkuse kontroll ning \u00fche seotud tabeli osas see kontroll kukub j\u00e4rjestikusse skaneerimisse<\/em> sobiva indeksi puudumise t\u00f5ttu. Nii s\u00fcndis diagnoos foreign_keys_without_index<\/a><\/noindex>.<\/p>\n
<\/p>\n
Viies probleem \u2013 null v\u00e4\u00e4rtus indeksites<\/h3>\n
<\/p>\n
Vaikimisi sisaldab Postgres btree-indeksite seas null-v\u00e4\u00e4rtuseid, kuid need ei ole seal tavaliselt vajalikud. Seet\u00f5ttu p\u00fc\u00fcan ma null'id eemaldada (diagnostika indexes_with_null_values<\/a><\/noindex>), luues osalisi indekseid nullable-veergude t\u00fc\u00fcbi p\u00f5hjal where  is not null<\/code>. Sel viisil olen suutnud v\u00e4hendada \u00fche meie indeksi suurust 1877 MB-lt 16 KB-le. Ja \u00fches teenuses v\u00e4henes andmebaasi suurus kokku 16% (4.3 GB absoluutsetes numbrites) null-v\u00e4\u00e4rtuste indeksitest v\u00e4ljaarvamise t\u00f5ttu. Kolossaalne ruumi kokkuhoid \u00fcsna lihtsate paranduste kaudu. \ud83d\ude42 <\/p>\n
<\/p>\n
Probleem kuus \u2013 primaarsete v\u00f5tmete puudumine<\/h3>\n
<\/p>\n
Postgres'i MVCC mehhanismi t\u00f5ttu<\/a><\/noindex> on v\u00f5imalik olukord, kus bloat<\/a><\/noindex>, teie tabeli suurus kasvab kiiresti suure hulga surnud kirje t\u00f5ttu. Ma arvasin naiivselt, et see meid ei puuduta ja et meie andmebaasiga sellist ei juhtu, sest me, oi-oi!!!, oleme ju normaalsed arendajad\u2026 Kui rumal ja naiivne ma olin\u2026<\/p>\n
<\/p>\n
\u00dchel kaunil p\u00e4eval tegi \u00fcks imeline migratsioon k\u00f5igis suurtes ja aktiivselt kasutatavates tabelites k\u00f5ik kirjed uuenduseks. Meie tabeli suurus suurenes j\u00e4rsult +100 GB v\u00f5rra. See oli \u00e4\u00e4rmiselt frustreeriv, kuid meie katsumused sellega ei l\u00f5ppenud. P\u00e4rast 15 tunni m\u00f6\u00f6dumist autovakumeerimisest sel tabelil, sai selgeks, et f\u00fc\u00fcsiline koht ei naase. Me ei saanud teenust peatada ega teha VACUUM FULL-i, seet\u00f5ttu otsustati kasutada pg_repack<\/a><\/noindex>. Siis selgus, et pg_repack<\/em> ei oska t\u00f6\u00f6delda tabeleid, millel pole esmast v\u00f5tmed v\u00f5i mingit unikaalsuse piirangut, ja meie tabelil ei olnud esmast v\u00f5tit. Nii s\u00fcndis diagnostika tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
Raamatukogu versioonis 0.1.5<\/strong> lisati v\u00f5imalus koguda andmeid tabelite ja indeksite paisumise kohta ning sellele \u00f5igel ajal reageerida.<\/p>\n
<\/p>\n
Probleemid seitse ja kaheksa \u2013 indeksite puudus ja kasutamata indeksid<\/h3>\n
<\/p>\n
Kaks j\u00e4rgmist diagnostikat \u2014 tables_with_missing_indexes<\/a><\/noindex> ja unused_indexes<\/a><\/noindex> \u2013 ilmusid oma l\u00f5plikus vormis suhteliselt hiljuti. Asi on selles, et neid ei saanud lihtsalt juurde lisada.<\/p>\n
<\/p>\n
Nagu ma juba mainisin, kasutame mitme replikaga konfiguratsiooni, kus lugemiskoormus erinevates hostides on p\u00f5him\u00f5tteliselt erinev. Tulemuseks on olukord, kus m\u00f5ned tabelid ja indeksid m\u00f5nes hostis praktiliselt ei kasutata ning anal\u00fc\u00fcsi jaoks tuleb koguda statistikat k\u00f5ikidest klastris olevatest hostidest. Statistika l\u00e4htestamine<\/a><\/noindex> on samuti vajalik igas klastris asuvas hostis, ei saa seda teha ainult peahostis.<\/p>\n
<\/p>\n
See l\u00e4henemine on v\u00f5imaldanud meil s\u00e4\u00e4sta tosin gigabaidi v\u00f5rra, eemaldades indeksid, mida kunagi ei kasutatud, ja lisades vajalikke indekseid harva kasutatavatele tabelitele.<\/p>\n
<\/p>\n
Kokkuv\u00f5tteks<\/h3>\n
<\/p>\n
Muidugi saab peaaegu k\u00f5iki diagnostikaid seadistada v\u00e4ljaarvamisnimekiri<\/a><\/noindex>. Nii saab kiiresti rakendada kontrollid teie rakenduses, et v\u00e4ltida uute vigade ilmnemist, ja seej\u00e4rel j\u00e4rk-j\u00e4rgult parandada vanu.<\/p>\n
<\/p>\n
M\u00f5ned diagnostikad v\u00f5ivad toimuda juba funktsionaalsete testide k\u00e4igus vahetult p\u00e4rast andmebaasi r\u00e4nde rakendamist. Ja see on t\u00f5en\u00e4oliselt \u00fcks v\u00f5imsamaid v\u00f5imalusi minu raamatukogus. N\u00e4iteks kasutamise kohta saab vaadata demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
Kasutamata v\u00f5i puuduvate indeksite ning bloat'i kontrollid on m\u00f5ttekad teha ainult reaalses andmebaasis. Kogutud v\u00e4\u00e4rtused v\u00f5ivad olla salvestatud ClickHouse<\/a><\/noindex> v\u00f5i saadetud monitooringus\u00fcsteemi.<\/p>\n
<\/p>\n
Ma loodan t\u00f5eliselt, et pg-index-health<\/strong> on kasulik ja n\u00f5utud. Saate samuti toetada teegira arendamist, teavitades avastatud probleemidest ja pakkudes uusi diagnostikate.<\/p>\n
Allikas: 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\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\udd47 PostgreSQL indeksite tervis Java arendaja vaatenurgast | ProHoster","description":"Tere. Minu nimi on Vanja ja ma olen Java-arendaja.","canonical_url":"https:\/\/prohoster.info\/et\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"et_EE","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\/et\/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\/et\/wp-json\/wp\/v2\/posts\/72875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/comments?post=72875"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/posts\/72875\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media\/72876"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/media?parent=72875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/categories?post=72875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/et\/wp-json\/wp\/v2\/tags?post=72875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}