MySql-CPU过高处理-开启MySQL慢查询日志
<span style="box-sizing: inherit; font-weight: bolder; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">开启MySQL慢查询日志</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"></span><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">sudo nano /etc/mysql/my.cnf</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">log_slow_queries = /var/log/mysql/slow.sql</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">long_query_time = 0.5</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">log-queries-not-using-indexes #不使用索引,这句可以不用</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">sudo service mysql restart</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">show variables like "%slow%"; 查看慢查询相关配置</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">SELECT SLEEP(1); 执行一个1秒的查询,上面定义了超过0.5秒的查询是慢查询,所以应该可以在慢查询日志里看到这条查询.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">show global status like '%slow%'; 查看一共执行了多少次慢查询,上面执行了1次,这里会增加1.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">mysqldumpslow /var/log/mysql/slow.sql > slow.sql</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">mysqldumpslow -a -s r -t 5 /var/log/mysql/slow.sql > slow.sql 返回最多行数(row)的前(top)5个查询.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">mysqldumpslow -a -s c -t 5 /var/log/mysql/slow.sql > slow.sql 按计数(count)顺序返回前(top)5个查询.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">日志的命名要注意/etc/logrotate.d/mysql-server的设置,否则可能影响logrotate对日志的归档.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="box-sizing: inherit; font-weight: bolder; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">MySQL query cache</span><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"> : 自动缓存SQL查询的结果,被缓存的SQL查询可以被反复地快速执行.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">cat /etc/mysql/my.cnf |grep query_cache</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">mysql -uroot -p -e "show variables" |grep query_cache</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">show variables like "%query_cache%";</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">写操作频繁,表经常变化,query cache就会经常被刷新(flush),这时MySQL性能反而会下降.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">所以说query cache有利有弊,应视情况合理实用.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"></span><span style="box-sizing: inherit; font-weight: bolder; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">MySQL5.6 表定义信息缓存和</span><span style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"></span><span style="box-sizing: inherit; font-weight: bolder; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">打开表的缓存数量默认值:</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">(你的这两个值都偏小)</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">table_definition_cache 1400</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">table_open_cache 2000</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">table_definition_cache 最小值可以设为400,此时mysqld内存占用为70MB,performance_schema_max_table_instances的值为445.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">table_definition_cache 默认值为1400,计算公式为400+(table_open_cache/2),此时mysqld内存占用为430MB左右.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">table_definition_cache是表定义信息缓存,用来存放表定义信息.当我们的MySQL中使用了较多的表的时候,此缓存无疑会提高对表定义信息的访问效率.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">注意,这里设置的是可以缓存的表定义信息的数目,而不是内存空间的大小.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">table_open_cache是打开表的缓存数量,不是定义内存的大小,而是定义可以缓存多少打开的表的文件句柄信息.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">如果定义的太小,那么MySQL在需要打开新表的时候就要不断的关闭已经打开的表和打开此次需要打开的表,性能会受到影响.</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">查看size和cache相关值:</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">mysql -uroot -p -e "show variables like '%size%'"</span><br style="box-sizing: inherit; color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;"><span style="color: rgba(0, 0, 0, 0.87); font-family: "PingFang SC", "Helvetica Neue", "Microsoft YaHei UI", "Microsoft YaHei", "Noto Sans CJK SC", Sathu, EucrosiaUPC, Arial, Helvetica, sans-serif;">mysql -uroot -p -e "show variables like '%cache%'"</span><p></p>
页:
[1]