前提,由于数据库最近性能不稳定,经常内存吃紧,有爆库的危险呀。
因为我受命去调查下原因,以下为我的工作报告,抛砖引玉。在和大家分享下的同时,希望也能听听大家的经验。
这是公司内部使用的数据库,但是受不住两三百的连接,真的很丢脸呀,居然让我搞得这么差劲。
1.mysql服务器访问速度慢,查看服务器的进程异常情况。top
2.服务器读写正常 iostat
3.内存(vmstat),硬盘读写(iostat)都正常,就是CPU(top)超高,系统CPU日志追踪(sar),系统在下午
三点几,CPU负载急剧上升。
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类型数据库,但是查询缓存呀,二进制日志缓存呀,我都没有启动呀,通过增加缓存大小,减少对数据库频繁的读操作。
连接数足矣,不过要添加超时机制,让超时的连接断开,不浪费系统资源。
请多多指教,谢谢。
转载于:https://blog.51cto.com/teemomo/953248