MySQL 5.7 Tuning
Sul tuning di MySQL sono state pubbicate innumerevoli pagine nel web...
ma MySQL ha avuto una continua evoluzione con nuovi algoritmi,
nuovi parametri di tuning e variazioni sulle impostazioni di default.
Con le ultime versioni 5.6 e 5.7 le variazioni sono state ancora piu' evidenti e
quindi la maggior parte delle pagine sul tuning di MySQL nel web
non sono piu' aggiornate ed affidabili!
Poiche' sono colpevole anch'io d'avere distribuito
nel tempo su web esempi di my.cnf inaffidabili,
ho deciso di rimediare con questo documento piu' aggiornato.
Questa semplice e pragmatica paginetta riporta quelli che sono
i principali parametri di tuning di MySQL in versione 5.7
evitando di riportare i parametri non piu' significativi poiche' obsoleti.
Nel seguito sono riportate alcune informazioni di interesse
organizzate in paragrafi specifici:
Introduzione,
Parametri principali,
E se...
(MyISAM, Query Cache, Replication),
Varie ed eventuali.
Le indicazioni che seguono sono per un pubblico DBA adulto,
per i minori o junior consigliamo la lettura di
questo documento introduttivo,
mentre le novita' della versione 5.7 sono riassunte in
questo documento.
Introduzione
Il tuning di una base dati e' un'attivita' che richiede competenza ed esperienza.
Se non sapete a cosa serve un parametro... non modificatelo!
Il tuning si fa se... serve.
Se una base dati e' di modeste dimensioni, le applicazioni consolidate
ed i sistemi correttamente dimensionati
e' sufficiente un'impostazione iniziale ragionevole dei parametri
e niente piu'.
Nessun tuning si sostituisce ad un disegno corretto della base dati e dell'SQL.
Prima di qualsiasi altra attivita' vanno controllati evenuali statement di lunga durata
e corretti quelli errati,
vanno creati tutti gli indici necessari e, nei casi in cui
opportuno, va utilizzato il partitioning e lo sharding.
Misurare, misurare, misurare!
Misurare le prestazioni prima delle attivita' di tuning, misurare tutti i test eseguiti
e continuare a misurare effettuata una modifica di parametri.
Senza misure non si fa tuning. Quali misure? Tutte quelle che servono!
In generale dipendono dall'applicazione e dall'utilizzo dei MySQL:
TPS, MRT, #utenti, active threads, ... e dai sistemi ospite: %CPU, %IOW, LA, ...
senza dimenticare le due principali: lo spazio ed il tempo
(che come e' noto sono fisicamente legate tra loro :).
Parametri principali
Ecco l'elenco dei parametri in ordine di importanza!
Naturalmente secondo il mio fallace personale giudizio...
- innodb_buffer_pool_size (default 128M):
imposta la dimensione della cache per i dati e gli indici InnoDB.
Questo e' il principale parametro di tuning e va tenuto il piu' alto possibile
per disporre dei dati in memoria anziche' accedere a disco.
Nei casi fortunati si puo' ospitare l'intera base dati in memoria,
altrimenti si imposta il parametro per utilizzare
tra il 50% e l'80% della RAM disponibile facendo attenzione
a non generare paginazione sul sistema ospite.
Un vantaggio della 5.7 e' che tale parametro e' diventato dinamico
(ovvero si puo' variare senza riavviare il DB).
Come verificare se serve?
Controllando il rapporto tra INNODB_BUFFER_POOL_READS e INNODB_BUFFER_POOL_READ_REQUESTS
[NdA quante letture fisiche si fanno rispetto alle richieste].
- innodb_log_file_size (48M):
e' la dimensione dei redo logs.
Anche se il default e' stato alzato rispetto alle versioni precedenti [NdE era solo 5M fino alla 5.6.8],
il valore e' comunque troppo basso per un utilizzo transazionale significativo.
Poiche' vi sono due log file con l'impostazione ad 1GB si hanno 2GB di occupazione disco
ed una configurazione adatta alla maggioranza dei carichi.
Se l'applicazione e' write-intensive si puo' arrivare a 8G.
In generale meno di una log_rotate all'ora e' un buon target.
In questo caso il rapporto da verificare e'
INNODB_LOG_WRITES / INNODB_LOG_WRITE_REQUESTS.
- innodb_flush_log_at_trx_commit (1):
indica la modalita' di scrittura dei log.
Con l'impostazione di default InnoDB e' ACID compliant:
ad ogni commit corrisponde una write (scrittura su log)
ed una flush (scrittura fisica su disco).
Non andrebbe mai cambiato pero'...
con l'impostazione a 2 (scrive ad ogni commit ed effettua un flush al secondo, per default)
o a 0 (effettua un flush al secondo) MySQL risulta significativamente piu' veloce
con un carico OLTP.
Quindi ci sono molte situazioni in cui si modifica il parametro accollandosi il rischio
di perdere un secondo di transazioni in caso di crash del sistema (impostandolo a 2).
Impostare a 0 rispetto a 2 generalmente non migliora significativamente le prestazioni ed aumenta solo il rischio.
- innodb_log_buffer_size (1MB):
e' la dimensione del buffer del log.
La dimensione di default va bene nella maggioranza dei casi.
Se si hanno transazioni con campi di grandi dimensioni (eg. blob)
il valore potrebbe non essere sufficiente:
quando il valore della variabile INNODB_LOG_WAITS
cresce tipicamente il parametro e' da aumentare.
- max_connections (151):
numero massimo di connessioni.
Da aumentare se si supera tale numero di sessioni al DB.
Ma vanno anche verificate le applicazioni per assicurarsi
che effettuino correttamente la disconnesione
(eventualmente si puo' forzare la disconnesione
abbassando wait_timeout e/o interactive_timeout)
e che non utilizzino connection pool troppo ampi.
Il parametro non va sovvradimensionato perche' ha un impatto
sul numero di file descriptor aperti e richiede l'aumento
del parametro table_open_cache.
Meglio configurare correttamente le applicazioni ed i relativi connection pool.
- sort_buffer_size (256K):
dimensione del buffer di sort.
L'allocazione e' per sessione e quindi il parametro va aumentato con attenzione
(meglio non superare i 2M).
Quando il valore della variabile SORT_MERGE_PASSES
cresce in modo significativo il parametro andrebbe aumentato.
Il parametro si puo' modificare anche per la singola sessione
che effettua query pesanti con ORDER BY e/o GROUP BY.
- skip_name_resolve (OFF):
flag risoluzione DNS.
Nel caso di client su cui non si riesce ad affettuare il reverse lookup
l'avvio delle sessioni MySQL e' rallentato.
Se si abilita le definizioni degli utenti
non possono utilizzare nomi di dominio, quindi generalmente lo si lascia disabilitato...
ma le eccezioni sono molte!
- Versione MySQL.
Gli aggiornamenti ed i fix presenti nelle nuove versioni MySQL
possono avere un impatto significativo sulle performances:
sempre meglio utilizzare (dopo averla provata)
l'ultima versione di produzione disponibile
[NdA eg. l'impostazione di range_optimizer_max_mem_size e' stata molto migliorata dalla 5.7.12,
anche se qualche volta occorre ancora ritoccare il parametro].
E se...
Come cambiare la configurazione per ottimizzare le prestazioni se si utilizza...
- MyIsam
MyIsam e' stato l'Engine di default fino alla versione 5.5
ma ora e' utilizzato in modo molto ridotto (eg. per il data dictionary):
in generale i parametri di default per il MyISAM nella versione 5.7 vanno benissimo come sono!
Ma se si utilizza ancora MyIsam in modo significativo per qualche applicazione
i parametri su cui effetture il tuning sono:
- key_buffer_size (8M):
e' la cache per gli indici MyISAM.
Deve essere allocato per
contenere tutti gli indici MyISAM (lo spazio occupato da tutti gli indici MyISAM si ottiene facilmente).
L'impostazione non deve mai superare il 40% della RAM anche se si utilizzano solo tabelle MyISAM
perche', a differenza della cache InnoDB,
questa cache contiene solo gli indici e MySQL si appoggia sulla cache di sistema operativo
per il caching dei dati.
La verifica e' semplice: KEY_READS / KEY_READ_REQUESTS.
- read_buffer_size (131072):
buffer di lettura.
Anche se non e' utilizzato solo per MyISAM la sua variazione e' significativa
sopratutto per ridurre il numero di accessi a disco sui sequential scan con tabelle MyISAM.
Attenzione: questo parametro (come pure join_buffer_size, sort_buffer_size e read_rnd_buffer_size)
e' allocato per sessione, quindi va aumentato con attenzione e solo quando necessario.
- CMS o applicazioni con query ripetute
Alcune applicazioni effettuano un numero molto elevato di query identiche
su dati che sono modificati con bassa frequenza.
Ricadono in questa tipologia anche applicazioni web CMS o simili
(eg. Wordpress, Joomla!, Drupal, Moodle, ...) anche se possono
utilizzare anche altri strumenti quali memcache.
Per questa tipologia di utilizzo del DB e' consigliabile l'uso della query cache,
che invece e' assolutamente da evitare per le applicazioni OLTP.
Quando una query viene risolta dalla cache la risposta e' immediata
perche' in effetti la query non viene ne compilata ne eseguita: semplicemente
si restituisce il valore gia' ottenuto in precedenza.
I principali parametri sono:
- query_cache_type (0):
flag di attivazione.
Va impostato ad 1 se si vuole attivare la cache.
L'utilizzo della query cache peggiora le prestazioni per molte applicazioni (eg. OLTP)
ma le migliora in modo significativo per alcune altre (eg. CMS).
- query_cache_size (0):
dimensione della query cache.
Un'impostazione ragionevole e' 16M ma si puo' arrivare anche a 256M.
In questo caso sono due gli indicatori utili per valutare l'efficacia
della query cache: quante query vengono risolte
(QCACHE_HITS / #statement/sql/select) e quanti eventi invalidano la cache
(QCACHE_HITS / QCACHE_INSERTS).
Attenzione che un valore troppo elevato di query_cache_size impatta
sulle prestazioni perche' vengono usati lock di basso livello
(mutex) che risultano come "Waiting for query cache lock" visualizzando la
PROCESSLIST.
La query cache si piu' deframmentare con FLUSH QUERY CACHE
[NdA mentre si ripulisce completamente con RESET QUERY CACHE].
L'utilizzo della query cache e' deprecato dalla 5.7.20 ed e' stato rimosso nella versione 8.0.
- MariaDB
MariaDB e' un fork di MySQL ed ha buone impostazioni di default,
quindi in generale il tuning prestazionale e' ancora meno necessario!
La maggioranza dei parametri sono gli stessi quindi valgono le indicazioni
gia' fornite per MySQL.
Dal punto di vista del data dictionary MariaDB 10.x e' molto vicino a MySQL 5.6.
Per un piu' completo monitoraggio prestazionale
e' consigliabile attivare il performance schema, che su alcune versioni
e' disabilitato per default [NdA e corrisponde al performance_schema di MySQL/Oracle 5.6].
L'abilitazione si effettua impostando nel my.cnf nella sezione [mysqld]:
performance_schema
performance_schema_events_statements_history_long_size=10000
Per le tabelle temporanee MariaDB utilizza l'Engine Aria la cui impostazione
piu' importante e' aria_pagecache_buffer_size.
Per un normale utilizzo l'impostazione di default (128MB) e' adeguata,
ma se l'Engine Aria viene utilizzato in modo significativo il parametro puo' essere aumentato.
- 8.0
E' un'altra versione!
Un poco di pazienza e scrivero' anche su quella... al momento usate gli stessi parametri consigliati per la 5.7
[NdE l'autore e' notoriamente pigro]
[NdA l'editore e' notoriamente ansioso: ho scritto una paginetta sul tuning MySQL 8.0!].
- Replication
La funzionalita' di replica dati di MySQL e' semplice da configurare, flessibile
ed affidabile: e' quindi molto utilizzata.
I parametri per la sua configurazione sono molteplici e descritti nei
relativi documenti (eg. Replica MySQL,
GTID,
Multi-Source).
La replica ha un peso tipicamente limitato sulle prestazioni del Master.
Mentre sugli Slave oltre alle prestazioni
un ulteriore aspetto molto importante e' il lag ovvero l'eventuale ritardo rispetto al Master.
Nel seguito riportiamo i parametri piu' significativi dal punto di vista delle performance,
sia per il Master che per gli Slave, quando e' attiva la replica:
- innodb_flush_log_at_trx_commit (1):
indica la modalita' di scrittura dei log.
L'abbiamo gia' visto... ma e' molto importante anche
nelle configurazioni in replica.
E' possibile utilizzare impostazioni diverse sugli Slave:
su Slave meno performanti del Master impostare a 2 il parametro
diminuisce il lag senza svantaggi particolari.
- innodb_flush_log_at_timeout (1):
indica l'intervallo di tempo tra flush in secondi.
Con I/O particolarmente lento sullo Slave
si puo' arrivare fino a 600 secondi, ovvero 10 minuti, superare tale valore non e' significativo.
- sync_binlog (1):
numero di transazioni sincronizzate in gruppo su disco.
Il default e' il valore piu' sicuro ma ha un impatto prestazionale sul Master
(e sul parallelismo negli Slave).
Si puo' aumentare (eg. 256) o disabilitare (impostandolo a 0)
anche se con il rischio di perdere transazioni sul binlog
nel caso di caduta del Master.
- relay_log_info_repository e master_info_repository (FILE):
repository informazioni per la replica.
Il default e' FILE ma per ragioni di performance e robustezza meglio impostarli entrambe a TABLE.
- slave-parallel-workers (0) slave-parallel-type (DATABASE):
indicano la modalita' di suddivisione del lavoro tra i thread dello slave.
Per default l'MTS (Multi Threaded Slave) e' disabilitato ma con un carico significativo e
se lo slave tende ad avere un lag, puo' migliorare notevolmente le prestazioni e ridurre il lag.
L'impostazione dell'MTS si esegue effettuando un tuning sul numero
di thread (eg. 4) e, sopratutto, impostando il parametro slave-parallel-type=LOGICAL_CLOCK
[NdA e se va peggio? Sperimentare iniziando con binlog-group-commit-sync-delay=3000,
il valore e' in microsecondi: si deve sperimentare il valore ottimale che dipende dal grado
di parallelismo e dal tipo di transazioni].
La replica asincrona di MySQL si e' evoluta nel tempo e vi sono diverse
variazioni orientate ad una maggiore affidabilita' e ad un miglioramento
della gestione.
In generale tutte hanno un impatto sulle prestazioni...
- Replica Semisincrona:
la replica semi-sincrona riduce la perdita di dati in caso di crash.
L'impatto sulle prestazioni e' significativo poiche' il client sul Master
attende il feedback dello Slave (di almeno uno degli Slave per essere precisi)
per effettuare il commit.
Con la replica semisincrona lo Slave lag e' molto piu' basso ma tutte le transazioni vengono rallentate
in attesa del sync (rpl_semi_sync_master_wait_point AFTER_SYNC).
Il parametro rpl_semi_sync_master_timeout (10000) indica il timeout in millisecondi
per passare alla replica asicrona, puo' essere abbassato per ridurre i delay sul client.
C'e' pero' un ultimo aspetto da considerare: se si utilizza la replica semi-sincrona
allora probabilmente si possono rilassare le condizioni di durabilita' sul Master
cambiando i relativi parametri (innodb_flush_log_at_trx_commit=2 e sync_binlog=0 o sync_binlog=128).
- Replica GTID:
la replica GTID battezza ogni transazione semplificandone la gestione.
Un impatto sulle prestazioni e' presente ma e' limitato: e' sempre
consigliato utilizzare il GTID per
i grandi vantaggi sulla gestione delle repliche e sulla verifica dei dati!
L'impostazione si effettua con i parametri enforce-gtid-consistency=ON e gtid-mode=ON.
Non vi sono parametri specifici con impatto sulle prestazioni (se non quelli gia' riportati).
- Group Replication / InnoDB Cluster:
consente di realizzare topologie di replica in alta affidabilita' basate sul consenso.
Per ridurre i conflitti ed ottenere le maggiori prestazioni
conviene utilizzare la modalita' single-primary (group_replication_single_primary_mode ON).
Le prestazioni della Group Replication sono inferiori a quelle della replica asincrona.
I parametri piu' efficaci sono gli stessi gia' indicati per la replica asincrona (eg.
innodb_flush_log_at_trx_commit, sync_binlog, slave-parallel-workers e slave-parallel-type).
- Galera Cluster:
utilizza il plugin WSREP sull'Engine InnoDB per creare un cluster MySQL replicato in modo sincrono.
I principali parametri di Galera si impostano nella variable WSREP_PROVIDER_OPTIONS,
anche se molti parametri si riferiscono alle temporizzazioni del Cluster alcuni hanno un impatto sulle prestazioni:
fc_limit e' il ritardo massimo, in numero di transazioni, che un nodo puo' avere
prima che si attivi il flow control e con fc_master_slave=YES viene mantenuto lo si rende fisso.
I parametri MySQL sulla buffer cache, per l'InnoDB e sulla replica hanno ovviamente un forte impatto.
Poiche' Galera garantisce la durability a livello di cluster a volte si rinuncia a quella a livello di singolo server
impostando innodb_flush_log_at_trx_commit=2...
Varie ed eventuali
Manca qualcosa? No: abbiamo descritto tutti i parametri utili!
In generale le configurazioni di default presenti nella 5.7 per tutti gli altri parametri
sono gia' ottimali...
table_open_cache: era un parametro molto importante per il tuning ma
il default e' passato a 2000 dalla 5.6 che e' un valore
generalmente sufficiente;
table_open_cache_instances: riduce le contese sui latch delle tabelle
ma in 5.7 il default e' 16, adatto per sfruttare anche i piu' recenti processori multi-core;
innodb_file_per_table: e' assolutamente conveniente abilitarlo, ma e' gia' cosi' per default;
innodb_data_file_path: consente di specificare i file e la dimensione di cui e' composta
la partizione InnoDB; era importante quando si aveva il limite dei 2GB sui file e non si poteva
utilizzare innodb_file_per_table, ora non si ha la necessita' di utilizzarlo;
log_bin: e' giustamente disabilitato (rallenta) per default,
e' pero' necessario per la replica o il PITR,
quindi se serve si abilita altrimenti lo si lascia disabilitato:
e' inutile riportalo come parametro di tuning;
binlog_format:
tranne in rari casi l'impostazione piu' efficiente e' quella ROW che e' gia' il default nella 5.7,
le alternative sono STATEMENT (utile per le prestazioni in qualche specifico caso) e MIXED;
tmp_table_size e max_heap_table_size:
vanno aumentati rispetto al default (16M) solo se il rapporto
CREATED_TMP_DISK_TABLES / CREATED_TMP_TABLES e' elevato e comunque dopo
aver corretto eventuali query mal scritte;
sync_relay_log: ha un forte impatto prestazionale sullo slave se viene
impostato =1 ma il default e' 10000;
innodb_flush_method: non cambiatelo!
la scelta del metodo di flush puo' essere importante per le prestazioni in alcuni casi
ma per valutare se cambiare rispetto al default fsync (eg. impostandolo a O_DIRECT che evita il double buffering)
e' opportuno un benchmark specifico sui dischi
ed una conoscenza esatta del comportamento dello storage e del sistema operativo,
alcune impostazioni sono corrette su versioni e file system specifici (eg. O_DIRECT_NO_FSYNC 5.7.25+)...
insomma io non lo cambio o ne sapete piu' di me o non cambiatelo;
innodb_buffer_pool_instances: indica il numero di regioni in cui il buffer pool e' diviso
per ridurre le contese dei latch. E' un'ottimizzazione significativa... ma il valore di default
e' gia' adatto alla maggioranza dei casi;
innodb_deadlock_detect: e' un nuovo parametro di tuning disponibile nella
5.7. Di default e' ad ON ed e' consigliato nella maggior parte dei casi.
Su un benchmark si puo' disattivare ma non e' tipicamente il caso su un sistema di produzione
perche' piu' efficace del classico timeout;
innodb_doublewrite: non cambiatelo!
a meno che non stiate eseguendo un benchmark non e' il caso di rischiare
la corruzione del DB;
performance_schema: e' vero che ha un peso per le prestazioni,
ma non e' consigliabile impostarlo ad OFF su un ambiente di produzione a meno che
non siate certi della buona qualita' dell'SQL di tutte le applicazioni ospitate
[NdA non sono tutti di questa opinione: su MariaDB per default e' disabilitato
e lo stesso avviene con MySQL su Amazon RDS;
al contrario Percona, benchmark alla mano, lo ritiene poco invasivo];
quando Qcache_lowmem_prunes e' elevato manca memoria alla query cache;
oltre alla query_cache_size si puo' impostare la dimensione
dell'allocazione per query query_cache_min_res_unit;
il default e' 4KB, se le query hanno un risultato di dimensioni ridotte
si puo' abbassare ma il tuning richiede l'analisi dei valori
di Qcache_total_blocks e Qcache_queries_in_cache nel tempo...
innodb_lru_scan_depth: il default (1024) va bene anche se ammetto di averlo abbassato
a 256 in un paio di casi,
toccare altri parametri (eg. innodb_io_capacity_max, innodb_io_capacity, innodb_page_cleaners, ...)
va fatto dopo prove specifiche e si conosce esattamente come agiscono;
spesso sono presenti attese su lock che attendono fino a
innodb_lock_wait_timeout, anche se e' possibile monitorarle con
INNODB_ROW_LOCK_TIME, INNODB_ROW_LOCK_WAITS, INNODB_ROW_LOCK_CURRENT_WAITS, ...
tipicamente non si puo' fare molto dal punto di vista del tuning
ma solo agire sull'applicazione;
max_allowed_packet: e' un parametro che spesso si modifica, ma non e' un parametro prestazionale;
vm.swappiness: da impostare sempre a 1 su un DB server, ma e' un parametro del sistema operativo!
...
Manca qualcosa? Sicuramente si!
Sarebbe un grave errore pensare di poter riassumere tutto in una paginetta.
I parametri MySQL
della versione 5.7 sono centinaia e sono tutti importanti per sfruttare le funzionalita'
ed ottenere le massime performance con MySQL.