The MySQL SYS Schema in MySQL 5.7.7
来源:互联网 发布:除广告软件 编辑:程序博客网 时间:2024/06/05 17:42
from: http://mysqlserverteam.com/the-mysql-sys-schema-in-mysql-5-7-7/
mysql 5.7 提供一个 SYS Schema,从这里面可以查 内存page、io、lock、latency等各种状态信息,为性能调优提供重要的依据。更详细,可以看看https://github.com/MarkLeith/mysql-sys/blob/master/README.md
举例,查找2个query 发生lock 争用的详情:
mysql> SELECT * FROM innodb_lock_waits\G*************************** 1. row *************************** wait_started: 2014-11-11 13:39:20 wait_age: 00:00:07 wait_age_secs: 7 locked_table: `db1`.`t1` locked_index: PRIMARY locked_type: RECORD waiting_trx_id: 867158 waiting_trx_started: 2014-11-11 13:39:15 waiting_trx_age: 00:00:12 waiting_trx_rows_locked: 0 waiting_trx_rows_modified: 0 waiting_pid: 3 waiting_query: UPDATE t1 SET val = val + 1 WHERE id = 2 waiting_lock_id: 867158:2363:3:3 waiting_lock_mode: X blocking_trx_id: 867157 blocking_pid: 4 blocking_query: UPDATE t1 SET val = val + 1 + SLEEP(10) WHERE id = 2 blocking_lock_id: 867157:2363:3:3 blocking_lock_mode: X blocking_trx_started: 2014-11-11 13:39:11 blocking_trx_age: 00:00:16 blocking_trx_rows_locked: 1 blocking_trx_rows_modified: 1 sql_kill_blocking_query: KILL QUERY 4sql_kill_blocking_connection: KILL 4
简单介绍见下文:
New in MySQL 5.7.7, the MySQL sys schema (originally the ps_helper project) is now included by default within the MySQL server!
For those unfamiliar with the sys
schema project, it is a database schema with a set of objects (views, stored procedures, stored functions, and table with a couple of triggers on it) that were implemented to give easy, human readable, DBA and Developer based use case access to the wealth of instrumentation data implemented primarily withinPerformance Schema, but also with various INFORMATION_SCHEMA tables as well.
First, what does included by default mean?
It means that when you initialize your MySQL 5.7 server for the first time, with eithermysql_install_db, or the new mysqld --initialize
option, the sys
schema is added alongside the other standard schemas, nothing further for you to do, just initialize your database instance as you normally would.
When upgrading from a previous version, and running mysql_upgrade, the instance is checked to see whether the sys
schema already exists or not, and creates or upgrades the schema appropriately. The version of the sys schema bundled with 5.7.7 is 1.4.0, so if you already have that version installed, mysql_ugprade will do nothing. However, if you have a version prior to that, mysql_upgrade will re-create the schema with the updated version:
If the sys
schema exists on upgrade, but the sys
.version
view does not exist, thenmysql_upgrade will return an error, as it will assume that the sys schema that is within the instance is a user created schema:
If you already have a schema called sys when upgrading to version 5.7.7 or later, you should ensure that it is renamed before running mysql_upgrade.
If you want to skip creating the sys schema with either mysql_install_db or mysql_upgrade, you can use the new --skip-sys-schema
option. You can always install it by hand at later time, using the $install_dir/share/mysql_sys_schema.sql file laid down with each installation (though this is not used within any of the above options, the DDL statements are compiled in for those).
After installation or upgrade, you then get access to the objects within the sys schema, enabling you to dive deeper in to the statistics needed to be able to answer some of the questions you get, or problems that you have to solve, on a day to day basis.
Questions like “Who is taking up all the resources on my database server?” can be quickly and easily answered with either the user summary views:
Or similar output with “Which hosts are hitting my database server those most?” and these tables:
Maybe you are ask yourself “Which objects are accessed the most, and how?”:
Or “What statements have the highest overall latency, and what statistics did they have?”
Or “Which statements are using temporary tables on disk?”
Or “Which tables take the most space in my InnoDB buffer pool?”
“Where is all the memory going on my instance?”
“Which database files cause the most IO, and what kind of IO pattern do they have?”
“Where is the time being spent the most within my instance?”
Now you can answer all of these questions, and many more, with ease.
The reference documentation for sys is currently being worked on (it’s a huge job), but in the mean time, if you want to see the full power of what you get from the sys schema bundled within MySQL 5.7.7, see the README on the GitHub project.
Try it out, and give us your feedback! If you spot any bugs, please report them on http://bugs.mysql.com/ under the new “MySQL Server: SYS Schema” category.
- The MySQL SYS Schema in MySQL 5.7.7
- MySQL 5.7 SYS系统SCHEMA
- MySQL- 5.7 sys schema笔记
- MySQL- 5.7 sys schema笔记
- MySQL- 5.7 sys schema笔记
- 专栏 | MySQL 5.7系列之sys schema(1) by吴炳锡
- 网易这样用sys schema优雅提升MySQL易用性
- mysql5.7 SYS Schema 详情
- mysql5.7 sys schema 1
- Import all grant statement of users in mysql schema !
- Import all grant statement of users in mysql schema !
- mysql 5.7新数据库sys解析
- 学习使用MySQL 5.7的sys库
- MySQL schema命名规则
- MySQL schema命名规则
- mysql schema设计优化
- mysql schema设计优化
- mysql performance schema
- android 动画实现(一)Tween动画
- URAL 1018 Binary Apple Tree (树形DP)
- jquery ajax异步调用方法中不能给全局变量赋值的原因及解决方法
- Python中的高级数据结构
- Mondrian下XMLA方式访问数据页面报错解决方案
- The MySQL SYS Schema in MySQL 5.7.7
- interface Java 中接口
- iOS中常见错误
- 模板方法
- Java web.xml session-config 属性配置
- iOS上传错误 ERROR ITMS-90049
- AFNetworking 3.0迁移指南
- 通过GeoIP获取ip所属地 (国家,城市,时区,邮编,经纬度等)
- 一招辨认sql中的varchar和char