Ciao.
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.
Di recente ho sistemato alcuni database nei nostri microservizi e ho scritto una libreria java , che semplifica questo lavoro, risparmia tempo e aiuta a evitare alcuni errori comuni commessi dagli sviluppatori. Proprio di questa libreria parleremo oggi.
Avvertenza
La versione principale di PostgreSQL con cui lavoro è la 10. Tutte le query SQL da me utilizzate sono state verificate anche sulla versione 11. La versione minima supportata è la 9.6.
Contesto
Tutto è iniziato quasi un anno fa con una situazione strana per me: la creazione concorrente di un indice inaspettatamente si è conclusa con un errore. L'indice stesso, come spesso accade, è rimasto in uno stato non valido nel database. L'analisi dei log ha mostrato una mancanza di . E così è iniziata l'avventura… Approfondendo la questione, ho scoperto una serie di problemi nella configurazione del database e, rimboccandomi le maniche, ho iniziato a sistemarli con occhi brillanti.
Il primo problema è la configurazione di default
Forse la metafora di Postgres avviabile su una macchina da caffè ha stufato, ma… la configurazione predefinita solleva davvero una serie di domande. Al minimo, vale la pena prestare attenzione a maintenance_work_mem, temp_file_limit, statement_timeout e lock_timeout.
Nel nostro caso maintenance_work_mem era di default 64 MB, mentre temp_file_limit ci servivano circa 2 GB; ci mancava letteralmente memoria per creare un indice su una grande tabella.
Quindi, ho raccolto una serie di pg-index-health parametri Il secondo problema è gli indici duplicati
I nostri database risiedono su dischi SSD, e utilizziamo
-configurazione con più data center, un host master e HA-numero totale di repliche. Lo spazio su disco è una risorsa molto preziosa per noi; è 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é consumano spazio e rallentano l'aggiornamento dei dati. nE così, recuperati tutti
indici non validi le relazioni di Oleg Bartunov, ho deciso di effettuare una "grande" pulizia. Si è 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 è che non sono necessari: Postgres si occuperà di tutto. Questi indici possono essere eliminati senza problemi, e per questo è stata creata la diagnosi , ho deciso di fare una "grande" pulizia. Si è scoperto che gli sviluppatori non amano leggere la documentazione del database. Non la amano affatto. A causa di questo, sorgono due errori tipici – un indice creato manualmente sulla chiave primaria e un indice "manuale" simile su una colonna unica. Il fatto è che non sono necessari – Postgres farà tutto da solo. Questi indici possono essere tranquillamente rimossi, e per questo è stata introdotta una diagnostica. .
Il terzo problema è gli indici sovrapposti
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ù complessi che includono più colonne. Così si creano indici su colonne A, A+B, A+B+C ecc. I primi due di questi indici possono essere tranquillamente eliminati, poiché sono prefissi del terzo. Questo aiuta a risparmiare spazio su disco e per questo c'è la diagnosi .
Il quarto problema è le chiavi esterne senza indici
Postgres consente di creare vincoli di chiave esterna senza specificare un indice di supporto. In molte situazioni questo non rappresenta un problema e può anche non manifestarsi… Fino a un certo punto…
È stato così 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 ha mostrato che le query come queste si bloccavano:
elimina da <table> dove id in (…)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è, non funzionava.
A soccorrere è giunto il meraviglioso explain analyze che ha rivelato che oltre all'eliminazione dei record nella tabella target, veniva anche eseguita una verifica dell'integrità referenziale, e su una delle tabelle correlate questa verifica cadeva in sequential scan a causa dell'assenza di un indice adeguato. Così è nata la diagnosi .
Il quinto problema è il valore null negli indici
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 ), creando indici parziali su colonne nullable del tipo where is not null. 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 è 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. 🙂
Il sesto problema – mancanza di chiavi primarie
A causa delle peculiarità del meccanismo si può verificare una situazione in cui 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é noi, oh wow!!!, siamo sviluppatori normali… Quanto ero sciocco e ingenuo…
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. È stato davvero frustrante, ma le nostre disavventure non sono finite lì. Dopo che l'autovacuum su questa tabella è 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 . E qui si è scoperto che pg_repack non è in grado di gestire tabelle senza una chiave primaria o altra limitazione di unicità, e nella nostra tabella non c'era una chiave primaria. Così è nata la diagnosi .
Nella versione della libreria 0.1.5 è stata aggiunta la possibilità di raccogliere dati sul bloat di tabelle e indici e di reagire in modo tempestivo.
I problemi sette e otto – mancanza di indici e indici non utilizzati
Le due diagnosi successive – e – sono comparse nel loro formato finale relativamente di recente. Questo perché non potevano essere semplicemente aggiunte.
Come ho già scritto, utilizziamo una configurazione con più repliche, e il carico di lettura su diversi host è fondamentalmente diverso. Ne consegue che alcune tabelle e indici su alcuni host vengano praticamente non utilizzati, e per l'analisi è necessario raccogliere statistiche da tutti gli host nel cluster. è necessario farlo anche su ogni host nel cluster, non può essere fatto solo sul master.
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.
In conclusione
Ovviamente, per praticamente tutte le diagnosi si può configurare un . In questo modo è possibile implementare rapidamente controlli nella tua applicazione, prevenendo la comparsa di nuovi errori, e poi correggere gradualmente quelli vecchi.
Parte delle diagnosi può essere eseguita già nei test funzionali subito dopo l'applicazione delle migrazioni del DB. E questo, forse, è una delle funzionalità più potenti della mia libreria. Un esempio di utilizzo può essere visto in .
I controlli per indici non utilizzati o mancanti, così come per il bloat, hanno senso farli solo su un DB reale. I valori raccolti possono essere registrati in o inviati a un sistema di monitoraggio.
Spero davvero che pg-index-health sia utile e richiesta. Puoi anche contribuire allo sviluppo della libreria segnalando problemi trovati e suggerendo nuove diagnosi.
Fonte: habr.com
