{"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\/sv\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","title":{"rendered":"H\u00e4lsa p\u00e5 index i PostgreSQL ur en Java-utvecklares perspektiv","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hej.<\/p>\n

<\/p>\n

Jag heter Vanya och jag \u00e4r Java-utvecklare. Det r\u00e5kade bli s\u00e5 att jag arbetar mycket med PostgreSQL - jag sysslar med att konfigurera databaser, optimera strukturer, prestanda och leker lite DBA p\u00e5 helgerna.<\/p>\n

<\/p>\n

Under senaste tiden har jag ordnat upp flera databaser i v\u00e5ra mikrotj\u00e4nster och skrivit ett Java-bibliotek pg-index-health<\/a><\/noindex>, som underl\u00e4ttar detta arbete, sparar min tid och hj\u00e4lper till att undvika vissa typiska misstag som utvecklare g\u00f6r. Just om detta bibliotek kommer jag att prata idag.<\/p>\n

<\/p>\n

\"H\u00e4lsa<\/p>\n

<\/p>\n

Ansvarsfriskrivning<\/h3>\n

<\/p>\n

Den huvudsakliga versionen av PostgreSQL som jag arbetar med \u00e4r 10. Alla SQL-fr\u00e5gor jag anv\u00e4nder har ocks\u00e5 testats p\u00e5 version 11. Den minimi st\u00f6dja versionen \u00e4r 9.6.<\/p>\n

<\/p>\n

Bakgrund<\/h3>\n

<\/p>\n

Det hela b\u00f6rjade f\u00f6r n\u00e4stan ett \u00e5r sedan med en situation som k\u00e4ndes m\u00e4rklig f\u00f6r mig: konkurrerande skapande av index p\u00e5 en tom plats avslutades med ett fel. Sj\u00e4lva indexet, som det ofta \u00e4r, f\u00f6rblev i ett ogiltigt tillst\u00e5nd i databasen. Logganalysen visade brist p\u00e5 temp_file_limit<\/a><\/noindex>. Och s\u00e5 b\u00f6rjade det... N\u00e4r jag gr\u00e4vde djupare uppt\u00e4ckte jag en hel upps\u00e4ttning problem i databasens konfiguration och, med \u00e4rmarna uppkavlade och glans i \u00f6gonen, satte jag ig\u00e5ng att \u00e5tg\u00e4rda dem.<\/p>\n

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

F\u00f6rsta problemet \u2013 standardkonfigurationen<\/h3>\n

<\/p>\n

Kanske \u00e4r metaforen om Postgres som kan k\u00f6ras p\u00e5 en kaffebryggare redan tr\u00f6ttsam f\u00f6r m\u00e5nga, men... standardkonfigurationen v\u00e4cker verkligen en hel del fr\u00e5gor. \u00c5tminstone b\u00f6r man uppm\u00e4rksamma maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> och lock_timeout<\/strong>.<\/p>\n

<\/p>\n

I v\u00e5rt fall maintenance_work_mem<\/strong> var som standard 64 megabyte, och temp_file_limit<\/strong> n\u00e5got runt 2 gigabyte - vi hade helt enkelt inte tillr\u00e4ckligt med minne f\u00f6r att skapa ett index p\u00e5 en stor tabell.<\/p>\n

<\/p>\n

D\u00e4rf\u00f6r har jag i pg-index-health<\/strong> samlat ett antal nyckel-<\/a><\/noindex>parametrar som jag tycker b\u00f6r justeras f\u00f6r varje databas.<\/p>\n

<\/p>\n

Andra problemet \u2013 dubblettindex<\/h3>\n

<\/p>\n

V\u00e5ra databaser lever p\u00e5 SSD-diskar, och vi anv\u00e4nder HA<\/em>-konfiguration med flera datacenter, en huvudv\u00e4rd och n<\/em>-antal replikat. Utrymme p\u00e5 disken \u00e4r en mycket v\u00e4rdefull resurs f\u00f6r oss; det \u00e4r lika viktigt som prestanda och CPU-anv\u00e4ndning. S\u00e5, \u00e5 ena sidan beh\u00f6ver vi index f\u00f6r snabb l\u00e4sning, \u00e5 andra sidan vill vi inte se on\u00f6diga index i databasen, eftersom de \u00e4ter utrymme och f\u00f6rdr\u00f6jer uppdatering av data.<\/p>\n

<\/p>\n

Och s\u00e5, efter att vi \u00e5terst\u00e4llt alla ogiltiga index<\/a><\/noindex> och sett p\u00e5 Oleg Bartunovs f\u00f6redrag<\/a><\/noindex>, jag beslutade mig f\u00f6r att genomf\u00f6ra en \"stor\" rensning. Det visade sig att utvecklare inte gillar att l\u00e4sa dokumentation om databaser. De gillar det verkligen inte. P\u00e5 grund av detta uppst\u00e5r tv\u00e5 typiska fel - manuellt skapade index p\u00e5 prim\u00e4rnyckeln och ett liknande \"handgjort\" index p\u00e5 en unik kolumn. Saken \u00e4r den att de inte beh\u00f6vs - Postgres sk\u00f6ter det sj\u00e4lv. S\u00e5dana index kan du tryggt ta bort, och f\u00f6r detta har vi en diagnostik. duplicated_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Tredje problemet - \u00f6verlappande index<\/h3>\n

<\/p>\n

De flesta nyb\u00f6rjande utvecklare skapar index f\u00f6r en kolumn. Gradvis, efter att ha vant sig vid detta, b\u00f6rjar folk optimera sina fr\u00e5gor och l\u00e4gga till mer komplexa index som inkluderar flera kolumner. S\u00e5dana index uppst\u00e5r f\u00f6r kolumner A<\/em>, A+B<\/em>, A+B+C<\/em> och s\u00e5 vidare. De f\u00f6rsta tv\u00e5 av dessa index kan du tryggt kasta bort, eftersom de \u00e4r prefix till det tredje. Detta sparar ocks\u00e5 mycket plats p\u00e5 disken, och f\u00f6r detta finns en diagnostik. intersected_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Fj\u00e4rde problemet - fr\u00e4mmande nycklar utan index<\/h3>\n

<\/p>\n

Postgres till\u00e5ter skapande av begr\u00e4nsningar f\u00f6r fr\u00e4mmande nycklar utan att ange st\u00f6djande index. I m\u00e5nga situationer \u00e4r detta inget problem, och det kan till och med inte p\u00e5verka... Tills vidare...<\/p>\n

<\/p>\n

S\u00e5 var det \u00e4ven f\u00f6r oss: bara vid n\u00e5got tillf\u00e4lle b\u00f6rjade jobbet, som k\u00f6rdes enligt schema och rensade databasen fr\u00e5n testbest\u00e4llningar, att \"lagra\" oss en master host. CPU och IO sk\u00f6t i h\u00f6jden, f\u00f6rfr\u00e5gningarna plockade och avbr\u00f6ts p\u00e5 grund av timeout, tj\u00e4nsten svarade med femhundra. En snabb analys pg_stat_activity<\/a><\/noindex> visade att f\u00f6rfr\u00e5gningar av typen:<\/p>\n

<\/p>\n

ta bort från <table> där id i (…)<\/code><\/pre>\n
<\/p>\n
Samtidigt fanns det naturligtvis ett index p\u00e5 id i m\u00e5ltabellen, och poster togs bort enligt villkoret, vilket var alldeles f\u00f6r lite. Det k\u00e4ndes som om allt borde fungera, men tyv\u00e4rr fungerade det inte.<\/p>\n
<\/p>\n
Till hj\u00e4lp kom den underbara explain analyze<\/strong> och ber\u00e4ttade att f\u00f6rutom att radera poster i m\u00e5ltabellen, s\u00e5 gjordes det ocks\u00e5 en kontroll av referensintegriteten, och p\u00e5 en av de relaterade tabellerna f\u00f6ll denna kontroll ner i sequential scan<\/em> p\u00e5 grund av bristande passande index. S\u00e5 f\u00f6ddes diagnostiken foreign_keys_without_index<\/a><\/noindex>.<\/p>\n
<\/p>\n
Femte problemet - null-v\u00e4rden i index<\/h3>\n
<\/p>\n
Som standard inkluderar Postgres null-v\u00e4rden i btree-index, men de \u00e4r oftast inte n\u00f6dv\u00e4ndiga d\u00e4r. D\u00e4rf\u00f6r anstr\u00e4nger jag mig f\u00f6r att ta bort dessa null-v\u00e4rden (diagnostik indexes_with_null_values<\/a><\/noindex>), genom att skapa partiella index p\u00e5 nullable-kolumner av typen where  is not null<\/code>. Genom denna metod lyckades jag minska storleken p\u00e5 ett av v\u00e5ra index fr\u00e5n 1877 MB till 16 KB. I en av tj\u00e4nsterna minskade storleken p\u00e5 databasen totalt sett med 16 % (med 4,3 GB i absoluta siffror) genom att utesluta null-v\u00e4rden fr\u00e5n indexen. En kolossal besparing av diskuppgifter med relativt enkla f\u00f6r\u00e4ndringar. \ud83d\ude42 <\/p>\n
<\/p>\n
Problem sex \u2013 avsaknad av prim\u00e4ra nycklar<\/h3>\n
<\/p>\n
P\u00e5 grund av egenskaperna hos mekanismen MVCC i Postgres<\/a><\/noindex> kan en s\u00e5dan situation uppst\u00e5 som bloat<\/a><\/noindex>, n\u00e4r storleken p\u00e5 din tabell snabbt v\u00e4xer p\u00e5 grund av ett stort antal d\u00f6da poster. Jag var naiv nog att tro att detta inte skulle drabba oss och att v\u00e5r databas inte skulle drabbas, f\u00f6r vi \u00e4r ju, wow!!!, helt normala utvecklare... Hur dum och naiv jag var...<\/p>\n
<\/p>\n
En vacker dag uppdaterade en fantastisk migration alla poster i en stor och aktivt anv\u00e4nd tabell. Vi fick +100 GB till storleken p\u00e5 tabellen utan anledning. Det var j\u00e4ttefrustrerande, men v\u00e5ra motg\u00e5ngar slutade inte d\u00e4r. Efter att autovakuum hade avslutats p\u00e5 denna tabell efter 15 timmar stod det klart att det fysiska utrymmet inte skulle \u00e5terkomma. Vi kunde inte stoppa tj\u00e4nsten och g\u00f6ra VACUUM FULL, s\u00e5 beslutet togs att anv\u00e4nda pg_repack<\/a><\/noindex>. H\u00e4r visade det sig att pg_repack<\/em> inte kan hantera tabeller utan prim\u00e4rnyckel eller n\u00e5got annat unikt begr\u00e4nsningskrav, och i v\u00e5r tabell fanns ingen prim\u00e4rnyckel. S\u00e5 diagnostiken f\u00f6ddes tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
I versionen av biblioteket 0.1.5<\/strong> l\u00e4ggs m\u00f6jligheten till att samla in data om tabellernas och indexens bloat och reagera p\u00e5 det i tid.<\/p>\n
<\/p>\n
Problem sju och \u00e5tta \u2013 brist p\u00e5 index och oanv\u00e4nda index<\/h3>\n
<\/p>\n
De tv\u00e5 f\u00f6ljande diagnostikerna \u2013 tables_with_missing_indexes<\/a><\/noindex> och unused_indexes<\/a><\/noindex> \u2013 i sin slutliga version d\u00f6k upp relativt nyligen. Anledningen \u00e4r att de inte bara kunde l\u00e4ggas till.<\/p>\n
<\/p>\n
Som jag redan har n\u00e4mnt anv\u00e4nder vi en konfiguration med flera repliker, och l\u00e4sbelastningen p\u00e5 olika v\u00e4rdar \u00e4r principiellt olika. I slut\u00e4ndan f\u00e5r vi en situation d\u00e4r vissa tabeller och index p\u00e5 vissa v\u00e4rdar praktiskt taget inte anv\u00e4nds, och f\u00f6r analysen m\u00e5ste vi samla statistiken fr\u00e5n alla v\u00e4rdar i klustret. Att \u00e5terst\u00e4lla statistiken<\/a><\/noindex> \u00e4r ocks\u00e5 n\u00f6dv\u00e4ndigt p\u00e5 varje v\u00e4rd i klustret, det g\u00e5r inte att g\u00f6ra detta endast p\u00e5 master.<\/p>\n
<\/p>\n
Denna metod har gjort det m\u00f6jligt f\u00f6r oss att spara flera tiotals gigabyte genom att ta bort index som aldrig har anv\u00e4nts, samt l\u00e4gga till saknade index p\u00e5 s\u00e4llan anv\u00e4nda tabeller.<\/p>\n
<\/p>\n
Som en avslutning<\/h3>\n
<\/p>\n
Sj\u00e4lvklart kan de flesta diagnoser st\u00e4llas in en lista \u00f6ver undantag<\/a><\/noindex>. P\u00e5 s\u00e5 s\u00e4tt kan kontroller snabbt implementeras i din applikation, vilket f\u00f6rhindrar uppkomsten av nya fel, och d\u00e4refter kan gamla fel gradvis fixas.<\/p>\n
<\/p>\n
En del diagnoser kan utf\u00f6ras redan under funktionella tester direkt efter att databasmigreringar har genomf\u00f6rts. Och detta \u00e4r kanske en av de kraftfullaste funktionerna i mitt bibliotek. Exempel p\u00e5 anv\u00e4ndning kan ses i demoversioner<\/a><\/noindex>.<\/p>\n
<\/p>\n
Kontroller f\u00f6r oanv\u00e4nda eller saknade index, samt f\u00f6r bloat, b\u00f6r endast utf\u00f6ras p\u00e5 en verklig databas. Samlade v\u00e4rden kan skrivas till ClickHouse<\/a><\/noindex> eller skickas till \u00f6vervakningssystemet.<\/p>\n
<\/p>\n
Jag hoppas verkligen att pg-index-health<\/strong> kommer att vara anv\u00e4ndbar och efterfr\u00e5gad. Du kan ocks\u00e5 bidra till utvecklingen av biblioteket genom att rapportera uppt\u00e4ckta problem och f\u00f6resl\u00e5 nya diagnoser.<\/p>\n
K\u00e4lla: 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\udd47Indexh\u00e4lsa i PostgreSQL genom \u00f6gonen p\u00e5 en Java-utvecklare | ProHoster","description":"Hej. Jag heter Vanya och jag \u00e4r Java-utvecklare.","canonical_url":"https:\/\/prohoster.info\/sv\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"sv_SE","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\/sv\/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\/sv\/wp-json\/wp\/v2\/posts\/72875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/comments?post=72875"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/posts\/72875\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media\/72876"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/media?parent=72875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/categories?post=72875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/sv\/wp-json\/wp\/v2\/tags?post=72875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}