山重水复疑无路,柳暗花明又一村

原创 2017年08月31日 23:17:42

同事报告说有个cdb mysql实例最近很慢,写入速度巨慢,而且是间歇性的有的时候每隔7到8分钟就卡一会,有的时候每隔12分钟就卡一会,问他们是否有定时任务在拉数据?他们说没有。

那是否有很多比较慢的sql把io资源消耗光了呢,去看慢查询记录,结果发现一条select都没有,反而是有很多insert语句,见鬼啦,这咋回事呢?


慢查询有很多记录,如下所示,insert on duplicate key update,粗粗一看,肯定是on duplicate key update的问题,如下:

# User@Host: hsh_ext[hsh_ext] @  [devtest.yikan.com]  Id: 37459
# Query_time: 1.170256  Lock_time: 0.000118 Rows_sent: 0  Rows_examined: 0
SET timestamp=1504065495;
/*id:57539043*/insert into hy_deive(record_time, platform, device_id,
    install_id, device_token, push_enabled,
    `uid`, model, app_version, is_login, device_type, created_at,
    updated_at)
    values

      (
      1504065494, 'android', '863049030002995',
      '417e03c9-b879-4741-86b6-beb8c1f42497', 'Anj6kMy77g-2sKlb7idPuxAQ58eXdE_JILDvT-xITBfb', 0,
      4234883169, 'OPPO', '3.36.2', 1, 'umeng',
      1504065494, 1504065494
      )
     , 
      (
      1504065494, 'android', '863049030002995',
      '417e03c9-b879-4741-86b6-beb8c1f42497', 'F5nrlikA1gCLSrLZ7Xby1ASn+fXqSJZ3xATxvkJtXzU=', 0,
      4234883169, 'OPPO', '3.36.2', 1, 'xiaomi',
      1504065494, 1504065494
      )
     , 
      (
      1504065494, 'android', '863049030002995',
      '417e03c9-b879-4741-86b6-beb8c1f42497', '0863049030002995200000184200CN01', 0,
      4234883169, 'OPPO', '3.36.2', 1, 'huawei',
      1504065494, 1504065494
      )

    on duplicate key update
    record_time = IF(record_time > values(record_time), record_time, values(record_time)),
    platform = IF(record_time > values(record_time), platform, values(platform)),
    install_id = IF(record_time > values(record_time), install_id, values(install_id)),
    device_token = IF(record_time > values(record_time), device_token, values(device_token)),
    push_enabled = IF(record_time > values(record_time), push_enabled, values(push_enabled)),
    model = IF(record_time > values(record_time), model, values(model)),
    app_version = IF(record_time > values(record_time), app_version, values(app_version)),
    is_login = IF(record_time > values(record_time), is_login, values(is_login)),
    updated_at = IF(record_time > values(record_time), updated_at, values(updated_at));



但是实际上,准备2条无用的insert into … values… on duplicate key update …..,很快就执行完了,不到0.01s,那为啥那个时候,还有那么多的慢查询记录呢?


去查看了cdb的监控记录,select、udpate、insert没有啥间隙性的尖刀出现,虽然有起伏有上升空间,但是都比较平稳,没有尖刀,大家看下面的图L
这里写图片描述

这里写图片描述



想到既然是insert语句,那么就去看binlog日志吧,看下所有的binlog日志,看看那个卡的时间点,到底都执行了些啥操作呢?


结果一看binlog列表,发现binlog每隔8分钟就会flush下,而这个flush的时间和慢查询的时间正好吻合。

binlog日志生成时间:
这里写图片描述


慢查询记录时间:
这里写图片描述


OK,那么问题很明显了,binlog日志太大,flush的时候导致binlog写入时间变慢,因为要写入新的binlog,需要时间。解决方案就是调整binlog最大值,将1G降低到128M。

mysql> show variables like 'max_binlog_size';
+-----------------+------------+
| Variable_name   | Value      |
+-----------------+------------+
| max_binlog_size | 1073741824 |
+-----------------+------------+
1 row in set (0.01 sec)

mysql> select 128*1024*1024;
+---------------+
| 128*1024*1024 |
+---------------+
|     134217728 |
+---------------+
1 row in set (0.01 sec)

mysql> set global max_binlog_size=134217728;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'max_binlog_size';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| max_binlog_size | 134217728 |
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> 

之后观察了3个小时,再也没有比较慢的insert语句了,而且开发那么反应这段时间也基本没有卡顿的现象了,事情暂时解决了,告一段落啦。


反思,互联网场景中,max_binlog_size值确实不宜过大,这点需要谨记。



问题扩展:

查看当前正在使用的binlog缓存情况:

MySQL:(none) 13:07:41> show global status like 'bin%';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| Binlog_cache_disk_use      | 1335001   |
| Binlog_cache_use           | 264238120 |
| Binlog_stmt_cache_disk_use | 0         |
| Binlog_stmt_cache_use      | 33        |
+----------------------------+-----------+
4 rows in set (0.00 sec)

MySQL:(none) 13:07:46> show global status like 'bin%';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| Binlog_cache_disk_use      | 1335006   |
| Binlog_cache_use           | 264240359 |
| Binlog_stmt_cache_disk_use | 0         |
| Binlog_stmt_cache_use      | 33        |
+----------------------------+-----------+
4 rows in set (0.00 sec)

MySQL:(none) 13:07:47> 
MySQL:(none) 13:07:48> show global status like 'bin%';
+----------------------------+-----------+
| Variable_name              | Value     |
+----------------------------+-----------+
| Binlog_cache_disk_use      | 1335428   |
| Binlog_cache_use           | 264437761 |
| Binlog_stmt_cache_disk_use | 0         |
| Binlog_stmt_cache_use      | 33        |
+----------------------------+-----------+
4 rows in set (0.00 sec)

MySQL:(none) 13:09:28>


查看binlog的cache设置:

MySQL:(none) 13:10:58> show variables like '%binlog_cache%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| binlog_cache_size     | 32768                |
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+
2 rows in set (0.00 sec)

MySQL:(none) 13:11:13> 


binlog_cache_size:

为每个session 分配的内存,在事务过程中用来存储二进制日志的缓存。binlog_cache_disk_use表示因为我们binlog_cache_size设计的内存不足导致缓存二进制日志用到了临时文件的次数;binlog_cache_use表示 用binlog_cache_size缓存的次数;当对应的Binlog_cache_disk_use 值比较大的时候而且Binlog_cache_use也比较大的时候,我们可以考虑适当的调高 binlog_cache_size 对应的值


进一步分析:
我们这里可以看到Binlog_cache_disk_use已经是1.27M,而且Binlog_cache_use值为264437761表示已经执行了2亿多次了,而且这2个值一直在变大,就表名,binlog_cache_size远远不够用,所以这里就可以提高binlog_cache_size的值了。

MySQL:(none) 13:40:08> set global binlog_cache_size=8388608;
Query OK, 0 rows affected (0.00 sec)

MySQL:(none) 13:40:22> 


引申下max_binlog_cache_size:

max_binlog_cache_size 表示的是binlog 能够使用的最大cache 内存大小,当我们执行多语句事务的时候 所有session的使用的内存超过max_binlog_cache_size的值时,就会报错:“Multi-statement transaction required more than ‘max_binlog_cache_size’ bytes ofstorage”,设置太大的话,会比较消耗内存资源;设置太小又会使用到临时文件即disk

山重水复疑无路,柳暗花明又一村——记一次在win7下安装MATLAB的经历

某人现在搞课程设计,要用到MATLAB,请求我帮忙装一下。其电脑是惠普的本本,系统是win7.   拿到本本后,我的第一个想法就是,把我机子上安装的MATLAB6.5的安装程序考过去,直接安装。顺利...

山重水复疑无路,柳暗花明又一村

最近在做微信支付项目,昨天除了微信还有两个营收系统的事情烦着我,一下子同时处理3个项目,还是挺恼人的。同时也说明了自己处理事情的方法不行,微信急着上线,用户体验部的同事在测试微信视觉效果的时候,老提一...

强叔侃墙 VPN篇 非Web应用访问受阻疑无路,私有头本地环回携手又一村

上一篇贴子中,强叔介绍了文件和URL这两种最为常见的细粒度资源在SSL VPN中的访问方法。但是在有些情况下,例如对于Telnet、SSH、Email等基于TCP的非Web应用的访问,文件共享和W...

柳暗花明又一村—学生管理系统验收

学生管理系统我是7月26日上午11:20正式开工,到今天晚上9:00第二次验收完,历时19天之多,终于结束了,其实也不算结束,因为这是一个新的开始...... 上周六8月10号进行第一次验收,其实当时...

移动浏览器桌面,柳暗花明又一村

p { margin-bottom: 0.21cm; }     在我们国内,自启动U盘似乎已经被人们遗忘,但是,在国际上,情况并不是这样的。何故?     去年第三季度...

浅析正则表达式——柳暗花明又一村篇

一、前言   本文章主要针对上篇文章进行补充,接上一篇文章http://www.cnblogs.com/dwlsxj/p/3532458.html,首先为什么开篇叫柳暗花明又一村,大家都知道这个...

linux内核学习(5)山重水复疑无路*

上次说到00-INDEX文件,然后把kbuild.txt说完,但是我们的还有多么遥远。说到这儿,肯定很多看了我文章的朋友会想,你到底想干嘛,对, 我也疑惑!这样的分析是否正确,不过,对于没多少见识的...

山重水复疑无路

这多天没有写博客了,因为一直在看《JavaScript DOM编程艺术》的第4章至第7章的内容,然后我才发现,真的是到后面自己越来越hold不住了。 在重新模仿每个示例代码前,我都会先缕清逻辑关系,...
  • stjwork
  • stjwork
  • 2015年12月25日 21:38
  • 240

山重水复疑无路_最快下降问梯度(深度学习入门系列之七)

摘要: “天下武功,唯快不破”。欲速览无限风光,必攀险峰;欲速抵山底幽谷,则必滚陡坡。这滚山坡的道理,其实就是梯度递减策略,而梯度递减策略,则是BP算法成功背后的“男(ji)人(chu)”。想知道为啥...

又一村习题

  • 2012年12月31日 19:37
  • 2KB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:山重水复疑无路,柳暗花明又一村
举报原因:
原因补充:

(最多只允许输入30个字)