通过performance schema收集慢查询
2024-09-05 06:51:15
MySQL5.6起performance schema自动开启，里面涉及记录 statement event的表
mysql> show tables like '%statement%';
+----------------------------------------------------+
| Tables_in_performance_schema (%statement%) |
+----------------------------------------------------+
| events_statements_current |
| events_statements_history |
| events_statements_history_long |
| events_statements_summary_by_account_by_event_name |
| events_statements_summary_by_digest |
| events_statements_summary_by_host_by_event_name |
| events_statements_summary_by_program |
| events_statements_summary_by_thread_by_event_name |
| events_statements_summary_by_user_by_event_name |
| events_statements_summary_global_by_event_name |
| prepared_statements_instances |
+----------------------------------------------------+
rows in set (0.00 sec)
statement event 的设置
详细参考官方文档：https://dev.mysql.com/doc/refman/5.6/en/performance-schema-statement-tables.ht
server端发生的所有的statement都会记录，当前未结束的会话发生的statement记录在表events_statements_current，已结束的会话线程发生的statement记录在events_statements_history，此表有大小限制，默认是100，达到限制会删除老的statement
设置记录哪些类型的statement，默认设置为所有的类型
mysql> SELECT * FROM setup_instruments WHERE NAME LIKE 'statement/%';
+---------------------------------------------+---------+-------+
| NAME | ENABLED | TIMED |
+---------------------------------------------+---------+-------+
| statement/sql/select | YES | YES |
| statement/sql/create_table | YES | YES |
| statement/sql/create_index | YES | YES |
...
| statement/sp/stmt | YES | YES |
| statement/sp/set | YES | YES |
| statement/sp/set_trigger_field | YES | YES |
| statement/scheduler/event | YES | YES |
| statement/com/Sleep | YES | YES |
| statement/com/Quit | YES | YES |
| statement/com/Init DB | YES | YES |
...
| statement/abstract/Query | YES | YES |
| statement/abstract/new_packet | YES | YES |
| statement/abstract/relay_log | YES | YES |
+---------------------------------------------+---------+-------+
关于对statement event相关表的设置，MySQL5.6 默认events_statements_history关闭，MySQL5.7开启
mysql> SELECT * FROM setup_consumers WHERE NAME LIKE '%statements%';
+--------------------------------+---------+
| NAME | ENABLED |
+--------------------------------+---------+
| events_statements_current | YES |
| events_statements_history | NO |
| events_statements_history_long | NO |
| statements_digest | YES |
+--------------------------------+---------+
如果通过pmm来收集慢查询，其查询的是表events_statements_history_long，MySQL5.6需要启用此表
启用events_statements_history
UPDATE setup_consumers SET ENABLED = 'NO'
WHERE NAME LIKE 'events_statements_history';
最新文章
- ZKWeb网页框架1.3正式发布
- JavaWeb-spring
- 1.5 基础知识——GP2.3 提供资源(Resources) 与 GP2.4 分配职责(Responisbility)
- asp.net接收ajax请求参数时为空的现象
- Hibernate单向一对多对象关系模型映射
- windows下codelite的使用
- Windows上python开发--2安装django框架
- window 配置 sendmail
- auto and static key words
- [置顶] SPL讲解(4)--Criteria操作篇
- Spark Streaming 调优指南
- Java并发之线程中断
- JS 遍历对象 jQuery遍历对象
- tomcat的systemctl启动脚本
- Java SE之反射技术[Class](三)
- discuz财付通也阵亡了
- nwjs 实现的 分块上传
- pandas 操作 excel
- iOS彩票项目--第三天，搭建竞技场和发现，搭建幸运选号和我的彩票界面
- Angular面试题二