Aujourd'hui, nous parlerons encore et encore de mySQL. Comprenons l'optimisation et parlons de nombreux paramètres de serveur.
Commençons.
Début
Le serveur nous le laissons être sur CentOS . Optimisera la méthode d'édition de la configuration de my.cnf .
La définition de certains paramètres peut améliorer les
performances de la base de données du serveur à plusieurs reprises!
Pour commencer, décidons ce que nous optimisons généralement - c'est-à-dire combien de tables sur quel moteur nous avons, quel matériel nous avons et sous quels paramètres nous ajusterons le tout.
Pour cela, nous prenons htop (comme un outil magnifique et intuitif):
yum install htop
Dériver htop :
htop
Nous obtenons quelque chose comme ceci:
écrivez-vous dans le my.cnf :
# 3 , 4
Voyons maintenant le nombre de tables et leurs types.
Pour cela, nous prenons le tuner mysql :
wget https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
Courons:
perl mysqltuner.pl
Conclusion approximative:
Écrivons-nous dans my.cnf :
# 64M myisam, 770M innoDB
Une configuration typique est généralement recommandée comme ceci:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
user = mysql pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-external-locking
max_allowed_packet = 16M
key_buffer_size = 16M
innodb_buffer_pool_size = 2048M
innodb_file_per_table = 1
innodb_flush_method = O_DIRECT
innodb_flush_log_at_trx_commit = 0
max_connections = 144 <a
href="https://ruhighload.com/query_cache_size+%d0%bf%d0%b0%d1
%80%d0%b0%d0%bc%d0%b5%d1%82%d1%80+%d0%b2+mysql"
target="_blank" style="color: rgb(232, 95, 99);">query_cache_size</a>
= 0 slow_query_log = /var/log/mysql/mysql-slow.log
long_query_time = 1
expire_logs_days = 10
max_binlog_size = 100M
[mysqldump]
quick
quote-names
max_allowed_packet = 16M
Voyons maintenant ce que nous allons optimiser ici, pourquoi, comment et pourquoi (surtout pourquoi ces paramètres ne suffisent pas.
Optimisation et configuration
Tout d'abord, vous pouvez faire défiler vers le bas de la sortie du tuner mysql et voir ce qu'il recommande. Dans notre cas, cela ressemble à ceci:
wget
https://raw.github.com/major/MySQLTuner-perl/master/mysqltuner.pl
perl mysqltuner.pl
Nous ne nous engagerons pas dans une substitution insensée, et passerons en revue les paramètres de mysql , qui peuvent nous intéresser en premier lieu. Qu'est-ce que c'est:
le skip-external- lock, - supprime le verrou extérieur, qui est plus rapide;
-nom-le sauter le RESOLVE , - permet à MySQL d'éviter la réponse pour demander la connexion client de vérification DNS au serveur MySQL .
Ainsi, le serveur MySQL n'utilisera que des URL
IP au lieu de noms d'hôte un peu, mais plus rapidement.
binlog_cache _ size, - la taille du cache pour stocker les modifications dans le journal binaire. Définit la taille du cache des transactions uniquement. Do 100M - n'est plus nécessaire.
innodb_stats_on_metadata = 0 (OFF) , - pour accélérer avec
INFORMATION_SCHEMA, SHOW TABLE STATUS ou SHOW INDEX désactiver la mise à jour des statistiques pour des fonctions telles que
quer y _cache_size = 128M et query_sache_type
= 1 , - demander des caches. 1 - en principe activé, limite de 128M . Il n'est pas
recommandé de le placer au-dessus de 256M , car cela peut entraîner un blocage.
Puisque nous avons plus que des tables InnoDB , il disparaît du lit de taille de cache .
Avec la version MySQL 5.6 query_cache_size désactivée, et avec la version 8.0, supprimée par
défaut, toutes les tables et index sont stockés dans un seul fichier, nous utilisons donc innodb_file_per_table = 1.
La valeur innodb_open_files et table_open_cache - il est recommandé de définir les deux options dans 4096 ou 8192 . A généralement calculé comme le nombre de tables dans toutes les bases multiplié par 2 , environ.
Lorsque vous travaillez avec InnoDB est le paramètre le plus important innodb_buffer_pool_size , il est défini sur le principe "plus il y en a, mieux c'est". Il est recommandé d'allouer jusqu'à 70 à 80% de la RAM du serveur.
innodb_log_file_size - affecte la vitesse d'écriture, définit la taille du journal des opérations (les opérations sont d'abord écrites dans le journal, puis appliquées aux données sur le disque). Plus ce journal est volumineux, plus les enregistrements fonctionneront rapidement (car il y en a plus dans le fichier journal). Il y a toujours deux fichiers et leur taille est la même. La valeur du paramètre définit la taille d'un fichier.
!️ innodb_log_file_size MySQL, ib_logfile-n ( /var/lib/mysql/), innodb_log_file_size MySQL.
MySQL - .
L'installation d'un innodb_log_file_size de grande taille peut entraîner une augmentation des performances, mais en même temps augmentera le temps de récupération, sélectionnez de 256M à 1G .
innodb_log _ buffer_size - taille du tampon de transaction. Il est généralement recommandé de ne pas appliquer si vous n'utilisez pas BLOB et TEXT large.
innodb_flush _ méthode , - définit la logique de vidage des données sur le disque. Dans les systèmes modernes utilisant des sites RAID et de sauvegarde, vous choisirez entre ODSYNCet ODIRECT , - le premier paramètre est le deuxième plus rapide, plus sûr.
_ size bed key_buffer - tampon pour travailler avec les clés et les index, et sort_buffer - tampon pour le tri. Si vous n'utilisez pas de tables MyISAM , il est recommandé de définir key_buffer_size sur 32 Mo pour stocker
les index de table temporaire .
Le paramètre thread_cache _ size indique le nombre de threads (threads), quittant le cache lorsqu'un client se déconnecte. Avec une nouvelle connexion, le thread n'est pas créé, mais extrait du cache, ce qui économise des ressources sous de lourdes charges.
innodb_flush_log_attrx_commit , - peut augmenter le débit des enregistrements de données dans les centaines de fois la base. Il détermine si Mysql va vider chaque opération sur le disque (dans un fichier journal).
innodb_flush_log_at_trx_commit = 1 est utilisé pour les cas
où la conservation des données - est la priorité numéro un.
innodb_flush_log_at_trx_commit = 2 pour les cas où une petite perte de données n'est pas critique. Il y a aussi 0 (zéro) - l'option la plus productive, mais non sûre.
max_connections - si vous obtenez l'erreur "Trop de connexions",cette option devrait être augmentée. Et donc, il n'y a pas de grand avantage à l'optimiser.
Le nombre de fichiers d'entrée / sortie dans les flux InnoDB spécifie les options innodb_read_io_threads , innodbwrite_io_threads , ce paramètre est généralement défini sur 4 ou 8 , la ROM rapide définie dans le SSD 16. Signification innodb_thread_concurrency définit le nombre de cœurs * 2 .
La configuration est comme ça:
[client]
port = 3306
socket = /var/run/mysqld/mysqld.sock
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock nice = 0
[mysqld]
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
old_passwords = 0
bind-address = 127.0.0.1
skip-external-locking
skip-name-resolve
binlog_cache_size = 100M
thread_cache_size = 32
innodb_stats_on_metadata = OFF
query_cache_limit = 1M
query_cache_size = 0 query_cache_type = 1
innodb_buffer_pool_size = 3G
innodb_log_file_size = 256
innodb_log_buffer_size = 6M
innodb_additional_mem_pool_size = 16M
innodb_flush_method = O_DSYNC
innodb_flush_log_at_trx_commit = 0
innodb_thread_concurrency = 6
innodb_file_per_table = 1
key_buffer_size = 32M
tmp_table_size = 64M
max_connections = 350
sort_buffer_size = 16M read_buffer_size = 1M
read_rnd_buffer_size = 1M
join_buffer_size = 8M
thread_stack = 1M
binlog_cache_size = 8M
tmp_table_size = 128M
table_open_cache = 2048
[mysqldump] quick
quote-names
max_allowed_packet = 16M
Et enfin, vous pouvez voir les recommandations du tuner et les suivre.
Conclusion
Voici une configuration si intéressante. Si vous trouvez cela difficile, vous devez d'abord utiliser une calculatrice mySQL , qui vous indiquera les principaux paramètres et vous permettra de ne pas dépasser la mémoire disponible - après tout, tout en dépend:
Merci de votre attention. Rejoignez la discussion.