pt-query-digest默认查询时间分布
# Query_time distribution
# 1us
# 10us #############################################
# 100us ################################################################
# 1ms ##########
# 10ms
# 100ms
# 1s
# 10s+
超过1ms上时间，区间太大了，可以做如下改进
# Query_time distribution
# 1us 0
# 10us ############################################# 31
# 100us ################################################################ 44
# 1ms ######## 6
# 5ms # 1
# 10ms 0
# 20ms 0
# 30ms 0
# 50ms 0
# 100ms 0
# 200ms 0
# 300ms 0
# 500ms 0
# 800ms 0
# 1s 0
# 10s+ 0
通过--review和--history命令，将分析的数据插入到数据中
Table: query_history Create Table: CREATE TABLE `query_history` ( `checksum` bigint(20) unsigned NOT NULL, `sample` text NOT NULL, `ts_min` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `ts_max` datetime NOT NULL DEFAULT '0000-00-00 00:00:00', `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 (`checksum`,`ts_min`,`ts_max`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1
mysql> show create table query_reviewG *************************** 1. row *************************** Table: query_review Create Table: CREATE TABLE `query_review` ( `checksum` bigint(20) unsigned NOT NULL, `fingerprint` text NOT NULL, `sample` text NOT NULL, `first_seen` datetime DEFAULT NULL, `last_seen` datetime DEFAULT NULL, `reviewed_by` varchar(20) DEFAULT NULL, `reviewed_on` datetime DEFAULT NULL, `comments` text, PRIMARY KEY (`checksum`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1