my.ini

Le but de ce chapitre est de mettre en place un bon fichier my.ini

En premier lieu, on regarde la mémoire libre ainsi que le nombre de processeur

cat /proc/meminfo
MemTotal:        3064328 kB
MemFree:          697228 kB
MemAvailable:    1337368 kB

cat /proc/cpuinfo 
2 processeur

Regardons ensuite le nombre de tables (nous prenons ici l'exemple de https://dev.mysql.com/doc/employee/en/employees-installation.html)

Remplissons la et faisons les requetes automatiques

mysql < employees.sql
mysql -t < test_employees_md5.sql

Configuration réseau et de base

bind_address                    = 127.0.0.1 # mettre  0.0.0.0 pour les connexions distantes
max_allowed_packet              = 256M # taille d'un packet de donnée
max_connect_errors              = 1000000 # eviction d'un client apres max_connect_errors
skip_external_locking # pour MyISAM desaloue les lock systeme
skip_name_resolve # pas de résolution DNS
sql_mode                       ="NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES"
tmpdir                          = /tmp
#user                            = mysql En cas d'installation en mode service, il faut préciser l'utilisateur qui execute le service

Parametrage du nombre de connexion:

Cela est issue au runtime de :

show status like '%connected%'

et faire

max_connections                = 151                                 # nombre de connexion maximal
max_user_connections           = 145                                 # nombre de connexion pour un utilisateur
thread_cache_size              = 151                                 # =max_connexions

Variable de sessions

sort_buffer_size               = 2M                                  # Could be too big for many small sorts
tmp_table_size                 = 32M                                 # taille des table temporaire

read_buffer_size               = 128k                                # a verifier avec le calcul mémoire
read_rnd_buffer_size           = 256k                                # a verifier avec le calcul mémoire
join_buffer_size               = 128k                                # a verifier avec le calcul mémoire


# Other buffers and caches

table_definition_cache         = 1400                                # nombre de table dans la base de donnée au max au min show global status like 'open_tables';
table_open_cache               = 2000                                # approximativement show global status like 'opened_tables'; / max_connection
table_open_cache_instances     = 16                                  # New default in 5.7

Parametrage de InnoDB

default_storage_engine          = InnoDB # le moteur de stockage est InnoDB
innodb_buffer_pool_instances    = 1     # 1 instance par Giga, donc ici 1 pour buffer pool chunk size de 1G
innodb_buffer_pool_size         = 410M    # 80% de la RAM
innodb_file_per_table           = 1
innodb_flush_log_at_trx_commit  = 0
innodb_flush_method             = O_DIRECT
innodb_file_format             = Barracuda # format du fichier interne 
innodb_log_buffer_size          = 5M # entre 5 et 10% du log file size
innodb_log_file_size            = 64M # 25% du buffer pool
innodb_stats_on_metadata        = 0
#innodb_temp_data_file_path     = ibtmp1:64M:autoextend:max:20G # Control the maximum size for the ibtmp1 file
innodb_thread_concurrency      = 4     # nombre de CPU -1/2
innodb_read_io_threads          = 8
innodb_write_io_threads         = 8

Les deux derniers paramètres sont issue de SHOW ENGINE INNODB STATUS et en particulier du nombre de requete pending qui doivent être intéfieur a pending*64<io_thread

Parametrage de MyISAM

key_buffer_size                = 8M                                  # 25% de la RAM 
myisam_recover_options         = 'BACKUP,FORCE'

Et enfin les logs:

log_warnings                   = 2                                   # MySQL 5.6, equivalent à log_error_verbosity = 3
# log_error_verbosity            = 3                                   # MySQL 5.7, equivalent ) log_warnings = 2,a supprimer pour mariadb
innodb_print_all_deadlocks     = 1


# Slow Query Log

slow_query_log_file            = %INSTANCEDIR%/log/%UNAME%_%INSTANCE%_slow.log   
slow_query_log                 = 0
log_queries_not_using_indexes  = 0                                   # pour les developpeurs
long_query_time                = 0.5
min_examined_row_limit         = 100

# Replication

#server_id                      = %SERVERID%                            # id
#log_bin                        = %INSTANCEDIR%/binlog/%UNAME%_%INSTANCE%_binlog            #emplacement
# master_verify_checksum         = ON                                  # MySQL 5.6
#binlog_cache_size              = 1M
#binlog_stmt_cache_size         = 1M
#max_binlog_size                = 128M                                # en fonction du trafic
#sync_binlog                    = 1                                   # Mettre a 0 pour avoir des problemes
#expire_logs_days               = 5                                   # We will survive easter holidays
#binlog_format                  = ROW                                 # ROW est ok pour la replication
# binlog_row_image               = MINIMAL                             # Since 5.6
# auto_increment_increment       = 2                                   # Pour Master/Master mettre 2 
# auto_increment_offset          = 1                                   # Pour Master/Master mettre 1 et 2

Calcul de la mémoire

Utiliser https://www.mysqlcalculator.com/ qui fait le calcul de la mémoire du serveur:

key_buffer_size
+ query_cache_size
+ tmp_table_size
+ innodb_buffer_pool_size
+ innodb_additional_mem_pool_size
+ innodb_log_buffer_size
+ max_connections
×
(sort_buffer_size
+ read_buffer_size
+ read_rnd_buffer_size
+ join_buffer_size
+ thread_stack
+ binlog_cache_size )

On remarqueras le nombre de connexions maximuns influe enormément sur la mémoire