{"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\/it\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","title":{"rendered":"La salute degli indici in PostgreSQL dal punto di vista di uno sviluppatore Java","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Ciao.<\/p>\n

<\/p>\n

Mi chiamo Vanya e sono uno sviluppatore Java. Ho accumulato molta esperienza con PostgreSQL, occupandomi della configurazione del database, dell'ottimizzazione della struttura e delle prestazioni, e ho anche un po' di esperienza come DBA nei fine settimana.<\/p>\n

<\/p>\n

Di recente ho sistemato alcuni database nei nostri microservizi e ho scritto una libreria java pg-index-health<\/a><\/noindex>, che semplifica questo lavoro, risparmia tempo e aiuta a evitare alcuni errori comuni commessi dagli sviluppatori. Proprio di questa libreria parleremo oggi.<\/p>\n

<\/p>\n

\"La<\/p>\n

<\/p>\n

Avvertenza<\/h3>\n

<\/p>\n

La versione principale di PostgreSQL con cui lavoro \u00e8 la 10. Tutte le query SQL da me utilizzate sono state verificate anche sulla versione 11. La versione minima supportata \u00e8 la 9.6.<\/p>\n

<\/p>\n

Contesto<\/h3>\n

<\/p>\n

Tutto \u00e8 iniziato quasi un anno fa con una situazione strana per me: la creazione concorrente di un indice inaspettatamente si \u00e8 conclusa con un errore. L'indice stesso, come spesso accade, \u00e8 rimasto in uno stato non valido nel database. L'analisi dei log ha mostrato una mancanza di temp_file_limit<\/a><\/noindex>. E cos\u00ec \u00e8 iniziata l'avventura\u2026 Approfondendo la questione, ho scoperto una serie di problemi nella configurazione del database e, rimboccandomi le maniche, ho iniziato a sistemarli con occhi brillanti.<\/p>\n

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

Il primo problema \u00e8 la configurazione di default<\/h3>\n

<\/p>\n

Forse la metafora di Postgres avviabile su una macchina da caff\u00e8 ha stufato, ma\u2026 la configurazione predefinita solleva davvero una serie di domande. Al minimo, vale la pena prestare attenzione a maintenance_work_mem<\/strong>, temp_file_limit<\/strong>, statement_timeout<\/strong> e lock_timeout<\/strong>.<\/p>\n

<\/p>\n

Nel nostro caso maintenance_work_mem<\/strong> era di default 64 MB, mentre temp_file_limit<\/strong> ci servivano circa 2 GB; ci mancava letteralmente memoria per creare un indice su una grande tabella.<\/p>\n

<\/p>\n

Quindi, ho raccolto una serie di pg-index-health<\/strong> parametri chiave, secondo me, che vale la pena configurare per ogni database.<\/a><\/noindex>Il secondo problema \u00e8 gli indici duplicati<\/p>\n

<\/p>\n

I nostri database risiedono su dischi SSD, e utilizziamo<\/h3>\n

<\/p>\n

-configurazione con pi\u00f9 data center, un host master e HA<\/em>-numero totale di repliche. Lo spazio su disco \u00e8 una risorsa molto preziosa per noi; \u00e8 altrettanto importante delle prestazioni e del consumo di CPU. Pertanto, da un lato abbiamo bisogno di indici per una lettura veloce, dall'altro non vogliamo vedere indici superflui nel database, poich\u00e9 consumano spazio e rallentano l'aggiornamento dei dati. n<\/em>E cos\u00ec, recuperati tutti<\/p>\n

<\/p>\n

indici non validi e dopo aver visionato<\/a><\/noindex> le relazioni di Oleg Bartunov, ho deciso di effettuare una \"grande\" pulizia. Si \u00e8 scoperto che gli sviluppatori non amano leggere la documentazione del database. Affatto. Questo porta a due errori comuni: l'indice creato manualmente sulla chiave primaria e un indice \"manuale\" su una colonna unica. Il punto \u00e8 che non sono necessari: Postgres si occuper\u00e0 di tutto. Questi indici possono essere eliminati senza problemi, e per questo \u00e8 stata creata la diagnosi relazioni di Oleg Bartunov<\/a><\/noindex>, ho deciso di fare una \"grande\" pulizia. Si \u00e8 scoperto che gli sviluppatori non amano leggere la documentazione del database. Non la amano affatto. A causa di questo, sorgono due errori tipici \u2013 un indice creato manualmente sulla chiave primaria e un indice \"manuale\" simile su una colonna unica. Il fatto \u00e8 che non sono necessari \u2013 Postgres far\u00e0 tutto da solo. Questi indici possono essere tranquillamente rimossi, e per questo \u00e8 stata introdotta una diagnostica. duplicated_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Il terzo problema \u00e8 gli indici sovrapposti<\/h3>\n

<\/p>\n

La maggior parte dei nuovi sviluppatori crea indici su una singola colonna. Man mano che, assaporando questo processo, le persone iniziano a ottimizzare le loro query e ad aggiungere indici pi\u00f9 complessi che includono pi\u00f9 colonne. Cos\u00ec si creano indici su colonne A<\/em>, A+B<\/em>, A+B+C<\/em> ecc. I primi due di questi indici possono essere tranquillamente eliminati, poich\u00e9 sono prefissi del terzo. Questo aiuta a risparmiare spazio su disco e per questo c'\u00e8 la diagnosi intersected_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Il quarto problema \u00e8 le chiavi esterne senza indici<\/h3>\n

<\/p>\n

Postgres consente di creare vincoli di chiave esterna senza specificare un indice di supporto. In molte situazioni questo non rappresenta un problema e pu\u00f2 anche non manifestarsi\u2026 Fino a un certo punto\u2026<\/p>\n

<\/p>\n

\u00c8 stato cos\u00ec anche per noi: a un certo punto, un job programmato per eliminare ordini di test ha cominciato a \"sovraccaricare\" il nostro host master. La CPU e IO affondavano, le query si bloccavano e venivano interrotte per timeout, e il servizio andava in errore 500. Un'analisi rapida pg_stat_activity<\/a><\/noindex> ha mostrato che le query come queste si bloccavano:<\/p>\n

<\/p>\n

elimina da <table> dove id in (…)<\/code><\/pre>\n
<\/p>\n
Nel frattempo, l'indice per id nella tabella target, ovviamente, esisteva e i record venivano eliminati per condizione senza creare problemi. Sembrava che tutto dovesse funzionare, ma, ahim\u00e8, non funzionava.<\/p>\n
<\/p>\n
A soccorrere \u00e8 giunto il meraviglioso explain analyze<\/strong> che ha rivelato che oltre all'eliminazione dei record nella tabella target, veniva anche eseguita una verifica dell'integrit\u00e0 referenziale, e su una delle tabelle correlate questa verifica cadeva in sequential scan<\/em> a causa dell'assenza di un indice adeguato. Cos\u00ec \u00e8 nata la diagnosi foreign_keys_without_index<\/a><\/noindex>.<\/p>\n
<\/p>\n
Il quinto problema \u00e8 il valore null negli indici<\/h3>\n
<\/p>\n
Per impostazione predefinita, Postgres include i valori null nei btree-indici, ma di solito non sono necessari. Pertanto, mi sforzo di eliminare questi null (diagnosi indexes_with_null_values<\/a><\/noindex>), creando indici parziali su colonne nullable del tipo where  is not null<\/code>. In questo modo sono riuscito a ridurre la dimensione di uno dei nostri indici da 1877 MB a 16 KB. Inoltre, in uno dei servizi la dimensione totale del DB \u00e8 diminuita del 16% (4,3 GB in cifre assolute) grazie all'esclusione dei valori null dagli indici. Un enorme risparmio di spazio su disco con modifiche abbastanza semplici. \ud83d\ude42 <\/p>\n
<\/p>\n
Il sesto problema \u2013 mancanza di chiavi primarie<\/h3>\n
<\/p>\n
A causa delle peculiarit\u00e0 del meccanismo MVCC in Postgres<\/a><\/noindex> si pu\u00f2 verificare una situazione in cui bloat<\/a><\/noindex>la dimensione della tua tabella cresce rapidamente a causa di un gran numero di record morti. Credevo ingenuamente che non ci riguardasse e che non ci sarebbero stati problemi con il nostro database, poich\u00e9 noi, oh wow!!!, siamo sviluppatori normali\u2026 Quanto ero sciocco e ingenuo\u2026<\/p>\n
<\/p>\n
Un bel giorno, una meravigliosa migrazione ha aggiornato tutti i record in una tabella grande e molto utilizzata. Abbiamo ottenuto +100 GB in dimensione della tabella dal nulla. \u00c8 stato davvero frustrante, ma le nostre disavventure non sono finite l\u00ec. Dopo che l'autovacuum su questa tabella \u00e8 durato 15 ore, era chiaro che lo spazio fisico non sarebbe tornato. Non potevamo fermare il servizio e fare un VACUUM FULL, quindi abbiamo deciso di utilizzare pg_repack<\/a><\/noindex>. E qui si \u00e8 scoperto che pg_repack<\/em> non \u00e8 in grado di gestire tabelle senza una chiave primaria o altra limitazione di unicit\u00e0, e nella nostra tabella non c'era una chiave primaria. Cos\u00ec \u00e8 nata la diagnosi tables_without_primary_key<\/a><\/noindex>.<\/p>\n
<\/p>\n
Nella versione della libreria 0.1.5<\/strong> \u00e8 stata aggiunta la possibilit\u00e0 di raccogliere dati sul bloat di tabelle e indici e di reagire in modo tempestivo.<\/p>\n
<\/p>\n
I problemi sette e otto \u2013 mancanza di indici e indici non utilizzati<\/h3>\n
<\/p>\n
Le due diagnosi successive \u2013 tables_with_missing_indexes<\/a><\/noindex> e unused_indexes<\/a><\/noindex> \u2013 sono comparse nel loro formato finale relativamente di recente. Questo perch\u00e9 non potevano essere semplicemente aggiunte.<\/p>\n
<\/p>\n
Come ho gi\u00e0 scritto, utilizziamo una configurazione con pi\u00f9 repliche, e il carico di lettura su diversi host \u00e8 fondamentalmente diverso. Ne consegue che alcune tabelle e indici su alcuni host vengano praticamente non utilizzati, e per l'analisi \u00e8 necessario raccogliere statistiche da tutti gli host nel cluster. Azzerare le statistiche<\/a><\/noindex> \u00e8 necessario farlo anche su ogni host nel cluster, non pu\u00f2 essere fatto solo sul master.<\/p>\n
<\/p>\n
Questo approccio ci ha permesso di risparmiare diversi decine di gigabyte rimuovendo indici che non erano mai stati utilizzati, oltre ad aggiungere indici mancanti su tabelle raramente utilizzate.<\/p>\n
<\/p>\n
In conclusione<\/h3>\n
<\/p>\n
Ovviamente, per praticamente tutte le diagnosi si pu\u00f2 configurare un elenco di eccezioni<\/a><\/noindex>. In questo modo \u00e8 possibile implementare rapidamente controlli nella tua applicazione, prevenendo la comparsa di nuovi errori, e poi correggere gradualmente quelli vecchi.<\/p>\n
<\/p>\n
Parte delle diagnosi pu\u00f2 essere eseguita gi\u00e0 nei test funzionali subito dopo l'applicazione delle migrazioni del DB. E questo, forse, \u00e8 una delle funzionalit\u00e0 pi\u00f9 potenti della mia libreria. Un esempio di utilizzo pu\u00f2 essere visto in demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
I controlli per indici non utilizzati o mancanti, cos\u00ec come per il bloat, hanno senso farli solo su un DB reale. I valori raccolti possono essere registrati in ClickHouse<\/a><\/noindex> o inviati a un sistema di monitoraggio.<\/p>\n
<\/p>\n
Spero davvero che pg-index-health<\/strong> sia utile e richiesta. Puoi anche contribuire allo sviluppo della libreria segnalando problemi trovati e suggerendo nuove diagnosi.<\/p>\n
Fonte: 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\udd47Salute degli indici in PostgreSQL visto da uno sviluppatore Java | ProHoster","description":"Ciao. Mi chiamo Vanya e sono uno sviluppatore Java.","canonical_url":"https:\/\/prohoster.info\/it\/blog\/administrirovanie\/zdorove-indeksov-v-postgresql-glazami-java-razrabotchika","robots":"max-image-preview:large","keywords":"","webmasterTools":{"miscellaneous":""},"schema":null,"og:locale":"it_IT","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\/it\/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\/it\/wp-json\/wp\/v2\/posts\/72875","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/comments?post=72875"}],"version-history":[{"count":0,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/posts\/72875\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media\/72876"}],"wp:attachment":[{"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/media?parent=72875"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/categories?post=72875"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prohoster.info\/it\/wp-json\/wp\/v2\/tags?post=72875"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}