MySQL InnoDB non rilascia spazio su disco dopo l’eliminazione delle righe di dati dalla tabella

Ho una tabella MySQL che utilizza il motore di archiviazione InnoDB; contiene circa 2 milioni di righe di dati. Quando ho cancellato le righe di dati dalla tabella, non ha rilasciato spazio su disco allocato. Né la dimensione del file ibdata1 si è ridotta dopo l’esecuzione del comando di optimize table .

C’è un modo per recuperare spazio su disco da MySQL?

Sono in una brutta situazione; questa applicazione è in esecuzione in circa 50 posizioni diverse e ora quasi tutti hanno problemi di spazio su disco insufficiente.

MySQL non riduce le dimensioni di ibdata1. Mai. Anche se utilizzi la optimize table per liberare lo spazio utilizzato dai record eliminati, la riutilizzerà in seguito.

Un’alternativa è configurare il server per l’uso di innodb_file_per_table , ma ciò richiederà un backup, il drop del database e il ripristino. Il lato positivo è che il file .ibd per la tabella viene ridotto dopo una optimize table .

Ho avuto lo stesso problema anch’io.

Quello che succede è che, anche se si rilascia il database, innodb non rilascerà ancora spazio su disco. Ho dovuto esportare, fermare mysql, rimuovere i file manualmente, avviare mysql, creare database e utenti e quindi importare. Grazie a dio avevo solo 200 MB di file, ma risparmiava 250 GB di file innodb.

Fallire dal design.

Se non si utilizza innodb_file_per_table , è ansible recuperare spazio su disco, ma piuttosto noioso e richiede una notevole quantità di tempo di inattività.

The How To è piuttosto approfondito – ma ho incollato la parte pertinente qui sotto.

Assicurati di conservare anche una copia del tuo schema nel tuo dump.

Attualmente, non è ansible rimuovere un file di dati dal tablespace di sistema. Per ridurre le dimensioni del tablespace di sistema, utilizzare questa procedura:

Usa mysqldump per scaricare tutte le tue tabelle InnoDB.

Arresta il server.

Rimuovi tutti i file tablespace esistenti, inclusi i file ibdata e ib_log. Se si desidera conservare una copia di backup delle informazioni, copiare tutti i file ib * in un’altra posizione prima di rimuovere i file nell’installazione MySQL.

Rimuovi tutti i file .frm per le tabelle InnoDB.

Configura un nuovo tablespace.

Riavvia il server.

Importa i file di dump.

Un altro modo per risolvere il problema della rigenerazione dello spazio è: Creare più partizioni all’interno di una tabella. Partizioni basate sul valore e basta rilasciare / troncare la partizione per recuperare lo spazio, che rilascerà lo spazio utilizzato da tutti i dati memorizzati nella partizione specifica.

Ci saranno alcune modifiche necessarie nello schema della tabella quando si introduce il partizionamento per la tabella come – Chiavi univoche, indici per includere la colonna di partizione ecc.

Esistono diversi modi per recuperare lo spazio su disco dopo aver eliminato i dati dalla tabella per il motore Inodb MySQL

Se non si utilizza innodb_file_per_table dall’inizio, il dumping di tutti i dati, l’eliminazione di tutti i file, la ricreazione del database e l’importazione dei dati di nuovo è solo un modo (controllare le risposte di FlipMcF sopra)

Se stai usando innodb_file_per_table, puoi provare

  1. Se è ansible eliminare tutti i dati, il comando truncate eliminerà i dati e recupererà lo spazio su disco per te.
  2. Il comando Alter table elimina e ricrea la tabella in modo che possa recuperare spazio su disco. Quindi dopo aver cancellato i dati, esegui alter table che non modifica nulla per rilasciare hardisk (es .: tabella TBL_A ha charset uf8, dopo aver cancellato i dati run ALTER TABLE TBL_A set di caratteri utf8 -> questo comando non modifica nulla dalla tabella ma rende mysql ricreare la tua tabella e riguadagnare spazio sul disco
  3. Crea TBL_B come TBL_A. Inserisci i dati selezionati che vuoi conservare da TBL_A a TBL_B. Rilasciare TBL_A e rinominare TBL_B in TBL_A. In questo modo è molto efficace se TBL_A e i dati che devono essere eliminati sono di grandi dimensioni (il comando delete in MySQL innodb ha prestazioni molto scarse)