Database MySQL: 10 trucchi per migliorarne le

performance

Scopriamo i segreti che molti

amministratori professionisti

di database usano

per migliorare le performance

#e-Commerce

Contenuti a cura di HostingTalk

Migliorare le performance e ottimizzare i tempi di risposta

dei database MySQL, come quelli forniti nei pacchetti hosting,

è 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

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.).

Prima di applicare i trucchi indicati di seguito,

prestiamo 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.

Possiamo 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).

Per gli amanti della shell, il comando per visionare

il contenuto di my.cfn è:

mentre le modifiche possono essere apportate con il comando

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:

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, controlliamo di aver impostato tutto correttamente e di aver usato anche le corrette unità di misura per ogni voce suggerita;

• non duplichiamo le impostazioni nei file di configurazione;

meglio usare un sistema di controllo della versione,

se si vuole tenere traccia delle modifiche apportate;

• non applichiamo 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!

1. Configuriamo il parametro innodb_buffer_pool_size

Il primo parametro da 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. Configuriamo il parametro innodb_buffer_pool_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. Configuriamo 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 è il possibile crash del server

che si trova a dover gestire oltre 1000 transazioni attive.

4. Configuriamo 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)

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. Configuriamo 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. Configuriamo 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 per tutti gli altri scenari d’uso.

7. Configuriamo 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

8. Configuriamo 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 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. Configuriamo 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.

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.

È anche possibile procedere da linea di comando,

magari usando lo strumento Mysqlcheck, in questo modo:

ottimizzazione:

riparazione:

check, riparazione e ottimizzazione su tutto il database:

Hosting Aruba

Grazie