博客园  :: 首页  :: 新随笔  :: 联系 :: 订阅 订阅  :: 管理

mysql优化案例

Posted on 2017-04-06 09:17  moss_tan_jun  阅读(147)  评论(0编辑  收藏  举报

今天发现网站页面打开非常慢,对处理过程简单记录了一下
http://weibo.com/formysql?is_hot=1

找问题

 

首先登录服务器使用 top 查看当前进程信息,发现排名第一的是 mysql,占用 cpu 达到了 100% 以上,这就明确了是 mysql 的问题

登录 mysql,使用 show processlist 查看下当前执行状态,发现了大量 LOCK 操作,也有多个 Copying to tmp table 的操作,说明有 sql 出现了问题,操作过于复杂,对临时表使用频繁,把其他操作阻塞了

解决

找到了问题后,把处理方向确定为 检查和修改配置、sql优化

(1)修改mysql配置

临时表

既然涉及了到了临时表,就先查看下目前临时表的信息

查看临时表的使用状态

show global status like 'created_tmp%';

发现 created_tmp_disk_tables 值过高,需要增加此值

再看一下现在临时表的大小

show variables like '%tmp_table_size%';

在现在值的基础上增加一些,重新设置临时表大小

线程缓存数

看当前线程情况

show global status like 'Thread%';

发现 threads_created 的值过大,表明MySQL服务器一直在创建线程

查看当前值

show variables like 'thread_cache_size';

此参数需要调高

打开表数量

查看打开表的情况

show global status like 'open%tables%';

发现 opened_tables 数量过大,说明 table_cache 的值可能太小

查看当前值

show variables like 'table_cache';

此参数需要调高

最大连接数

查看当前允许的最大连接数

show variables like 'max_connections';

查看服务器连接数的峰值

show global status like 'Max_used_connections';

峰值还没到最大限制,不需要修改

 

join buffer 和 sort buffer

 

查看现有值

 

SELECT @@sort_buffer_size;

SELECT @@join_buffer_size;

 

是默认值,需要修改

 

修改配置

 

确定了要修改的参数后,修改 my.cnf ,例如

 

table_cache = 64
sort_buffer_size = 8M
join_buffer_size = 4M
thread_cache_size = 300
thread_concurrency = 8
tmp_table_size = 246M


(2)sql优化

从 show processlist 结果集中找出主要的复杂语句,对其进行 explain 和 profile 分析

进行索引优化,把复杂的sql 根据业务拆分为多个小的sql


以上过程完成后,mysql已经恢复正常状态,后期再对其他sql进行优化和缓存处理