【分享】mysql 数据库性能追踪与分析

billicon

前提，由于数据库最近性能不稳定，经常内存吃紧，有爆库的危险呀。因为我受命去调查下原因，以下为我的工作报告，抛砖引玉。在和大家分享下的同时，希望也能听听大家的经验,那我就献丑了，呵呵。
      这是公司内部使用的数据库，但是受不住两三百的连接，真的很丢脸呀，居然让我搞得这么差劲。

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



2.服务器读写正常　iostat


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



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


５.连接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)。


８.以下为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,混合类型日志，异步读入，
每个二进制日志文件可以达到１Ｇ多。
有必要将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类型数据库，但是查询缓存呀，二进制日志缓存呀，我都没有启动呀，通过增加缓存大小，减少对数据库频繁的读操作。
       连接数足矣，不过要添加超时机制，让超时的连接断开，不浪费系统资源。
请多多指教，谢谢。

cenalulu

感谢分享，很棒的一个完整问题定位过程。
不过有几点可以改进下：
当问题已经定位到CPU瓶颈时，第一件事情应该是定位慢查询 ，而不是增加缓存。
因为CPU消耗高，几乎是一定可以断定是有慢查询。而缓存并不一定能解决所有慢查询。
通过tcpdump + pt-query-digest 基本上就可以追踪到问题SQL。
直接改写SQL，优化效果会更明显

wenhq

不错啊，LZ支持！！！