Ottimizzare le prestazioni di MySQL e MariaDB con MySQLTuner

Non appena abbiamo fatto il setup di un nuovo server, è necessario spesso effettuare un controllo sulle prestazioni del server MySQL, MariaDB o Percona installato sulla macchina. Ottimizzare la scrittura delle tabelle, la cache e tutti i piccoli errori di configurazione può portare anche ad un significativo incremento di prestazioni. Ricordiamo, inoltre, che le configurazioni di default possono non essere sicure!

Per effettuare un controllo sistematico, possiamo usare il MySQLTuner, uno script open source scritto in Perl che effettua una lunga lista di controlli  (circa 300) sul database installato sul nostro server, tra i quali:

• Informazioni di sicurezza e accesso
• Confronto della versione installata sul server con un database di bollettini di sicurezza per capire se è vulnerabile
• Controlla cache, indici e connessioni
• Controlla slow queries e performance di join e altre operazioni comuni

Questo script NON modifica alcuna configurazione. Si tratta di uno script di sola lettura che analizza e vi suggerisce le impostazioni più veloce e sicure. Sta a noi, eventualmente, modificare la configurazione del server e beneficiare dei consigli.

Come detto, è uno script in perl, pertanto è necessario scaricare un singolo file più due file aggiuntivi che contengono rispettivamente un dizionario delle password non sicure e una lista di vulnerabilità note.

Eseguiamo questi comandi nella shell:

wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

A questo punto eseguiamo lo script:

perl mysqltuner.pl --cvefile=vulnerabilities.csv

Otterremo a schermo una lunga lista di valutazioni segnate da un [OK], se la configurazione è corretta, e [!!] dove lo script ci suggerisce che ci sarebbe bisogno di un qualche aggiustamento.

Ecco ad esempio una serie di raccomandazioni che MySQLTuner ci indica:

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Control warning line(s) into /var/log/mysqld.log file
    Control error line(s) into /var/log/mysqld.log file
    Restrict Host for user@% to user@SpecificDNSorIp
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate persistent connections to reduce connection usage
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries which have no LIMIT clause
    Set thread_cache_size to 4 as a starting value
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (4257) variable 
    should be greater than table_open_cache (2048)
    Performance shouldn't be activated for MySQL and MariaDB 5.5 and lower version
    Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
    max_connections (> 151)
    wait_timeout (< 28800)
    interactive_timeout (< 28800)
    query_cache_size (>= 8M)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    thread_cache_size (start at 4)
    table_open_cache (> 2048)
    performance_schema = OFF disable PFS
    innodb_file_per_table=ON
    innodb_buffer_pool_size (>= 47M) if possible.
    innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=1M) if possible.

Modifichiamo la configurazione di MySQL o MariaDB

Proviamo ad applicare qualche consiglio che lo script ci indica, nello specifico, proveremo a modificare questi valori:

• wait_timeout sotto 28000
• query_cache_size maggiore o uguale a 8M
• innodb_file_per_table settato a ON

Per prima cosa, troviamo il file my.cnf, che si troverà in posizione diversa a seconda del sistema. Generalmente, si trova in /etc/mysql/my.cnf oppure /etc/my.cnf. Apriamo il file con un qualsiasi editor, nel nostro caso vim

vi /etc/my.cnf

Modifichiamo le variabili come suggerito e, se non esistono, aggiungiamole al file.

Una volta salvato il file my.cnf, riavviamo MySQL o MariaDB per applicare la nuova configurazione:

/etc/init.d/mysqld restart

Per verificare che i nuovi setting siano applicati possiamo rilanciare MySQLTuner: non dovremmo trovare i parametri appena modificati tra i suggerimenti.