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 -O

Et s'utilise le plus simplement du monde (avec les options qui vont bien) :


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:
    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
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_log_buffer_size (>= 16M)

À part query_cache_type, les paramètres à adapter paraissent justes.