百分百源码网-让建站变得如此简单！ 登录 注册 签到领金币！

主页 | 如何升级VIP | TAG标签

当前位置: 主页>网站教程>数据库> 初识MySQL调优之机能监控
分享文章到：

初识MySQL调优之机能监控

发布时间：12/01 来源：未知 浏览： 关键词：
MySQL优化主要从其逻辑架构的中心层-剖析、优化、施行阶段着手。mysql教程栏目将细致介绍。

mysql教程栏目介绍相识机能监控。

MySQL优化主要从其逻辑架构的中心层-剖析、优化、施行阶段着手。

实验历程中，我是用的MySQL版本是5.7，对于MySQL的迅速安装，可参照 怎样在CentOS7下迅速安装MySQL5.7。

MySQL调优，我们先从机能监控开端，寻点感觉。

运用show profile

show profile是MySQL的查询分析工具。

mysql> show variables like '%profil%';
+------------------------+-------+| Variable_name          | Value |
+------------------------+-------+| have_profiling         | YES   |
| profiling              | OFF   |
| profiling_history_size | 15    |
+------------------------+-------+3 rows in set (0.00 sec)

可以看到此工具默许是OFF（禁用）的，可以通过办事器变量在目前会话级别动态的修改

mysql> set profiling=1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

当设定完成之后，在办事器上施行的所有语句，都会测量其耗费的工夫和其他一些查询施行状态变动相干的数据。

我们来新建一个表来演示一下：

mysql> create database mytest;
Query OK, 1 row affected (0.01 sec)

mysql> use mytest;
Database changed
mysql> create table stu(id int(8), name varchar(20));
Query OK, 0 rows affected (0.07 sec)

mysql> insert into stu(id, name) values(1, 'Jack Ma');
Query OK, 1 row affected (0.01 sec)

mysql> insert into stu(id, name) values(1, 'Pony');
Query OK, 1 row affected (0.03 sec)

查询一下stu表：

mysql> select * from stu;
+------+---------+| id   | name    |
+------+---------+|    1 | Jack Ma |
|    2 | Pony    |
+------+---------+2 rows in set (0.00 sec)

这个2 rows in set (0.00 sec)就是用下令行施行一个sql后给出的提醒，并且还有施行工夫0.00秒。

在mysql的下令行模式下只能显示两位小数的工夫，可以运用如下下令查看具体的施行工夫

mysql> show profile;
+----------------------+----------+| Status               | Duration |
+----------------------+----------+| starting             | 0.000061 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000019 |
| init                 | 0.000020 |
| System lock          | 0.000009 |
| optimizing           | 0.000005 |
| statistics           | 0.000014 |
| preparing            | 0.000011 |
| executing            | 0.000003 |
| Sending data         | 0.000121 |
| end                  | 0.000012 |
| query end            | 0.000011 |
| closing tables       | 0.000011 |
| freeing items        | 0.000021 |
| cleaning up          | 0.000021 |
+----------------------+----------+

这样我们可以清晰地看到，Starting、System lock系统锁、Sending data等等一系列操纵离别用了多少工夫。

通过上面的演示，我们可以看到，show profile一施行完就给我们返回每项的施行工夫，并没有显示是针对哪条sql的，其实它就是列出了比来一条你施行的sql语句的施行工夫。

那一定有可以看以前施行过的多条sql语句的状况吧？是的，show profiles

show profiles

mysql> show profiles;
+----------+------------+------------------------------------------------+| Query_ID | Duration   | Query                                          |
+----------+------------+------------------------------------------------+|        1 | 0.02907350 | create table stu(id int(8), name varchar(20))  |
|        2 | 0.00337800 | create database mytest                         |
|        3 | 0.02786850 | SELECT DATABASE()                              |
|        4 | 0.00065300 | show databases                                 |
|        5 | 0.00086700 | show tables                                    |
|        6 | 0.06554900 | create table stu(id int(8), name varchar(20))  |
|        7 | 0.00079850 | insert into stu(1, 'Jack')                     |
|        8 | 0.06901975 | show create table stu                          |
|        9 | 0.00008800 | insert into stu(1, 'Jack')                     |
|       10 | 0.00616000 | insert into stu(id, name) values(1, 'Jack Ma') |
|       11 | 0.03119675 | insert into stu(id, name) values(1, 'Pony')    |
|       12 | 0.03124900 | update stu set id=2 where name='Pony'          |
|       13 | 0.00036975 | select * from stu                              |
|       14 | 0.00034925 | select * from stu                              |
+----------+------------+------------------------------------------------+14 rows in set, 1 warning (0.00 sec)

列出了施行过的sql语句施行工夫，我们可以挑一个查询来独自看，比方我想看insert into stu(id, name) values(1, 'Pony')这条语句的状况，可以用下面的操纵：

mysql> show profile for query 11;
+----------------------+----------+
| Status               | Duration |
+----------------------+----------+
| starting             | 0.000072 |
| checking permissions | 0.000009 |
| Opening tables       | 0.000021 |
| init                 | 0.000018 |
| System lock          | 0.000008 |
| update               | 0.000094 |
| end                  | 0.000005 |
| query end            | 0.030815 |
| closing tables       | 0.000036 |
| freeing items        | 0.000081 |
| cleaning up          | 0.000039 |
+----------------------+----------+11 rows in set, 1 warning (0.00 sec)

也就是说可以依据Query_ID联合show profile来看。

MySQL官网上，对于show profile语句：

SHOW PROFILE [type [, type] ... ]
    [FOR QUERY n]
    [LIMIT row_count [OFFSET offset]]

type: {
    ALL
  | BLOCK IO
  | CONTEXT SWITCHES
  | CPU
  | IPC
  | MEMORY
  | PAGE FAULTS
  | SOURCE
  | SWAPS
}

show profile、for query n前面已经说过了，下面我列一下每个type有哪些意思。

all 显示所有机能信息

block io 显示块io操纵的次数

context switches 显示高低文切换次数，被动和自动

cpu 显示会员cpu工夫、系统cpu工夫

IPC 显示发送和承受的新闻数目

Memory 暂未实现

page faults 显示页差错数目

source 显示源码中的函数名称与位置

swaps 显示swap的次数

实操一下看看

mysql> show profiles;
+----------+------------+-------------------------------+| Query_ID | Duration   | Query                         |
+----------+------------+-------------------------------+|        1 | 0.00009625 | show variable like '%profi%'  |
|        2 | 0.00373950 | show variables like '%profi%' |
|        3 | 0.00025825 | select * from stu             |
+----------+------------+-------------------------------+3 rows in set, 1 warning (0.00 sec)

我们针对Query_ID为3的进行查看

  • all
mysql> show profile all for query 3\G
*************************** 1. row ***************************             Status: starting
           Duration: 0.000070
           CPU_user: 0.000000
         CPU_system: 0.000064
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: NULL
        Source_file: NULL
        Source_line: NULL*************************** 2. row ***************************             Status: checking permissions           Duration: 0.000011
           CPU_user: 0.000000
         CPU_system: 0.000009
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: check_access
        Source_file: sql_authorization.cc
        Source_line: 809*************************** 3. row ***************************             Status: Opening tables
           Duration: 0.000021
           CPU_user: 0.000000
         CPU_system: 0.000021
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: open_tables
        Source_file: sql_base.cc
        Source_line: 5793*************************** 4. row ***************************             Status: init           Duration: 0.000019
           CPU_user: 0.000000
         CPU_system: 0.000019
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: handle_query
        Source_file: sql_select.cc
        Source_line: 128*************************** 5. row ***************************             Status: System lock
           Duration: 0.000009
           CPU_user: 0.000000
         CPU_system: 0.000009
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: mysql_lock_tables
        Source_file: lock.cc
        Source_line: 330*************************** 6. row ***************************             Status: optimizing           Duration: 0.000005
           CPU_user: 0.000000
         CPU_system: 0.000004
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: optimize
        Source_file: sql_optimizer.cc
        Source_line: 158*************************** 7. row ***************************             Status: statistics
           Duration: 0.000014
           CPU_user: 0.000000
         CPU_system: 0.000014
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: optimize
        Source_file: sql_optimizer.cc
        Source_line: 374*************************** 8. row ***************************             Status: preparing           Duration: 0.000011
           CPU_user: 0.000000
         CPU_system: 0.000010
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: optimize
        Source_file: sql_optimizer.cc
        Source_line: 482*************************** 9. row ***************************             Status: executing           Duration: 0.000004
           CPU_user: 0.000000
         CPU_system: 0.000004
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: exec
        Source_file: sql_executor.cc
        Source_line: 126*************************** 10. row ***************************             Status: Sending data
           Duration: 0.000050
           CPU_user: 0.000000
         CPU_system: 0.000050
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: exec
        Source_file: sql_executor.cc
        Source_line: 202*************************** 11. row ***************************             Status: end
           Duration: 0.000005
           CPU_user: 0.000000
         CPU_system: 0.000005
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: handle_query
        Source_file: sql_select.cc
        Source_line: 206*************************** 12. row ***************************             Status: query end
           Duration: 0.000008
           CPU_user: 0.000000
         CPU_system: 0.000008
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: mysql_execute_command
        Source_file: sql_parse.cc
        Source_line: 4956*************************** 13. row ***************************             Status: closing tables
           Duration: 0.000008
           CPU_user: 0.000000
         CPU_system: 0.000008
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: mysql_execute_command
        Source_file: sql_parse.cc
        Source_line: 5009*************************** 14. row ***************************             Status: freeing items           Duration: 0.000013
           CPU_user: 0.000000
         CPU_system: 0.000013
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: mysql_parse
        Source_file: sql_parse.cc
        Source_line: 5622*************************** 15. row ***************************             Status: cleaning up           Duration: 0.000014
           CPU_user: 0.000000
         CPU_system: 0.000013
  Context_voluntary: 0Context_involuntary: 0
       Block_ops_in: 0
      Block_ops_out: 0
      Messages_sent: 0
  Messages_received: 0
  Page_faults_major: 0
  Page_faults_minor: 0
              Swaps: 0
    Source_function: dispatch_command
        Source_file: sql_parse.cc
        Source_line: 193115 rows in set, 1 warning (0.00 sec)

查询最后的\G的目的是格局化显示一下查询效果。

这里我们可以看到，show profile all可以看到所有的机能信息。

  • block io
mysql> show profile block io for query 3;
+----------------------+----------+--------------+---------------+| Status               | Duration | Block_ops_in | Block_ops_out |
+----------------------+----------+--------------+---------------+| starting             | 0.000070 |            0 |             0 |
| checking permissions | 0.000011 |            0 |             0 |
| Opening tables       | 0.000021 |            0 |             0 |
| init                 | 0.000019 |            0 |             0 |
| System lock          | 0.000009 |            0 |             0 |
| optimizing           | 0.000005 |            0 |             0 |
| statistics           | 0.000014 |            0 |             0 |
| preparing            | 0.000011 |            0 |             0 |
| executing            | 0.000004 |            0 |             0 |
| Sending data         | 0.000050 |            0 |             0 |
| end                  | 0.000005 |            0 |             0 |
| query end            | 0.000008 |            0 |             0 |
| closing tables       | 0.000008 |            0 |             0 |
| freeing items        | 0.000013 |            0 |             0 |
| cleaning up          | 0.000014 |            0 |             0 |
+----------------------+----------+--------------+---------------+15 rows in set, 1 warning (0.00 sec)

最后两列就表示块io操纵的次数。

下面只把各个type的语法列出来，演示结果不在此赘述了。

  • show profile context switches for query 3
  • show profile cpu for query 3
  • show profile ipc for query 3
  • show profile page faults for query 3
  • show profile source for query 3
  • show profile swaps for query 3

show profile用起来感觉很爽啊，他能帮我们看到sql语句各局部的施行工夫，这对我们定位线上sql施行慢的题目很有帮忙啊，but，官网上有这么一段话：

Note

The SHOW PROFILE and SHOW PROFILES statements are deprecated and will be removed in a future MySQL release. Use the Performance Schema instead;

注意

不举荐运用SHOW PROFILE和SHOW PROFILES语句，并将在今后的MySQL版本中将其删除。

改用Performance Schema；

好吧，你说删除就删除。但是在MySQL删除show profile以前，我们还是可以用的，究竟很香嘛。

既然后续版本会用Performance Schema，那么我们还看下它是何方神圣吧！

Performance Schema

MySQL的performance schema用于监控MySQL server在一个较初级另外运转历程中的资源耗损、资源期待等状况。运用performance schema可以更加容易的监控MySQL。

在MySQL的5.7版本中，机能模式是默许开启的，要是想要显式的关闭的话需要修改配置文件，不克不及直接进行修改，会报错Variable 'performance_schema' is a read only variable。

mysql> show variables like 'performance_schema';
+--------------------+-------+| Variable_name      | Value |
+--------------------+-------+| performance_schema | ON    |
+--------------------+-------+mysql> set performance_schema=0;
ERROR 1238 (HY000): Variable 'performance_schema' is a read only variable

在配置文件中修改performance_schema的属性值，on表示开启，off表示关闭

[mysqld]
performance_schema=ON

看下performance_schema数据库是什么功能表:

mysql> use performance_schema;
mysql> show tables;

一共有87张表，离别存储着相干的信息。

可以通过show create table tablename来查看新建表的时候的表构造：

mysql> show create table threads\G
*************************** 1. row ***************************       Table: threadsCreate Table: CREATE TABLE `threads` (  `THREAD_ID` bigint(20) unsigned NOT NULL,  `NAME` varchar(128) NOT NULL,  `TYPE` varchar(10) NOT NULL,  `PROCESSLIST_ID` bigint(20) unsigned DEFAULT NULL,  `PROCESSLIST_USER` varchar(32) DEFAULT NULL,  `PROCESSLIST_HOST` varchar(60) DEFAULT NULL,  `PROCESSLIST_DB` varchar(64) DEFAULT NULL,  `PROCESSLIST_COMMAND` varchar(16) DEFAULT NULL,  `PROCESSLIST_TIME` bigint(20) DEFAULT NULL,  `PROCESSLIST_STATE` varchar(64) DEFAULT NULL,  `PROCESSLIST_INFO` longtext,  `PARENT_THREAD_ID` bigint(20) unsigned DEFAULT NULL,  `ROLE` varchar(64) DEFAULT NULL,  `INSTRUMENTED` enum('YES','NO') NOT NULL,  `HISTORY` enum('YES','NO') NOT NULL,  `CONNECTION_TYPE` varchar(16) DEFAULT NULL,  `THREAD_OS_ID` bigint(20) unsigned DEFAULT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf81 row in set (0.00 sec)

我一看到threads这个表名，还有THREAD_ID、PROCESSLIST_ID等等字段，就感觉这些比拼重要啊有木有！

再来看一下setup_consumers这个表：

mysql> show create table setup_consumers;
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| Table           | Create Table                                                                                                                                           |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+| setup_consumers | CREATE TABLE `setup_consumers` (  `NAME` varchar(64) NOT NULL,  `ENABLED` enum('YES','NO') NOT NULL) ENGINE=PERFORMANCE_SCHEMA DEFAULT CHARSET=utf8 |
+-----------------+--------------------------------------------------------------------------------------------------------------------------------------------------------+1 row in set (0.00 sec)

这个consumer总感觉似曾认识啊！

其实，想要搞明确后续的内容，我们需要了解两个根本概念：

  • instruments 生产者，用于采集mysql中许许多多的操纵发生的事件信息，对应配置表中的配置项我们可以称为监控采集配置项。
  • consumers 消费者，对应的消费者表用于存储来自instruments采集的数据，对应配置表中的配置项我们可以称为消费存储配置项。

performance_schema表的分类

performance_schema库下的表可以按照监视不一样的纬度就行分组。

--语句事件记载表，这些表记载了语句事件信息，目前语句事件表events_statements_current、历史语句事件表events_statements_history和长语句历史事件表events_statements_history_long、以及聚合后的摘要表summary，其中，summary表还可以依据帐号(account)，主机(host)，程序(program)，线程(thread)，会员(user)和全局(global)再进行细分)show tables like '%statement%';--期待事件记载表，与语句事件类型的相干记载表相似：show tables like '%wait%';--阶段事件记载表，记载语句施行的阶段事件的表show tables like '%stage%';--事务事件记载表，记载事务相干的事件的表show tables like '%transaction%';--监控文件系统层调取的表show tables like '%file%';--监视内存运用的表show tables like '%memory%';--动态对performance_schema进行配置的配置表show tables like '%setup%';

来，那一个事务相干的来瞅一眼：

mysql> show tables like '%transaction%';
+------------------------------------------------------+| Tables_in_performance_schema (%transaction%)         |
+------------------------------------------------------+| events_transactions_current                          |
| events_transactions_history                          |
| events_transactions_history_long                     |
| events_transactions_summary_by_account_by_event_name |
| events_transactions_summary_by_host_by_event_name    |
| events_transactions_summary_by_thread_by_event_name  |
| events_transactions_summary_by_user_by_event_name    |
| events_transactions_summary_global_by_event_name     |
+------------------------------------------------------+8 rows in set (0.00 sec)

performance_schema的简略配置与运用

数据库刚刚初始化并启动时，并非所有instruments(事件采集项，在采集项的配置表中每一项都有一个开关字段，或为YES，或为NO)和consumers(与采集项相似，也有一个对应的事件类型保留表配置项，为YES就表示对应的表保留机能数据，为NO就表示对应的表不保留机能数据)都启用了，所以默许不会收集所有的事件。

可能你需要检测的事件并没有打开，需要进行设定，可以运用如下两个语句打开对应的instruments和consumers（行计数可能会因MySQL版本而异)。

打开期待事件的采集器配置项开关，需要修改setup_instruments配置表中对应的采集器配置项

mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';
Query OK, 267 rows affected (0.03 sec)
Rows matched: 321  Changed: 267  Warnings: 0

打开期待事件的保留表配置开关，修改setup_consumers配置表中对应的配置项

mysql> UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%';
Query OK, 3 rows affected (0.04 sec)
Rows matched: 3  Changed: 3  Warnings: 0

当配置完成之后可以查看目前server正在做什么。

  1. 可以通过查询events_waits_current表来得知，该表中每个线程只包括一行数据，用于显示每个线程的最新监视事件：

释义：

该信息表示线程id为3的线程正在期待buf_dblwr_mutex锁，期待工夫为69917。
属性注明：
	id:事件来自哪个线程，事件编号是多少
	event_name:表示检测到的具体的内容
	source:表示这个检测代码在哪个源文件中以及行号
	timer_start:表示该事件的开端工夫
	timer_end:表示该事件的完毕工夫
	timer_wait:表示该事件总的破费工夫
注意：_current表中每个线程只保存一笔记录，一旦线程完成工作，该表中不会再记载该线程的事件信息。
  1. _history表中记载每个线程应当施行完成的事件信息，但每个线程的事件信息只会记载10条，再多就会被遮盖，（下面的演示可以看到每个线程只要10笔记录）。_history_long表中记载所有线程的事件信息，但总记载数目是10000，超过就会被遮盖掉
mysql> mysql> select thread_id,event_id,event_name,timer_wait from events_waits_history order by thread_id limit 21;
+-----------+----------+------------------------------------------+------------+| thread_id | event_id | event_name                               | timer_wait |
+-----------+----------+------------------------------------------+------------+|         3 |     4042 | wait/synch/mutex/innodb/buf_pool_mutex   |      47785 |
|         3 |     4043 | wait/synch/mutex/innodb/flush_list_mutex |      43761 |
|         3 |     4044 | wait/synch/mutex/innodb/buf_pool_mutex   |      58851 |
|         3 |     4045 | wait/synch/mutex/innodb/buf_dblwr_mutex  |      73438 |
|         3 |     4046 | wait/synch/mutex/innodb/buf_pool_mutex   |     222829 |
|         3 |     4047 | wait/synch/mutex/innodb/buf_pool_mutex   |      42755 |
|         3 |     4048 | wait/synch/mutex/innodb/flush_list_mutex |      44767 |
|         3 |     4049 | wait/synch/mutex/innodb/buf_pool_mutex   |      59857 |
|         3 |     4050 | wait/synch/mutex/innodb/buf_dblwr_mutex  |      72432 |
|         3 |     4041 | wait/synch/mutex/innodb/buf_pool_mutex   |     243452 |
|        17 |     2442 | wait/synch/mutex/innodb/sync_array_mutex |      74947 |
|        17 |     2440 | wait/synch/mutex/innodb/sync_array_mutex |      82492 |
|        17 |     2439 | wait/synch/mutex/innodb/sync_array_mutex |     458233 |
|        17 |     2438 | wait/synch/mutex/innodb/log_sys_mutex    |     239428 |
|        17 |     2437 | wait/synch/mutex/innodb/sync_array_mutex |      85510 |
|        17 |     2446 | wait/synch/mutex/innodb/sync_array_mutex |      84504 |
|        17 |     2445 | wait/synch/mutex/innodb/sync_array_mutex |      77462 |
|        17 |     2444 | wait/synch/mutex/innodb/log_sys_mutex    |     238422 |
|        17 |     2443 | wait/synch/mutex/innodb/sync_array_mutex |      83498 |
|        17 |     2441 | wait/synch/mutex/innodb/log_sys_mutex    |     229368 |
|        19 |     7281 | wait/synch/mutex/innodb/flush_list_mutex |      43761 |
+-----------+----------+------------------------------------------+------------+21 rows in set (0.00 sec)
  1. summary表供给所有事件的汇总信息，该组中的表以不一样的方式汇总事件数据（如：按会员，按主机，按线程等等）。

例如：要查看哪些instruments占用最多的工夫，可以通过对events_waits_summary_global_by_event_name表的COUNT_STAR或SUM_TIMER_WAIT列进行查询（这两列是对事件的记载数施行COUNT（*）、事件记载的TIMER_WAIT列施行SUM（TIMER_WAIT）统计而来）

mysql> SELECT EVENT_NAME,COUNT_STAR FROM events_waits_summary_global_by_event_name  ORDER BY COUNT_STAR DESC LIMIT 10;
+-----------------------------------------------+------------+| EVENT_NAME                                    | COUNT_STAR |
+-----------------------------------------------+------------+| wait/synch/mutex/innodb/buf_pool_mutex        |       3357 |
| wait/synch/mutex/innodb/log_sys_mutex         |       3357 |
| wait/synch/mutex/innodb/sync_array_mutex      |       2241 |
| wait/synch/mutex/innodb/flush_list_mutex      |       2238 |
| wait/io/file/sql/FRM                          |       1590 |
| wait/synch/mutex/innodb/log_flush_order_mutex |       1119 |
| wait/synch/mutex/innodb/log_sys_write_mutex   |       1119 |
| wait/synch/mutex/innodb/fil_system_mutex      |       1119 |
| wait/synch/mutex/innodb/dict_sys_mutex        |       1119 |
| wait/synch/mutex/innodb/buf_dblwr_mutex       |       1119 |
+-----------------------------------------------+------------+10 rows in set (0.10 sec)
  1. instance表记载了哪些类型的对象会被检测。这些对象在被server运用时，在该表中将会发生一条事件记载。

例如，file_instances表列出了文件I/O操纵及其关联文件名

mysql> select * from file_instances limit 10;
+----------------------------------------+--------------------------------------+------------+| FILE_NAME                              | EVENT_NAME                           | OPEN_COUNT |
+----------------------------------------+--------------------------------------+------------+| /usr/share/mysql/english/errmsg.sys    | wait/io/file/sql/ERRMSG              |          0 |
| /usr/share/mysql/charsets/Index.xml    | wait/io/file/mysys/charset           |          0 |
| /var/lib/mysql/ibdata1                 | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/ib_logfile0             | wait/io/file/innodb/innodb_log_file  |          2 |
| /var/lib/mysql/ib_logfile1             | wait/io/file/innodb/innodb_log_file  |          2 |
| /var/lib/mysql/mysql/engine_cost.ibd   | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/gtid_executed.ibd | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/help_category.ibd | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/help_keyword.ibd  | wait/io/file/innodb/innodb_data_file |          3 |
| /var/lib/mysql/mysql/help_relation.ibd | wait/io/file/innodb/innodb_data_file |          3 |
+----------------------------------------+--------------------------------------+------------+10 rows in set (0.00 sec)

常用配置项的参数注明

  1. 启动选项
performance_schema_consumer_events_statements_current=TRUE#可否在mysql server启动时就开启events_statements_current表的记载功能(该表记载目前的语句事件信息)，启动之后也可以在setup_consumers表中运用UPDATE语句进动作态更新setup_consumers配置表中的events_statements_current配置项，默许值为TRUEperformance_schema_consumer_events_statements_history=TRUE#与performance_schema_consumer_events_statements_current选项相似，但该选项是用于配置可否记载语句事件短历史信息，默许为TRUEperformance_schema_consumer_events_stages_history_long=FALSE#与performance_schema_consumer_events_statements_current选项相似，但该选项是用于配置可否记载语句事件长历史信息，默许为FALSE除了statement(语句)事件以外，还支撑：wait(期待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件同样都有三个启动项离别进行配置，但这些期待事件默许未启用，要是需要在MySQL Server启动时一同启动，则平常需要写进my.cnf配置文件中
performance_schema_consumer_global_instrumentation=TRUE
可否在MySQL Server启动时就开启全局表（如：mutex_instances、rwlock_instances、cond_instances、file_instances、users、hostsaccounts、socket_summary_by_event_name、file_summary_by_instance等大局部的全局对象计数统计和事件汇总统计信息表 ）的记载功能，启动之后也可以在setup_consumers表中运用UPDATE语句进动作态更新全局配置项
默许值为TRUEperformance_schema_consumer_statements_digest=TRUE可否在MySQL Server启动时就开启events_statements_summary_by_digest 表的记载功能，启动之后也可以在setup_consumers表中运用UPDATE语句进动作态更新digest配置项
默许值为TRUEperformance_schema_consumer_thread_instrumentation=TRUE可否在MySQL Server启动时就开启

events_xxx_summary_by_yyy_by_event_name表的记载功能，启动之后也可以在setup_consumers表中运用UPDATE语句进动作态更新线程配置项
默许值为TRUEperformance_schema_instrument[=name]
可否在MySQL Server启动时就启用某些采集器，因为instruments配置项多达数千个，所以该配置项支撑key-value模式，还支撑%号进行通配等，如下:# [=name]可以指定为具体的Instruments名称（但是这样要是有多个需要指定的时候，就需要运用该选项屡次），也可以运用通配符，可以指定instruments雷同的前缀+通配符，也可以运用%代表所有的instruments## 指定开启单个instruments--performance-schema-instrument= 'instrument_name=value'## 运用通配符指定开启多个instruments--performance-schema-instrument= 'wait/synch/cond/%=COUNTED'## 开关所有的instruments--performance-schema-instrument= '%=ON'--performance-schema-instrument= '%=OFF'注意，这些启动选项要生效的条件是，需要设定performance_schema=ON。别的，这些启动选项虽然没法运用show variables语句查看，但我们可以通过setup_instruments和setup_consumers表查询这些选项指定的值。
  1. 系统变量
show variables like '%performance_schema%';
--重要的属性解释
performance_schema=ON
/*
控制performance_schema功能的开关，要运用MySQL的performance_schema，需要在mysqld启动时启用，以启用事件收集功能
该参数在5.7.x以前支撑performance_schema的版本中默许关闭，5.7.x版本开端默许开启
注意：要是mysqld在初始化performance_schema时发明没法分配任何相干的内部缓冲区，则performance_schema将主动禁用，并将performance_schema设定为OFF
*/

performance_schema_digests_size=10000
/*
控制events_statements_summary_by_digest表中的最大行数。要是发生的语句摘要信息超过此最大值，便没法继续存入该表，此时performance_schema会添加状态变量
*/
performance_schema_events_statements_history_long_size=10000
/*
控制events_statements_history_long表中的最大行数，该参数控制所有会话在events_statements_history_long表中能够寄存的总事件记载数，超过这个限定之后，最早的记载将被遮盖
全局变量，只读变量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其以前的版本默许为10000，5.6.6及其之后的版本默许值为-1，平常状况下，主动盘算的值都是10000 * 5.7.x版本中，默许值为-1，平常状况下，主动盘算的值都是10000
*/
performance_schema_events_statements_history_size=10
/*
控制events_statements_history表中单个线程（会话）的最大行数，该参数控制单个会话在events_statements_history表中能够寄存的事件记载数，超过这个限定之后，单个会话最早的记载将被遮盖
全局变量，只读变量，整型值，5.6.3版本引入 * 5.6.x版本中，5.6.5及其以前的版本默许为10，5.6.6及其之后的版本默许值为-1，平常状况下，主动盘算的值都是10 * 5.7.x版本中，默许值为-1，平常状况下，主动盘算的值都是10
除了statement(语句)事件以外，wait(期待)事件、state(阶段)事件、transaction(事务)事件，他们与statement事件同样都有三个参数离别进行存储限定配置，有乐趣的同窗自行研究，这里不再赘述
*/
performance_schema_max_digest_length=1024
/*
用于控制规范化情势的SQL语句文本在存入performance_schema时的限定长度，该变量与max_digest_length变量相干(max_digest_length变量含义请自行查阅相干材料)
全局变量，只读变量，默许值1024字节，整型值，取值范畴0~1048576
*/
performance_schema_max_sql_text_length=1024
/*
控制存入events_statements_current，events_statements_history和events_statements_history_long语句事件表中的SQL_TEXT列的最大SQL长度字节数。 超出系统变量performance_schema_max_sql_text_length的局部将被丢弃，不会记载，个别状况下不需要调整该参数，除非被截断的局部与其他SQL比起来有很大悬殊
全局变量，只读变量，整型值，默许值为1024字节，取值范畴为0~1048576，5.7.6版本引入
落低系统变量performance_schema_max_sql_text_length值可以减少内存运用，但要是汇总的SQL中，被截断局部有较大悬殊，会致使没有方法再对这些有较大悬殊的SQL进行区分。 添加该系统变量值会添加内存运用，但关于汇总SQL来讲可以更精确地区分不一样的局部。
*/

比拼成心义：performance_schema实践操纵

根本理解了表的相干信息之后，可以通过这些表进行现实的查询操纵来进行现实的剖析。

--1、哪类的SQL施行最多？SELECT DIGEST_TEXT,COUNT_STAR,FIRST_SEEN,LAST_SEEN FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--2、哪类SQL的均匀相应工夫最多？SELECT DIGEST_TEXT,AVG_TIMER_WAIT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--3、哪类SQL排序记载数最多？SELECT DIGEST_TEXT,SUM_SORT_ROWS FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--4、哪类SQL扫描记载数最多？SELECT DIGEST_TEXT,SUM_ROWS_EXAMINED FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--5、哪类SQL运用暂时表最多？SELECT DIGEST_TEXT,SUM_CREATED_TMP_TABLES,SUM_CREATED_TMP_DISK_TABLES FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--6、哪类SQL返回效果集最多？SELECT DIGEST_TEXT,SUM_ROWS_SENT FROM events_statements_summary_by_digest ORDER BY COUNT_STAR DESC--7、哪个表物理IO最多？SELECT file_name,event_name,SUM_NUMBER_OF_BYTES_READ,SUM_NUMBER_OF_BYTES_WRITE FROM file_summary_by_instance ORDER BY SUM_NUMBER_OF_BYTES_READ + SUM_NUMBER_OF_BYTES_WRITE DESC--8、哪个表逻辑IO最多？SELECT object_name,COUNT_READ,COUNT_WRITE,COUNT_FETCH,SUM_TIMER_WAIT FROM table_io_waits_summary_by_table ORDER BY sum_timer_wait DESC--9、哪个索引拜访最多？SELECT OBJECT_NAME,INDEX_NAME,COUNT_FETCH,COUNT_INSERT,COUNT_UPDATE,COUNT_DELETE FROM table_io_waits_summary_by_index_usage ORDER BY SUM_TIMER_WAIT DESC--10、哪个索引素来没实用过？SELECT OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME FROM table_io_waits_summary_by_index_usage WHERE INDEX_NAME IS NOT NULL AND COUNT_STAR = 0 AND OBJECT_SCHEMA <> 'mysql' ORDER BY OBJECT_SCHEMA,OBJECT_NAME;--11、哪个等待事件消耗时间最多？SELECT EVENT_NAME,COUNT_STAR,SUM_TIMER_WAIT,AVG_TIMER_WAIT FROM events_waits_summary_global_by_event_name WHERE event_name != 'idle' ORDER BY SUM_TIMER_WAIT DESC--12-1、剖析某条SQL的执行情况，包括statement信息，stege信息，wait信息SELECT EVENT_ID,sql_text FROM events_statements_history WHERE sql_text LIKE '%count(*)%';--12-2、查看每个阶段的时间消耗SELECT event_id,EVENT_NAME,SOURCE,TIMER_END - TIMER_START FROM events_stages_history_long WHERE NESTING_EVENT_ID = 1553;--12-3、查看每个阶段的锁等待情况SELECT event_id,event_name,source,timer_wait,object_name,index_name,operation,nesting_event_id FROM events_waits_history_longWHERE nesting_event_id = 1553;

对于Performance Schema的东西，我们还可以参照 官网继续进行细致的理解。

我们来思索一下，否可以做一个系统出来，通过查询Performance Schema的表的一些信息，并将其展现到web端，这样我们就可以进行可视化监控了？

show processlist

运用show processlist查看连贯的线程个数，来调查可否有批量线程处于不正常的状态或者其他不正常的特征。

mysql> show processlist;
+----+------+-----------+--------------------+---------+------+----------+------------------+| Id | User | Host      | db                 | Command | Time | State    | Info             |
+----+------+-----------+--------------------+---------+------+----------+------------------+|  6 | root | localhost | performance_schema | Query   |    0 | starting | show processlist |
+----+------+-----------+--------------------+---------+------+----------+------------------+1 row in set (0.00 sec)

属性注明：

id session id

user 操纵的会员

host 操纵的主机

db 操纵的数据库

command 目前状态

  • sleep：线程正在期待客户端发送新的要求
  • query：线程正在施行查询或正在将效果发送给客户端
  • locked：在mysql的办事层，该线程正在期待表锁
  • analyzing and statistics：线程正在收集存储引擎的统计信息，并生成查询的施行规划
  • Copying to tmp table：线程正在施行查询，而且将其效果集都复制到一个暂时表中
  • sorting result：线程正在对效果集进行排序
  • sending data：线程可能在多个状态之间传送数据，或者在生成效果集或者向客户端返回数据

info 细致的sql语句

time 响应下令施行工夫

state 下令施行状态

小结

MySQL调优之机能监控：

  1. show profile 运用show profile查询分析工具，可以指定具体的type
  2. performance schema 运用performance schema来更加容易的监控mysql

  3. show processlist 运用show processlist查看连贯的线程个数，来调查可否有批量线程处于不正常的状态或者其他不正常的特征

更多相干免费学习举荐：mysql教程(视频)

以上就是初识MySQL调优之机能监控的细致内容，更多请关注 百分百源码网 其它相干文章！

打赏

打赏

取消

感谢您的支持，我会继续努力的!

扫码支持
扫码打赏，你说多少就多少

打开支付宝扫一扫，即可进行扫码打赏哦

百分百源码网 建议打赏1～10元，土豪随意，感谢您的阅读！

相关文章

共有151人阅读，期待你的评论！发表评论
昵称： 网址： 验证码： 点击我更换图片
最新评论

本文标签

广告赞助

能出一分力是一分吧！

订阅获得更多模板

本文标签

广告赞助

订阅获得更多模板

关于我们-免责声明-业务报价-广告服务-意见反馈-常见问题-网站地图

百分百源码网声明：本站所有模板文章除标明原创外，均来自网络转载，版权归作者所有，如果有侵犯权益，请联系本站删除！

Copyright © 2021 完整无错带数据网站源码模板下载平台! WWW.BAIDOW.COM.