mysql 数据库性能追踪与分析

来源:本站原创 Linux 超过1,461 views围观 0条评论

前提,由于数据库最近性能不稳定,经常内存吃紧,有爆库的危险呀。因为我受命去调查下原因,以下为我的工作报告,抛砖引玉。在和大家分享下的同时,希望也能听听大家的经验,那我就献丑了,呵呵。
这是公司内部使用的数据库,但是受不住两三百的连接,真的很丢脸呀,居然让我搞得这么差劲。
1.mysql服务器访问速度慢,查看服务器的进程异常情况。top
clip_image002
clip_image003
2.服务器读写正常 iostat
clip_image005
3.内存(vmstat),硬盘读写(iostat)都正常,就是CPU(top)超高,系统CPU日志追踪(sar),系统在下午
三点几,CPU负载急剧上升。
clip_image007
4.由上可知,知道是mysql进程问题,连接超多呀。
clip_image009
5.连接mysql服务器客户端,show processlist,可以看到大量的连接。连接分析如下
说明现在服务器有238个连接,其实空的连接有188个,188中又有172 个是在打酱油的。
有56个线程正在处理,不过在wait for table.
clip_image011
6.排除NULL,现在了解服务器中忙碌的数据库,数据库networkresourcemanager很忙呀,waiting,checking,sending.操作集中
clip_image013
7.我们为分析下wait for table的线程的操作内容。这个很重要,实际影响到服务器性能的缘由。
说明线程正在对int_networkresourcemanager的figureline表进程操作,很忙呀,瓶颈在这里(wait for)。
clip_image015
8.以下为figureline的表结构情况,可以知道它是innodb存储引擎支持的表结构
clip_image017
#######################################################################
#######################################################################
#######################################################################
#######################################################################
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类型数据库,但是查询缓存呀,二进制日志缓存呀,我都没有启动呀,通过增加缓存大小,减少对数据库频繁的读操作。
连接数足矣,不过要添加超时机制,让超时的连接断开,不浪费系统资源。
请多多指教,谢谢。

文章出自:CCIE那点事 http://www.jdccie.com/ 版权所有。本站文章除注明出处外,皆为作者原创文章,可自由引用,但请注明来源。 禁止全文转载。
本文链接:http://www.jdccie.com/?p=1508转载请注明转自CCIE那点事
如果喜欢:点此订阅本站
上篇文章:
  • 相关文章
  • 为您推荐
  • 各种观点

暂时还木有人评论,坐等沙发!
发表评论

您必须 [ 登录 ] 才能发表留言!