mysql查询优化~ 慢日志的记录细节

mysql慢日志详解

一 参数
    slow_query_log 是否打开慢日志 默认为1
    slow_query_log_file 设置慢日志的具体路径和文件 默认为 datadir/slow.log
    long_query_time 语句执行时间记录阈值,默认1S 
    min_examined_row_limit 扫描行数阈值,默认为0
    log_queries_not_using_indexes=1的时候,只要是没有用到索引的语句,一定会记录,执行级别高于时间
    log_throttle_queries_not_using_indexes 配合上面参数,限制每分钟的无索引sql记录,防止文件过大
    long_slow_admin_statements　记录那些慢的optimize table，analyze table和alter table语句

二 状态变量
   show global status like 'slow_queries'; 统计慢日志的生成总量,可以根据这个值制作曲线图
三 细节说明
  1 等待锁的时间会统计到mysql慢日志里么
     1 如果是MDL锁等待不会记录等待的mysql语句
     2 如果是FTWRDL操作,等待锁的mysql语句会记录到慢日志里
    3 在高并发情况下由于thread_concurrency太小导致无法进入innodb层,同样会记录到慢日志.(在这种情况下动态调整下即可,此参数已在5.7移除) innodb_thread_concurrency 代替这个参数,默认并不会限制
 2 为什么单纯调整了慢日志时间,却不生效
   因为调整的是全局变量,全局变量对于PHP等短链接是即时获取的,对于java等长链接应用必须释放重连才会生效,可以先关闭慢日志再打开进行尝试
3 一般情况下满足什么条件会记录慢日志
  1 扫描行数>=min_examined_row_limit 2执行时间>=long_query_time
4 mysql的慢日志记录方式
  1 mysql.slow_log(csv引擎) 2 slow.log(多采用后者)

四 mysql慢日志切割方案
 1 保留原文件名
   每一段时间进行cp cp -rp slow.log slow_datatime.log
   每天cat /dev/null > slow.log 进行清空
 2 不保留原文件名
   每一段时间进行 新slow.log的生成设置
   tmp_log=`$mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "select concat('$slowquery_dir','slowquery_',date_format(now(),'%Y%m%d%H'),'.log');"|grep log|sed -n -e '2p'`
   $mysql_client -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "set global slow_query_log=1;set global long_query_time=$slowquery_long_time;"
 3 设置中央日志服务器,进行统一收集,采用rsync,以IP地址为目录
五 分析
  1 采用 ELK 在中央日志服务器进行分析日志(推荐)
  2 采用 pt-query-disgist 在中央日志服务器进行分析日志并插入数据库的库表
六 pt-quer-disist利用
  1 远程用户必须有建表权限
  2 关键参数
   --review h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review #DB配置
   --history h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review_history #DB配置
    --no-report --limit=100%
    --filter=" $event->{add_column} = length($event->{arg}) and $event->{hostname}=$hostname " # #filter
    $slowquery_file #慢日志文件
  3 相关问题
    1 pt-query-disgist本身不包含IP地址(需要改造)
    2 pt-query-disgist 的history表必须定期清理,否则一旦数据量大就会出现查询性能问题(本人就遇到过此问题)

七   pt-query-digst远程收集

  1 重建history 表

  

CREATE TABLE `mysql_slow_query_review_history` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`hostname_max` varchar(100) NOT NULL DEFAULT '0',
`db_max` varchar(100) DEFAULT NULL,
`user_max` varchar(100) DEFAULT NULL,
`checksum` bigint(20) unsigned NOT NULL,
`sample` text NOT NULL,
`ts_min` datetime NOT NULL,
`ts_max` datetime NOT NULL,
`ts_cnt` float DEFAULT NULL,
`Query_time_sum` float DEFAULT NULL,
`Query_time_min` float DEFAULT NULL,
`Query_time_max` float DEFAULT NULL,
`Query_time_pct_95` float DEFAULT NULL,
`Query_time_stddev` float DEFAULT NULL,
`Query_time_median` float DEFAULT NULL,
`Lock_time_sum` float DEFAULT NULL,
`Lock_time_min` float DEFAULT NULL,
`Lock_time_max` float DEFAULT NULL,
`Lock_time_pct_95` float DEFAULT NULL,
`Lock_time_stddev` float DEFAULT NULL,
`Lock_time_median` float DEFAULT NULL,
`Rows_sent_sum` float DEFAULT NULL,
`Rows_sent_min` float DEFAULT NULL,
`Rows_sent_max` float DEFAULT NULL,
`Rows_sent_pct_95` float DEFAULT NULL,
`Rows_sent_stddev` float DEFAULT NULL,
`Rows_sent_median` float DEFAULT NULL,
`Rows_examined_sum` float DEFAULT NULL,
`Rows_examined_min` float DEFAULT NULL,
`Rows_examined_max` float DEFAULT NULL,
`Rows_examined_pct_95` float DEFAULT NULL,
`Rows_examined_stddev` float DEFAULT NULL,
`Rows_examined_median` float DEFAULT NULL,
`Rows_affected_sum` float DEFAULT NULL,
`Rows_affected_min` float DEFAULT NULL,
`Rows_affected_max` float DEFAULT NULL,
`Rows_affected_pct_95` float DEFAULT NULL,
`Rows_affected_stddev` float DEFAULT NULL,
`Rows_affected_median` float DEFAULT NULL,
`Rows_read_sum` float DEFAULT NULL,
`Rows_read_min` float DEFAULT NULL,
`Rows_read_max` float DEFAULT NULL,
`Rows_read_pct_95` float DEFAULT NULL,
`Rows_read_stddev` float DEFAULT NULL,
`Rows_read_median` float DEFAULT NULL,
`Merge_passes_sum` float DEFAULT NULL,
`Merge_passes_min` float DEFAULT NULL,
`Merge_passes_max` float DEFAULT NULL,
`Merge_passes_pct_95` float DEFAULT NULL,
`Merge_passes_stddev` float DEFAULT NULL,
`Merge_passes_median` float DEFAULT NULL,
`InnoDB_IO_r_ops_min` float DEFAULT NULL,
`InnoDB_IO_r_ops_max` float DEFAULT NULL,
`InnoDB_IO_r_ops_pct_95` float DEFAULT NULL,
`InnoDB_IO_r_ops_stddev` float DEFAULT NULL,
`InnoDB_IO_r_ops_median` float DEFAULT NULL,
`InnoDB_IO_r_bytes_min` float DEFAULT NULL,
`InnoDB_IO_r_bytes_max` float DEFAULT NULL,
`InnoDB_IO_r_bytes_pct_95` float DEFAULT NULL,
`InnoDB_IO_r_bytes_stddev` float DEFAULT NULL,
`InnoDB_IO_r_bytes_median` float DEFAULT NULL,
`InnoDB_IO_r_wait_min` float DEFAULT NULL,
`InnoDB_IO_r_wait_max` float DEFAULT NULL,
`InnoDB_IO_r_wait_pct_95` float DEFAULT NULL,
`InnoDB_IO_r_wait_stddev` float DEFAULT NULL,
`InnoDB_IO_r_wait_median` float DEFAULT NULL,
`InnoDB_rec_lock_wait_min` float DEFAULT NULL,
`InnoDB_rec_lock_wait_max` float DEFAULT NULL,
`InnoDB_rec_lock_wait_pct_95` float DEFAULT NULL,
`InnoDB_rec_lock_wait_stddev` float DEFAULT NULL,
`InnoDB_rec_lock_wait_median` float DEFAULT NULL,
`InnoDB_queue_wait_min` float DEFAULT NULL,
`InnoDB_queue_wait_max` float DEFAULT NULL,
`InnoDB_queue_wait_pct_95` float DEFAULT NULL,
`InnoDB_queue_wait_stddev` float DEFAULT NULL,
`InnoDB_queue_wait_median` float DEFAULT NULL,
`InnoDB_pages_distinct_min` float DEFAULT NULL,
`InnoDB_pages_distinct_max` float DEFAULT NULL,
`InnoDB_pages_distinct_pct_95` float DEFAULT NULL,
`InnoDB_pages_distinct_stddev` float DEFAULT NULL,
`InnoDB_pages_distinct_median` float DEFAULT NULL,
`QC_Hit_cnt` float DEFAULT NULL,
`QC_Hit_sum` float DEFAULT NULL,
`Full_scan_cnt` float DEFAULT NULL,
`Full_scan_sum` float DEFAULT NULL,
`Full_join_cnt` float DEFAULT NULL,
`Full_join_sum` float DEFAULT NULL,
`Tmp_table_cnt` float DEFAULT NULL,
`Tmp_table_sum` float DEFAULT NULL,
`Tmp_table_on_disk_cnt` float DEFAULT NULL,
`Tmp_table_on_disk_sum` float DEFAULT NULL,
`Filesort_cnt` float DEFAULT NULL,
`Filesort_sum` float DEFAULT NULL,
`Filesort_on_disk_cnt` float DEFAULT NULL,
`Filesort_on_disk_sum` float DEFAULT NULL,
PRIMARY KEY (id,ts_min),
UNIQUE KEY `hostname_max` (`hostname_max`,`checksum`,`ts_min`,`ts_max`),
KEY `idx_checksum` (`checksum`) USING BTREE,
KEY `idx_query_time_max` (`Query_time_max`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2 脚本插入

   核心代码

$pt_query_digest --user=$lepus_db_user --password=$lepus_db_password --port=$lepus_db_port --review h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review --history h=$lepus_db_host,D=$lepus_db_database,t=mysql_slow_query_review_history --no-report --limit=100% --filter="$event->{Bytes} = length($event->{arg}) and $event->{hostname}="$hostname" " $slowquery_file >/tmp/lepus_slowquery.log  

3 补充

 1 如果想新增字段 必须写  $event->{hostname}="$hostname"  类似  hostname为主机名

  2 数据库建表 必须以 column_max为字段名,切记

五 慢日志分析角度

     1 慢日志的类型->(增删查改出现占比)

     2 慢日志Query_time和Rows_examined->(自己设定阈值出现占比)

     3 慢日志切割每小时的大小对比->(对比出现故障的时间点,缩小查询范围)

     4 同一种类sql语句出现的频率->(条件不同,但是模具相同)