用Oracle的眼光来学习MySQL 5.7的sys(下)(r11笔记第25天)

昨天写了篇分析sys的文章,用Oracle的眼光来学习MySQL 5.7的sys(上)(r11笔记第24天) 收到了一些朋友的反馈,还不错,今天继续努力,再整理一篇。

sys的借鉴意义

今天还和同事偶然聊起sys schema的事情,我觉得有几个地方要值得借鉴。

1)原本需要结合information_schema,performance_schema查询的方式,现在有了视图的方式,显示更加直观

2)sys schema的有些功能在早期版本可能无从查起,或者很难查询,现在这些因为新版本的功能提炼都做出来了

3)如果想好好掌握这些视图的内涵,可以随时查看表的关联关系,对于理解MySQL的运行原理和问题的分析大有帮助,当然这个地方只能点到为止。

按照这种情况,没准以后会直接用sys替代information_schema,performance_schema,没准以后还会出更丰富的功能,类似Oracle中的免费的statspack,还有闭源的AWR,实时的性能数据抓取,自动性能分析和诊断,自动优化任务等,当然纯属猜想,Oracle里也都是这么走过来的。

对于这些视图,我按照名字和类别简单归类

sys下的视图分类

sys下的视图分了哪些层面呢。我简单来总结一下,大体分为一下几个层面

host_summary,这个是服务器层面的,比如里面的视图host_summary_by_file_io

user_summary,这个是用户层级的,比如里面的视图user_summary_by_file_io

innodb,这个是innodb层面的,比如innodb_buffer_stats_by_schema

io,这个是I/O层的统计,比如视图 io_global_by_file_by_bytes

memory,关于内存的使用情况,比如视图memory_by_host_by_current_bytes

schema,关于schema级别的统计信息,比如schema_table_lock_waits

session,关于会话级别的,这个视图少一些,就两个,session和session_ssl_status

statement,关于语句级别的,比如statements_with_errors_or_warnings

wait,关于等待的,这个还是处于起步阶段,等待模型有待完善,目前只有基于io/file, lock/table, io/table这三个方面,提升空间还很大。

其实里面innodb,schema,statement这三部分是格外需要关注的。

sys下的innodb视图

比如innodb部分的视图innodb_lock_waits

我们做个小测试来说明一下。我们开启两个会话。

会话1: start transaction; update test set id=100;

会话2: update test set id=102;

这个时候如果在没有sys的情况下,我们需要查看information_schema.innodb_locks和innodb_trx,有的时候还会查看show engine innodb status来得到一些信息佐证。

查看Innodb_locks

> select *from information_schema.innodb_locks\G
*************************** 1. row ***************************
    lock_id: 961672:356:3:2
lock_trx_id: 961672
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test`
 lock_index: GEN_CLUST_INDEX
 lock_space: 356
  lock_page: 3
   lock_rec: 2
  lock_data: 0x00000011D9EB
*************************** 2. row ***************************
    lock_id: 961671:356:3:2
lock_trx_id: 961671
  lock_mode: X
  lock_type: RECORD
 lock_table: `test`.`test`
 lock_index: GEN_CLUST_INDEX
 lock_space: 356
  lock_page: 3
   lock_rec: 2
  lock_data: 0x00000011D9EB
2 rows in set (0.00 sec)

查看innodb_trx

> select *from information_schema.innodb_trx\G
*************************** 1. row ***************************
                    trx_id: 961671
                 trx_state: RUNNING
               trx_started: 2016-12-26 22:25:52
     trx_requested_lock_id: NULL
          trx_wait_started: NULL
                trx_weight: 3
       trx_mysql_thread_id: 1149233
                 trx_query: NULL
       trx_operation_state: NULL
         trx_tables_in_use: 0
         trx_tables_locked: 1
          trx_lock_structs: 2
     trx_lock_memory_bytes: 1136
           trx_rows_locked: 1
         trx_rows_modified: 1
   trx_concurrency_tickets: 0
       trx_isolation_level: READ COMMITTED
         trx_unique_checks: 1
    trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
 trx_adaptive_hash_latched: 0
 trx_adaptive_hash_timeout: 0
          trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)面对这些情况,该怎么处理,比如要杀掉会话,可能还会有些模棱两可。

我们来看看使用innodb_lock_waits的结果。这个过程语句都给你提供好了,只有1行信息,就是告诉你产生了阻塞,现在可以使用kill的方式终止会话,kill语句都给你提供好了。

> select * from innodb_lock_waits\G
*************************** 1. row ***************************
                wait_started: 2016-12-26 22:28:24
                    wait_age: 00:01:38
               wait_age_secs: 98
                locked_table: `test`.`test`
                locked_index: GEN_CLUST_INDEX
                 locked_type: RECORD
              waiting_trx_id: 961672
         waiting_trx_started: 2016-12-26 22:28:24
             waiting_trx_age: 00:01:38
     waiting_trx_rows_locked: 2
   waiting_trx_rows_modified: 0
                 waiting_pid: 1149284
               waiting_query: update test set id=102
             waiting_lock_id: 961672:356:3:2
           waiting_lock_mode: X
             blocking_trx_id: 961671
                blocking_pid: 1149233
              blocking_query: NULL
            blocking_lock_id: 961671:356:3:2
          blocking_lock_mode: X
        blocking_trx_started: 2016-12-26 22:25:52
            blocking_trx_age: 00:04:10
    blocking_trx_rows_locked: 1
  blocking_trx_rows_modified: 1
     sql_kill_blocking_query: KILL QUERY 1149233
sql_kill_blocking_connection: KILL 1149233
1 row in set (0.01 sec)  

当然默认事务还是有一个超时的设置,可以看到确实是update test set id=102阻塞了。已经因为超时取消。

> update test set id=102; ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

不过innodb相关的视图确实不多,只有3个,都蛮实用的。

sys下的schema视图

我们继续看看schema层面的视图,这部分内容就很实用了。

schema_auto_increment_columns      
schema_index_statistics
schema_object_overview
schema_redundant_indexes
schema_table_lock_waits 
schema_table_statistics 
schema_table_statistics_with_buffer
schema_tables_with_full_table_scans
schema_unused_indexes

如果要查看一个列值溢出的情况,比如是否列的自增值会超出数据类型的限制,这个问题对很多MySQL DBA一直以来都是一个挑战,视图schema_auto_increment_columns就给你包装好了,直接用即可。以下输出略微做了调整。

table_name        | column_name |column_type |  max_value  | auto_increment
------------------+-------------+------------+---------------+-------------
ta_newyear2_back  | id          |int(11)     |  2147483647 |9945076       |
tb_activate_code  | id          |int(11)     |  2147483647 |1851387       |
sys_oper_log      | id          |int(11)     |  2147483647 |126867        |

如果一个表的索引没有使用到,以前pt工具也可以做一些分析,现在查个视图就搞定了。当然索引的部分,一方面和采样率也有关系,不是一个绝对的结果。查看schema_unused_indexes的结果如下:

+-----------------+--------------------------------+-----------------------
| object_schema   | object_name                    | index_name            
+-----------------+--------------------------------+-----------------------
| activity_log    | bfgifts_logininfo              | index_cn              
| activity_log    | bf_sendq_reglog                | in_bf_sendq_reglog    
| activity_log    | db_singlecharge_records        | idx_db_singlecharge_re
| activity_log    | FFO_RECHARGE_CONS              |

如果要查看那些表走了全表扫描,性能情况,可以查看schema_tables_with_full_table_scans,查询结果如下,如果数据量本身很大,这个结果就会被放大,值得关注。

+-----------------+--------------------+-------------------+-----------+
| object_schema   | object_name        | rows_full_scanned | latency   |
+-----------------+--------------------+-------------------+-----------+
| mobile_billing  | tb_activate_code   |      133704990876 | 20.74 h   |
| mobile_billing  | tb_appkey_config   |          56067246 | 5.32 m    |
| mobile_billing  | tb_goods           |          11323673 | 1.20 m    |
| mobile_billing  | tb_app             |          11104405 | 28.86 s   |

如果查看一些冗余的索引,可以参考 schema_redundant_indexes,删除的SQL语句都给你提供好了。

*************************** 9. row ***************************
              table_schema: zzb_test
                table_name: tes_activate_list
      redundant_index_name: INDEX_SMS_ID
   redundant_index_columns: SMS_ID
redundant_index_non_unique: 0
       dominant_index_name: PRIMARY
    dominant_index_columns: SMS_ID
 dominant_index_non_unique: 0
            subpart_exists: 0
            sql_drop_index: ALTER TABLE `zzb_test`.`sms_activate_list` DROP INDEX `INDEX_SMS_ID`

sys下的statement视图

接下来是statement层面的视图,大体有下面的一些

statement_analysis                            
statements_with_errors_or_warnings            
statements_with_full_table_scans              
statements_with_runtimes_in_95th_percentile   
statements_with_sorting                       
statements_with_temp_tables 

这部分内容对于分析语句的性能还是尤其有用的。

比如查看语句的排序情况,资源使用情况,延时等都会提供出来。

> select *from statements_with_sorting;                    
+-------------------------------------------------------------------+----------------+------------+---------------
| query                                                             | db             | exec_count | total_latency 
+-------------------------------------------------------------------+----------------+------------+---------------
| SELECT * FROM `tb_goods` WHERE ... TION` ASC , `GOODS_PRICE` ASC  | mobile_billing |      26334 | 29.84 s       
| SELECT `id` , `appname` , `app ... ? ORDER BY `create_date` DESC  | mobile_billing |      13664 | 9.04 s        
| SELECT `channel0_` . `id` AS ` ... annel0_` . `create_date` DESC  | mobile_billing |       6832 | 4.83 s    

在这里SQL语句做了删减,不过大体能看出语句的信息,执行次数和 延时等都可以看到。

对于SQL语句中生成的临时表可以查看statements_with_temp_tables ,比如某一个语句生成的临时表情况,都做了统计。

db      | exec_count | total_latency | memory_tmp_tables | disk_tmp_tables 
--------+------------+---------------+-------------------+-----------------
test    |         27 | 2.36 s        |              3442 |             263 
backend |         18 | 1.06 s        |              1314 |             

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2016-12-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏IT大咖说

如何让数据增量更新节省资源,耗时减半

内容来源:本文内容由阿里大数据计算服务(MaxCompute)团队投稿提供。IT 大咖说(微信id:itdakashuo)经授权发布,转载请标明出处。

1405
来自专栏数据和云

DBA入门之路:察微知渐细致入微

在DBA的职业生涯中,要面临无数的艰难险阻、排忧解难,所以细致入微,严谨认真的风格必不可少。养成了察微知渐的习惯,才能在分析诊断故障时层剖缕析,直指核心;而我也...

2113
来自专栏前端黑板报

MySQL之什么是MySQL

这篇教程是帮助你回答:什么是MySQL?以及告诉你为何 MySQL 是世界上最流行的开源数据库的原因。

2044
来自专栏别先生

增量数据,如果下次增量数据存在重复数据,如何解决。

1、如果增量数据,每次增量数据可能会存在增量数据,如何解决。思路,首先可以复制一个备份表,然后将主表中存在的数据,在备份表中进行删除,然后将备份表插入到主表,最...

691
来自专栏杂烩

Mycat基准测试报告 原

好久没上OSC,上面安排测下Mycat,于是申请服务器,花了两个周做出这个东西,供以借鉴。

1831
来自专栏杨建荣的学习笔记

merge语句导致的性能问题紧急优化 (r9笔记第85天)

晚上正在休息的时候,突然收到一封报警邮件。 报警内容: CPU utilization is too high ------------------------...

2805
来自专栏决胜机器学习

数据库专题(二) ——数据库设计

数据库专题(二)——数据库设计 (原创内容,转载请注明来源,谢谢) 一、数据库设计规范——范式 数据库设计,需要遵循设计原则,最主要的设计原则是范式。范式是...

3557
来自专栏Jerry的SAP技术分享

SAP CRM Survey调查问卷的存储模型

数据库表CRM_SVY_DB_SVS,通过如下的函数CRM_SVY_DB_SVS_CREATE插入:

1603
来自专栏PPV课数据科学社区

【一文打尽】SQL 数据分析常用语句.....收藏

• 1 基础查询 • 2 字符串\数字\日期时间 • 3 聚合数据查询 • 4 子查询 • 5 联接\组合查询 • 6 高级查询 • 7 更新数据 阅读提醒:点...

40310
来自专栏lonelydawn的前端猿区

MySQL应用优化

一、基本语句优化原则 (1).尽量避免在索引列上进行运算或函数操作,这样会导致索引失效 如: select * from t where Year(d)>=20...

1935

扫码关注云+社区