1、分析慢查询日志
(1)慢查询日志特点与参数
写日志是顺序存储,对空间需求较大。
慢查日志 会记录所有符合条件的sql,包括回滚日志。
show_query_log: 启动停止慢查询日志 可以运行中更改 set global show_query_log=on
通过脚本定时开关
show_query_log_file :指定慢查日志的存储路径和文件 日志存储和数据存储分开存储。存储在不同的磁盘分区更好
long_query_time: 指定慢查日志sql执行时间的阈值 超过此值的查询会记录到日志中。单位是秒 100微秒 = 0.0001秒。默认10秒。
log_queries_not_using_indexes : 是否记录未使用索引的sql。即使查询执行的小于long_query_time ,只要没使用索引,就会被记录。会避免暂时数据量小执行可以接受,但是数据量大的时候将变慢的查询。
(2)分析工具
工具:
mysqldumpslow -s r -t 10 slow-mysql.log
-s order (c,t,l,r,at,al,ar)
按照哪种排序方式输出结果
c: 查询总次数
t:查询总时间
l:锁时间
r:总数据行
这些参数前边加a指的是平均。
-t :指的是前几条
其他工具
apt-get install percona-toolkit
pt-query-digest --explain h=127.0.0.1,u=root,p=p@ssW0rd slow-mysql.log
2、实时获取性能状态
可以查找Processlist 表。使用如下命令
select id,user,host,DB,command,time,state,info from information_schema.PROCESSLIST where time >=60;
3、mysql解析预处理参数
查询缓存是hash全值匹配。
query_cache_type设置查询缓存是否可用 不使用查询缓存 建议 off
query_cache_size 查询缓存的大小 建议0
4、确定查询处理各个阶段所消耗的时间
(1) 使用profile
set profiling = 1
启用之后会记录每次查询的时间
show profiles
查看第N个查询消耗的总时间的信息
show profile for query N
可以看到Sending data 用时最多
Sending data 通常可以使用索引来优化。
查看第N个cpu的使用信息
(2)使用performance_schema
performance_schema 是 mysql 5.5 引入的存储引擎
启用:
UPDATE `setup_instruments` SET `enabled` = 'YES', `TIMED` = 'YES' WHERE NAME LIKE
'stage%';
UPDATE `setup_consumers` SET `enabled` = 'YES' WHERE NAME LIKE
'events%';
可以分析mysql数据库中每一个线程的性能
查询mysql 性能的sql语句:
SELECT a.`THREAD_ID`, SQL_TEXT, c.EVENT_NAME,(c.TIMER_END-c.TIMER_START)/1000000000 AS DURATION
FROM events_statements_history_long a
JOIN threads b on a.`THREAD_ID`=b.`THREAD_ID`
JOIN events_stages_history_long c ON c.`THREAD_ID`=b.`THREAD_ID` AND c.`EVENT_ID` BETWEEN a.`EVENT_ID` AND a.`END_EVENT_ID`
ORDER BY a.`THREAD_ID`,c.EVENT_ID
5、大表处理
对于大表数据可以分批处理。
大表结构:因为改变表结构会锁表。可以通过下列方式完成大表结构的修改:
建立新表 新表结构就是需要的结构;原表增加触发器;原表数据拷贝到新表;同步完成对老表加一个排它锁;重新命名新表为老表的名字;删除重命名的老表。
使用如下命令即可
pt-online-schema-change --alter=”MODIFY c VARCHAR(150) NOT NULL DEFAULT ‘’ ” --user=root --password=PassWord D=dbname,t=tablename--charset=utf8 --excute