Codezusammenfassung mehrerer wichtiger Methoden zur Berechnung und Optimierung des MySQL-Leistungsindex

Der folgende Editor bietet Ihnen eine Zusammenfassung mehrerer wichtiger Leistungsindexberechnungen und Optimierungsmethoden für MySQL. Der Herausgeber findet es ziemlich gut, deshalb werde ich es jetzt mit Ihnen teilen und es allen als Referenz geben. Folgen wir dem Editor, um einen Blick darauf zu werfen

1 QPS-Berechnung (Abfragen pro Sekunde)

Für DB basierend auf der MyISAM-Engine

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, der durchschnittliche QPS von MySQL seit dem Start. Wenn Sie den QPS in einem bestimmten Zeitraum berechnen möchten, können Sie das Intervall t2-t1 während der Spitzenzeit erhalten Berechnen Sie dann t2 separat und den q-Wert zum Zeitpunkt t1, QPS=(q2-q1)/(t2-t1)

Für DB basierend auf der InnnoDB-Engine

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, die QPS-Abfragemethode innerhalb eines bestimmten Zeitraums ist dieselbe wie oben.

2 TPS-Berechnung (Transaktionen pro Sekunde)

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 Thread-Verbindungsnummer und Trefferquote

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)

Laut Threads_connected lässt sich abschätzen, wie groß der Wert thread_cache_size eingestellt werden sollte. Im Allgemeinen ist 250 eine gute Obergrenze. Wenn der Speicher groß genug ist, kann dies auch der Fall sein auf den Wert thread_cache_size und threads_connected eingestellt werden.

Wenn der Wert groß ist oder weiter wächst, können Sie den Wert von thread_cache_size entsprechend erhöhen Im Ruhezustand belegt jeder Thread etwa 256 KB Speicher. Wenn der Speicher also ausreicht, wird durch eine zu kleine Einstellung nicht viel Speicher gespart, es sei denn, der Wert überschreitet einige Tausend.

4-Tabellen-Cache

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

Der Cache für offene Tabellen und der Tabellendefinitions-Cache, den wir eingerichtet haben

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)

Für MyISAM:

Jedes Mal, wenn MySQL eine Tabelle öffnet, werden einige Daten in den Table_open_cache-Cache gelesen. Wenn MySQL die entsprechenden Informationen in diesem Cache nicht finden kann, , wird es direkt von der Festplatte gelesen, daher sollte der Wert groß genug eingestellt werden, um die Notwendigkeit zu vermeiden, die Tabellendefinition erneut zu öffnen und zu analysieren. Er wird im Allgemeinen auf das 10-fache von max_connections eingestellt, es ist jedoch am besten, ihn innerhalb von 10.000 zu halten .

Eine weitere Grundlage besteht darin, den Wert von open_tables entsprechend dem Wert festzulegen. Wenn Sie feststellen, dass sich der Wert von open_tables jede Sekunde stark ändert, müssen Sie möglicherweise den Wert von table_open_cache erhöhen.

table_definition_cache wird normalerweise einfach auf die Anzahl der Tabellen gesetzt, die auf dem Server vorhanden sind, es sei denn, es sind Zehntausende Tabellen vorhanden.

Für InnoDB:

Im Gegensatz zu MyISAM stehen die geöffnete Tabelle und die geöffnete Datei von InnoDB nicht in direktem Zusammenhang, dh wenn die frm-Tabelle geöffnet wird , sie entsprechen Die ibd-Datei ist möglicherweise geschlossen.

Daher verwendet InnoDB nur table_definiton_cache und nicht table_open_cache.

Die FRM-Datei wird in table_definition_cache gespeichert und die IDB wird durch innodb_open_files bestimmt (vorausgesetzt, bei aktivierter innodb_file_per_table) ist es am besten, innodb_open_files groß genug einzustellen, damit der Server alle .ibd-Dateien gleichzeitig geöffnet halten kann.

5 Maximale Anzahl von Verbindungen

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

Die von uns festgelegte max_connections-Größe

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

Normalerweise die Größe von max_connections sollte größer als der Statuswert „Max_used_connections“ sein. Der Statuswert „Max_used_connections“ gibt an, ob es während eines bestimmten Zeitraums Spitzen bei den Serververbindungen gibt. Wenn der Wert größer als der Wert „max_connections“ ist, bedeutet dies, dass der Client abgelehnt wurde mindestens einmal. Es kann einfach so eingestellt werden, dass es die folgenden Bedingungen erfüllt: Max_used_connections/ max_connections=0,8

6 Innodb-Cache-Trefferrate

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)

Pufferpool-Trefferrate = (Innodb_buffer_pool_read_requests)/(Innodb_buffer_pool_read_requests + Innodb_buffer_pool_read_ahead + Innodb_buffer_pool_reads) = 99,994 %

Wenn der Wert weniger als 99,9 % beträgt, wird empfohlen, den Wert von innodb_buffer_ zu erhöhen pool_size. Dieser Wert ist im Allgemeinen auf 75 % bis 85 % der Gesamtspeichergröße eingestellt, oder der vom Betriebssystem benötigte Cache wird berechnet +Der für jede MySQL-Verbindung erforderliche Speicher (z. B. Sortierpuffer und temporäre Tabelle) +MyISAM-Schlüsselcache, der verbleibende Speicher Wird an innodb_buffer_pool_size übergeben, sollte jedoch nicht zu groß eingestellt werden, da dies zu häufigem Speicheraustausch, langen Aufwärm- und Abschaltzeiten usw. führen kann.

7 MyISAM-Schlüsselpuffer-Trefferrate und Puffernutzungsrate

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)

Puffernutzungsrate=1-(Key_blocks_unused*key_cache_block_size /key_buffer_size) =18,6 %

Lesetrefferrate=1-Key_reads /Key_read_requests=99,98 %

Schreibtrefferrate =1-Key_writes / Key_write_requests =99,05 %

Sichtbar Die Nutzungsrate von Der Puffer ist nicht hoch. Wenn nach längerer Zeit nicht alle Schlüsselpuffer aufgebraucht sind, können Sie eine Reduzierung der Puffergröße in Betracht ziehen.

Die Schlüssel-Cache-Trefferquote hat möglicherweise keine große Bedeutung, da sie anwendungsbezogen ist. Einige Anwendungen funktionieren gut mit einer Trefferquote von 95 %, andere erfordern 99,99 %, sodass die Cache-Rate pro Sekunde erfahrungsgemäß ist Unter der Annahme, dass ein unabhängiger Datenträger 100 zufällige Lesevorgänge pro Sekunde ausführen kann, sind 5 Pufferfehler pro Sekunde möglicherweise nicht zu einer E/A-Auslastung, 80 Fehler pro Sekunde können jedoch zu Problemen führen.

Cache-Fehler pro Sekunde=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，如果值比较大则表示系统的锁阻塞情况比较严重

Das obige ist der detaillierte Inhalt vonCodezusammenfassung mehrerer wichtiger Methoden zur Berechnung und Optimierung des MySQL-Leistungsindex. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!