设为首页收藏本站

LinuxTone | 运维专家网论坛 - 最棒的Linux运维与开源架构技术交流社区!

 找回密码
 注册

QQ登录

只需一步,快速开始

#公告#抱歉,网站将关闭,不再开放。由于PC时代已远逝 。在这个后移动互联网时代,我们继续携手前行,保持对技术的热情。共同构建linuxtone知识星球欢迎加入,一起讨论技术、招聘人才、分享资源。请新老linuxtone人 扫码移步到 知识星球:linuxtone

学习是一种信仰!分享是一种快乐!能力= 心态 * 沟通 * 知识 (你的每一天需要正能量!)

 网站的发展需要你贡献一份力量!希望你能每天坚持看贴1小时,并回答网友的问题!祝你在浏览论坛的过程中取得进步!谢谢!linuxtone加油!大家加油! 友情提示: 你今天学习了吗?你今天进步了吗?少一点抱怨!多一点进步!Life is short ! Why not linuxtone ?  

网站的发展、感谢每位坛友的努力!

查看: 8905|回复: 12

[性能测试] mysql 数据库性能追踪与分析   [复制链接]

Rank: 8Rank: 8

签到
206
注册时间
2012-5-7
最后登录
2016-11-24
在线时间
78 小时
阅读权限
90
积分
21561
帖子
42
主题
4
精华
0
UID
19945
发表于 2012-8-3 23:44:32 |显示全部楼层
前提,由于数据库最近性能不稳定,经常内存吃紧,有爆库的危险呀。因为我受命去调查下原因,以下为我的工作报告,抛砖引玉。在和大家分享下的同时,希望也能听听大家的经验,那我就献丑了,呵呵。
      这是公司内部使用的数据库,但是受不住两三百的连接,真的很丢脸呀,居然让我搞得这么差劲。

1.mysql服务器访问速度慢,查看服务器的进程异常情况。top



2.服务器读写正常 iostat



3.内存(vmstat),硬盘读写(iostat)都正常,就是CPU(top)超高,系统CPU日志追踪(sar),系统在下午
三点几,CPU负载急剧上升。




4.由上可知,知道是mysql进程问题,连接超多呀。



5.连接mysql服务器客户端,show processlist,可以看到大量的连接。连接分析如下
说明现在服务器有238个连接,其实空的连接有188个,188中又有172 个是在打酱油的。
有56个线程正在处理,不过在wait for table.



6.排除NULL,现在了解服务器中忙碌的数据库,数据库networkresourcemanager很忙呀,waiting,checking,sending.操作集中




7.我们为分析下wait for table的线程的操作内容。这个很重要,实际影响到服务器性能的缘由。
说明线程正在对int_networkresourcemanager的figureline表进程操作,很忙呀,瓶颈在这里(wait for)。



8.以下为figureline的表结构情况,可以知道它是innodb存储引擎支持的表结构


#######################################################################
#######################################################################
#######################################################################
#######################################################################
my.cnf参数,读取参数值需要加大一倍,read_buffer_size/read_rnd_buffer_size是读取参数值,加大点。

说明连接失败情况,客户端非法中断连接次数,我们有必要查看错误日志,错误挺多。
到时再整理提交一份错误日志表单。
cat show\ status.txt  | grep -i aborted
| Aborted_clients                   | 2944        |
| Aborted_connects                  | 2252        |

前面都说了系统卡在查询,我们有必要启动慢查询日志找到瓶颈,有必要调试。
mysql> show variables like "%slow%";
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| log_slow_queries    | OFF                             |
| slow_launch_time    | 2                               |
| slow_query_log      | OFF                             |
| slow_query_log_file | /data/mysql/testmysql1-slow.log |
+---------------------+---------------------------------+

说明我们已经他们了14598个线程,现在有238个线程,正在运行的为67个。和前面对应,问题他们是查询类的,我们没有缓存呀。。
cat show\ status.txt  | grep  "Threads"
| Threads_cached                    | 0           |
| Threads_connected                 | 238         |
| Threads_created                   | 14598       |
| Threads_running                   | 67          |  

我们承诺有886个连接,现在最大用了391,说明服务器性能慢不是连接限制。
cat show\ status.txt  | grep -i connections
| Connections                       | 14599       |
| Max_used_connections              | 391         |
mysql> show variables like "%max_connection%";
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 886   |
+-----------------+-------+

这是表锁情况分析,居然存在5个表锁,要开慢查询调优。
cat show\ status.txt  | grep -i table_lock
| Table_locks_immediate             | 2568624     |
| Table_locks_waited                | 5           |

已经启动查询缓存,但是居然没有query_cache_size值,主要用于
查询,居然都没有,导致状态信息都没有查询信息。
cat show\ status.txt  | grep -i qcache
| Qcache_free_blocks                | 0           |
| Qcache_free_memory                | 0           |
| Qcache_hits                       | 0           |
| Qcache_inserts                    | 0           |
| Qcache_lowmem_prunes              | 0           |
| Qcache_not_cached                 | 0           |
| Qcache_queries_in_cache           | 0           |
| Qcache_total_blocks               | 0           |
show variables like "%query_cache%";
+------------------------------+---------+
| Variable_name                | Value   |
+------------------------------+---------+
| have_query_cache             | YES     |
| query_cache_limit            | 1048576 |
| query_cache_min_res_unit     | 4096    |
| query_cache_size             | 0       |
| query_cache_type             | ON      |
| query_cache_wlock_invalidate | OFF     |
+------------------------------+---------+

通过状态发现磁盘和内存对innodb的缓存根本就没有起作用,没数据呀。
通过变量信息,可以知道二进制缓存仅为32KB,混合类型日志,异步读入,
每个二进制日志文件可以达到1G多。
有必要将binlog_cache_size设置为32M.
mysql> show global status like "%binlog%";
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Binlog_cache_disk_use  | 0     |
| Binlog_cache_use       | 0     |
| Com_binlog             | 0     |
| Com_show_binlog_events | 0     |
| Com_show_binlogs       | 2     |
+------------------------+-------+
5 rows in set (0.00 sec)
mysql> show variables like "%binlog%";
+--------------------------------+----------------------+
| Variable_name                  | Value                |
+--------------------------------+----------------------+
| binlog_cache_size              | 32768                |
| binlog_format                  | MIXED                |
| innodb_locks_unsafe_for_binlog | OFF                  |
| max_binlog_cache_size          | 18446744073709547520 |
| max_binlog_size                | 1073741824           |
| sync_binlog                    | 0                    |
+--------------------------------+----------------------+
6 rows in set (0.00 sec)

原因:主要客户端不停地向服务器发出查询语句,而这些查询语句都是对整个数据表进行遍历处理,完成一次需要很多资源,而且数据库居然没有开启缓存,使得相同的工作重复无意义的执行,浪费系统资源。而服务器没有能力处理的请求则wait for队列中,用户体验极差。
最终的处理结果:
       根据我目前的了解和知识水平,我会将mysql的慢查询日志开启,找出相应的语句,再进行优化处理。
       主要是innodb类型数据库,但是查询缓存呀,二进制日志缓存呀,我都没有启动呀,通过增加缓存大小,减少对数据库频繁的读操作。
       连接数足矣,不过要添加超时机制,让超时的连接断开,不浪费系统资源。
请多多指教,谢谢。
现在我和群开发人员在一起工作,近朱者赤,学点开发也发吧。

Rank: 8Rank: 8

注册时间
2009-3-28
最后登录
2018-4-14
在线时间
171 小时
阅读权限
90
积分
28567
帖子
138
主题
7
精华
0
UID
2182
发表于 2012-8-3 23:57:01 |显示全部楼层
提示: 作者被禁止或删除 内容自动屏蔽

使用道具 举报

Rank: 8Rank: 8

注册时间
2011-9-16
最后登录
2019-3-21
在线时间
847 小时
阅读权限
90
积分
231961
帖子
2158
主题
2
精华
0
UID
15647
发表于 2012-8-4 08:08:01 |显示全部楼层
感谢楼主分享经验!

使用道具 举报

Rank: 8Rank: 8

注册时间
2012-3-15
最后登录
2016-7-14
在线时间
108 小时
阅读权限
90
积分
42372
帖子
16
主题
1
精华
0
UID
18703
发表于 2012-8-4 08:51:53 |显示全部楼层
谢谢分享,最近正在学习MySQL。

使用道具 举报

论坛元老

小运维——知我罪我,其惟UNIX-like

Rank: 8Rank: 8

注册时间
2010-11-12
最后登录
2016-8-4
在线时间
1122 小时
阅读权限
90
积分
91360
帖子
477
主题
21
精华
0
UID
11131
发表于 2012-8-4 10:24:19 |显示全部楼层
LZ可以试着把mysql进程的NICE 值调高如-5 可以缓解CPU压力(相对于mysqld),LZ的load值一点也不高,无压力可言,还有内存3G未用内存无压力,开慢查询时同时要开mysql 的debug 日志方便分析SQL语句(分析优化SQL语句让DBA与程序员去做吧你只是找出SQL慢的语句就可以)
天道酬勤——UNIX真美!GNU/Linux更美!
子曰:君子务本,本立而道生!

使用道具 举报

Rank: 8Rank: 8

注册时间
2012-5-7
最后登录
2016-11-24
在线时间
78 小时
阅读权限
90
积分
21561
帖子
42
主题
4
精华
0
UID
19945
发表于 2012-8-4 13:34:14 |显示全部楼层
lnfsddy 发表于 2012-8-4 10:24
LZ可以试着把mysql进程的NICE 值调高如-5 可以缓解CPU压力(相对于mysqld),LZ的load值一点也不高,无压力 ...

        非常感谢你的回答,你的建议对我很有帮助,谢谢。
现在我和群开发人员在一起工作,近朱者赤,学点开发也发吧。

使用道具 举报

Rank: 6Rank: 6

注册时间
2012-4-27
最后登录
2016-7-8
在线时间
38 小时
阅读权限
70
积分
7933
帖子
38
主题
3
精华
0
UID
19792
发表于 2012-8-22 10:51:35 |显示全部楼层
抱歉,您不能对发表于 4 小时前的帖子进行评分  ..



这是神马情况。。现在都是8月22日了。

使用道具 举报

Rank: 6Rank: 6

注册时间
2012-8-28
最后登录
2013-1-14
在线时间
18 小时
阅读权限
70
积分
4275
帖子
6
主题
0
精华
0
UID
35070
发表于 2012-10-10 09:40:08 |显示全部楼层
不错,学习啦!

使用道具 举报

Rank: 6Rank: 6

注册时间
2009-3-12
最后登录
2019-3-8
在线时间
43 小时
阅读权限
70
积分
8996
帖子
28
主题
1
精华
0
UID
1869
发表于 2012-11-1 14:47:29 |显示全部楼层
不错,这篇顶了。

使用道具 举报

Rank: 8Rank: 8

注册时间
2012-3-11
最后登录
2014-6-11
在线时间
79 小时
阅读权限
90
积分
20334
帖子
36
主题
11
精华
0
UID
18580
发表于 2012-11-5 20:10:29 |显示全部楼层
正常情况是不会有那么多连接的,我们的mysql是做在线交友的,正常情况也就30个以内,你注意多观察一下看那些语句有锁表,把那些比较突出的先优化掉,然后再开慢查询,详细的找找。推荐使用Navicat for MySQL  里面有个服务器监控,很好找哪些锁表的SQL。

使用道具 举报

Rank: 8Rank: 8

注册时间
2012-1-12
最后登录
2013-11-9
在线时间
93 小时
阅读权限
90
积分
22583
帖子
74
主题
0
精华
0
UID
17298
发表于 2012-11-29 10:09:20 |显示全部楼层
好,分析的不错,学习了。

使用道具 举报

Rank: 8Rank: 8

注册时间
2012-6-30
最后登录
2015-3-27
在线时间
126 小时
阅读权限
90
积分
23947
帖子
44
主题
0
精华
0
UID
28733
发表于 2013-6-6 13:25:42 |显示全部楼层
不错,学习下

使用道具 举报

Rank: 6Rank: 6

注册时间
2010-9-6
最后登录
2014-3-31
在线时间
50 小时
阅读权限
70
积分
6901
帖子
52
主题
3
精华
0
UID
8864
发表于 2014-3-31 16:47:56 |显示全部楼层
我只想说,你扯这么多,浪费时间,设置根本没啥问题。show processlist看看查询就知道了。query cache病不是说一定要开的,很多的情况都应该关闭。慢查询应该常开。看看慢日志,问题全解决

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

IT运维专家网感谢您的支持

合作联系: QQ:67888954/MSN:cnseek@msn.com/mail:netseek@linuxtone.org

Archiver|手机版|感谢所有关心和支持过LinuxTone的朋友们 转载本站内容请注明原作者名及出处 ( 京ICP备08103151 )   |

GMT+8, 2019-3-21 12:10 , Processed in 0.032457 second(s), 14 queries , Apc On.

Powered by Discuz! X2 Licensed

© 2001-2011 Comsenz Inc.

回顶部