{"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

Mina nimi on Vanya ja olen Java arendaja. Juhtus nii, et t\u00f6\u00f6tan palju PostgreSQL-iga \u2013 tegeleme andmebaasi seadistamise, struktuuri optimeerimise, j\u00f5udluse ning natuke m\u00e4ngin DBA n\u00e4dalavahetustel.<\/p>\n

<\/p>\n

Viimasel ajal 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 t\u00e4na ka jutt.<\/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. K\u00f5ik minu kasutatavad SQL p\u00e4ringud on samuti kontrollitud 11. versioonis. Minimaalne toetatud versioon on 9.6.<\/p>\n

<\/p>\n

Eellugu<\/h3>\n

<\/p>\n

K\u00f5ik algas peaaegu aasta tagasi kummalise olukorraga: konkurentsi k\u00e4igus indeksi loomine eba\u00f5nnestus. Ise indeks, nagu ikka, j\u00e4i andmebaasi kehtetuks. Logide anal\u00fc\u00fcs n\u00e4itas, et puudub temp_file_limit<\/a><\/noindex>. Ja siis l\u00e4ks lahti... Kaevudes s\u00fcgavamale, avastasin terve hunniku probleeme andmebaasi konfiguratsioonis ja t\u00f5ukasin k\u00e4ised \u00fcles, pannes s\u00e4rama silmad, hakkasin neid parandama.<\/p>\n

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

Esimene probleem \u2013 vaikes\u00e4tte<\/h3>\n

<\/p>\n

T\u00f5en\u00e4oliselt on metafoor Postgresist, mida saab k\u00e4ivitada kohvimasinal, k\u00f5igil juba k\u00f5rini, aga... vaikeconfiguraatsioon t\u00f5epoolest tekitab k\u00fcsimusi. V\u00e4hemalt 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 vaikes\u00e4teteks 64 MB, kuid temp_file_limit<\/strong> ligikaudu 2 GB - meil oli lihtsalt puudu m\u00e4lu indeksi loomise jaoks suurel tabelil.<\/p>\n

<\/p>\n

Seet\u00f5ttu olen pg-index-health<\/strong> koondanud mitmed v\u00f5tme<\/a><\/noindex>, minu arvates, parameetrid, mida tasub iga andmebaasi jaoks seadistada.<\/p>\n

<\/p>\n

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

<\/p>\n

Meie andmebaasid asuvad SSD ketastel ja kasutame HA<\/em>-konfiguratsiooni mitme andmekeskusega, meistriserveri ja n<\/em>-arvu koopiatega. Ruumi kettal on meie jaoks \u00e4\u00e4rmiselt v\u00e4\u00e4rtuslik ressurss; see on sama oluline kui j\u00f5udlus ja CPU tarbimine. Seet\u00f5ttu vajame \u00fchtepidi indekseid kiireks lugemiseks, aga teisest k\u00fcljest ei soovi me n\u00e4ha andmebaasis liigseid indekseid, kuna need s\u00f6\u00f6vad ruumi ja aeglustavad andmete uuendamist.<\/p>\n

<\/p>\n

Ja nii, taastades k\u00f5ik kehtetuks j\u00e4\u00e4nud indeksid<\/a><\/noindex> ja olles vaadanud Oleg Bartunovi ettekandeid<\/a><\/noindex>, otsustasin teha \u201esuure\u201c puhastuse. Selgus, et arendajad ei armasta andmebaasi dokumentatsiooni lugeda. \u00dckski neist ei armasta. Selle t\u00f5ttu tekivad kaks t\u00fc\u00fcpilist viga \u2013 k\u00e4sitsi loodud indeks p\u00f5hiv\u00f5tmele ja sarnane \u201ek\u00e4si\u201d indeks unikaalsele veerule. Asjaolu on see, et neid pole vaja \u2013 Postgres teeb k\u00f5ik ise. Sellised indeksid v\u00f5ib julgelt kustutada ja nende jaoks on v\u00e4lja antud diagnostika. dubleeritud_indeksid<\/a><\/noindex>.<\/p>\n

<\/p>\n

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

<\/p>\n

Enamik algajaid arendajaid loob indekseid \u00fche veeru jaoks. Aja jooksul, kui nad hakkavad seda protsessi korralikult hindama, hakkavad nad optimeerima oma p\u00e4ringuid ja lisama keerulisemaid indekseid, mis h\u00f5lmavad mitmeid veerge. Nii tekivad indeksid veergudele A<\/em>, A+B<\/em>, A+B+C<\/em> jne. Esimesed kaks neist indeksitest v\u00f5ib julgelt v\u00e4lja visata, kuna need on kolmanda prefiksid. See s\u00e4\u00e4stab ka diskiruumi ja selleks on olemas diagnostika. katkendlikud_indeksid<\/a><\/noindex>.<\/p>\n

<\/p>\n

Neljas probleem \u2013 v\u00e4lisv\u00f5tmed ilma indeksiteta<\/h3>\n

<\/p>\n

Postgres v\u00f5imaldab luua v\u00e4lisv\u00f5tme piiranguid n\u00e4itamata toetavat indeksi. Paljudes olukordades ei ole see probleemiks, ja isegi ei pruugi end kuidagi ilmutada\u2026 Kuni hetkeni\u2026<\/p>\n

<\/p>\n

Nii oli ka meiega: lihtsalt mingil hetkel ajastatud t\u00f6\u00f6, mis kustutatakse testtellimustest, hakkas \u201ekoguma\u201c meile meistrihostit. CPU ja IO l\u00e4ksid lakke, p\u00e4ringud pidurdusid ja aegusid, teenus andis 500 viga. Kiire anal\u00fc\u00fcs pg_stat_activity<\/a><\/noindex> n\u00e4itas, et takerdusid p\u00e4ringud t\u00fc\u00fcpi:<\/p>\n

<\/p>\n

kustuta <table> kus id on (…)<\/code><\/pre>\n
<\/p>\n
Samas, indeks ID j\u00e4rgi sihtlaudas oli loomulikult olemas ja kirjeid kustutati tingimuse j\u00e4rgi \u00fcsna v\u00e4he. Tundus, et k\u00f5ik peaks t\u00f6\u00f6tama, kuid kahjuks ei t\u00f6\u00f6tanud.<\/p>\n
<\/p>\n
Abi tuli imelise explain analyze<\/strong> ja \u00fctles, et lisaks kirje eemaldamisele sihtlaudades toimub ka linkide terviklikkuse kontroll ja \u00fchel seotud laual j\u00f5uab see kontroll j\u00e4rjestikusse skaneerimisse<\/em> sobiva indeksi puudumise t\u00f5ttu. Nii s\u00fcndis diagnostika. valmis_v\u00f5tmed_ilma_indeksita<\/a><\/noindex>.<\/p>\n
<\/p>\n
Viies probleem \u2013 null v\u00e4\u00e4rtus indeksites<\/h3>\n
<\/p>\n
Vaikimisi sisaldab Postgres null-v\u00e4\u00e4rtusi btree-indeksites, kuid need ei ole seal reeglina vajalikud. Seet\u00f5ttu \u00fcritan ma need nullid hoolikalt eemaldada (diagnostika indeksid_null_v\u00e4\u00e4rtustega<\/a><\/noindex>), luues osutilised indeksid nullitavate veergude jaoks, nagu kus  ei ole null<\/code>Sel viisil suutsin ma v\u00e4hendada \u00fche meie indeksi suurust 1877 MB-st 16 KB-ks. Ja \u00fches teenuses v\u00e4henes andmebaasi kogu suurus 16% (valus m\u00f5\u00f5tudes 4,3 GB) nullv\u00e4\u00e4rtuste eemaldamise t\u00f5ttu indeksitest. Kolossaalne ruumi kokkuhoid \u00fcsna lihtsate t\u00e4iustuste kaudu. \ud83d\ude42 <\/p>\n
<\/p>\n
Probleem kuues \u2013 esmase v\u00f5tme puudumine<\/h3>\n
<\/p>\n
Seoses mehhanismi erip\u00e4radega MVCC Postgres'is<\/a><\/noindex> on v\u00f5imalik olukord, kus bloat<\/a><\/noindex>teie tabeli suurus kasvab kiiresti suurte hulkade surnud kirjeid t\u00f5ttu. Ma arvasin naiivselt, et me ei pea selle p\u00e4rast muretsema, ja et meie andmebaas ei satu sellesse situatsiooni, kuna me, v\u00e4hemasti, oleme normaalsed arendajad\u2026 Kui rumal ja naiivne ma olin\u2026<\/p>\n
<\/p>\n
\u00dcks ilus p\u00e4ev uue migratsiooni k\u00e4igus v\u00e4rskendati k\u00f5iki kirjeid suures ja aktiivselt kasutuses olevas tabelis. Saime +100 GB tabeli suurenduseks niisama. See oli kohutavalt kurb, kuid meie seiklused sellega ei l\u00f5ppenud. P\u00e4rast 15 tunni jooksul l\u00f5ppenud automaatsest t\u00fchjendamisest selgus, et f\u00fc\u00fcsiline ruum ei naase. Me ei saanud teenust peatada ja teha VACUUM FULL, seet\u00f5ttu otsustati kasutada pg_repack<\/a><\/noindex>. Ja siis selgus, et pg_repack<\/em> ei oska t\u00f6\u00f6tada tabelitega, kus puudub esmane v\u00f5ti v\u00f5i muu unikaalsuse piirang, ning meie tabelil ei olnud esmase v\u00f5ti. Niimoodi s\u00fcndis diagnostika tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
Raamatukogu versioonis 0.1.5<\/strong> ilmnes v\u00f5imalus koguda andmeid tabelite ja indeksite paisumise kohta ning sellele \u00f5igel ajal reageerida.<\/p>\n
<\/p>\n
Probleemid seitse ja kaheksa \u2013 indeksite puudumine ja kasutamata indeksid<\/h3>\n
<\/p>\n
Kaks j\u00e4rgmist diagnostikat \u2013 tables_with_missing_indexes<\/a><\/noindex> ja unused_indexes<\/a><\/noindex> ilmnesid oma l\u00f5plikus vormis suhteliselt hiljuti. Asi on selles, et neid ei saanud lihtsalt niisama lisada.<\/p>\n
<\/p>\n
Nagu ma juba mainisin, kasutame mitme koopia konfigureerimist ja lugemise koormus erinevatel hostidel on sisuliselt erinev. L\u00f5ppkokkuv\u00f5ttes tekib olukord, kus teatud tabelid ja indeksid m\u00f5nel hostil praktiliselt ei kasutata, ja anal\u00fc\u00fcsimiseks on vajalik koguda statistikat k\u00f5igilt klastris olevatelt hostidelt. Statistika t\u00fchjendamine<\/a><\/noindex> on samuti vajalik igas hostis klastri sees, ei saa seda teha ainult meistril.<\/p>\n
<\/p>\n
Selle l\u00e4henemisega suutsime s\u00e4\u00e4sta mitu tosinat gigabaiti, kustutades indekseid, mida kunagi ei kasutatud, ning lisades puuduolevad indekseid harva kasutatavatele tabelitele.<\/p>\n
<\/p>\n
Kokkuv\u00f5tteks<\/h3>\n
<\/p>\n
Muidugi on enamikku diagnostikast v\u00f5imalik seadistada v\u00e4lja arvatud nimekiri<\/a><\/noindex>. Nii saab kiiresti rakendada kontrolle teie rakenduses, v\u00e4ltides uusi vigu ja seej\u00e4rel j\u00e4rk-j\u00e4rgult vana parandades.<\/p>\n
<\/p>\n
M\u00f5ne diagnostika v\u00f5ib teostada juba funktsionaalsetes testides kohe p\u00e4rast andmebaasi migratsioonide rakendamist. Ja see on t\u00f5en\u00e4oliselt \u00fcks minu teegi k\u00f5ige v\u00f5imsamaid funktsioone. Kasutamise n\u00e4idet saab vaadata demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
Kontrollid mittekasutatud v\u00f5i puuduvate indeksete ning bloat'i osas on m\u00f5ttekas teostada ainult reaalsetes andmebaasides. Kogutud v\u00e4\u00e4rtused v\u00f5ivad olla salvestatud ClickHouse<\/a><\/noindex> v\u00f5i saadetud j\u00e4lgimisse s\u00fcsteemi.<\/p>\n
<\/p>\n
Loodan v\u00e4ga, et pg-index-health<\/strong> osutub kasulikuks ja n\u00f5utuks. Samuti saate aidata teegi arengut, teavitades avastatud probleemidest ja pakkudes uusi diagnostikaid.<\/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\udd47PostgreSQL indekste tervis Java-arendaja silmade l\u00e4bi | ProHoster","description":"Tere. Minu nimi on Vanya ja 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}]}}