mysql 系统信息采集和SQL追踪

1. mysql 系统信息采集和SQL追踪

目录

• 1. mysql 系统信息采集和SQL追踪
  • 1.1. mysql的存储过程对sql和数据库信息的采集
    • 1.1.1. msyql整体性能的诊断报告 diagnostics()
    • 1.1.2. 跟踪某一条sql的执行信息 ps_trace_statement_digest()
    • 1.1.3. statement_performance_analyzer()
    • 1.1.4. 跟踪某个线程的执行过程 ps_trace_thread()

1.1. mysql的存储过程对sql和数据库信息的采集

mysql提供了以下几个存储过程可以用来追踪某一个sql执行的情况，或者生成系统的统计信息报告

• diagnostics()
• ps_trace_statement_digest()
• statement_performance_analyzer()
• ps_trace_thread()

1.1.1. msyql整体性能的诊断报告 diagnostics()

diagnostics() 存储过会生成一个关于当前msyql整体性能的诊断报告

eg： (采集60s的信息，中间会等待30s)

tee diagnostics.log
call sys.diagnostics(null,null,'current');
notee;

1.1.2. 跟踪某一条sql的执行信息 ps_trace_statement_digest()

在工作中如果发现某一条sql执行时间比较长，或者觉得性能又问题，那么久可以使用下面的方式获取这条sql的摘要信息：
ps_trace_statement_digest()存储可以根据提供的SQL语句摘要哈希值跟踪收集这些sql语句的执行过程中的性能诊断信息
eg：在60秒内跟踪指定sql语句，每0.1秒收集一次性能诊断信息。

set @digest=statement_digest('select * from test where id=23');

跟踪60s，每0.1s跟踪一次，最后两个true表示重置状态变量，和自动激活需要的消费者
call sys.ps_trace_statement_digest(@digest,60,0.1,true,true); 

1.1.3. statement_performance_analyzer()

可以生产当前MYSQL实例中正在运行的SQL语句的两个快照，并对比这两个快照，生产增量报告

1.首先，先关闭掉当前性能信息的收集，这样可以避免对我们收集的信息的一些干扰
call sys.ps_setup_disable_thread(connection_id());
2.然后创建一个存放收集的性能信息的数据库：
create database if not exists analysisdb;
3.创建一个临时表存放快照
call sys.statement_performance_analyzer('create_tmp','analysisdb.tmp_ana',null);
4.生成初始化快照
call sys.statement_performance_analyzer('snapshot',null,null);
5.把生成的初始化快照放到临时表里面
call sys.statement_performance_analyzer('save','analysisdb.tmp_ana',null);
6.休眠一分钟
do sleep(60);
7.然后给系统一些压力
mysqlslap -hlocalhost -uroot -proot --socket /data/mysql/data3307/mysql3307.sock --concurrency=100,500 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000


8.生产快照
call sys.statement_performance_analyzer('snapshot',null,null);

9.
call sys.statement_performance_analyzer('delta','analysisdb.tmp_ana','analysis');


10.收集完成之后清理相关表格
call sys.statement_performance_analyzer('cleanup',null,null);

drop temporary table analysisdb.tmp_ana;

11.激活对当前会话性能信息的收集(生成的是图片报告)
call sys.ps_setup_enable_thread(connection_id());

1.1.4. 跟踪某个线程的执行过程 ps_trace_thread()

可以跟踪某个线程的执行过程，把这个线程执行的所有sql语句性能信息都记录下来，并输出报告。
这个存储过程使用于执行存储过程或者多个sql语句的线程。
eg：
采用当前的配置启动对51号线程的跟踪，跟踪60s，没收集一次性能信息，生产性能报告文件：
call sys.ps_trace_thread(51.'/tmp/td_51.dot',null,null,true,false,false);

1.查询需要跟踪的线程ID
select ps_current_thread_id();

2.查看安全文件现在
show variables like 'secure_f%';

3.调用存储过程，数据报告
跟踪51号线程，输出到/tmp目录下，文件名按照时间命名，两个null，第一个表示收集时间，默认是60S，第二个是
null表示收集时间的间隔，默认是1s，后面的true表示收集性能信息之前是否重置性能数据，
第二个ture,是否激活所有用到的消费者，false表示是否激活debug状态
call sys.ps_trace_thread(57,concat('/tmp/thread-',replace(now(),' ','-'),'.dot'),null,null,true,true,false);

4.在当前线程上运行一些负载
mysqlslap -hlocalhost -uroot -proot --socket /data/mysql/data3307/mysql3307.sock --concurrency=100,500 --iterations=1 --auto-generate-sql --auto-generate-sql-load-type=mixed --auto-generate-sql-add-autoincrement --engine=innodb --number-of-queries=5000