performance_schema
问题的引出
带着疑问去学习-----performance_schema从何而来？
经过mysql的版本的更迭，在细节部分会逐渐完善，还记得之前怎样去查询语句的执行时间，IO的操作时间吗？
简单回顾一下----一张表，两个关键字（show profile/profiles）
剩下的不在写了，在mysql8.0中输入以上语句，返回的都是空的，以为8.0中默认没开启profiling, 开启方式-----set profiling =1;
并给出了警告提示
mysql> show profile for query 1 ;
Empty set, 1 warning (0.00 sec)
查看警告信息-----
-- deprecated 熟悉吧---被废弃的，将来可能会移除，非常符合oracle公司的风格 --
mysql> show warnings \G
*************************** 1. row ***************************
Level: Warning
Code: 1287
Message: 'SHOW PROFILE' is deprecated and will be removed in a future release. Please use Performance Schema instead
1 row in set (0.00 sec)
什么是 performance_schema?
来看英文翻译—
一改往日的性能查看模式（ show profiles …)，performance_schema是一个用于监控我们数据库的一个工具，类似于java的守护线程一样，在背后默默工作；
默认情况下performance_schema 数据库时开启的状态，performance_schema在5.7.x及其以上版本中默认启用（5.6.x及其以下版本默认关闭，如果是5.6之前的版本，需要在配置文件中修改；），开启后几乎不会影响系统性能，因为他的优先级很低；
performance_schema 作为MySQL中的基础数据库之一，
mysql> show databases ;
+--------------------+
| Database |
+--------------------+
| gavin |
| information_schema | -- 运行时主要关注关于元数据的一些信息，
| mysql | --- 存放 相关基础数据---用户数据、权限等
| performance_schema |-- 运行时主要关注性能方面的信息---如 查询时间、io等相关资源的消耗 --
| sakila |
| sys |
| world |
+--------------------+
7 rows in set (0.01 sec)
我们来看一下performance_schema表里面都有些啥-----
mysql> use performance_schema ;
Database changed
mysql> show tables;
+------------------------------------------------------+
| Tables_in_performance_schema |
+------------------------------------------------------+
| accounts ----账户表 |
| events_errors_summary_by_account_by_error --事件错误 |
| 省略了很多行 |
| events_statements_current --事务状态 |
| 省略了很多行 |
| events_transactions_current |
| 省略了很多行 |
| events_waits_current -- 等待 |
| 省略了很多行 |
| file_instances --文件 |
| 省略了很多行 |
+------------------------------------------------------+
110 rows in set (0.00 sec)
关于performance_schema数据库中的表看起来很多其实做一下分类也并不多
1，可以从命名上看出来----有很多表名称 开头是一样的，
如 ----- 关于等待事件的表
mysql> show tables like 'events_waits%';
+-----------------------------------------------+
| Tables_in_performance_schema (events_waits%) |
+-----------------------------------------------+
| events_waits_current |
| events_waits_history |
| events_waits_history_long |
| events_waits_summary_by_account_by_event_name |
| events_waits_summary_by_host_by_event_name |
| events_waits_summary_by_instance |
| events_waits_summary_by_thread_by_event_name |
| events_waits_summary_by_user_by_event_name |
| events_waits_summary_global_by_event_name |
+-----------------------------------------------+
9 rows in set (0.00 sec)
2，看一下该数据库中的存储引擎----
performance_schema既可以视为一张表，也可被视为存储引擎。如果该引擎可用，则应该在INFORMATION_SCHEMA.ENGINES表或SHOW ENGINES语句的输出中都可以看到它的SUPPORT值为YES，
mysql> show engines ;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)
有很多数据引擎对-------管他呢， 如果要把这些表都整明白确实不容易，也没必要，我们常用的也就 innodb 和myisam,当然在表中你还会发现一个叫PERFORMANCE_SCHEMA 的引擎，这引擎跟 innodb 和myisam不一样，而跟memory一样不会持久化到磁盘里，而是存储到内存中的，退出数据库后相应信息也就消失了；
我们来理顺一下-----查询性能这些信息什么时候能查到？ 在数据库运行的时候查，查完就查完了，每次操作的语句可能不一样（我下次可能不这么查）所以没必要存放在磁盘中保留下来，对吧！
那么性能监控器的工作模式--------就类似于Java中生产者消费者一样，你操作了数据库，那么数据库性能监控器就记录关于性能的数据放在内存中（以键值对的方式），你查的时候我给你展现出来，你不查我先放在那里，你走了（mysql服务器重启），我就把它销毁了；-----这就是TM流水账式的操作啊。。。。。
性能监控的完全体
默认情况下性能监控器是打开的状态，但是并不是每一项关于性能的细节都是开启状态，
我们随便选一张表来查看----啊不能随便选，要与设置开关相关的才行 即setup_开头的表
mysql> select * from setup_instruments where enabled ='no' or timed ='no';
+----------------------------------------------------------------------------+---------+-------+------------+------------+---------------------------------------------------------------------------------------------------------------------+
| NAME | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
+----------------------------------------------------------------------------+---------+-------+------------+------------+---------------------------------------------------------------------------------------------------------------------+
| wait/synch/mutex/pfs/LOCK_pfs_share_list | NO | NO | singleton | 1 | Components can provide their own performance_schema tables. This lock protects the list of such tables definitions. |
| wait/synch/mutex/sql/TC_LOG_MMAP::LOCK_tc | NO | NO | | 0 | NULL |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit | NO | NO | | 0 | NULL |
| wait/synch/mutex/sql/MYSQL_BIN_LOG::LOCK_commit_queue | NO | NO | | 0
省略。。。。。。。
445 rows in set (0.00 sec)
如果有需要的话 可以设置成 ON；
mysql> UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES' where name like 'wait%';
Query OK, 331 rows affected (0.01 sec)
Rows matched: 384 Changed: 331 Warnings: 0
mysql> UPDATE setup_consumers SET ENABLED = 'YES' where name like '%wait%';
Query OK, 3 rows affected (0.00 sec)
Rows matched: 3 Changed: 3 Warnings: 0
配置好后就可以详细查看性能信息了------
mysql> sELECT * FROM events_statements_current limit 1\G
*************************** 1. row ***************************
THREAD_ID: 75 -- 线程ID
EVENT_ID: 69 --事件ID
END_EVENT_ID: 69 -- 结束事件-ID
EVENT_NAME: statement/sql/show_create_table --
SOURCE: init_net_server_extension.cc:96
TIMER_START: 11513064061400000 ---开始时间
TIMER_END: 11513064572900000 ---结束时间
TIMER_WAIT: 511500000 ---等待时间
LOCK_TIME: 245000000 -----锁占用的的时间
SQL_TEXT: SHOW CREATE TABLE `sys`.`sys_config` -- sql语句
DIGEST: --摘要 8f23b04fbcf94a77aadd8de74ef8d52927846e1eb61e11b754ac525e114ec81a
DIGEST_TEXT: SHOW CREATE TABLE `sys` . `sys_config`
CURRENT_SCHEMA: sys -- 当前表
OBJECT_TYPE: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
OBJECT_INSTANCE_BEGIN: NULL
MYSQL_ERRNO: 0
RETURNED_SQLSTATE: NULL
MESSAGE_TEXT: NULL
ERRORS: 0
WARNINGS: 0
ROWS_AFFECTED: 0
ROWS_SENT: 0
ROWS_EXAMINED: 0
CREATED_TMP_DISK_TABLES: 0
CREATED_TMP_TABLES: 0
SELECT_FULL_JOIN: 0
SELECT_FULL_RANGE_JOIN: 0
SELECT_RANGE: 0
SELECT_RANGE_CHECK: 0
SELECT_SCAN: 0
SORT_MERGE_PASSES: 0
SORT_RANGE: 0
SORT_ROWS: 0
SORT_SCAN: 0
NO_INDEX_USED: 0
NO_GOOD_INDEX_USED: 0
NESTING_EVENT_ID: 65
NESTING_EVENT_TYPE: TRANSACTION -- 当前事务类型
NESTING_EVENT_LEVEL: 0
STATEMENT_ID: 7822
1 row in set (0.00 sec)
查看性能信息常用的命令
哪类sql执行最多？？？？------分析用户操作和需求
mysql> select digest_text ,count_star ,first_seen ,last_seen from events_statements_summary_by_digest order by count_star desc \G
*************************** 1. row ***************************
digest_text: INSERT INTO `City` VALUES (...)
count_star: 4079
first_seen: 2021-09-03 18:18:06.959215
last_seen: 2021-09-03 18:18:07.665518
*************************** 2. row ***************************
digest_text: INSERT INTO `CountryLanguage` VALUES (...)
count_star: 984
first_seen: 2021-09-03 18:18:07.800364
last_seen: 2021-09-03 18:18:07.946546
*************************** 3. row ***************************
。。。。。。。。。。。。省略。。。。。。。。。。。。
查看sql平均响应时间---- 查看查询语句的执行时间效率
mysql> select digest_text ,avg_timer_wait from events_statements_summary_by_digest order by count_star desc \G
*************************** 1. row ***************************
digest_text: INSERT INTO `City` VALUES (...)
avg_timer_wait: 113300000
*************************** 2. row ***************************
digest_text: INSERT INTO `CountryLanguage` VALUES (...)
avg_timer_wait: 97500000
省略。。。。。。。。。
哪类 sql 排序记录最多 可分析用户喜爱
mysql> select digest_text ,sum_sort_rows from events_statements_summary_by_digest order by count_star desc \G
----省略 。。。。。。。。。。。。。。。。。。。。。。。。。。
*************************** 513. row ***************************
digest_text: SHOW CREATE TABLE `performance_schema` . `setup_consumers`
sum_sort_rows: 0
*************************** 514. row ***************************
digest_text: SELECT SCHEMA ( )
sum_sort_rows: 0
*************************** 515. row ***************************
digest_text: SELECT `empno` , `ename` , `job` , `sal` FROM `emp`
sum_sort_rows: 0
*************************** 516. row ***************************
digest_text: SELECT `digest_text` , `count_star` , `first_seen` , `last_seen` FROM `events_statements_summary_by_digest` ORDER BY `count_star` DESC
sum_sort_rows: 0
*************************** 517. row ***************************
digest_text: SELECT `digest_text` , `avg_timer_wait` FROM `events_statements_summary_by_digest` ORDER BY `count_star` DESC
sum_sort_rows: 516
517 rows in set (0.01 sec)
哪类sql扫描记录最多
select digest_text ,sum_rows_examined from events_statements_summary_by_digest order by count_star desc \G
*************************** 516. row ***************************
digest_text: SELECT `digest_text` , `count_star` , `first_seen` , `last_seen` FROM `events_statements_summary_by_digest` ORDER BY `count_star` DESC
sum_rows_examined: 0
*************************** 517. row ***************************
digest_text: SELECT `digest_text` , `avg_timer_wait` FROM `events_statements_summary_by_digest` ORDER BY `count_star` DESC
sum_rows_examined: 1032
*************************** 518. row ***************************
digest_text: SELECT `digest_text` , `sum_sort_rows` FROM `events_statements_summary_by_digest` ORDER BY `count_star` DESC
sum_rows_examined: 1034
518 rows in set (0.02 sec)
哪类sql使用临时表最多
mysql> select digest_text ,sum_created_tmp_tables,sum_created_tmp_disk_tables from events_statements_summary_by_digest order by count_star desc \G
sum_created_tmp_disk_tables: 0
*************************** 519. row ***************************
digest_text: SELECT `digest_text` , `sum_rows_examined` FROM `events_statements_summary_by_digest` ORDER BY `count_star` DESC
sum_created_tmp_tables: 0
sum_created_tmp_disk_tables: 0
*************************** 520. row ***************************
digest_text: SELECT * FROM `performance_schema` . `events_statements_summary_by_digest` LIMIT ?, ...
sum_created_tmp_tables: 0
sum_created_tmp_disk_tables: 0
*************************** 521. row ***************************
digest_text: SHOW CREATE TABLE `performance_schema` . `events_statements_summary_by_digest`
sum_created_tmp_tables: 0
sum_created_tmp_disk_tables: 0
521 rows in set (0.01 sec)
summary表提供所有事件的汇总信息。该组中的表以不同的方式汇总事件数据（如：按用户，按主机，按线程等等）。
select digest_text ,sum_created_tmp_tables,sum_created_tmp_disk_tables from events_statements_summary_by_digest order by count_star desc limit 1 \G
*************************** 1. row ***************************
digest_text: INSERT INTO `City` VALUES (...)
sum_created_tmp_tables: 0
sum_created_tmp_disk_tables: 0
1 row in set (0.10 sec)
mysql>
_current表中每个线程只保留一条记录，且一旦线程完成工作，该表中不会再记录该线程的事件信息，
mysql> SELECT * FROM events_waits_current limit 1\G
*************************** 1. row ***************************
THREAD_ID: 14
EVENT_ID: 10612
END_EVENT_ID: 10612
EVENT_NAME: wait/synch/mutex/innodb/dblwr_mutex
SOURCE: buf0dblwr.cc:396
TIMER_START: 14100886997260266
TIMER_END: 14100886997830206
TIMER_WAIT: 569940
SPINS: NULL
OBJECT_SCHEMA: NULL
OBJECT_NAME: NULL
INDEX_NAME: NULL
OBJECT_TYPE: NULL
OBJECT_INSTANCE_BEGIN: 2774696303920
NESTING_EVENT_ID: NULL
NESTING_EVENT_TYPE: NULL
OPERATION: lock
NUMBER_OF_BYTES: NULL
FLAGS: NULL
1 row in set (0.00 sec)
EVENT_NAME: wait/synch/mutex/innodb/dblwr_mutex表示
线程ID为14的线程正在等待innodb存储引擎的dblwr_mutex锁，这是innodb存储引擎的一个互斥锁，等待时间为569940皮秒
_history表中记录每个线程已经执行完成的事件信息，但每个线程的只事件信息只记录10条，再多就会被覆盖掉
SELECT THREAD_ID,EVENT_ID,EVENT_NAME,TIMER_WAIT FROM events_waits_history ORDER BY THREAD_ID limit 2;
+-----------+----------+----------------------------------------------------+------------+
| THREAD_ID | EVENT_ID | EVENT_NAME | TIMER_WAIT |
+-----------+----------+----------------------------------------------------+------------+
| 14 | 9551 | wait/synch/mutex/innodb/log_limits_mutex | 75026 |
| 14 | 9552 | wait/synch/mutex/innodb/buf_pool_flush_state_mutex | 291410 |
+-----------+----------+----------------------------------------------------+------------+
2 rows in set (0.02 sec)
等等主要是搞懂几表存放的信息，首先是表名，然后是表的内容；
小结------
1，开启性能监控细节的表--------setup_****有关的表
2，性能监控一般不会影响系统性能，
3，提高性能主要是从以下两个方面着手-----
减少执行次数----频率，
减少操作数量-----数量
真对性能优化的指标,市面上已经有很多软件可以查看数据库的性能数据-----
所以只要了解大概就可以了,我是说非运维工程师;
247
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
