Ciao.
Mi chiamo Vania e sono uno sviluppatore Java. È 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.
Negli ultimi tempi ho sistemato diversi database nei nostri microservizi e ho scritto una libreria Java , che semplifica questo lavoro, risparmia il mio tempo e aiuta a evitare alcuni errori comuni commessi dagli sviluppatori. Proprio di questa libreria parleremo oggi.
Avviso
La versione principale di PostgreSQL con cui lavoro è la 10. Tutte le query SQL che utilizzo sono state testate anche sulla versione 11. La versione minima supportata è la 9.6.
Antefatti
Tutto è iniziato quasi un anno fa con una situazione strana per me: la creazione concorrente di un indice in un luogo qualsiasi si è conclusa con un errore. L'indice stesso, come spesso accade, è rimasto in uno stato non valido nel database. L'analisi dei log ha evidenziato la mancanza di . E così è cominciata… Scavando più 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.
Il primo problema – la configurazione predefinita
Probabilmente, la metafora di Postgres che può essere eseguito su una macchina da caffè è già diventata fastidiosa, ma… la configurazione predefinita solleva davvero molte 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 64 MB per impostazione predefinita, mentre temp_file_limit circa 2 GB – ci mancava semplicemente memoria per creare un indice su una grande tabella.
Pertanto, in pg-index-health ho raccolto una serie di , a mio avviso, chiave, che vale la pena configurare per ogni database.
Il secondo problema – indici duplicati
I nostri database risiedono su dischi SSD e utilizziamo HA-configurazione con più centri dati, un master host e n-un numero di repliche. Lo spazio su disco è una risorsa molto preziosa per noi; è 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é consumano spazio e rallentano l'aggiornamento dei dati.
E così, ripristinando tutti i e osservando , ho deciso di fare una «grande» pulizia. Si è 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 «manuale» su una colonna unica. Il fatto è che non sono necessari: Postgres può fare tutto da solo. Questi indici possono essere tranquillamente rimossi, e per questo è stata creata una diagnostica .
Il terzo problema – indici sovrapposti
La maggior parte degli sviluppatori principianti crea indici su una colonna. Gradualmente, dopo aver acquisito familiarità con la questione, le persone iniziano a ottimizzare le proprie query e ad aggiungere indici più complessi, che includono più colonne. Così nascono gli indici sulle colonne A, A+B, A+B+C e così via. I primi due di questi indici possono essere tranquillamente eliminati, poiché sono prefissi del terzo. Questo fa risparmiare spazio su disco e per questo esiste una diagnostica .
Il quarto problema – chiavi esterne senza indici
Postgres consente di creare vincoli di chiave esterna senza specificare un indice di supporto. In molte situazioni questo non è un problema e può anche non manifestarsi ... fino a un certo punto ...
È stato proprio così anche per noi: a un certo punto, un job che veniva eseguito periodicamente e puliva il database da ordini di prova ha cominciato a «sovraccaricare» il nostro master host. CPU e IO volavano alle stelle, le query rallentavano e venivano interrotte per timeout, il servizio restituiva errori 500. Un’analisi rapida ha mostrato che le query che si bloccavano erano di tipo:
elimina da <table> dove id in (…)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.
In soccorso è arrivato il meraviglioso explain analyze che ha rivelato che oltre all'eliminazione delle righe nella tabella di destinazione, avviene anche un controllo dell'integrità referenziale, e su una delle tabelle collegate questo controllo si trasforma in sequential scan a causa dell'assenza di un indice adeguato. Così è nata la diagnostica .
Il quinto problema – valori null negli indici
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 ), creando indici parziali su colonne nullable in base a where is not nullIn 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 è 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. 🙂
Problema sei – assenza di chiavi primarie
A causa delle peculiarità del meccanismo può verificarsi una situazione in cui , 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ò, perché noi, ahem!!!, siamo sviluppatori normali... Quanto ero stupido e ingenuo...
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ì. Dopo che il vacuum automatico su questa tabella si è concluso dopo 15 ore, è diventato chiaro che lo spazio fisico non sarebbe tornato. Non potevamo fermare il servizio e fare un VACUUM FULL, quindi è stata presa la decisione di utilizzare . E qui si è scoperto che pg_repack non sa gestire tabelle senza chiave primaria o altro vincolo di unicità, e sulla 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 delle tabelle e degli indici e reagire tempestivamente.
Problemi sette e otto – mancanza di indici e indici non utilizzati
Le due diagnosi successive sono e – nella loro forma finale sono apparse relativamente di recente. Il fatto è che non potevano semplicemente essere aggiunte così.
Come ho già scritto, utilizziamo una configurazione con più repliche, e il carico di lettura su host diversi è 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 è necessario raccogliere statistiche da tutti gli host nel cluster. deve essere fatto anche su ogni host nel cluster, non si può farlo solo sul master.
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.
In conclusione
Naturalmente, praticamente per tutte le diagnosi è possibile configurare . In questo modo è possibile implementare rapidamente i controlli nella tua applicazione, prevenendo l'insorgere di nuovi errori e successivamente correggendo quelli esistenti.
Parte delle diagnosi possono essere eseguite già nei test funzionali subito dopo l'applicazione delle migrazioni del database. E questo, forse, è una delle funzionalità più potenti della mia libreria. Puoi vedere un esempio di utilizzo in .
I controlli per indici non utilizzati o mancanti, così come per il bloat, hanno senso solo su un database reale. I valori raccolti possono essere salvati 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 riscontrati e proponendo nuove diagnosi.
Fonte: habr.com
