Ottimizzare MySQL per migliorare le prestazioni del database

Categoria: Tutorial

Ottimizzare MySQL per migliorare le prestazioni del database

MySQL è un database open source utilizzato nella maggior parte dei siti web dinamici e incluso in tutti i nostri piani di hosting condiviso. Le sue prestazioni sono regolamentate da un sistema di accesso molto avanzato, ulteriormente ottimizzabile e, se lo si desidera, da “tarare”.

Prima di procedere alla definizione delle tecniche per migliorare le prestazioni di accesso ai dati e ottimizzare MySQL, è bene fare una serie di premesse che aiuteranno a capire al meglio l’argomento in questione.

Cosa cambia tra i vari storage-engine di MySQL?

MySQL offre l’accesso ai dati in lettura / scrittura mediante appositi storage engine, ovvero librerie che offrono un’interfaccia per la gestione delle informazioni memorizzate: lettura, scrittura, buffering e così via.

I due più diffusi sono MyISAM ed InnoDB, due motori di memorizzazione differenti per una serie di aspetti, tra cui il fatto che solo il secondo supporta le transazioni, ovvero operazioni consecutive sul database che avvengono sotto condizioni di atomicità, consistenza, isolamento e durabilità (ACID) e con la possibilità – informalmente parlando – di effettuare operazioni di ripristino della stato iniziale in caso di errori.

Confronto storage engine di MySQL

In termini prestazionali, MyISAM è considerato più veloce di InnoDB, per quanto tale affermazione dipenda molto dal tipo di applicazione, dal numero di accessi al database, dalla dimensione delle tabelle e così via.

MyISAM è più performante se abbiamo piccole tabelle in sola lettura (secondo un test di performance segnalato su StackOverflow) mentre in altri casi, è InnoDB che mostra un comportamento più veloce, specie in presenza di istruzioni di lock delle tabelle.

Nel caso il cui il database venga utilizzato per un CMS come Drupal, Joomla o WordPress, le differenze possono essere davvero minime o non esistere affatto.

Come ottimizzare un database

Come ottimizzare un database

In genere un’operazione di ottimizzazione di una o più tabelle del database può avvenire con il seguente comando:

OPTIMIZE TABLE tabella1 [, tabella2, …]

Si ricorda come questo genere di operazione sia legata esclusivamente alla struttura del database e non, ad esempio, alle modalità di accesso ai dati (l’ottimizzazione delle query è un discorso differente, per intenderci).

Come ottimizzare un database MyISAM

Esistono varie ottimizzazioni specifiche a seconda degli engine utilizzati: MyISAM è stato quello di default fino alla versione 5.5, è in genere performante e richiede poche risorse (RAM, hard disk).

Per compattare il numero di righe frammentate ed evitare sprechi di spazio, problemi che solitamente derivano dalla cancellazione o dall’aggiornamento (ovvero durante un’utilizzo costante delle risorse nel tempo), è possibile sfruttare il comando myisamchk via shell MySQL, quindi dopo aver fatto login ad esempio via SSH, entrando nel client e digitando:

myisamchk -r tabella_x

Come ottimizzare un database InnoDB

InnoDB supporta pienamente le proprietà ACID viste in precedenza, possiede un supporto agli errori molto avanzato che permette tra l’altro, il ripristino dello stato dei dati in caso di necessità. Questo engine viene solitamente utilizzato per applicazioni molto avanzate, o comunque in ambiti che diano priorità alla concorrenza ed all’affidabilità dell’estrazione di informazioni.

L’ottimizzazione dei dati in questo caso, è decisamente più complessa e per questo motivo si consiglia di prendere in considerazione i seguenti suggerimenti:

  • valutare la possibilità di risparmiare spazio sfruttando l’AUTO_INCREMENT della chiave in corrispondenza della colonna PRIMARY KEY;
  • utilizzare il tipo VARCHAR anzichè CHAR, in modo da risparmiare byte nel buffer pool e ridurre il numero di operazioni in I/O;
  • valutare la possibilità di sfruttare il formato COMPRESSED delle colonne anche qui per ottimizzare I/O e buffering;

Altre operazioni riguardano le transazioni e possono essere analizzate sulla documentazione ufficiale.

Come cambiare l’engine di MySQL

Se il server supporta entrambi i tipi, possiamo – se necessario – switchare da un tipo di engine all’altro con un comando di questo tipo (ad esempio sulla tabella_x):

ALTER TABLE tabella_x ENGINE=INNODB;

ALTER TABLE tabella_x ENGINE=MYISAM;

WordPress utilizza di default MyISAM che è anche l’engine più diffuso disponibile nei pacchetti hosting commerciali o perlomeno con una versione di MySQL inferiore alla 5.5, per quanto non sia previsto uno standard preciso nelle proprie specifiche.

Questo motore va benissimo per siti con una soglia di traffico non elevata, mentre InnoDB si usa solitamente per applicazioni web di livello più alto.

Alla luce di queste considerazioni un database con supporto MyISAM, per definizione è spesso “immune” alle ottimizzazioni che verranno eseguite su di esso mentre InnoDB è più moderno e più performante ma anche più difficile da manipolare e configurare.

Se vuoi fare dei test e provare ad ottimizzare MySQL, prova i  nostri servizi hosting! In regalo per te un codice sconto che ti consente di acquistare PERSONAL a soli €15,00.

Ottimizzare MySQL per migliorare le prestazioni del database ultima modifica: 2015-02-19T11:38:01+00:00 da Salvatore CapolupoAuthor di HostingVirtuale

Commenti (0)

Aggiungi un commento