MySQL – Tuning Database

Capita di trovarsi a gestire un gran quantitativo di dati in una o più tabelle, confrontandosi di conseguenza con le performance del proprio database.

Il mio gran quantitativo di dati corrispondeva a circa 600.000 righe (300 MB) tutte stipate in un’unica tabella. Questo mi hanno portato ad avere tempi di inserimento molto lunghi, con conseguenti cadute di velocità anche in lettura.

Quello che vediamo in questo post non è un vero e proprio tuning, ma una serie di passi (intelligenti??) per ottimizzare i nostri dati. Non metteremo mano ai file di configurazione di MySQL, anche se vi consiglio un buon tool online che vi propone un file my.conf in base alle vostre esigenze: Percona. ATTENZIONE! Effettuate sempre dei backup prima di modificare questi file.

Vi racconto la mia storia
Avevo una tabella con più di 600.000 record, non ottimizzata, non deframmetata e con dati molto vecchi e non più utilizzati.
Uno script giornaliero, che effettuava inserimenti massivi su tale tabelle, aveva raggiunto tempi di esecuzione molto elevati: più di 5 ore.
Durante l’esecuzione dello script dovevo mantenere accettabile anche le letture dei dati e non è bastato avvalersi di un provider con dischi SSD, come Digital Ocean, per sopperire al problema.

Dopo una breve analisi e un po’ di tentativi ecco quali accortezze ho implementato:

  • Passaggio delle tabelle da MyISAM a InnoDB (+)
  • Rimozione dei dati vecchi e non più utilizzati (++)
  • Ottimizzazione delle tabelle con la seguente query (++):
    OPTIMIZE TABLE nome_tabella
  • Deframmentazione della tabelle con la seguente query (+++):
    ALTER TABLE nome_tabella ENGINE=INNODB

Risultato finale
Numero di righe: 240.000
Spazio occupato dalla tabella: 125 MB
Tempo di esecuzione dello script: 1 ora e 31 minuti

Che dite? Non male.

Conclusioni
Nel mio caso ho dovuto schedulare queste operazioni con cadenza giornaliera, altrimenti nel giro di una settimana mi sarei ritrovato nella pessima condizione iniziale.

Buon tuning a tutti! 😉