数据库优化的指标------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,提高性能主要是从以下两个方面着手-----
减少执行次数----频率,
减少操作数量-----数量

真对性能优化的指标,市面上已经有很多软件可以查看数据库的性能数据-----

在这里插入图片描述在这里插入图片描述所以只要了解大概就可以了,我是说非运维工程师;

  • 1
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 打赏
    打赏
  • 1
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论 1
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

CodeMartain

祝:生活蒸蒸日上!

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付:¥1
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值