Convertire le tabelle da MyISAM a InnoDB

Il Task

Hai deciso di trasformare le tue tabelle MyISAM in InnoDB. Dovrebbe bastare un ALTER TABLE foo ENGINE=InnoDB. Ma hai sentito dire che potrebbero verificarsi dei problemi sottili.

Qui vengono descritti, insieme al modo per risolverli.

Raccomandazione. Per cercare i problemi, può aiutare (almeno su *nix):

mysqldump --no-data --all-databases >schemas
egrep 'CREATE|PRIMARY' schemas   # per le chiavi primarie
egrep 'CREATE|FULLTEXT' schemas  # cerca gli indici FULLTEXT
egrep 'CREATE|KEY' schemas       # cerca varie combinazioni di indici

Capire il funzionamento degli indici aiuta a comprendere meglio che cosa può velocizzare o rallentare InnoDB.

Problemi sugli indici

(La maggioranza di questa Raccomandazioni e alcuni di questi Fatti presentano delle eccezioni.)

Fatto. Ogni tabella InnoDB ha una PRIMARY KEY. Se non ne viene creata una esplicitamente, verrà usata la prima chiave non-NULL e UNIQUE. Se non è possibile, viene aggiunto un intero di 6-byte, nascosto.

Raccomandazione. Cercare le tabelle senza una PRIMARY KEY. Specificarne una esplicitamente, anche un AUTO_INCREMENT artificiale. Non è un requisito obbligatorio, ma su InnoDB questo consiglio è più importante che su MyISAM. Un giorno potresti aver bisogno di scorrere la tabella; sena una chiave primaria esplicita, non è possibile.

Fatto. I campi della PRIMARY KEY sono inclusi in ogni Chiave Secondaria.

• Si cerchino gli indici ridondanti tenendo questo in mente.

PRIMARY KEY(id),
INDEX(b), -- in effetti è come INDEX(b, id)
INDEX(b, id) -- in effetti è come INDEX(b)

• (Mantenere uno degli indici, on entrambi)

• Si notino questioni sottili come

PRIMARY KEY(id),
UNIQUE(b), -- mantenere: vincolo di univocità
INDEX(b, id) -- eliminarlo

• Inoltre, siccome la PK e i dati coesistono:

PRIMARY KEY(id),
INDEX(id, b) -- eliminarlo: non aggiunge quasi niente

Contrasto. Questa funzionalità di MyISAM non esiste in InnoDB; il valore di 'id' inizia da 1 per ogni diverso valore di 'abc':

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (abc, id)

Un modo per simulare questa 'funzionalità' di MyISAM potrebbe essere qualcosa del genere: Ecco ciò che desideri, ma non funziona perché ci sono due riferimenti alla tabella:

INSERT INTO foo
    (other, id, ...)
    VALUES
    (123, (SELECT MAX(id)+1 FROM foo WHERE other = 123), ...);

Invece, hai bisogno di una variante. (potresti già avere un BEGIN...COMMIT.)

BEGIN;
SELECT @id := MAX(id)+1 FROM foo WHERE other = 123;
INSERT INTO foo
    (other, id, ...)
    VALUES
    (123, @id, ...);
COMMIT;

E' necessario usare una transazione per impedire agli altri thread di aggiudicarsi lo stesso id.

Raccomandazioni. Cerca queste PRIMARY KEY. Se ne trovi, valuta se cambiare le scelte di progettazione. Non esiste un'alternativa semplice. Tuttavia, la seguente dovrebbe andare bene. (Assicurati che il tipo di id sia sufficientemente grande.):

id INT UNSIGNED NOT NULL AUTO_INCREMENT,
PRIMARY KEY (abc, id),
UNIQUE(id)

Raccomandazione. Mantenere piccola la PRIMARY KEY. Se si hanno delle Chiavi Secondarie, bisogna ricordarsi che queste includono la PK. Una PK di grandi dimensioni intasa le Chiavi Secondarie. Beh, forse no — se nei campi vi sono molte sovrapposizioni. Esempio: PRIMARY KEY(a,b,c), INDEX(c,b,a) — niente dati aggiuntivi.

Recommendation. Check AUTO_INCREMENT sizes.

• BIGINT non è quasi mai necessario. Spreca almeno 4 bytes per riga (rispetto a INT).
• Si usino sempre UNSIGNED e NOT NULL.
• MEDIUMINT UNSIGNED (massimo 16M) dovrebbe essere sufficiente, invece di INT
• E' meglio essere sempre pessimisti — eseguire un ALTER è doloroso.

Contrasto. "Partizionamento Verticale". E' la tecnica che consiste nel dividere artificialmente una tabella per spostare le grandi colonne (per esempio i BLOB) in un'altra tabella, parallela. In MyISAM porta dei benefici, perché si evita di inciampare in un BLOB quando non ce necessita di usarlo. InnoDB registra i BLOB i TEXT in modo diverso — 767 byte si trovano nel record, il resto è in un altro blocco. Perciò potrebbe (ma non è detto) valere la pena di riunire insieme le tabelle. Cautela: una riga InnoDB è limitata a 8KB e il valore di 767 conta su questo.

Fact. Gli indici FULLTEXT e SPATIAL non sono disponibilie per InnoDB.

Raccomandazione. Cercare questi indici. Mantenere queste tabelle in MyISAM. Meglio ancora, tramile il Partizionamento Verticale (vedi sopra) separare il minor numero possibile di colonne da InnoDB.

Fatto. La lunghezza massima degli indici varia a seconda degli Storage Engine. (E' difficile che questa variazione crei problemi, ma è meglio stare attenti.) MyISAM permette 1000 byte; InnoDB permette 767 byte, che è troppo poco per:

VARCHAR(255) CHARACTER SET utf8.

ERROR 1071 (42000): Specified key was too long; max key length is 767 bytes

Fatto. La PRIMARY KEY è compresa nei dati. Perciò, SHOW TABLE STATUS mostra un Index_length di 0 byte (o 16KB) nel caso di tabelle senza Chiavi Secondarie. Altrimenti, Index_length sono le dimensioni totali delle Chiavi Secondarie.

Fatto. La PRIMARY KEY è compresa nei dati. Perciò, la ricerca esatta della PK potrebbe essere leggermente più rapida in InnoDB. E le scansioni "range" (su un intervallo) sulla PK sono in gener epiù veloci.

Fatto. Le ricerche sulle Chiavi Secondarie attraversa il BTree di quelle chiavi, legge la PRIMARY KEY, poi attraversa il BTree della PK. Perciò, le ricerche sulle chiavi secondarie sono un po' più lente in InnoDB.

Contrasto. I campi della PRIMARY KEY sono inclusi in ogni Chiave Secondaria. Questo può causare un "Using index" (nel piano di EXPLAIN) su InnoDB in casi in cui questo non accadrebbe in MyISAM. (è un leggero miglioramento nelle prestazioni e controbilancia il doppio passaggio che altrimenti sarebbe necessario.) Tuttavia, quando "Using index" è utile con una PRIMARY KEY, MyISAM esegue un "index scan", mentre InnoDB deve eseguire un "table scan".

Come MyISAM. Quasi sempre

INDEX(a)   -- eliminarlo, perchè l'altro indice contiene questo
INDEX(a,b)

Contrasto. I dati sono registrati nell'ordine della PK. Questo significa che i record "recenti" sono raggruppati insieme alla fine. In questo modo si ottiene una "vicinanza dei riferimenti" migliore che in MyISAM.

Come MyISAM. L'ottimizzatore non usa quasi mai due indici in un'unica SELECT. (5.1 esegue occasionalmente un "index merge".) Le SELECT nelle subquery e le UNION possono usare indici in modo indipendente.

Problema sottile. Quando si elimina una riga, il suo valore nell'id AUTO_INCREMENT viene bruciato. Lo stesso dicasi per REPLACE, che è una DELETE seguita da una INSERT.

Problema molto sottile. La replica avviene con i COMMIT. Se diversi thread utilizzano le transazioni, i valori AUTO_INCREMENT potrebbero arrivare agli slave in ordine sparso. Una transazione inizia, si prenota un id. Poi un'altra transazione prenota un altro id, ma esegue il COMMIT prima dell'altra.

Come MyISAM. L'indicizzazione con i prefissi, in genere è male sia con InnoDB sia con MyISAM. Esempio: INDEX(foo(30))

Problemi non relativi agli indici

Lo spazio su disco per InnoDB è probabilmente 2-3 volte maggiore rispetto a MyISAM.

MyISAM e InnoDB utilizzano la RAM in modo radicalmente diverso. Se si convertono tutte le tabelle, occorre fare delle modifiche significative:

• key_buffer_size — piccolo ma non zero; diciamo 10M;
• innodb_buffer_pool_size — 70% della RAM disponibile

InnoDB has essentially no need for CHECK, OPTIMIZE, or ANALYZE. Remove them from your maintenance scripts. (No real harm if you keep them.)

Gli script di backup devono essere controllati. Per ricreare una tabella MyISAM basta copiare tre file. Non esiste una tecnica simile su InnoDB. Allo stesso modo, catturare una tabella o database per la copia da un ambiente di produzione a un ambiente di sviluppo non è possibile. Passare a mysqldump.

Comprendere l'autocommit e BEGIN/COMMIT.

• (default) autocommit = 1: In assenza delle istruzioni BEGIN e COMMIT, ogni comando è di per sé una transazione. Questo comportamento è simile a quello di MyISAM, ma non è il migliore.
• autocommit = 0: i COMMIT chiudono una transazione e ne iniziano un'altra. Per me, è un po' povero.
• (raccomandato) BEGIN...COMMIT dà il controllo su quali sequenze di operazioni devono essere considerate una transazione e atomiche. Si può usare l'istruzione ROLLBACK se si deve annullare tutto e tornare a BEGIN.

Perl DBIx::DWIW e Java JDBC hanno chiamate API per eseguire BEGIN e COMMIT. Queste probabilmente sono preferibili, piuttosto che eseguire BEGIN e COMMIT.

Cercare errori ovunque! Siccome InnoDB utilizza il lock a livello di riga, potrebbe inciampare in deadlock che non ci si aspetta. L'engine esegue automaticamente un ROLLBACK fino a BEGIN. Normalmente per recuperare si rifà tutto, ricominciando da BEGIN. Si noti che anche questa è un'ottima ragione per usare BEGIN.

Rimuovere LOCK/UNLOCK TABLES. Sostituirli con (una sorta di) BEGIN ... COMMIT. (Sì, LOCK funziona, ma è meno efficiente e comporta problemi sottili.)

In 5.1, ALTER ONLINE TABLE può velocizzare parecchio alcune operazioni. (Normalmente ALTER TABLE copia la tabella e ricostruisce gli indici.)

I "limiti" su ogni aspetto sono differenti tra MyISAM e InnoDB. A meno di non avere tabelle grandi, righe corpose, molti indici, etc, è difficile avere problemi per via di un limite diverso.

Un misto di MyISAM e InnoDB? Va bene. Ma ci sono cose da considerare.

• Le impostazioni della RAM devono essere cambiate di conseguenza.
• Le JOIN tra diversi Engines funzionano.
• Una transazione che ha effetto su tabelle di entrambi i tipi può eseguire un ROLLBACK su InnoDB, ma lascerà intatte le modifiche su MyISAM.
• Replica: le istruzioni su MyISAM vengono replicate dopo che la loro esecuzione è terminata; le istruzioni su InnoDB vengono lasciate in attesa fino al COMMIT.

FIXED (oppure DYNAMIC) non ha significato in InnoDB.

PARTITION — E' possibile partizionare le tabelle MyISAM e InnoDB. Si ricordi la seguente regola generale: bisogna scegliere tra due alternative:

• non usare chiavi UNIQUE (o PRIMARY) oppure
• inserire il valore su cui si basa il partizionamento su tutte le chiavi UNIQUE.

Su InnoDB la prima possibilità non è consigliata. La seconda non è buona se si usa AUTO_INCREMENT.

PRIMARY KEY nelle PARTITION — Considerato che tutte le chiavi devono includere il campo su cui si basa il partizionamento, come funziona AUTO_INCREMENT? Bene, sembra esistere un caso speciale molto comodo:

• Questo funziona: PRIMARY KEY(autoinc, partition_key)

• Questo non funziona, su InnoDB: PRIMARY KEY(partition_key, autoinc)

Insomma, AUTO_INCREMENT aumenterà correttamente, e i valori saranno univoci tra tutte le partizioni, solo quando è impostato sul primo campo della PRIMARY KEY.

Vedi anche

Rick James ha gentilmente concesso di utilizzare questo articolo nella Knowledgebase. L'originale è qui: http://mysql.rjweb.org/doc.php/myisam2innodb

Il sito di Rick James contiene altri utili trucchi, how-to, ottimizzazioni e suggerimenti per il debug.