Home News Database MySQL, 10 trucchi per migliorarne le performance
Database MySQL, 10 trucchi per migliorarne le performance PDF Stampa E-mail
Venerdì 20 Febbraio 2015 15:00

Migliorare le performance e ottimizzare i tempi di risposta dei database MySQL, come quelli forniti nei pacchetti hosting Aruba, è di fondamentale importanza per consentire al proprio sito Web di funzionare in modo efficiente, riducendo i ritardi di caricamento delle informazioni e di visualizzazione delle pagine.

Questa riduzione è particolarmente necessaria quando si lavora con alcuni CMS come WordPress e Drupal ed è vantaggiosa, perché oltre a offrire una migliore esperienza utente, garantisce anche un miglior posizionamento in termini di rank. Infatti, la velocità di caricamento e risposta del sito Web alle richieste di elaborazione è uno dei parametri fondamentali degli algoritmi di classificazione dei motori di ricerca come Google.

Riuscire a ottimizzare un database MySQL non è così complicato come si possa credere e sottoponendo ad audit il proprio DB, si può facilmente revisionare la configurazione MySQL per trovare i miglioramenti da apportare.

Per quante centinaia di opzioni possano esistere per ottimizzare le performance di un database MySQL, in realtà è importante mettere mano ad alcuni parametri cruciali che, se ben impostati, rendono il database pronto a sostenere qualsiasi tipologia di carico elaborativo.

Quelli che seguiranno sono 10 trucchi che aiutano a migliorare le performance di un DB MySQL basato su motore InnoDB (praticamente tutti quelli dei maggiori CMS, come WordPress, Moodle, Prestashop, ecc.).

Alcune raccomandazioni prima di modificare il file di configurazione MySQL

Prima di applicare i trucchi indicati di seguito, è bene prestare attenzione ad alcune avvertenze che, se rispettate, evitano di mandare in tilt il database con tutti i problemi che potrebbero conseguire da questa incresciosa situazione.

Quindi, è bene:

  • modificare un solo parametro alla volta, per verificarne l’eventuale beneficio in termini di performance e per individuare subito l’errore qualora l’operazione di modifica non andasse per il verso giusto;
  • tutte le modifiche indicate sono apportate alla sezione mysqld del file di configurazione my.cfn. Puoi visualizzare i dati di configurazione dei parametri contenuti nel my.cfn direttamente online, entrando nel pannello phpMyAdmin e cliccando la voce Motori presente nella schermata centrali e facendo clic poi su InnoDB (prima voce dell’elenco).

Database MySQL, 10 trucchi per migliorarne le performance

Per gli amanti della shell, il comando per visionare il contenuto di my.cfn è:

cat /etc/my.cnf

mentre le modifiche possono essere apportate con il comando

nano /etc/my.cnf

editando il parametro di interesse e poi cliccando su CTRL + O per salvare la modifica e CTRL + X per uscire dall’editor.

  • ricordarsi di riavviare sempre il processo MySQL, con il seguente comando:

service mysql restart

e se le modifiche non sono visibili, verificare che le stesse siano state apportate alle voci e alle sezioni di configurazione giuste;

  • se il servizio MySQL dovesse rifiutare di riavviarsi, controllare di aver impostato tutto correttamente e di aver usato anche le corrette unità di misura per ogni voce suggerita;
  • non duplicare le impostazioni nei file di configurazione; meglio usare un sistema di controllo della versione, se si vuole tenere traccia delle modifiche apportate;
  • non applicare assurde regole matematiche del tipo: “ora il mio server ha il doppio della RAM, raddoppio i valori di tutti i parametri di cache, ecc.”. Questa tipologia di regole non funzionano mai!

Parametri MySQL da modificare/configurare per un database performante

1.      Configurare il parametro innodb_buffer_pool_size

Il primo parametro da andare a controllare in qualsiasi installazione MySQL InnoDB è innodb_buffer_pool_size che rappresenta la quantità di memoria messa a disposizione per il caching dei dati e degli indici: bisogna impostare questo parametro al più alto valore possibile, in modo da assicurarsi l’uso dello spazio in memoria e non quello del disco per la maggior parte delle operazioni di lettura. I valori tipici per questo parametro dipendono dalla RAM a disposizione e possono essere pari a 5-6 GB per server con 8 GB di RAM, 20-25 GB per server con 32 GB di RAM e 100-120 GB per server con 128 GB di RAM.

2.      Configurare il parametro innodb_log_file_size

Questa è la dimensione dei file di log per le operazioni di redo, che garantiscono scritture veloci e l’opportunità di recuperare i dati in scrittura in caso di crash recovery. Si può partire con un valore di questo parametro pari a 512M, ma se si lavora con applicazioni a cicli di scrittura intensi e con MySQL 5.6, allora si può partire già da valori più alti come 4GB.

3.      Configurare il parametro max_connections

A volte, ci si può scontrare con l’errore Too many connections, che indica un valore basso impostato per il parametro max_connections. Questo accade perché alcune applicazioni non chiudono correttamente la connessione al database MySQL e così si rende necessario aumentare il valore delle connessioni gestibili oltre le 151 gestite di default. Bisogna comunque prestare attenzione ai valori usati. Il problema di valori troppo alti (oltre i 1000) è il possibile crash del server che si trova a dover gestire contemporaneamente oltre 1000 transazioni attive.

4.      Configurare il parametro innodb_file_per_table

Questa impostazione serve per indicare a MySQL se si vuole conservare i dati e gli indici in un unico tablespace (impostazione su OFF) o in un file IBD separato per ogni tabella (impostazione su ON). Il consiglio è di impostare il parametro su ON (valore di default per MySQL 5.6) soprattutto se si effettuano operazioni di cancellazione e ricostruzione tabelle od operazioni di compressione, che permettono di recuperare spazio. Evitare però di usare l’impostazione su ON qualora si abbia a che fare con un database dal numero di tabelle davvero elevato (oltre 10 mila).

5.      Configurare il parametro innodb_flush_log_at_trx_commit

Il valore di default impostato per questo parametro, indica che il MySQL InnoDB è completamente ACID-compliant ed è il valore ideale in termini di sicurezza dei dati, ma rallenta le operazioni di sistema con scritture su disco continue, soprattutto per un uso intenso dei log redo. Impostare il valore a 2 significa perdere un po’ in termini di affidabilità, ma guadagnare in performance, perché le transazioni sono inviate ai log redo solo una volta ogni secondo. Si tratta comunque di un buon compromesso fra affidabilità e performance. Il valore 0 velocizza ancora di più le operazioni, ma c’è il rischio di perdita dati in caso di crash.

6.      Configurare il parametro innodb_flush_metod

Indica come i dati e i log vengono scritte su disco. Il valore consigliato è O_DIRECT in caso di hardware con controller RAID e fdatasync (che è il valore predefinito) per tutti gli altri scenari d’uso.

7.      Configurare il parametro innodb_log_buffer_size

Rappresenta la dimensione del buffer per le transazioni non ancora terminate. Il valore di default, 1 MB, è corretto, ma se si hanno transazioni più pesanti in termini di dati, si rischia di riempirlo facilmente. Bisogna quindi guardare al parametro innodb_log_waits e se non è pari a zero, allora è opportuno aumentare il valore di questo buffer.

8.      Configurare il parametro query_cache_size

La cache query è un collo di bottiglia ben noto per molte applicazioni MySQL, in cui deve essere attivata, in realtà, per questioni di stabilità. In altri casi o se si sta progettando il proprio database MySQL da zero, il parametro query_cache_size dovrebbe essere impostato su 0 (zero), cercando di velocizzare le query in altro modo, ossia con una buona indicizzazione, con cache esterne e via discorrendo.

9.      Configurare il parametro log_bin

Attivare il logging binario è necessario sia per far funzionare il server come replica di un database server master, sia se sul server (in configurazione singola) si vuole abilitare il sistema di backup automatico. Una volta creati, questi file log vengono conservati per sempre e non è difficile che si rischi di esaurire lo spazio disco. Per questo motivo, è sempre bene impostare anche il parametro expire_logs_days per indicare a MySQL ogni quanti giorni cancellare i log binari più vecchi.

10.  Configurare il parametro skip_name_resolve

Quando si connette un client, il server effettua una risoluzione dell’hostname, interrogando i DNS. Qualora i DNS siano lenti, le operazioni diverranno lente di conseguenza. Per questo è consigliabile configurare MySQL perché disabiliti i lookup DNS, anche se ci potrebbero essere delle limitazioni con le operazioni di GRANT, con le quali bisogna stare attenti, soprattutto quando si applica questo parametro su sistemi esistenti.

Consiglio bonus per migliorare le performance MySQL: ottimizzazione e riparazione tabelle

Un ultimo consiglio per migliorare le performance di un database MySQL è quello di applicare un’ottimizzazione delle tabelle e un’eventuale riparazione.

Effettuare queste operazioni non è complicato.

Ad esempio, è possibile ottimizzare le tabelle di un database direttamente da phpMyAdmin, autenticandosi, scegliendo il database, selezionando tutte le tabelle con un clic sul link Seleziona tutti presente in fondo alla lista delle tabelle e, infine, scegliendo la voce Ripara tabella (prima) e poi Ottimizza tabella direttamente dal menu a tendina Se selezionati.

Database MySQL, 10 trucchi per migliorarne le performance

È anche possibile procedere da linea di comando, magari usando lo strumento Mysqlcheck, in questo modo:

ottimizzazione:

mysqlcheck -o nomedatabase nometabella -u root –p

riparazione:

mysqlcheck -r nomedatabase nometabella -u root –p

check, riparazione e ottimizzazione su tutto il database:

mysqlcheck -u root -p --auto-repair -c -o nomedatabase

In casi più specifici, si può anche pensare di ricorrere a determinati tool come MySQL Workbench (ottimo anche per la progettazione e l’amministrazione dei database MySQL) o Percona Toolkit for MySQL.

 

Tutorial

Vuoi approfondire degli argomenti specifici? Segui i miei tutorial, per dubbi o domande contattami.

Vai ai tutorial

Flickr

Guarda le mie foto su Flickr. Hai bisogno di foto particolari? Vuoi imparare a fotografare? Contattami
flickr_logo

Servizi Online

Vuoi predisporre il tuo sito web personalizzato? Hai bisogno di aiuto o consulenza?

Acquista i servizi online

Contattami

Per approfondimenti o informazioni su corsi di formazione o lavori contattami.

Richiedi uno scambio link con il mio sito.