Hey.
Mi chiamo Vanya e sono uno sviluppatore Java. Si dà il caso che lavoro molto con PostgreSQL: impostando il database, ottimizzando la struttura, le prestazioni e giocando un po' con DBA nei fine settimana.
Recentemente ho riordinato diversi database nei nostri microservizi e ho scritto una libreria Java , che semplifica questo lavoro, mi fa risparmiare tempo e mi aiuta a evitare alcuni errori comuni commessi dagli sviluppatori. È di questa biblioteca di cui parleremo oggi.
Negazione di responsabilità
La versione principale di PostgreSQL con cui lavoro è la 10. Tutte le query SQL che utilizzo sono testate anche sulla versione 11. La versione minima supportata è la 9.6.
Sfondo
Tutto è iniziato quasi un anno fa con una situazione per me strana: la creazione competitiva di un indice all'improvviso si è conclusa con un errore. L'indice stesso, come al solito, è rimasto nel database in uno stato non valido. L'analisi dei registri ha mostrato una carenza . E si parte... Scavando più a fondo, ho scoperto un sacco di problemi nella configurazione del database e, rimboccandomi le maniche, ho iniziato a risolverli con una scintilla negli occhi.
Problema uno: configurazione predefinita
Probabilmente tutti sono piuttosto stanchi della metafora su Postgres, che può essere eseguito su una macchina per il caffè, ma... la configurazione predefinita solleva davvero una serie di domande. Come minimo, vale la pena prestare attenzione manutenzione_lavoro_mem, limite_file_temp, timeout_istruzione и lock_timeout.
Nel nostro caso, manutenzione_lavoro_mem era il valore predefinito di 64 MB e limite_file_temp qualcosa intorno ai 2 GB: semplicemente non avevamo memoria sufficiente per creare un indice su una tabella di grandi dimensioni.
Pertanto, in pg-indice-salute Ho raccolto una serie , a mio avviso, i parametri che dovrebbero essere configurati per ciascun database.
Problema due: indici duplicati
I nostri database risiedono su unità SSD e utilizziamo HA-configurazione con più data center, host master e n-numero di repliche. Lo spazio su disco è per noi una risorsa molto preziosa; non è meno importante delle prestazioni e del consumo della CPU. Pertanto, da un lato, abbiamo bisogno di indici per una lettura veloce e, dall'altro, non vogliamo vedere indici non necessari nel database, poiché consumano spazio e rallentano l'aggiornamento dei dati.
E ora, avendo ripristinato tutto e aver visto abbastanza , ho deciso di organizzare una “grande” epurazione. Si è scoperto che agli sviluppatori non piace leggere la documentazione del database. A loro non piace molto. Per questo motivo, si verificano due errori tipici: un indice creato manualmente su una chiave primaria e un indice "manuale" simile su una colonna univoca. Il fatto è che non sono necessari: Postgres farà tutto da solo. Tali indici possono essere eliminati in modo sicuro e la diagnostica è apparsa a questo scopo .
Problema tre: indici che si intersecano
La maggior parte degli sviluppatori alle prime armi crea indici su una singola colonna. A poco a poco, dopo aver sperimentato a fondo questa attività, le persone iniziano a ottimizzare le proprie query e ad aggiungere indici più complessi che includono diverse colonne. Ecco come appaiono gli indici sulle colonne A, A + B, A + B + C e così via. I primi due di questi indici possono essere tranquillamente scartati, poiché sono prefissi del terzo. Ciò consente anche di risparmiare molto spazio su disco e per questo è disponibile la diagnostica .
Problema quattro: chiavi esterne senza indici
Postgres ti consente di creare vincoli di chiave esterna senza specificare un indice di supporto. In molte situazioni questo non è un problema e potrebbe anche non manifestarsi... Per il momento...
È stato lo stesso con noi: è solo che ad un certo punto un lavoro, eseguito secondo un programma e cancellando il database dagli ordini di prova, ha iniziato ad essere "aggiunto" a noi dall'host principale. CPU e IO sono andati sprecati, le richieste sono rallentate e sono andate in timeout, il servizio era cinquecento. Analisi rapida ha mostrato che query come:
delete from <table> where id in (…)In questo caso, ovviamente, nella tabella di destinazione era presente un indice per ID e pochissimi record venivano eliminati in base alla condizione. Sembrava che tutto dovesse funzionare ma, ahimè, non è stato così.
Il meraviglioso è venuto in soccorso spiegare analizzare e ha detto che oltre all'eliminazione dei record nella tabella di destinazione, esiste anche un controllo dell'integrità referenziale e su una delle tabelle correlate questo controllo fallisce scansione sequenziale per la mancanza di un indice adeguato. Così è nata la diagnostica .
Problema cinque: valore nullo negli indici
Per impostazione predefinita, Postgres include valori null negli indici btree, ma di solito non sono necessari lì. Pertanto, cerco diligentemente di eliminare questi valori nulli (diagnostics ), creando indici parziali su colonne nullable per tipo where <A> is not null. In questo modo ho potuto ridurre la dimensione di uno dei nostri indici da 1877 MB a 16 KB. E in uno dei servizi, la dimensione del database è diminuita complessivamente del 16% (di 4.3 GB in numeri assoluti) a causa dell'esclusione dei valori nulli dagli indici. Enorme risparmio di spazio su disco con modifiche molto semplici. 🙂
Problema sei: mancanza di chiavi primarie
A causa della natura del meccanismo una situazione come questa è possibile quando le dimensioni della tabella crescono rapidamente a causa di un gran numero di record obsoleti. Credevo ingenuamente che questo non ci avrebbe minacciato e che questo non sarebbe successo alla nostra base, perché noi, wow!!!, siamo sviluppatori normali... Quanto sono stato stupido e ingenuo...
Un giorno, una meravigliosa migrazione ha preso e aggiornato tutti i record in una tabella ampia e utilizzata attivamente. Abbiamo ottenuto all'improvviso +100 GB alle dimensioni della tabella. È stato un vero peccato, ma le nostre disavventure non sono finite qui. Dopo che l'auto-vuoto su questo tavolo si è concluso 15 ore dopo, è diventato chiaro che la posizione fisica non sarebbe tornata. Non potevamo interrompere il servizio e rendere il VACUUM FULL, quindi abbiamo deciso di utilizzarlo . E poi si è scoperto che pg_repack non sa come elaborare le tabelle senza una chiave primaria o altri vincoli di unicità e la nostra tabella non aveva una chiave primaria. Così è nata la diagnostica .
Nella versione da libreria 0.1.5 È stata aggiunta la possibilità di raccogliere dati da numerose tabelle e indici e di rispondere in modo tempestivo.
Problemi sette e otto: indici insufficienti e indici inutilizzati
Le due diagnostiche seguenti sono: и – sono apparsi nella loro forma finale relativamente di recente. Il punto è che non potevano semplicemente essere presi e aggiunti.
Come ho già scritto, utilizziamo una configurazione con più repliche e il carico di lettura su host diversi è fondamentalmente diverso. Di conseguenza, la situazione risulta che alcune tabelle e indici su alcuni host non vengono praticamente utilizzati e per l'analisi è necessario raccogliere statistiche da tutti gli host nel cluster. Ciò è necessario anche su ogni host del cluster; non è possibile farlo solo sul master.
Questo approccio ci ha permesso di risparmiare diverse decine di gigabyte rimuovendo gli indici mai utilizzati e aggiungendo indici mancanti alle tabelle utilizzate raramente.
In conclusione
Naturalmente è possibile configurare quasi tutte le diagnostiche . In questo modo, puoi implementare rapidamente i controlli nella tua applicazione, impedendo la comparsa di nuovi errori e quindi correggere gradualmente quelli vecchi.
Alcuni test diagnostici possono essere eseguiti nei test funzionali immediatamente dopo l'implementazione delle migrazioni del database. E questa è forse una delle caratteristiche più potenti della mia libreria. Un esempio di utilizzo si trova in .
Ha senso eseguire controlli per indici inutilizzati o mancanti, nonché per bloat, solo su un database reale. I valori raccolti possono essere registrati in o inviato al sistema di monitoraggio.
Lo spero davvero pg-indice-salute sarà utile e richiesto. Puoi anche contribuire allo sviluppo della libreria segnalando i problemi riscontrati e suggerendo nuove diagnostiche.
Fonte: habr.com