L'espansione della tabella e dell'indice si verifica quando l'operazione di pulizia non tiene il passo con il tasso di DML (Data Manipulation Language) rispetto a una tabella. Alcuni problemi di gonfiore di tabelle e indici sono inevitabili e generalmente accettabili perché le transazioni possono riutilizzare lo spazio. L'eccessivo bloat causa problemi di prestazioni, come tempi di query più lenti, aumento dell'I/O, utilizzo meno efficiente del buffer condiviso e della cache e aumento dei costi di archiviazione.
Il modo migliore per stimare l'aumento delle dimensioni è utilizzare il modulo
pgstattuple. Tuttavia, l'utilizzo di pgstattuple è meno efficiente quando l'estensione deve
leggere tutte le pagine di una relazione. Se non puoi utilizzare pgstattuple, puoi utilizzare
altri script per stimare il bloat, anche se questo approccio fornisce una precisione inferiore.
Leggere l'output di pgstattuple
Quando utilizzi pgstattuple per segnalare le caratteristiche di archiviazione fisica di una tabella o di un indice, l'output è nel seguente formato:
> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len | 7431888896
tuple_count | 66666666
tuple_len | 2796296271
tuple_percent | 37.63
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 3873153652
free_percent | 52.12
Di seguito è descritto l'output:
table_len(7431888896)- Le dimensioni totali della tabella in byte. Include tutte le tuple attive, le tuple
eliminate e lo spazio libero. In questo caso,
large_tableoccupa circa 7088 GB. tuple_count(66666666)- Rappresenta il numero di tuple attive, ovvero righe visibili e attive, nella tabella. La tabella contiene circa 66 milioni di righe attive.
tuple_len(2796296271)- La lunghezza totale in byte di tutte le tuple live combinate, ovvero circa 2,6 GB.
tuple_percent(37.63)La percentuale di
table_lenoccupati dalle tuple live. Si calcola in questo modo:(
tuple_len/table_len) ∗ 100 = (2796296271/7431888896) ∗ 100 ≈37.63percentoQuesto output indica che i dati attivi utilizzano meno del 40% dello spazio su disco della tabella.
dead_tuple_count(0)Il numero di tuple non valide, ovvero righe che le operazioni di vacuum non hanno ancora rimosso dopo aggiornamenti o eliminazioni. Un valore pari a
0indica che la tabella è pulita e non contiene tuple non più utilizzate in attesa di essere recuperate. Ciò spesso suggerisce cheVACUUMo autovacuum viene eseguito in modo efficiente sulla tabella.dead_tuple_len(0)La lunghezza totale in byte di tutte le tuple morte. In linea con
dead_tuple_count, un valore di0indica che le righe morte non consumano spazio.dead_tuple_percent(0)La percentuale di
table_lenoccupata dalle tuple morte. Si calcola in questo modo:(
dead_tuple_len/table_len) ∗ 100 = (0/7431888896) ∗ 100 =0percentofree_space(3873153652)Questo valore rappresenta la quantità di spazio libero nelle pagine di dati della tabella. Puoi riutilizzare questo spazio per nuovi inserimenti o aggiornamenti senza richiedere l'espansione della tabella. Ciò indica una quantità significativa di spazio libero, circa 3,7 GB.
free_percent(52.12)Questo valore rappresenta la percentuale di spazio libero del
table_len. Si calcola nel seguente modo:(
free_space/table_len) ∗ 100 = (3873153652/7431888896) ∗ 100 ≈52.12percento
Stimare l'espansione utilizzando le query
Puoi stimare il bloat utilizzando query sulle tabelle del catalogo di sistema, anche se questo metodo è meno preciso rispetto all'utilizzo di estensioni come pgstattuple. Se utilizzi script come
PostgreSQL table bloat check,
le statistiche della tabella devono essere aggiornate, altrimenti la stima non è accurata.
Quando confronti l'output di una query con il modulo pgstattuple, le stime sono comparabili, ma non sono misurazioni esatte del bloat. Il modulo è
ancora l'indicatore più preciso di bloat perché la query SQL si basa sul
motore di statistiche cumulative di PostgreSQL, mentre il modulo legge le pagine di dati
effettive.
L'esempio seguente confronta l'output di due metodi diversi per analizzare l'espansione della tabella in PostgreSQL per una tabella denominata large_table.
> SELECT * FROM pgstattuple('large_table');
-[ RECORD 1 ]------+-----------
table_len | 2475892736
tuple_count | 22000000
tuple_len | 922925931
tuple_percent | 37.28
dead_tuple_count | 0
dead_tuple_len | 0
dead_tuple_percent | 0
free_space | 1293696900
free_percent | 52.25
Vs.
-[ RECORD 1 ]+------------
databasename | postgres
schemaname | public
tablename | large_table
can_estimate | t
est_rows | 21965400
pct_bloat | 54
mb_bloat | 1269.24
table_mb | 2361.195
Eccesso di indirizzi
Puoi gestire il bloat nei seguenti modi, ognuno dei quali presenta vantaggi e svantaggi che richiedono un'attenta pianificazione:
- Esegui un'aspirazione completa. Questa operazione richiede un blocco completo della tabella per la durata del vacuum e richiede tempi di inattività.
- Sposta i dati nella nuova tabella. Per utilizzare questo metodo, devi sospendere le modifiche alla tabella per assicurarti che non vengano trasferite alla nuova relazione.
- Utilizza
pg_squeeze. Questa estensione riduce il bloat online. L'estensione crea una tabella shadow, inserisce tutti i dati e poi mantiene le modifiche alla tabella utilizzando uno slot di replica fino all'ultimo passaggio, che consiste nello scambio della tabella gonfia con la tabella non gonfia appena creata. Poi, richiede un breve blocco della tabella per spostare la tabella non espansa in modo da sostituire quella espansa. Questo metodo richiede il doppio dello spazio dell'oggetto per la durata dell'operazione, ma è il meno invasivo per il normale funzionamento.