{"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\/da\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","title":{"rendered":"Indeksh\u00e5ndtering i PostgreSQL set fra en Java-udviklers perspektiv","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Hej.<\/p>\n

<\/p>\n

Mit navn er Vanya, og jeg er Java-udvikler. Det s\u00e5 tilf\u00e6ldigvis s\u00e5dan ud, at jeg arbejder meget med PostgreSQL \u2013 jeg besk\u00e6ftiger mig med databaseops\u00e6tning, optimering af struktur, ydeevne og leger lidt DBA i weekenden.<\/p>\n

<\/p>\n

P\u00e5 det seneste har jeg ryddet op i flere databaser i vores mikrotjenester og har skrevet et java-bibliotek pg-index-health<\/a><\/noindex>, som letter dette arbejde, sparer mig tid og hj\u00e6lper med at undg\u00e5 nogle typiske fejl beg\u00e5et af udviklere. Det er pr\u00e6cis det bibliotek, vi skal tale om i dag.<\/p>\n

<\/p>\n

\"Indeksh\u00e5ndtering<\/p>\n

<\/p>\n

Ansvarsfraskrivelse<\/h3>\n

<\/p>\n

Den prim\u00e6re version af PostgreSQL, som jeg arbejder med, er 10. Alle de SQL-foresp\u00f8rgsler, jeg bruger, er ogs\u00e5 testet p\u00e5 version 11. Den mindste underst\u00f8ttede version er 9.6.<\/p>\n

<\/p>\n

Background<\/h3>\n

<\/p>\n

Det hele startede for n\u00e6sten et \u00e5r siden med en m\u00e6rkelig situation for mig: en konkurrerende oprettelse af et indeks p\u00e5 et helt blankt omr\u00e5de endte med en fejl. Selve indekset, som s\u00e6dvanligt, forblev i en ugyldig tilstand i databasen. Analyse af loggene viste mangel p\u00e5 temp_file_limit<\/a><\/noindex>. Og s\u00e5 begyndte det... Da jeg gravede dybere, opdagede jeg en hel r\u00e6kke problemer i databasekonfigurationen, og med \u00e6rmerne oppe begyndte jeg entusiastisk at rette dem.<\/p>\n

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

Det f\u00f8rste problem \u2013 standardkonfigurationen<\/h3>\n

<\/p>\n

M\u00e5ske er metaforen om Postgres, der kan k\u00f8re p\u00e5 en kaffemaskine, allerede tr\u00e6ttende for alle, men... standardkonfigurationen rejser virkelig en r\u00e6kke sp\u00f8rgsm\u00e5l. I det mindste er det v\u00e6rd at l\u00e6gge m\u00e6rke til maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> og lock_timeout<\/strong>.<\/p>\n

<\/p>\n

I vores tilf\u00e6lde maintenance_work_mem<\/strong> var som standard 64 megabyte, og temp_file_limit<\/strong> omkring 2 gigabyte \u2013 vi manglede simpelthen hukommelse til at oprette et indeks p\u00e5 en stor tabel.<\/p>\n

<\/p>\n

Derfor i pg-index-health<\/strong> jeg samlede en r\u00e6kke n\u00f8gleelementer<\/a><\/noindex>, som efter min mening er parametre, der b\u00f8r tilpasses til hver database.<\/p>\n

<\/p>\n

Andet problem \u2013 duplikerede indekser<\/h3>\n

<\/p>\n

Vores databaser lever p\u00e5 SSD-diske, og vi bruger HA<\/em>-konfiguration med flere datacentre, en master-server og n<\/em>-et antal replikaer. Pladsen p\u00e5 disken er en meget v\u00e6rdifuld ressource for os; den er lige s\u00e5 vigtig som ydeevne og CPU-forbrug. Derfor, p\u00e5 den ene side, har vi brug for indekser til hurtig l\u00e6sning, men p\u00e5 den anden side \u00f8nsker vi ikke at se un\u00f8dvendige indekser i databasen, da de optager plads og bremser dataopdateringer.<\/p>\n

<\/p>\n

Og s\u00e5, efter at have gendannet alle ugyldige indekser<\/a><\/noindex> og efter at have set opl\u00e6g fra Oleg Bartunov<\/a><\/noindex>, jeg besluttede at foretage en \u00abstor\u00bb oprydning. Det viste sig, at udviklere ikke bryder sig om at l\u00e6se dokumentationen for databaser. De elsker det ikke overhovedet. Dette f\u00f8rer til to typiske fejl - manuelt oprettede indekser p\u00e5 prim\u00e6re n\u00f8gler og et tilsvarende \u00abmanuelt\u00bb indeks p\u00e5 unikke kolonner. Problemet er, at de ikke er n\u00f8dvendige - Postgres klarer det selv. S\u00e5danne indekser kan trygt fjernes, og der er nu diagnosticering for dette. duplicated_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Problem tre - overlappende indekser<\/h3>\n

<\/p>\n

De fleste nye udviklere opretter indekser p\u00e5 en kolonne. Efterh\u00e5nden, n\u00e5r de for alvor har smagt p\u00e5 dette, begynder folk at optimere deres foresp\u00f8rgsler og tilf\u00f8je mere komplekse indekser, der inkluderer flere kolonner. S\u00e5dan opst\u00e5r indekser p\u00e5 kolonnerne A<\/em>, A+B<\/em>, A+B+C<\/em> osv. De f\u00f8rste to af disse indekser kan trygt fjernes, da de er pr\u00e6fikser til det tredje. Dette sparer ogs\u00e5 betydeligt plads p\u00e5 disken, og der er diagnosticering for dette. intersected_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Problem fire - fremmede n\u00f8gler uden indekser<\/h3>\n

<\/p>\n

Postgres tillader oprettelse af begr\u00e6nsninger for fremmede n\u00f8gler uden at angive en underst\u00f8ttende indeks. I mange situationer er dette ikke et problem og kan endda ikke vise sig at have nogen konsekvenser\u2026 Indtil videre\u2026<\/p>\n

<\/p>\n

S\u00e5dan var det for os: lige pludselig blev jobs, der blev k\u00f8rt efter tidsplan og rensede databasen for testordrer, begyndt at \u00abstable\u00bb vores master host. CPU og IO sk\u00f8d i vejret, foresp\u00f8rgsler blev langsomme og stoppede p\u00e5 grund af timeout, servicen svarede med 500. En hurtig analyse pg_stat_activity<\/a><\/noindex> viste, at foresp\u00f8rgsler som:<\/p>\n

<\/p>\n

slet fra <table> hvor id i (…)<\/code><\/pre>\n
<\/p>\n
derudover var indekset p\u00e5 id i m\u00e5l-tabellen naturligvis til stede, og der blev slettet meget f\u00e5 poster efter betingelsen. Det s\u00e5 ud til, at alt burde fungere, men desv\u00e6rre gjorde det ikke.<\/p>\n
<\/p>\n
Til unds\u00e6tning kom den vidunderlige explain analyze<\/strong> og fortalte, at udover at slette poster i m\u00e5l-tabellen, blev der ogs\u00e5 kontrolleret for referentiel integritet, og p\u00e5 en af de relaterede tabeller ramte denne kontrol sequential scan<\/em> p\u00e5 grund af manglen p\u00e5 et passende indeks. S\u00e5ledes blev diagnosticering f\u00f8dt. foreign_keys_without_index<\/a><\/noindex>.<\/p>\n
<\/p>\n
Problem fem - null v\u00e6rdier i indekser<\/h3>\n
<\/p>\n
Som standard inkluderer Postgres null v\u00e6rdier i btree-indekser, men de er som regel ikke n\u00f8dvendige. Derfor g\u00f8r jeg en indsats for at fjerne disse null'er (diagnosticering indexes_with_null_values<\/a><\/noindex>), og opretter partielle indekser p\u00e5 nullable-kolonner af typen where  is not null<\/code>. P\u00e5 denne m\u00e5de lykkedes det mig at reducere st\u00f8rrelsen af en af vores indekser fra 1877 MB til 16 KB. Og i en af tjenesterne blev den samlede database st\u00f8rrelse reduceret med 16% (med 4,3 GB i absolutte tal) ved at udelukke null-v\u00e6rdier fra indekserne. En kolossal besparelse af diskplads ved relativt enkle justeringer. \ud83d\ude42 <\/p>\n
<\/p>\n
Problem seks - mangel p\u00e5 prim\u00e6re n\u00f8gler<\/h3>\n
<\/p>\n
P\u00e5 grund af mekanismens s\u00e6rlige karakteristika MVCC i Postgres<\/a><\/noindex> kan en s\u00e5dan situation opst\u00e5, som bloat<\/a><\/noindex>, hvor st\u00f8rrelsen af din tabel hurtigt vokser p\u00e5 grund af et stort antal d\u00f8de poster. Jeg troede naivt, at dette ikke ville ramme os, og at vores database ikke ville opleve det, for vi er jo, wow!!!, normale udviklere... Hvor dum og naiv jeg var...<\/p>\n
<\/p>\n
P\u00e5 en smuk dag tog en vidunderlig migration og opdaterede alle poster i en stor og aktivt anvendt tabel. Vi fik +100 GB til tabelens st\u00f8rrelse ud af det bl\u00e5. Det var skide frustrerende, men vores elendigheder sluttede ikke her. Efter at autovakuumet p\u00e5 denne tabel sluttede efter 15 timer, blev det klart, at det fysiske plads ikke ville vende tilbage. Vi kunne ikke stoppe tjenesten og udf\u00f8re VACUUM FULL, s\u00e5 vi besluttede at bruge pg_repack<\/a><\/noindex>. Og her viste det sig, at pg_repack<\/em> den ikke kan h\u00e5ndtere tabeller uden prim\u00e6re n\u00f8gler eller andre unikke begr\u00e6nsninger, og p\u00e5 vores tabel var der ingen prim\u00e6re n\u00f8gler. S\u00e5dan blev diagnosen tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
I versionen af biblioteket 0.1.5<\/strong> blev muligheden tilf\u00f8jet til at samle data om bloat fra tabeller og indekser og reagere rettidigt p\u00e5 det.<\/p>\n
<\/p>\n
Problemer syv og otte - mangel p\u00e5 indekser og ubrugte indekser<\/h3>\n
<\/p>\n
De n\u00e6ste to diagnoser - tables_with_missing_indexes<\/a><\/noindex> og unused_indexes<\/a><\/noindex> \u2013 optr\u00e5dte relativt for nylig i deres endelige form. Sagen er, at de ikke bare kunne tilf\u00f8jes s\u00e5dan.<\/p>\n
<\/p>\n
Som jeg allerede har n\u00e6vnt, bruger vi en konfiguration med flere replikaer, og belastningen p\u00e5 de l\u00e6sende noder er fundamentalt forskellig. Resultatet er, at nogle tabeller og indekser p\u00e5 nogle noder stort set ikke bruges, og for at analysere skal man samle statistikker fra alle noder i klyngen. At nulstille statistikker<\/a><\/noindex> skal ogs\u00e5 g\u00f8res p\u00e5 hver node i klyngen; man kan ikke kun g\u00f8re det p\u00e5 masteren.<\/p>\n
<\/p>\n
Denne tilgang gjorde det muligt for os at spare flere ti-GB ved at fjerne indekser, der aldrig blev brugt, samt tilf\u00f8je manglende indekser til sj\u00e6ldent anvendte tabeller.<\/p>\n
<\/p>\n
Som konklusion<\/h3>\n
<\/p>\n
Naturligvis kan n\u00e6sten alle diagnosticeringer konfigureres undtagelsesliste<\/a><\/noindex>. P\u00e5 denne m\u00e5de kan kontroller hurtigt implementeres i din applikation, hvilket forhindrer nye fejl i at opst\u00e5 og giver mulighed for gradvist at rette gamle.<\/p>\n
<\/p>\n
Nogle diagnostik kan allerede udf\u00f8res i funktionelle tests umiddelbart efter anvendelsen af databasemigrationer. Og det er m\u00e5ske en af de mest kraftfulde funktioner i mit bibliotek. Et eksempel p\u00e5 brug kan ses i demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
Kontroller for ubrugte eller manglende indekser samt for bloat, giver mening at udf\u00f8re kun p\u00e5 en reel database. De indsamlede v\u00e6rdier kan skrives til ClickHouse<\/a><\/noindex> eller sendes til overv\u00e5gningssystemet.<\/p>\n
<\/p>\n
Jeg h\u00e5ber virkelig, at pg-index-health<\/strong> vil v\u00e6re nyttig og efterspurgt. Du kan ogs\u00e5 bidrage til udviklingen af biblioteket ved at rapportere om problemer og foresl\u00e5 nye diagnosticeringer.<\/p>\n
Kilde: 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\udd47Indexernes sundhed i PostgreSQL set gennem en Java-udviklers \u00f8jne | ProHoster","description":"Hej. Mit navn er Vanya, og jeg er Java-udvikler.","canonical_url":"https:\/\/prohoster.info\/da\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"da_DK","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\/da\/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\/da\/wp-json\/wp\/v2\/posts\/72875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/comments?post=72875"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/posts\/72875\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/media\/72876"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/media?parent=72875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/categories?post=72875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/da\/wp-json\/wp\/v2\/tags?post=72875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}