碎碎念

该篇与前两篇不同，前两篇每个模块都是一个独立的小案例，本篇不涉及具体案例，但是确能帮助你解决很多问题，工欲善其事,必先利其器，MySQL自带的监控对我们来说，就是一把神兵利器，只要合理使用，能够让我们在日常工作中事半功倍。Now，开始你今日的学习之旅吧。

监控项配置

案例说明

在MySQL中，performance_schema系统库提供了很多监控项，这些监控项记录MySQL在实际运行过程中的一些状态和资源使用情况，但是在生产中，开启performance_schema会对数据库性能有一定的影响，如果对性能要求很高，又希望遇到一些问题时能借助MySQL自身的监控进行快捷排查问题，是不是有折中的方法呢，我们可以单独开启部分监控项，这样既可以排查问题，又能减少性能损耗，那么，开启哪些监控项呢，我建议的是开启如下监控项，涉及的范围包括MDL锁监控，内存监控，语句监控，在之前的工作中，这几个监控在问题排查方面提供了很多帮助。

performance_schema库监控项的开启可以有两种方式，一种是通过更新performance_schema.setup_instruments和performance_schema.setup_consumers表打开监控开关，另外一种是通过在配置文件中配置，然后重启数据库，一般选择先动态设置，然后持久化到配置文件中，这样既不需要重启，下一次重启后配置也不会失效。且先看通过配置文件如何配置吧。配置方法:

[mysqld]
performance-schema-instrument ='wait/lock/metadata/sql/mdl=ON'
performance-schema-instrument = 'memory/% = COUNTED'
performance-schema-instrument = 'stage/innodb/alter%=ON'
performance-schema-consumer-events-stages-current=ON
performance-schema-consumer-events-stages-history=ON
performance-schema-consumer-events-stages-history-long=ON

上面是在配置文件配置的方法，与之对应的就是动态配置的方法了，登录数据库后执行如下操作即可。动态设置：

update performance_schema.setup_instruments set enabled = 'yes' where name like 'stage/innodb/alter%';
update performance_schema.setup_consumers set enabled = 'yes' where name like '%stages%';

update performance_schema.setup_instruments set enabled = 'yes' where name = 'wait/lock/metadata/sql/mdl';

update performance_schema.setup_instruments set enabled = 'yes' where name like 'memory/%';

以上两种方法，都学废了嘛，现在先去检查一下自己的数据库实例有没开启这些监控项吧，没开启的要抓紧了。

ddl执行进度

案例说明

作为数据库搬运工，DDL操作对你来说应该是很熟悉了，如果表的数据量不大，DDL操作很快，如果表的数据量偏大，这时候DDL操作耗费的时间可能就比较长了，对于大表DDL操作，一般有两种操作方式，一种通过工具实现，一般使用pt-osc或者ghost工具，另外一种就是使用原生的DDL操作，对于工具来说，会显示DDL操作的进度，便于我们对整个操作需要的全部时间有个大致预估，对于原生的DDL操作，无法直接查看进度，这时候，难道我们就傻等着嘛，当然不是，优秀的你遇到问题当然是选择解决问题了。

通过配置上面的监控项后，可以通过查看sys库或者performance库来查看DDL操作的进度，具体如下: 通过performance_schema库查询：

SELECT STMT.SQL_TEXT,        STAGE.EVENT_NAME,        CONCAT(WORK_COMPLETED, '/', WORK_ESTIMATED) AS PROGRESS,        CONCAT(ROUND(100 * WORK_COMPLETED / WORK_ESTIMATED, 2), ' %') AS PROCESSING_PCT,        SYS.FORMAT_TIME(STAGE.TIMER_WAIT) AS TIME_COSTS,        CONCAT(ROUND((STAGE.TIMER_END - STMT.TIMER_START) / 1E12 *                     (WORK_ESTIMATED - WORK_COMPLETED) / WORK_COMPLETED,                     2),               ' s') AS REMAINING_SECONDS   FROM PERFORMANCE_SCHEMA.EVENTS_STAGES_CURRENT     STAGE,        PERFORMANCE_SCHEMA.EVENTS_STATEMENTS_CURRENT STMT  WHERE STAGE.THREAD_ID = STMT.THREAD_ID    AND STAGE.NESTING_EVENT_ID = STMT.EVENT_ID\G;
*************************** 1. row ***************************
         SQL_TEXT: alter table student3 add index idx_name(name)
       EVENT_NAME: stage/innodb/alter table (flush)
         PROGRESS: 116841/118953
   PROCESSING_PCT: 98.22 %
       TIME_COSTS: 2.44 s
REMAINING_SECONDS: 0.18 s

如上,是通过performance_schema库查看DDL进度的方式，下面，再演示一下通过sys库查看DDL进度的方式: 通过sys库查询：

#查询排除自己以外的语句进度信息
mysql> select * from sys.session where conn_id!=connection_id()\G;
*************************** 1. row ***************************
                thd_id: 47
               conn_id: 12
                  user: root@localhost
                    db: test
               command: Query
                 state: alter table (insert)
                  time: 6
     current_statement: alter table student3 add index idx_name(name)
     statement_latency: 6.34 s
              progress: 67.68
          lock_latency: 1.84 ms
         rows_examined: 0
             rows_sent: 0
         rows_affected: 0
            tmp_tables: 0
       tmp_disk_tables: 0
             full_scan: NO
        last_statement: NULL
last_statement_latency: NULL
        current_memory: 24.29 MiB
             last_wait: NULL
     last_wait_latency: NULL
                source: NULL
           trx_latency: NULL
             trx_state: NULL
        trx_autocommit: NULL
                   pid: 1153257
          program_name: mysql

performance_schema库显示的信息可以查看剩余时间,sys库不能，不过都可以实现DDL进度的查看，具体使用哪种可以根据个人喜好，无论黑猫白猫，能抓老鼠就行。

mdl锁相关

案例说明

在生产上，你是否遇到过执行某个SQL操作被阻塞的场景，通过show processlist可以查看到state列显示Waiting for table metadata lock或者Waiting for global read lock，这时候就是遇到了MDL锁。前者是表级锁，后者是全局锁。针对这两种情况，有不同的排查方法，以下展开描述。

对于表级MDL锁，可以通过查询sys.schem_table_lock_waits视图查看mdl锁的占用情况，详细示例如下:

mysql> select * from sys.schema_table_lock_waits\G
*************************** 1. row ***************************
               object_schema: my_test
                 object_name: t
           waiting_thread_id: 45
                 waiting_pid: 11
             waiting_account: root@localhost
           waiting_lock_type: SHARED_WRITE
       waiting_lock_duration: TRANSACTION
               waiting_query: select * from t where c=20 for update
          waiting_query_secs: 167
 waiting_query_rows_affected: 0
 waiting_query_rows_examined: 0
          blocking_thread_id: 43
                blocking_pid: 9
            blocking_account: root@localhost
          blocking_lock_type: SHARED_READ_ONLY
      blocking_lock_duration: TRANSACTION
     sql_kill_blocking_query: KILL QUERY 9
sql_kill_blocking_connection: KILL 9
1 row in set (0.01 sec)

注:
waiting_pid:被阻塞的线程ID
waiting_query:被阻塞的SQL
blocking_pid:MDL锁的线程ID，即锁源头
sql_kill_blocking_connection:杀掉锁源头，释放锁的方法
blocking_lock_type:锁源头锁类型
blocking_lock_duration:锁源头锁生命周期

sys.schema_table_lock_waits视图数据其实来源于performance_schema.metadata_locks表，但是做了过滤和其他操作，增强了可读性，但是无法查看全局锁信息，对于全局锁，需要通过performance_schema.metadata_locks表查看。

详细示例如下:

mysql> SELECT thd.processlist_id,
       mdl.OBJECT_TYPE,
       mdl.OBJECT_SCHEMA,
       mdl.OBJECT_NAME,
       mdl.LOCK_TYPE,
       mdl.LOCK_DURATION,
       mdl.LOCK_STATUS,
       gs.sql_text
FROM performance_schema.metadata_locks mdl
INNER JOIN performance_schema.threads thd ON mdl.owner_thread_id = thd.thread_id
AND processlist_id <> connection_id()
AND LOCK_DURATION='EXPLICIT'
JOIN performance_schema.events_statements_current gs ON mdl.owner_thread_id = gs.THREAD_ID;
+----------------+-------------+----------+---------------+-------------+------------------------------+
| processlist_id | OBJECT_TYPE | LOCK_TYPE| LOCK_DURATION | LOCK_STATUS | sql_text                     |
+----------------+-------------+----------+---------------+-------------+------------------------------+
|              3 | GLOBAL      | SHARED   | EXPLICIT      | GRANTED     | flush tables with read lock  |
|              3 | COMMIT      | SHARED   | EXPLICIT      | GRANTED     | flush tables with read lock  |
+----------------+-------------+----------+---------------+-------------+------------------------------+
2 rows in set (0.00 sec)

上面就是针对表级MDL锁和全局MDL锁的查看方法，另外，针对MDL锁的详细描述，参考源码注释描述如下，源码文件对应于sql/mdl.cc文件和sql/mdl.h文件，仅供参考:

MDL_INTENTION_EXCLUSIVE(IX)
意图独占的元数据锁，仅用于作用域锁，可升级为独占锁，与其他IX锁兼容，和作用域锁及X锁不兼容。
该锁会在很多操作时出现，例如DML/DDL操作，DELETE/UPDATE/INSERT/FOR UPDATE等DML操作会在GLOBAL上加IX锁，然后才会在本对象上加锁，DDL语句至少会在GLOBAL上加IX锁，对象所属SCHEMA上加IX锁，本对象加锁。持续周期为MDL_STATEMENT。

MDL_SHARED(S)
共享元数据锁，在我们仅对对象的元数据感兴趣且没有意图访问对象数据的情况下使用(例如用于存储过程或准备预处理语句期间)。
我们还将此类型的锁误用于打开 HANDLER，因为通过这个语句获取的锁必须与由 LOCK TABLES ... WRITE 语句获取的锁兼容，即 SNRW(我们无法在 HANDLER ... OPEN 时获取S锁并将其升级为HANDLER ... READ 时的SR锁，因为这不解决在同一连接中具有打开 HANDLER 的情况下等待表级锁的DML语句需要中止的问题)。为了避免发生死锁，当SNRW锁升级为表上的X锁时可能会发生死锁，而表上有一个由正在执行升级操作的线程拥有的活跃S锁，该线程反过来等待执行升级的线程拥有的表级锁时，我们必须在这种情况下使thr_abort_locks_for_thread()工具。对存储过程的锁不会出现这个问题，因为我们不会为它们使用SNRW锁。当在PREPARE调用期间使用S锁时，也不会出现此问题，因为在这种情况下不会获取表级锁。
一般用于flush tables with read lock中，会阻塞所有DELETE/UPDATE/INSERT/FOR UPDATE等DML和DDL操作，也会阻塞commit操作。持续时间为MDL_EXPLICIT。

MDL_SHARED_HIGH_PRIO(SH)
高优先级共享元数据锁，用于不需要访问对象数据的场景，即只需要访问元数据，例如information_schema表，SH锁和SNRW锁兼容，因此持有SH锁不应该试图获取任何类型的表级别或者行级别的锁定，这可能会导致死锁，另外，在获取SH锁时，连接不应该等待任何资源，这可能会导致X锁的饥饿或者SNW/SNRW锁升级到X锁期间发生死锁，和X不兼容。持续时间为MDL_TRANSACTION。

MDL_SHARED_READ(SR)
意图读取数据的共享元数据锁，持有这种锁的连接在获取适当的表和行级锁之后可以读取表元数据并读取表数据，如果在表上持有SR MDL锁，则在表上具有类似的表级锁。被用于表上的select，subqueries,lock table ...read语句。持续时间为MDL_TRANSACTION。

MDL_SHARED_WRITE(SW)
意图修改数据的共享元数据锁，持有SW锁的连接可以读取表元数据并修改或在获取适当的表和行级锁之后读取表数据，包含INSERT/UPDATE/DELETE操作，或者SELECT ...FOR UPDATE,但是不包含LOCK TABLE ...WRITE/DDL操作。持续时间为MDL_TRANSACTION。

MDL_SHARED_WRITE_LOW_PRIO(SWL)
相当于MDL_SHARED_WRITE锁的一个低配版，优先级比MDL_SHARED_READ_ONLY低，用于修改表时候使用LOW_PRIORITY子句的DML语句。

MDL_SHARED_UPGRADABLE(SU)
一个可升级的共享元数据锁，允许并发更新和读取表数据，持有该种锁的连接可以读取表元数据并读取表数据，不能修改数据，因此与SRO锁兼容，可以升级到SNW/SNWR/X锁，一旦SU升级到SNRW,数据可以自由地被进行修改，用于alter table的第一阶段(对应algorithm=inplace的online ddl)，持续时间为MDL_TRANSACTION。

MDL_SHARED_READ_ONLY(SRO)
一个只读的共享元数据锁，当需要从表中读取数据时，阻止对数据和元数据的所有并发修改，应用于lock tables read语句。持续时间为MDL_TRANSACTION。

MDL_SHARED_NO_WRITE(SNW)
可升级的共享元数据锁，允许读操作，不允许更新数据操作，持有该锁的连接可以读取表元数据并读取表数据，可以升级到X元数据锁，因为这种锁与SNRW/SW不兼容，需要合适的引擎层的锁以便于进行数据读取(对于MyISAM的TL_READ*和Innodb中的shared row locks)，用于alter table操作的第一阶段，复制表的数据时允许读操作，但是不允许更新操作。这也是algorithm=copy和algorithm=inplace的一个关键区别。持续时间为MDL_TRANSACTION。

MDL_SHARED_NO_READ_WRITE(SNRW)
可升级的共享元数据锁，允许其他连接访问表元数据(desc)，但是不允许访问数据，阻止所有读取或者更新表数据的操作，同时允许information_schema和show查询，可以升级为X元数据锁，用于lock tables write语句，与除了S/SH之外的任何其他锁都不兼容。持续时间为MDL_TRANSACTION。

MDL_EXCLUSIVE(X)
独占元数据锁，持有该锁的连接可以修改表的元数据和数据，持有该锁时，不能授予其他类型的元数据锁，用于create/drop/rename table语句和其他DDL语句的某些阶段。持续时间为MDL_TRANSACTION。

以上MDL锁类型总结结合源码注释中对MDL锁的描述和高鹏老师的一篇关于MDL锁的博客进行学习，如果希望更深入MDL锁学习，也可以参考高鹏老师的博客链接https://www.jianshu.com/p/570b2ddecebe 。

内存使用排查

案例说明

除了锁问题，内存问题也算是MySQL中比较难排查的一类问题了。通过配置监控，我们可以从不同维度查看MySQL中内存的使用情况，这样在遇到内存问题时才能快准狠地解决问题。

可以通过sys库查看MySQL中内存的使用情况，可以根据主机，线程，用户，事件不同的维度进行统计分组，分别对应着视图 sys.memory_by_host_by_current_bytes(主机维度)，sys.memory_by_thread_by_current_bytes(线程维度)，sys.memory_by_user_by_current_bytes(用户维度)，sys.memory_global_by_current_bytes(事件维度)。(ps:视图前x$导致显示异常，所以这里去掉了)，以主机维度为例:

根据主机分组的内存使用情况
mysql>  select host,current_count_used/1024/1024 as used_MB ,current_allocated/1024/1024 as allocated_MB,current_avg_alloc/1024/1024 as avg_alloc_MB,current_max_alloc/1024/1024 as max_alloc_MB,total_allocated/1024/1024 as total_all_MB from sys.x$memory_by_host_by_current_bytes limit 5;
+--------------+------------+---------------+----------------+---------------+-----------------+
| host | used_MB | allocated_MB | avg_alloc_MB | max_alloc_MB | total_all_MB |
+--------------+------------+---------------+----------------+---------------+-----------------+
| 127.0.0.1 | 0.44133854 | 1936.61927986 | 0.004184778595 | 1877.86367798 | 125803.03408051 |
| background | 0.19166565 | 1263.20511723 | 0.006285353088 | 1048.50000000 | 7123.12300968 |
| localhost | 0.00107384 | 0.16798401 | 0.000149186516 | 6.39992523 | 745.00982475 |
| 10.186.61.18 | 0.00002956 | 0.01354504 | 0.000436936664 | 0.00781250 | 0.06849575 |
+--------------+------------+---------------+----------------+---------------+-----------------+
4 rows in set (0.00 sec)

也可以通过performance_schema库查看内存使用情况，可以从五个维度进行分组，账户和事件(对应表performance_schema.memory_summary_by_account_by_event_name)，主机和事件(对应表performance_schema.memory_summary_by_host_by_event_name)，线程和事件(对应表performance_schema.memory_summary_by_thread_by_event_name)，用户和事件(对应表performance_schema.memory_summary_by_user_by_event_name)，事件(对应表performance_schema.memory_summary_global_by_event_name)，以线程和事件维度为例:

根据线程和事件分组的内存使用情况
mysql> select * from performance_schema.memory_summary_by_thread_by_event_name limit 5;
+-----------+-----------------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
| THREAD_ID | EVENT_NAME                                          | COUNT_ALLOC | COUNT_FREE | SUM_NUMBER_OF_BYTES_ALLOC | SUM_NUMBER_OF_BYTES_FREE | LOW_COUNT_USED | CURRENT_COUNT_USED | HIGH_COUNT_USED | LOW_NUMBER_OF_BYTES_USED | CURRENT_NUMBER_OF_BYTES_USED | HIGH_NUMBER_OF_BYTES_USED |
+-----------+-----------------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
|         1 | memory/sql/Locked_tables_list::m_locked_tables_root |           0 |          0 |                         0 |                        0 |              0 |                  0 |               0 |                        0 |                            0 |                         0 |
|         1 | memory/sql/display_table_locks                      |           0 |          0 |                         0 |                        0 |              0 |                  0 |               0 |                        0 |                            0 |                         0 |
|         1 | memory/sql/THD::transactions::mem_root              |          35 |         34 |                    143920 |                   139808 |              0 |                  1 |               2 |                        0 |                         4112 |                      8224 |
|         1 | memory/sql/Delegate::memroot                        |           4 |          0 |                      3968 |                        0 |              0 |                  4 |               4 |                        0 |                         3968 |                      3968 |
|         1 | memory/sql/thd::main_mem_root                       |          45 |         44 |                    491136 |                   482928 |              0 |                  1 |              14 |                        0 |                         8208 |                    236688 |
+-----------+-----------------------------------------------------+-------------+------------+---------------------------+--------------------------+----------------+--------------------+-----------------+--------------------------+------------------------------+---------------------------+
5 rows in set (0.00 sec)

除此之外，还可以将内存监控相关的表和其他表进行关联查询，能够了解更详细的信息，例如具体某条sql占用的内存使用情况，示例如下:

mysql> select t.processlist_id,concat(t.PROCESSLIST_USER,'@',t.PROCESSLIST_host) as account,m.event_name, m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024 as mem_used, t.PROCESSLIST_INFO from performance_schema.memory_summary_by_thread_by_event_name m, performance_schema.threads t where m.thread_id = t.thread_id and processlist_id is not null order by CURRENT_NUMBER_OF_BYTES_USED desc limit 5;
+----------------+----------------+-----------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| processlist_id | account | event_name | mem_used | PROCESSLIST_INFO |
+----------------+----------------+-----------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 119992 | root@localhost | memory/sql/JOIN_CACHE | 0.007812500000 | select t.processlist_id,concat(t.PROCESSLIST_USER,'@',t.PROCESSLIST_host) as account,m.event_name, m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024 as mem_used, t.PROCESSLIST_INFO from performance_schema.memory_summary_by_thread_by_event_name m, performance_schema.threads t where m.thread_id = t.thread_id and processlist_id is not null order by CURRENT_NUMBER_OF_BYTES_USED desc limit 5 |
| 119992 | root@localhost | memory/sql/sp_head::main_mem_root | 0.000969290733 | select t.processlist_id,concat(t.PROCESSLIST_USER,'@',t.PROCESSLIST_host) as account,m.event_name, m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024 as mem_used, t.PROCESSLIST_INFO from performance_schema.memory_summary_by_thread_by_event_name m, performance_schema.threads t where m.thread_id = t.thread_id and processlist_id is not null order by CURRENT_NUMBER_OF_BYTES_USED desc limit 5 |
| 119992 | root@localhost | memory/innodb/mem0mem | 0.000255170278 | select t.processlist_id,concat(t.PROCESSLIST_USER,'@',t.PROCESSLIST_host) as account,m.event_name, m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024 as mem_used, t.PROCESSLIST_INFO from performance_schema.memory_summary_by_thread_by_event_name m, performance_schema.threads t where m.thread_id = t.thread_id and processlist_id is not null order by CURRENT_NUMBER_OF_BYTES_USED desc limit 5 |
| 119992 | root@localhost | memory/myisam/MI_INFO | 0.000079445541 | select t.processlist_id,concat(t.PROCESSLIST_USER,'@',t.PROCESSLIST_host) as account,m.event_name, m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024 as mem_used, t.PROCESSLIST_INFO from performance_schema.memory_summary_by_thread_by_event_name m, performance_schema.threads t where m.thread_id = t.thread_id and processlist_id is not null order by CURRENT_NUMBER_OF_BYTES_USED desc limit 5 |
| 119992 | root@localhost | memory/myisam/MYISAM_SHARE | 0.000024937093 | select t.processlist_id,concat(t.PROCESSLIST_USER,'@',t.PROCESSLIST_host) as account,m.event_name, m.CURRENT_NUMBER_OF_BYTES_USED/1024/1024/1024 as mem_used, t.PROCESSLIST_INFO from performance_schema.memory_summary_by_thread_by_event_name m, performance_schema.threads t where m.thread_id = t.thread_id and processlist_id is not null order by CURRENT_NUMBER_OF_BYTES_USED desc limit 5 |
+----------------+----------------+-----------------------------------+----------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

可以发现内存分配的event可以分配为两类，memory/sql/xxx,memory/innodb/xxx(这里innodb代表innodb引擎，如果使用了其他引擎，例如myisam，则为/memory/myisam/xxx),另外，mem_root事件用于到内存管理和线程管理，mem0mem是InnoDB存储引擎中用于分配和管理内存的模块。在源码sql/sql_class.h文件中对main_mem_root有如下描述:

 /**
    This memory root is used for two purposes:
    - for conventional queries, to allocate structures stored in main_lex
    during parsing, and allocate runtime data (execution plan, etc.)
    during execution.
    - for prepared queries, only to allocate runtime data. The parsed
    tree itself is reused between executions and thus is stored elsewhere.
  */
  MEM_ROOT main_mem_root;
  Diagnostics_area main_da;
  Diagnostics_area m_parser_da;              /**< cf. get_parser_da() */
  Diagnostics_area m_query_rewrite_plugin_da;
  Diagnostics_area *m_query_rewrite_plugin_da_ptr;

今日份总结

本期的技能小分享就到此结束了，从MySQL监控项的配置，动态开启到DDL操作进度的牢牢把握，MDL锁的深入探索，MySQL内存问题的抽丝剥茧，虽然不涉及具体的案例，但是授人以鱼，不如授人以渔，技术都学会了，还怕遇到问题不知所措嘛，淡定的来一句"小小问题我轻松拿捏"。(ps:有时间就会及时更新案例的，如果你也热爱学习，也可以后台联系我，彼此就多了一个朋友，哈哈。)

ITPub博客

MySQL中强烈推荐你开启的监控项

碎碎念

监控项配置

案例说明

ddl执行进度

案例说明

mdl锁相关

案例说明

内存使用排查

案例说明

今日份总结

温馨提示

最新文章