Le tabelle gestite dal DBMS MySQL sono spesso sottoposte a numerose sollecitazioni sia da parte degli utilizzatori (accessi ai database, aggiornamenti, estrazioni e cancellazioni di dati), sia dalla macchina Host in cui sono archiviate (traffico, downtime, crash di sistema e così via).
Queste sollecitazioni producono degli effetti sui dati memorizzati che alla lunga possono modificarne la struttura e danneggiarne la coerenza; per evitare situazioni spiacevoli o per riparare malfunzionamenti è sempre bene operare una manutenzione costante sulle tabelle attraverso gli strumenti che MySQL stesso ci mette a disposizione.
Nel corso di questo breve articolo analizzeremo la sintassi e le funzioni di alcuni dei più importanti comandi per la manutenzione.
ANALYZE TABLE
ANALYZE TABLE è un comando che ci permette di recuperare informazioni riguardanti lo stato di una determinata tabella al momento del monitoraggio.
Questa istruzione consente di analizzare e memorizzare la distribuzione delle chiavi all'interno di una tabella; nelle tabelle di tipo MyISAM e BDB la tabella subisce un processo di locking in lettura; mentre per quanto riguarda le tabelle InnoDB queste sono interessate da un lock in scrittura.
ANALYZE TABLE funziona quindi per le tipologie di tabelle più diffuse e per quelle di tipo MyISAM ed è praticamente un comando omologo all'istruzione myisamchk --analyze.
Il DBMS utilizza la memorizzazione della distribuzione delle chiavi per stabilire l'ordine in cui le tabelle saranno relazionate nelle richieste di join, l'utilizzatore dovrà quindi avere i privilegi di inserimento ed estrazione dei dati per poter lanciare il comando ANALYZE TABLE.
L'output generato da questo comando è costituito da 4 colonne:
- Il nome della tabella, che avrà appunto questo dato come valore ("table").
- Il dato relativo all'operazione effettuata, che sarà sempre "analyze" ("op").
- La tipologia di messaggio restituito che potrà essere per esempio una notifica di status, un errore, un info o un warning ("Msg_type").
- Il testo stesso della notifica, diverso a seconda dell'esito seguito all'operazione di analisi della tabella passata come parametro ("Msg_text").
Il processo di archiviazione dell'ordinamento delle chiavi prodotto dal comando potrà a sua volta essere monitorato grazie all'istruzione SHOW INDEX. Nel caso in cui dopo l'esecuzione di un comando ANALYSE TABLE la tabella non subisca alcuna modifica, una esecuzione successiva non produrrà una nuova analisi.
Sintassi:
ANALYZE TABLE nome_tabella [, nome_tabella2..]
CHECK TABLE
CHECK TABLE è un comando che può essere applicato a più tabelle contemporaneamente e consente di localizzare in essa eventuali errori. Questa istruzione può operare su tabelle di tipo MyISAM e InnoDB nella maggior parte delle versioni del DBMS ma è accettato soltanto da MySQL 5.0.16 o superiore per le tabelle di tipo ARCHIVE.
Nelle versioni di MySQL come la 5.0.2 o superiore, questo comando dà anche la possibilità di monitorare una tabella alla ricerca di malfunzionamenti dovuti all'errata gestione della Viste, come nel caso di referenze a Viste non più definite.
L'output prodotto da CHECK TABLE possiede la stessa struttura del comando ANALYSE TABLE proposto in precedenza (la colonna "op" sarà valorizzata in questo caso con "check") Per quanto riguarda i valori contenuti sono invece da tenere in considerazione le colonne "Msg_type" e "Msg_text"; in caso di funzionamento ottimale infatti la prima dovrebbe contenere il valore "status" mentre la seconda dovrebbe essere pari a "ok". Se così non fosse bisognerà ricorrere a comandi per la riparazione delle tabelle.
In alternativa ad "ok" può essere presente il valore Table is already up to date, il che indica che la tabella non necessita di operazioni di checking.
Dalla versione 5.0.19 di MySQL è stata introdotta l'opzione FOR UPGRADE che può appunto tornare utile nel caso di operazioni di aggiornamento. In pratica questa opzione permette al comando di rilevare incompatibilità nei tipi di dati presenti all'interno delle tabelle o negli indici utilizzati. Nel caso in cui siano effettivamente presenti possibili incompatibilità, allora il DBMS indicherà nel relativo file ".frm" della tabella la versione corrente di MySQL dopo un monitoraggio completo della stessa, quest'indicazione permetterà in futuro di operare check più rapidi in caso di trasferimenti tra versioni equivalenti.
Vi sono poi altre opzioni che possono essere utilizzate nella creazione di istruzioni contenenti il comando CHECK TABLE:
- QUICK permette di non effettuare scansioni sui record per la ricerca di collegamenti non corretti.
- FAST controlla unicamente le tabelle che non sono state chiuse in modo corretto.
- CHANGED ignora le tabelle che non hanno subito modifiche dall'ultimo controllo e che non sono state chiuse correttamente.
- MEDIUM permette di effettuare una scansione sui record per verificare la validità dei collegamenti; inoltre consente di calcolare i checksum per verificare l'integrità dei record e delle chiavi.
- EXTENDED effettua una scansione completa di record e chiavi; l'esecuzione di questo comando può richiedere anche parecchio tempo ma può essere utile per monitorare la consistenza della tabella passata come parametro.
Nel caso in cui non siano specificate le opzioni QUICK, MEDIUM o EXTENDED, per le tabelle di tipo MyISAM l'opzione di default sarà MEDIUM, se non vengono specificate le opzioni CHANGED e FAST allora l'opzione predefinita sarà QUICK. In ogni caso sarà possibile combinare più opzioni per ottenere check adatti alle proprie esigenze di monitoraggio.
Sintassi:
CHECK TABLE nome_tabella [, nome_tabella..] ... [opzione] ...
opzioni = {FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED}
OPTIMIZE TABLE
OPTIMIZE TABLE è un comando che può essere utilizzato in seguito all'eliminazione di grosse quantità di record o dopo consistenti cambiamenti a carico dei dati archiviati. In pratica la funzione svolta da questo comando è quella di recuperare spazio inutilizzato e di deframmentare i file contenenti i dati.
Per poter essere utilizzato, OPTIMIZE TABLES prevede i privilegi di estrazione ed inserimento dei dati e possono essere passate come parametri soltanto tabelle di tipo MyISAM, InnoDB e ARCHIVE, l'ultima tipologia è prevista solo da MySQL 5.0.16. Inoltre è bene sapere che al momento dell'ottimizzazione le tabelle interessate subiscono un lock in lettura e scrittura.
OPTIMIZE TABLE ripara una tabella MyISAM nel caso in cui vengano trovati dei record cancellati o frammentati, estrae le pagine indice nel caso in cui queste non lo siano e aggiorna le statistiche della tabella. Per le tabelle di tipo InnoDB il comando è ricompresso nelle istruzioni ALTER TABLE con le quali è possibile ricostruire le tabelle per l'aggiornamento degli indici e l'eliminazione dello spazio inutilizzato.
L'output restituito dall'esecuzione di OPTIMIZE TABLE è lo stesso descritto per i due comandi di manutenzione elencati in precedenza, ma in questo caso la colonna "op" presenterà il valore "optimize".
Sintassi:
OPTIMIZE TABLE nome_tabella [, nome_tabella..]
REPAIR TABLE e RESTORE TABLE
REPAIR TABLE è un comando che viene utilizzato per la riparazione di tabelle corrotte ed è equivalente all'istruzione myisamchk --recover nome_tabella; può essere utilizzato per tabelle di tipo MyISAM e ARCHIVE e necessita dei privilegi di inserimento ed estrazione dei record.
Si tratta di un comando di emergenza, da utilizzare soltanto in caso di gravi malfunzionamenti e perdita di dati, l'istruzione è infatti in grado di riportare i contenuti archiviati alla situazione di normalità, ma non di riparare eventuali malfunzionamenti nella gestione degli stessi le cui cause dovranno in ogni caso essere ricercate per evitare il ripetersi di problemi a danno della coerenza dei dati.
Il comando REPAIR TABLE, può produrre in output numerosi record contenenti informazioni riguardanti le tabelle coinvolte. La struttura della tabella prodotta in seguito all'esecuzione del comando sarà la stessa delle istruzioni precedentemente descritte, in questo caso però la colonna "op" presenterà il valore "repair"; in condizioni di normalità la colonna "Msg_type" avrà associato il valore "status" mentre avremo "OK" per la colonna "Msg_test".
Nel caso in cui non dovesse comparire il valore "OK" allora si dovrà ricorrere al comando già descritto myisamchk --recover nome_tabella.
Tra le opzioni supportate vi sono QUICK che tenterà di riparare soltanto gli indici della tabella (equivalente a myisamchk --recover --quick) ed EXTENDED che creerà gli indici un record alla volta invece che uno cumulativo per l'ammontare delle informazioni archiviate (equivalente a myisamchk --safe-recover)
Sintassi:
REPAIR TABLE nome_tabella [, nome_tabella..] ... [QUICK] [EXTENDED]
RESTORE TABLE è invece un comando che ricostruisce una o più tabelle sulla base di un salvataggio operato tramite un'istruzione contenente il comando BACKUP TABLE specificando il percorso completo alla cartella contenente il back-up. In pratica vengono ripristinati sia i file ",frm" che i ".MYD" relativi ai dati.
Con RESTORE TABLE le tabelle esistenti non verranno sovrascritte e il DBMS risponderà con una notifica di errore nel caso in cui si tenti di sovrascrivere una tabella già presente. Questo comando è utilizzabile unicamente per le tabelle di tipo MyISAM esattamente come per BACKUP TABLE, quest'ultimo però è ormai considerato come deprecato e può essere sostituito con mysqlhotcopy.
L'output di RESTORE TABLE è quello classico a 4 colonne dei comandi di manutenzione, in questo caso però la colonna "op" sarà valorizzata come "restore".
Sintassi:
RESTORE TABLE nome_tabella [, nome_tabella] ... FROM '/path/backup'
