Audit et performance MySQL / MariaDB
Voici un petit outil pour réaliser rapidement un audit et obtenir des conseils pour améliorer les performances de MySQL / MariaDB.
L'outil se récupère comme suit :
wget http://mysqltuner.pl/ -O mysqltuner.pl
Et s'utilise le plus simplement du monde (avec les options qui vont bien) :
./mysqltuner.pl
Sur l'installation auditée, voici quelques conseils :
General recommendations:
/var/log/mysqld.logis > 32Mb, you should analyze why or implement a rotation log strategy such as logrotate!
Control warning line(s) into /var/log/mysqld.log file
Control error line(s) into /var/log/mysqld.log file
Remove Anonymous User accounts - there are 2 anonymous accounts.
Set up a Password for user with the following SQL statement ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Set up a Secure Password for user@host ( SET PASSWORD FOR 'user'@'SpecificDNSorIp' = PASSWORD('secure_password'); )
Restrict Host for user@% to user@SpecificDNSorIp
Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=1
Increasing the query_cache size over 128M may reduce performance
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
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 (1024000) variable
should be greater than table_open_cache (4096)
Performance should be activated for better diagnostics
Consider installing Sys schema from https://github.com/mysql/mysql-sys
Variables to adjust:
query_cache_type (=0)
query_cache_size (> 1G) [see warning above]
join_buffer_size (> 256.0K, or always use indexes with joins)
tmp_table_size (> 2G)
max_heap_table_size (> 16M)
table_open_cache (> 4096)
performance_schema = ON enable PFS
innodb_log_file_size * innodb_log_files_in_group should be equals to 1/4 of buffer pool size (=8G) if possible.
innodb_buffer_pool_instances(=16)
innodb_log_buffer_size (>= 16M)
À part query_cache_type
, les paramètres à adapter paraissent justes.