Méthodes de réglage et de test des performances MySQL

Après que notre service Mysql ait fonctionné pendant un certain temps, il ralentit pour des raisons inconnues. Comment pouvons-nous en découvrir la raison ?

1. Indicateurs clés

Dans l'évaluation des performances des bases de données, il existe plusieurs indicateurs qui sont très importants. Leur utilisation pour évaluer les capacités de la base de données n'a pas d'importance à quel point ils peuvent jouer. , mais ils peuvent représenter plus clairement les capacités de la base de données sous certains aspects.

1.IOPS

IOPS : Opération d'entrée/sortie par seconde, le nombre de requêtes IO traitées par seconde.
Nous savons que les E/S sont la capacité de lecture et d'écriture du disque. Par exemple, lire 300 M par seconde et écrire 200 M par seconde correspond au débit de données (un autre indicateur clé de la capacité d'E/S), mais IOPS ne signifie pas débit de données en lecture et en écriture, IOPS fait référence à 每秒能够处理的 I/O 请求次数.

Si vous souhaitez que le système d'E/S réponde assez rapidement, plus les IOPS sont élevées, mieux c'est. Parce que les IOPS sont liées au matériel, donc pour améliorer les IOPS, la solution actuelle consiste essentiellement à utiliser le matériel traditionnel. La solution consiste à en utiliser plusieurs. Une fois le disque bloc réparti via RAID, les capacités de lecture et d'écriture des E/S sont améliorées. Nous pouvons également utiliser des disques SSD (SSD) pour augmenter les IOPS, mais le coût des SSD peut être relativement élevé.

2.QPS

QPS : Query Per Second, le nombre de requêtes (requêtes) par seconde.
Ce paramètre est très important et peut refléter intuitivement les performances du système. C'est comme les IOPS mesurant le nombre de requêtes qu'un disque peut recevoir par seconde.

Nous pouvons exécuter la commande status en mode ligne de commande MySQL, et la dernière ligne d'informations de sortie renvoyée contient l'indicateur QPS.

3.TPS

TPS : Transaction Par Seconde, nombre de transactions par seconde.
Les paramètres TPS ne sont pas fournis nativement par MySQL. Si nous devons les calculer nous-mêmes, nous pouvons utiliser la formule de calcul :

TPS = (Com_commit + Com_rollback) / Seconds

Cette formule a deux variables d'état, représentant le nombre de soumissions et le nombre de soumissions. le nombre de restaurations. Les secondes sont l'intervalle de temps que nous définissons.

2. Indicateurs clés du test TPCC

TPCC-MySQL est un programme de test de référence MySQL développé par Percona sur la base de la spécification TPCC. Nous utilisons cet ensemble d'outils pour tester les trois indicateurs importants précédents. .

1. Installation et utilisation de l'outil TPCC

Pour une installation spécifique, vous pouvez lire ces deux articles de blog sur l'installation et l'utilisation des tests de l'outil de test de stress mysql tpcc-mysql, mysql performance test-tpcc, TPCC est plus capable de simuler les affaires en ligne.

3. Optimisation de la configuration des paramètres de la base de données

Si les paramètres de la base de données sont configurés de manière raisonnable, l'efficacité opérationnelle peut être considérablement améliorée, c'est-à-dire que l'utilisation des ressources système peut être maximisée.

1. Paramètres liés à la connexion

1.1 max_connections

max_connections : Spécifiez le serveur MySQL 最大并发连接数, la valeur varie de 1 à 100 000, la valeur par défaut est 151.
Ce paramètre est très important car il détermine le nombre maximum de sessions pouvant être connectées au service MySQL en même temps. Lors de la définition de ce paramètre, en fonction de la configuration et des performances du serveur de base de données, ce n'est généralement pas un gros problème de définir la valeur du paramètre entre 500 et 2000.

1.2 max_connect_errors

max_connect_errors : 指定允许连接不成功的最大尝试次数, la valeur varie de 1~2^64, la valeur par défaut est 100 dans la version 5.6.6.

Assurez-vous de ne pas ignorer ce paramètre. Si le nombre d'erreurs lors de la tentative de connexion dépasse la valeur spécifiée par ce paramètre, le serveur n'autorisera plus les nouvelles connexions. Oui, il refusera la connexion. fournit toujours des services, il ne peut pas. Une nouvelle connexion est créée. Vous pouvez utiliser FLUSH HOSTS pour effacer le statut ou redémarrer le service de base de données, mais le coût est trop élevé et vous ne le faites généralement pas, alors 这个参数的默认值太小，这里建议将之设置为 10 万以上的量级.

1.3 interactive_timeout et wait_timeout

Les deux paramètres sont liés au délai d'expiration automatique de la session de connexion, le premier est utilisé pour spécifier le temps d'attente avant de fermer la connexion interactive, et le second est utilisé pour. spécifier la fermeture des connexions non interactives. Le temps d'attente avant la connexion interactive, l'unité est en secondes, la valeur par défaut est 28800, soit 8 heures.

1.4 skip-name-resolve

skip-name-resolve : Cela peut être simplement compris comme la désactivation de la résolution DNS. Notez qu'il s'agit du comportement du serveur et que le client n'est pas vérifié lorsque. connexion. Nom d'hôte au lieu de simplement l'adresse IP. Si ce paramètre est spécifié, lors de la création d'un utilisateur et de l'octroi des autorisations, la colonne HOST doit être une adresse IP plutôt qu'un nom d'hôte. Il est recommandé d'activer ce paramètre, ce qui permettra d'accélérer la connexion réseau. Cela équivaut à ignorer la résolution du nom d'hôte.

1.5 back_log

back_log : Spécifiez MySQL 连接请求队列中存放的最大连接请求数量, dans la 5.6.6 version 前, la valeur par défaut est 50 et la valeur maximale ne dépasse pas 65535. Dans la version 5.6.6 de 后, la valeur par défaut est -1, ce qui signifie qu'elle est automatiquement ajustée par MySQL. Le soi-disant auto-ajustement a en fait des règles, c'est-à-dire 50+ (max_connections/5).

Ce paramètre est principalement utilisé pour traiter un grand nombre de demandes de connexion dans un court laps de temps. Le thread principal MySQL ne peut pas allouer (ou créer) des threads de connexion pour chaque demande de connexion dans le temps. ? Il ne peut pas le rejeter directement, 于是就将一部分请求放到等待队列中待处理, la longueur de cette file d'attente est la valeur du paramètre back_log Si la file d'attente est également pleine, alors les demandes de connexion suivantes seront rejetées.

2.文件相关参数

2.1 sync_binlog

sync_binlog：指定同步二进制日志文件的平率，默认为0.
如果要性能，则指定该参数为0，为了安全起见则指定该参数值为 1.

2.2 expire_logs_day

expire_logs_day：指定设置二进制日志文件的生命周期，超出则将自动被删除，参数值以天为单位，值得范围从0~99，默认值是0，建议将该参数设置为 7~14 之间，保存一到两周就足够了。

2.2 max_binlog_size

max_binlog_size： 指定二进制日志的大小，值得范围从 4KB~1GB，默认为 1GB。

3.缓存控制参数

3.1 thread_cache_size

thread_cache_size:指定MySQL为快速重用而缓存的线程数量。值得范围从 0~16384，默认值为0.
一般当客户端中断连接后，为了后续再有连接创建时，能够快速创建成功，MySQL 会将客户端中断的连接放入缓存区，而不是马上中断释放资源。这样当有新的客户端请求连接时，就可以快速创建成功。因此，本参数最好保持一定的数量，建议设置在 300~500 之间均可.另外，线程缓存的命中率也是一项比较重要的监控指标，计算规则为（1-Threads_created/Connections）* 100%,我们可以通过该指标来优化和调整thread_cache_size参数。

3.2 query_cache_type

sql_cache意思是说，将查询结果放入查询缓存中。
sql_no_cache意思是查询的时候不缓存查询结果。
sql_buffer_result意思是说，在查询语句中，将查询结果缓存到临时表中。

这三者正好配套使用。sql_buffer_result将尽快释放表锁，这样其他sql就能够尽快执行。

使用 FLUSH QUERY CACHE 命令，你可以整理查询缓存，以更好的利用它的内存。这个命令不会从缓存中移除任何查询。FLUSH TABLES 会转储清除查询缓存。
RESET QUERY CACHE 使命从查询缓存中移除所有的查询结果。

那么mysql到底是怎么决定到底要不要把查询结果放到查询缓存中呢？

是根据query_cache_type这个变量来决定的。

这个变量有三个取值：0,1,2，分别代表了off、on、demand。
mysql默认为开启 on

意思是说，如果是0，那么query cache 是关闭的。
如果是1，那么查询总是先到查询缓存中查找，即使使用了sql_no_cache仍然查询缓存，因为sql_no_cache只是不缓存查询结果，而不是不使用查询结果。

select count(*) from innodb;
1 row in set (1.91 sec)

select sql_no_cache count(*) from innodb;
1 row in set (0.25 sec)

如果是2，DEMAND。
在my.ini中增加一行
query_cache_type=2
重启mysql服务

select count(*) from innodb;
1 row in set (1.56 sec)

select count(*) from innodb;
1 row in set (0.28 sec)

没有使用sql_cache,好像仍然使用了查询缓存

select sql_cache count(*) from innodb;
1 row in set (0.28 sec)

使用sql_cache查询时间也一样，因为sql_cache只是将查询结果放入缓存，没有使用sql_cache查询也会先到查询缓存中查找数据

结论：只要query_cache_type没有关闭，sql查询总是会使用查询缓存，如果缓存没有命中则开始查询的执行计划到表中查询数据。

query cache优缺点
优点很明显，对于一些频繁select query，mysql直接从cache中返回相应的结果集，而不用再从表table中取出，减少了IO开销。
即使query cache的收益很明显，但是也不能忽略它所带来的一些缺点：

1. query语句的hash计算和hash查找带来的资源消耗。mysql会对每条接收到的select类型的query进行hash计算然后查找该query的cache是否存在，虽然hash计算和查找的效率已经足够高了，一条query所带来的消耗可以忽略，但一旦涉及到高并发，有成千上万条query时，hash计算和查找所带来的开销就的重视了；

2. query cache的失效问题。如果表变更比较频繁，则会造成query cache的失效率非常高。表变更不仅仅指表中的数据发生变化，还包括结构或者索引的任何变化；

3. 对于不同sql但同一结果集的query都会被缓存，这样便会造成内存资源的过渡消耗。sql的字符大小写、空格或者注释的不同，缓存都是认为是不同的sql（因为他们的hash值会不同）；

4. 相关参数设置不合理会造成大量内存碎片，相关的参数设置会稍后介绍。

合理利用query cache
query cache有利有弊，合理的使用query cache可以使其发挥优势，并且有效的避开其劣势。

1. 并不是所有表都适合使用query cache。造成query cache失效的原因主要是相应的table发生了变更，那么就应该避免在变化频繁的table上使用query cache。mysql中针对query cache有两个专用的sql hint：SQL_NO_CACHE和SQL_CACHE，分别表示强制不使用和强制使用query cache，通过强制不使用query cache，可以让mysql在频繁变化的表上不使用query cache，这样减少了内存开销，也减少了hash计算和查找的开销；

更多有关query cache详情文章，请看这里的原文：mysql query cache优化

3.3 query_cache_size

query_cache_size：指定用于缓存查询结果集的内存区大小，该参数值应为 1024 的整数倍。

这个参数不能太大，也不能太小，查询缓存至少会需要 40KB 的空间分配给其自身结构，太小时缓存结果集就没有意义，热点数据保存不了多少，而且总是很快就被刷新出去；但也不能太大，否则可能过多占用内存资源，影响整机性能，再说太大也没有意义，因为即便数据不被刷新，但只要源数据发生变更，缓存中的数据也就自动失效了，这种情况下分配多大都没有意义。个人建议设置不要超过 256MB。

3.4 query_cache_limit

query_cache_limit：用来控制查询缓存，能够缓存的单条 SQL 语句生成的最大结果集，默认是 1MB，超出的就不要进入查询缓存。这个大小对于很多场景都够了，缩小可以考虑，加大就不用了。

3.5 sort_buffer_size

sort_buffer_size：指定单个会话能够使用的排序区的大小，默认值为 256KB，建议设置为 1~4MB 之间。

3.6 read_buffer_size

read_buffer_size:指定随机读取时的数据缓存区大小，默认是 256KB，最大能够支持4GB，适当加大本参数，对于提升全表扫描的效率会有帮助。

4.InnoDB专用参数

4.1 innodb_buffer_pool_size

innodb_buffer_pool_size：指定InnoDB引擎专用的缓存区大小，用来缓存表对象的数据及索引信息，默认值为 128MB，最大能够支持（2^64 -1）B.

如果你有很多事务的更新，插入或删除很操作，通过修改innodb_buffer_pool 大小这个参数会大量的节省了磁盘I / O。

innodb_buffer_pool_size 是个全局参数，其所分配的缓存区将供所有被访问到的InnoDb表对象使用，若MySQL数据库中的表对象以 InnoDb 为主，那么本参数的值就越大越好，官方文档中建议，可以将该参数设置为服务器物理内存的70%~80%。

4.2 innodb_buffer_instances

innodb_buffer_instances：指定 InnoDB 缓存池分为多少个区域来使用，值得范围从 1~64，默认值为-1，表示由 InnoDB 自行调整。

只有当innodb_buffer_pool_size参数值大于1GB时，本参数才有效，那么本参数怎么设置呢？个人感觉可以参照 InnoDB 缓存池的大小，以 GB 为单位，每GB指定一个instances。例如当innodb_buffer_pool_size设置为16GB时，则指定 innodb_buffer_instances 设置为 16 即可。

5.参数优化案例

测试服务器有 16GB的物理内存，假定其峰值最大的连接数为 500 个，表对象使用InnoDB 存储引擎，我们的内存参数如何配置呢？

具体配置如下：
（1）、首先，为操作系统预留 20% 的内存，约为 3GB。
（2）、与线程相关的几个关键参数设置如下：

  sort_buffer_size=2m
  read_buffer_size=2m
  read_rnd_buffer_size=2m
  join_buffer_size=2m

预计连接数达到峰值时，线程预计最大将有可能占用 500 *（2+2+2+2）= 4GB内存（理论最大值）。

（3）、剩下的空间 16-3-4=9GB，就可以全部都分配给InnoDB 的缓存池，设定相关的参数如下：

innodb_buffer_pool_size=9g
innodb_thread_concurrency=8
innodb_flush_method=O_DIRECT
innodb_log_buffer_size=16m
innodb_flush_log_at_trx_commit=2

四、MySQL系统状态

想要了解MySQL服务当前在做什么，有个非常重要并且极为常用的命令：

SHOW [FULL] PROCESSLIST

SHOW PROCESSLIST 命令将每一个连接的线程，作为一条独立的记录输出。

还有相似的语句，
SHOW PROFILES 和 SHOW PROFILE可以获取会话执行语句过程中，资源的使用情况。

---

Ce qui précède est le contenu détaillé de. pour plus d'informations, suivez d'autres articles connexes sur le site Web de PHP en chinois!