Résumé du code de plusieurs méthodes importantes de calcul et d'optimisation de l'indice de performance MySQL

L'éditeur ci-dessous vous apportera un résumé de plusieurs calculs d'indices de performance et méthodes d'optimisation importants pour MySQL. L'éditeur le trouve plutôt bon, je vais donc le partager avec vous maintenant et le donner comme référence pour tout le monde. Suivons l'éditeur pour jeter un œil

1 calcul QPS (requêtes par seconde)

Pour une base de données basée sur le moteur MyISAM

MySQL> show GLOBAL status like 'questions';
+---------------+------------+
| Variable_name | Value   |
+---------------+------------+
| Questions   | 2009191409 |
+---------------+------------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime    | 388402 |
+---------------+--------+
1 row in set (0.00 sec)

QPS=questions/uptime=5172, le QPS moyen de mysql depuis son démarrage Si vous souhaitez calculer le QPS sur une certaine période de temps, vous pouvez obtenir l'intervalle t2-t1 pendant la période de pointe, et puis calculez t2 séparément et la valeur q au temps t1, QPS=(q2-q1)/(t2-t1)

Pour la base de données basée sur le moteur InnnoDB

mysql> show global status like 'com_update';
+---------------+----------+
| Variable_name | Value  |
+---------------+----------+
| Com_update  | 87094306 |
+---------------+----------+
1 row in set (0.00 sec)

mysql> show global status like 'com_select';
+---------------+------------+
| Variable_name | Value   |
+---------------+------------+
| Com_select  | 1108143397 |
+---------------+------------+
1 row in set (0.00 sec)


mysql> show global status like 'com_delete';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Com_delete  | 379058 |
+---------------+--------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';

+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime    | 388816 |
+---------------+--------+
1 row in set (0.00 sec)

QPS=( com_update+com_insert+com_delete +com_select)/uptime=3076, la méthode de requête QPS dans une certaine période est la même que ci-dessus.

2 Calcul TPS (transactions par seconde)

mysql> show global status like 'com_commit';

+---------------+---------+
| Variable_name | Value  |
+---------------+---------+
| Com_commit  | 7424815 |
+---------------+---------+
1 row in set (0.00 sec)

mysql> show global status like 'com_rollback';
+---------------+---------+
| Variable_name | Value  |
+---------------+---------+
| Com_rollback | 1073179 |
+---------------+---------+
1 row in set (0.00 sec)

mysql> show global status like 'uptime';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Uptime    | 389467 |
+---------------+--------+
1 row in set (0.00 sec)

TPS=(com_commit+com_rollback)/uptime=22

3 Numéro de connexion du fil et taux de réussite

mysql> show global status like 'threads_%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| Threads_cached  | 480  |   //代表当前此时此刻线程缓存中有多少空闲线程
| Threads_connected | 153  |  //代表当前已建立连接的数量，因为一个连接就需要一个线程，所以也可以看成当前被使用的线程数
| Threads_created  | 20344 |  //代表从最近一次服务启动，已创建线程的数量
| Threads_running  | 2   |   //代表当前激活的（非睡眠状态）线程数
+-------------------+-------+
4 rows in set (0.00 sec)

mysql> show global status like 'Connections';
+---------------+-----------+
| Variable_name | Value   |
+---------------+-----------+
| Connections  | 381487397 |
+---------------+-----------+
1 row in set (0.00 sec)

线程缓存命中率=1-Threads_created/Connections  = 99.994%

我们设置的线程缓存个数

mysql> show variables like '%thread_cache_size%';
+-------------------+-------+
| Variable_name   | Value |
+-------------------+-------+
| thread_cache_size | 500  |
+-------------------+-------+
1 row in set (0.00 sec)

Selon Threads_connected, il est possible d'estimer la taille de la valeur thread_cache_size. De manière générale, 250 est une bonne limite supérieure. Si la mémoire est suffisamment grande, elle peut également être définie. être défini sur la valeur thread_cache_size et threads_connected. Les valeurs sont les mêmes

Ou en observant la valeur threads_created, si la valeur est grande ou continue de croître, vous pouvez augmenter la valeur de thread_cache_size de manière appropriée ; En état de veille, chaque thread occupe environ 256 Ko de mémoire, donc lorsque la mémoire est suffisante, une configuration trop petite n'économisera pas beaucoup de mémoire à moins que la valeur ne dépasse quelques milliers.

Cache de 4 tables

mysql> show global status like 'open_tables%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Open_tables  | 2228 |
+---------------+-------+
1 row in set (0.00 sec)

Le cache des tables ouvertes et le cache de définition de table que nous avons mis en place

mysql> show variables like 'table_open_cache';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| table_open_cache | 16384 |
+------------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'table_defi%';
+------------------------+-------+
| Variable_name     | Value |
+------------------------+-------+
| table_definition_cache | 2000 |
+------------------------+-------+
1 row in set (0.00 sec)

Pour MyISAM :

Chaque fois que mysql ouvre une table, il lira certaines données dans le cache table_open_cache. Lorsque mysql ne trouve pas les informations correspondantes dans ce cache, , il sera lu directement à partir du disque, la valeur doit donc être suffisamment grande pour éviter d'avoir à rouvrir et réanalyser la définition de la table. Elle est généralement définie sur 10 fois max_connections, mais il est préférable de la conserver dans les limites de 10 000. .

Une autre base consiste à définir en fonction de la valeur du statut open_tables. Si vous constatez que la valeur de open_tables change considérablement chaque seconde, vous devrez peut-être augmenter la valeur de table_open_cache.

table_definition_cache est généralement simplement défini sur le nombre de tables qui existent sur le serveur, à moins qu'il n'y ait des dizaines de milliers de tables.

Pour InnoDB :

Contrairement à MyISAM, la table ouverte et le fichier ouvert d'InnoDB ne sont pas directement liés, c'est-à-dire lorsque la table frm est ouverte , ils correspondent Le fichier ibd peut être fermé

Par conséquent, InnoDB n'utilisera que table_definiton_cache et n'utilisera pas table_open_cache

Le fichier frm est enregistré dans table_definition_cache et l'idb est déterminé par innodb_open_files ; (à condition que innodb_file_per_table soit activé), il est préférable de définir innodb_open_files suffisamment grand pour que le serveur puisse garder tous les fichiers .ibd ouverts en même temps.

5 Nombre maximum de connexions

mysql> show global status like 'Max_used_connections';
+----------------------+-------+
| Variable_name    | Value |
+----------------------+-------+
| Max_used_connections | 1785 |
+----------------------+-------+
1 row in set (0.00 sec)

La taille max_connections que nous définissons

mysql> show variables like 'max_connections%';
+-----------------+-------+
| Variable_name  | Value |
+-----------------+-------+
| max_connections | 4000 |
+-----------------+-------+
1 row in set (0.00 sec)

Généralement la taille de max_connections doit être défini pour être supérieur à la valeur d'état Max_used_connections. La valeur d'état Max_used_connections indique s'il y a des pics de connexions au serveur pendant une certaine période de temps. Si la valeur est supérieure à la valeur max_connections, cela signifie que le client a été rejeté. au moins une fois. Il peut simplement être configuré pour répondre aux conditions suivantes : Max_used_connections/ max_connections=0.8

6 Taux de réussite du cache Innodb

mysql> show global status like 'innodb_buffer_pool_read%';
+---------------------------------------+--------------+
| Variable_name             | Value    |
+---------------------------------------+--------------+
| Innodb_buffer_pool_read_ahead_rnd   | 0      |
| Innodb_buffer_pool_read_ahead     | 268720    |   //预读的页数
| Innodb_buffer_pool_read_ahead_evicted | 0      |   
| Innodb_buffer_pool_read_requests   | 480291074970 | //从缓冲池中读取的次数
| Innodb_buffer_pool_reads       | 29912739   |     //表示从物理磁盘读取的页数
+---------------------------------------+--------------+
5 rows in set (0.00 sec)

Taux de réussite du pool de tampons = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads )=99,994%

Si la valeur est inférieure à 99,9%, il est recommandé d'augmenter la valeur de innodb_buffer_ pool_size. Cette valeur est généralement réglé à 75% -85% de la taille totale de la mémoire, ou le cache requis par le système d'exploitation est calculé +La mémoire requise pour chaque connexion mysql (comme le tampon de tri et la table temporaire) +Cache de clé MyISAM, la mémoire restante est. donné à innodb_buffer_pool_size, mais il ne doit pas être trop grand, ce qui entraînerait des échanges de mémoire fréquents, de longs temps de préchauffage et d'arrêt, etc.

7 Taux de réussite du tampon de clé MyISAM et taux d'utilisation du tampon

mysql> show global status like 'key_%';
+------------------------+-----------+
| Variable_name     | Value   |
+------------------------+-----------+
| Key_blocks_not_flushed | 0     |
| Key_blocks_unused   | 106662  |
| Key_blocks_used    | 107171  |
| Key_read_requests   | 883825678 |
| Key_reads       | 133294  |
| Key_write_requests   | 217310758 |
| Key_writes       | 2061054  |
+------------------------+-----------+
7 rows in set (0.00 sec)

mysql> show variables like '%key_cache_block_size%';
+----------------------+-------+
| Variable_name    | Value |
+----------------------+-------+
| key_cache_block_size | 1024 |
+----------------------+-------+
1 row in set (0.00 sec)


mysql> show variables like '%key_buffer_size%';
+-----------------+-----------+
| Variable_name  | Value   |
+-----------------+-----------+
| key_buffer_size | 134217728 |
+-----------------+-----------+
1 row in set (0.00 sec)

Taux d'utilisation du tampon = 1- (Key_blocks_unused*key_cache_block_size /key_buffer_size) =18,6%

Taux de réussite en lecture=1-Key_reads /Key_read_requests=99,98%

Taux de réussite en écriture=1-Key_writes / Key_write_requests =99,05%

Visible Le taux d'utilisation de le tampon n'est pas élevé. Si tous les tampons de clés ne sont pas utilisés après une longue période, vous pouvez envisager de réduire la taille du tampon.

Le taux de réussite du cache de clé peut ne pas signifier grand-chose car il est lié à l'application. Certaines applications fonctionnent bien avec un taux de réussite de 95 %, et certaines nécessitent 99,99 %, donc par expérience, le taux de cache par seconde est le nombre. Le nombre d'échecs est plus important en supposant qu'un disque indépendant peut effectuer 100 lectures aléatoires par seconde, alors 5 échecs de tampon par seconde peuvent ne pas entraîner d'occupation d'E/S, mais 80 par seconde peuvent causer des problèmes.

Cache manqué par seconde=Key_reads/uptime=0.33

8 临时表使用情况

mysql> show global status like 'Created_tmp%';
+-------------------------+----------+
| Variable_name      | Value  |
+-------------------------+----------+
| Created_tmp_disk_tables | 19226325 |
| Created_tmp_files    | 117   |
| Created_tmp_tables   | 56265812 |
+-------------------------+----------+
3 rows in set (0.00 sec)

mysql> show variables like '%tmp_table_size%';
+----------------+----------+
| Variable_name | Value  |
+----------------+----------+
| tmp_table_size | 67108864 |
+----------------+----------+
1 row in set (0.00 sec)

可看到总共创建了56265812 张临时表，其中有19226325 张涉及到了磁盘IO，大概比例占到了0.34，证明数据库应用中排序，join语句涉及的数据量太大，需要优化SQL或者增大tmp_table_size的值，我设的是64M。该比值应该控制在0.2以内。

9 binlog cache使用情况

mysql> show status like 'Binlog_cache%'; 
+-----------------------+----------+
| Variable_name     | Value  |
+-----------------------+----------+
| Binlog_cache_disk_use | 15    |
| Binlog_cache_use   | 95978256 |
+-----------------------+----------+
2 rows in set (0.00 sec)

mysql> show variables like 'binlog_cache_size';
+-------------------+---------+
| Variable_name   | Value  |
+-------------------+---------+
| binlog_cache_size | 1048576 |
+-------------------+---------+
1 row in set (0.00 sec)

Binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数

Binlog_cache_use 表示 用binlog_cache_size缓存的次数

当对应的Binlog_cache_disk_use 值比较大的时候 我们可以考虑适当的调高 binlog_cache_size 对应的值

10 Innodb log buffer size的大小设置

mysql> show variables like '%innodb_log_buffer_size%';
+------------------------+---------+
| Variable_name     | Value  |
+------------------------+---------+
| innodb_log_buffer_size | 8388608 |
+------------------------+---------+
1 row in set (0.00 sec)
mysql> show status like 'innodb_log_waits';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| Innodb_log_waits | 0   |
+------------------+-------+
1 row in set (0.00 sec)

innodb_log_buffer_size我设置了8M，应该足够大了；Innodb_log_waits表示因log buffer不足导致等待的次数，如果该值不为0，可以适当增大innodb_log_buffer_size的值。

11 表扫描情况判断

mysql> show global status like 'Handler_read%';
+-----------------------+--------------+
| Variable_name     | Value    |
+-----------------------+--------------+
| Handler_read_first  | 19180695   |
| Handler_read_key   | 30303690598 |
| Handler_read_last   | 290721    |
| Handler_read_next   | 51169834260 |
| Handler_read_prev   | 1267528402  |
| Handler_read_rnd   | 219230406  |
| Handler_read_rnd_next | 344713226172 |
+-----------------------+--------------+
7 rows in set (0.00 sec)

Handler_read_first：使用索引扫描的次数，该值大小说不清系统性能是好是坏

Handler_read_key：通过key进行查询的次数，该值越大证明系统性能越好

Handler_read_next：使用索引进行排序的次数
Handler_read_prev：此选项表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY ... DESC

Handler_read_rnd：该值越大证明系统中有大量的没有使用索引进行排序的操作，或者join时没有使用到index

Handler_read_rnd_next：使用数据文件进行扫描的次数，该值越大证明有大量的全表扫描，或者合理地创建索引，没有很好地利用已经建立好的索引

12 Innodb_buffer_pool_wait_free

mysql> show global status like 'Innodb_buffer_pool_wait_free';
+------------------------------+-------+
| Variable_name        | Value |
+------------------------------+-------+
| Innodb_buffer_pool_wait_free | 0   |
+------------------------------+-------+
1 row in set (0.00 sec)

该值不为0表示buffer pool没有空闲的空间了，可能原因是innodb_buffer_pool_size设置太大，可以适当减少该值。

13 join操作信息

mysql> show global status like 'select_full_join';
+------------------+-------+
| Variable_name  | Value |
+------------------+-------+
| Select_full_join | 10403 |
+------------------+-------+
1 row in set (0.00 sec)

该值表示在join操作中没有使用到索引的次数，值很大说明join语句写得很有问题

mysql> show global status like 'select_range';
+---------------+----------+
| Variable_name | Value  |
+---------------+----------+
| Select_range | 22450380 |
+---------------+----------+
1 row in set (0.00 sec)

该值表示第一个表使用ranges的join数量，该值很大说明join写得没有问题，通常可查看select_full_join和select_range的比值来判断系统中join语句的性能情况

mysql> show global status like 'Select_range_check';
+--------------------+-------+
| Variable_name   | Value |
+--------------------+-------+
| Select_range_check | 0   |
+--------------------+-------+
1 row in set (0.00 sec)

如果该值不为0需要检查表的索引是否合理，表示在表n+1中重新评估表n中的每一行的索引是否开销最小所做的联接数，意味着表n+1对该联接而言并没有有用的索引。

mysql> show GLOBAL status like 'select_scan';
+---------------+-----------+
| Variable_name | Value   |
+---------------+-----------+
| Select_scan  | 116037811 |
+---------------+-----------+
1 row in set (0.00 sec)

select_scan表示扫描第一张表的连接数目，如果第一张表中每行都参与联接，这样的结果并没有问题；如果你并不想要返回所有行但又没有使用到索引来查找到所需要的行，那么计数很大就很糟糕了。

14 慢查询

mysql> show global status like 'Slow_queries';
+---------------+--------+
| Variable_name | Value |
+---------------+--------+
| Slow_queries | 114111 |
+---------------+--------+
1 row in set (0.00 sec)

该值表示mysql启动以来的慢查询个数，即执行时间超过long_query_time的次数，可根据Slow_queries/uptime的比值判断单位时间内的慢查询个数，进而判断系统的性能。

15表锁信息

mysql> show global status like 'table_lock%';
+-----------------------+------------+
| Variable_name     | Value   |
+-----------------------+------------+
| Table_locks_immediate | 1644917567 |
| Table_locks_waited  | 53     |
+-----------------------+------------+
2 rows in set (0.00 sec)

这两个值的比值：Table_locks_waited /Table_locks_immediate 趋向于0，如果值比较大则表示系统的锁阻塞情况比较严重

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!