{"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 visto da un sviluppatore Java","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

Ciao.<\/p>\n

<\/p>\n

Mi chiamo Vania e sono uno sviluppatore Java. \u00c8 capitato che lavori molto con PostgreSQL: mi occupo della configurazione del database, dell'ottimizzazione della struttura e delle prestazioni, e mi diletto anche a fare il DBA nei fine settimana.<\/p>\n

<\/p>\n

Negli ultimi tempi ho sistemato diversi database nei nostri microservizi e ho scritto una libreria Java pg-index-health<\/a><\/noindex>, che semplifica questo lavoro, risparmia il mio 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

Avviso<\/h3>\n

<\/p>\n

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

<\/p>\n

Antefatti<\/h3>\n

<\/p>\n

Tutto \u00e8 iniziato quasi un anno fa con una situazione strana per me: la creazione concorrente di un indice in un luogo qualsiasi 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 evidenziato la mancanza di temp_file_limit<\/a><\/noindex>. E cos\u00ec \u00e8 cominciata\u2026 Scavando pi\u00f9 a fondo, ho scoperto un intero groviglio di problemi nella configurazione del database e, rimboccandomi le maniche, con gli occhi pieni di entusiasmo, mi sono messo a risolverli.<\/p>\n

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

Il primo problema \u2013 la configurazione predefinita<\/h3>\n

<\/p>\n

Probabilmente, la metafora di Postgres che pu\u00f2 essere eseguito su una macchina da caff\u00e8 \u00e8 gi\u00e0 diventata fastidiosa, ma\u2026 la configurazione predefinita solleva davvero molte 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 64 MB per impostazione predefinita, mentre temp_file_limit<\/strong> circa 2 GB \u2013 ci mancava semplicemente memoria per creare un indice su una grande tabella.<\/p>\n

<\/p>\n

Pertanto, in pg-index-health<\/strong> ho raccolto una serie di parametri<\/a><\/noindex>, a mio avviso, chiave, che vale la pena configurare per ogni database.<\/p>\n

<\/p>\n

Il secondo problema \u2013 indici duplicati<\/h3>\n

<\/p>\n

I nostri database risiedono su dischi SSD e utilizziamo HA<\/em>-configurazione con pi\u00f9 centri dati, un master host e n<\/em>-un numero di repliche. Lo spazio su disco \u00e8 una risorsa molto preziosa per noi; \u00e8 altrettanto importante quanto le prestazioni e il consumo di CPU. Pertanto, da un lato, abbiamo bisogno di indici per una lettura rapida, ma dall'altro, non vogliamo vedere nel database indici superflui, poich\u00e9 consumano spazio e rallentano l'aggiornamento dei dati.<\/p>\n

<\/p>\n

E cos\u00ec, ripristinando tutti i indici non validi<\/a><\/noindex> e osservando le presentazioni di Oleg Bartunov<\/a><\/noindex>, ho deciso di fare una \u00abgrande\u00bb pulizia. Si \u00e8 scoperto che gli sviluppatori non amano leggere la documentazione del DB. Non la amano affatto. Questo porta a due errori tipici: un indice creato manualmente sulla chiave primaria e un indice simile \u00abmanuale\u00bb su una colonna unica. Il fatto \u00e8 che non sono necessari: Postgres pu\u00f2 fare tutto da solo. Questi indici possono essere tranquillamente rimossi, e per questo \u00e8 stata creata una diagnostica duplicated_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Il terzo problema \u2013 indici sovrapposti<\/h3>\n

<\/p>\n

La maggior parte degli sviluppatori principianti crea indici su una colonna. Gradualmente, dopo aver acquisito familiarit\u00e0 con la questione, le persone iniziano a ottimizzare le proprie query e ad aggiungere indici pi\u00f9 complessi, che includono pi\u00f9 colonne. Cos\u00ec nascono gli indici sulle colonne A<\/em>, A+B<\/em>, A+B+C<\/em> e cos\u00ec via. I primi due di questi indici possono essere tranquillamente eliminati, poich\u00e9 sono prefissi del terzo. Questo fa risparmiare spazio su disco e per questo esiste una diagnostica intersected_indexes<\/a><\/noindex>.<\/p>\n

<\/p>\n

Il quarto problema \u2013 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 \u00e8 un problema e pu\u00f2 anche non manifestarsi ... fino a un certo punto ...<\/p>\n

<\/p>\n

\u00c8 stato proprio cos\u00ec anche per noi: a un certo punto, un job che veniva eseguito periodicamente e puliva il database da ordini di prova ha cominciato a \u00absovraccaricare\u00bb il nostro master host. CPU e IO volavano alle stelle, le query rallentavano e venivano interrotte per timeout, il servizio restituiva errori 500. Un\u2019analisi rapida pg_stat_activity<\/a><\/noindex> ha mostrato che le query che si bloccavano erano di tipo:<\/p>\n

<\/p>\n

elimina da <table> dove id in (…)<\/code><\/pre>\n
<\/p>\n
D'altra parte, ovviamente, l'indice per id nella tabella di destinazione esisteva e le righe venivano eliminate sulla base di una condizione piuttosto semplice. Sembrava che tutto dovesse funzionare, ma, purtroppo, non funzionava.<\/p>\n
<\/p>\n
In soccorso \u00e8 arrivato il meraviglioso explain analyze<\/strong> che ha rivelato che oltre all'eliminazione delle righe nella tabella di destinazione, avviene anche un controllo dell'integrit\u00e0 referenziale, e su una delle tabelle collegate questo controllo si trasforma in sequential scan<\/em> a causa dell'assenza di un indice adeguato. Cos\u00ec \u00e8 nata la diagnostica foreign_keys_without_index<\/a><\/noindex>.<\/p>\n
<\/p>\n
Il quinto problema \u2013 valori null negli indici<\/h3>\n
<\/p>\n
Per impostazione predefinita, Postgres include i valori null negli indici btree, ma di solito non sono necessari. Per questo motivo mi sforzo di eliminare questi null (diagnostica indexes_with_null_values<\/a><\/noindex>), creando indici parziali su colonne nullable in base a 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. E in uno dei servizi, la dimensione del DB \u00e8 diminuita complessivamente del 16% (4,3 GB in cifre assolute) grazie all'esclusione dei valori nulli dagli indici. Un'enorme economia di spazio su disco con modifiche piuttosto semplici. \ud83d\ude42 <\/p>\n
<\/p>\n
Problema sei \u2013 assenza di chiavi primarie<\/h3>\n
<\/p>\n
A causa delle peculiarit\u00e0 del meccanismo MVCC in Postgres<\/a><\/noindex> pu\u00f2 verificarsi una situazione in cui bloat<\/a><\/noindex>, la dimensione della tua tabella cresce rapidamente a causa del gran numero di record morti. Credevo ingenuamente che non ci riguardasse e che la nostra base non sarebbe mai stata soggetta a ci\u00f2, perch\u00e9 noi, ahem!!!, siamo sviluppatori normali... Quanto ero stupido e ingenuo...<\/p>\n
<\/p>\n
Un giorno meraviglioso, una fantastica migrazione ha aggiornato tutte le righe in una grande tabella utilizzata attivamente. Ci siamo trovati con +100 GB alla dimensione della tabella dal nulla. Era incredibilmente deludente, ma le nostre disavventure non sono finite l\u00ec. Dopo che il vacuum automatico su questa tabella si \u00e8 concluso dopo 15 ore, \u00e8 diventato chiaro che lo spazio fisico non sarebbe tornato. Non potevamo fermare il servizio e fare un VACUUM FULL, quindi \u00e8 stata presa la decisione di utilizzare pg_repack<\/a><\/noindex>. E qui si \u00e8 scoperto che pg_repack<\/em> non sa gestire tabelle senza chiave primaria o altro vincolo di unicit\u00e0, e sulla 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 delle tabelle e degli indici e reagire tempestivamente.<\/p>\n
<\/p>\n
Problemi sette e otto \u2013 mancanza di indici e indici non utilizzati<\/h3>\n
<\/p>\n
Le due diagnosi successive sono tables_with_missing_indexes<\/a><\/noindex> e unused_indexes<\/a><\/noindex> \u2013 nella loro forma finale sono apparse relativamente di recente. Il fatto \u00e8 che non potevano semplicemente essere aggiunte cos\u00ec.<\/p>\n
<\/p>\n
Come ho gi\u00e0 scritto, utilizziamo una configurazione con pi\u00f9 repliche, e il carico di lettura su host diversi \u00e8 fondamentalmente diverso. Di conseguenza, si verifica una situazione in cui alcune tabelle e indici su alcuni host vengono praticamente non utilizzati, e per l'analisi \u00e8 necessario raccogliere statistiche da tutti gli host nel cluster. Resettare le statistiche<\/a><\/noindex> deve essere fatto anche su ogni host nel cluster, non si pu\u00f2 farlo solo sul master.<\/p>\n
<\/p>\n
Questo approccio ci ha permesso di risparmiare diverse decine di gigabyte rimuovendo gli indici che non sono mai stati utilizzati e aggiungendo gli indici mancanti su tabelle poco usate.<\/p>\n
<\/p>\n
In conclusione<\/h3>\n
<\/p>\n
Naturalmente, praticamente per tutte le diagnosi \u00e8 possibile configurare l'elenco delle eccezioni<\/a><\/noindex>. In questo modo \u00e8 possibile implementare rapidamente i controlli nella tua applicazione, prevenendo l'insorgere di nuovi errori e successivamente correggendo quelli esistenti.<\/p>\n
<\/p>\n
Parte delle diagnosi possono essere eseguite gi\u00e0 nei test funzionali subito dopo l'applicazione delle migrazioni del database. E questo, forse, \u00e8 una delle funzionalit\u00e0 pi\u00f9 potenti della mia libreria. Puoi vedere un esempio di utilizzo in una demo<\/a><\/noindex>.<\/p>\n
<\/p>\n
I controlli per indici non utilizzati o mancanti, cos\u00ec come per il bloat, hanno senso solo su un database reale. I valori raccolti possono essere salvati 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 riscontrati e proponendo 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\udd47Lo stato 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}]}}