干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】

时间:2022-09-01 00:22:04

转自:

干货分享:MySQL之化险为夷的【钻石】抢购风暴 - Vanos_韩尛哲 - 博客园
http://www.cnblogs.com/Vanos-lcp/p/5642097.html

抢购钻石不稀奇,稀奇的是有钱赚不到,事情发生在2015年5月20日,大好的日子自然少不了商家的参与。即可为您还原现场,解决思路献给各位,请欣赏Show Time,everybody~

1、优化起因及工作准备

  2014年5月20日下午三点四十接到对方不愿意透漏姓名的“王大锤”领导的电话,对方火急火燎的仅提供了网站访问慢一条信息,当时博主那个心里一万只XX奔腾而过,俗话说的好,酒肉穿肠过,拿人钱财必替人消灾。

  对博主来说网站访问慢,首先不能乱了阵脚,先想到的就是看web、先看静态,如果静态ok就看动态,如果还不ok就看存储,再不行就看访问DB时长是否正常。此时原因就可以定位了。不会再有其他原因了。如果你太菜,那你可以把我的思路背过,相信对你来说是一个很好的帮助,此时一边与对方沟通更可能多的获得信息,可是对方一点都不懂,只好无能为力,与对方协商相关责任制后立即登录服务器(本人兼职XX钻世界集团技术顾问一职)。

  凭借个人经验查看web负载并不高,静态访问速度正常,由于线上活动正在进行,晚一分钟对商家即是损失,此时无法进行许多系统的排查,直接则判断是否是后端DB的问题?随登录DB查看负载。发现DB负载不正常,就没有进行其他的判断(什么IO看一下啊,内存看一下啊,网卡看一下啊,再看公司都倒闭了。),紧急恢复问题就是最大化的恢复问题,找到问题所在即刻解决问题。此时判断数据库有慢查询。

1 ================2015年5月20日 13:38:08日负载如下:================
2 [lcp@ZCdb01 ~]$ uptime
3 13:50:36 up 122 days, 21:51, 1 user, load average: 6.44, 5.76, 5.38
4
5 [lcp@ZCdb01 ~]$ uptime
6 13:51:38 up 122 days, 21:22, 1 user, load average: 8.01, 6.30, 5.58

2、判断问题所在 

 随登录数据库show full processlist;此工具运维人员必备,干了几年的运维别说你不会。不会的话看了我的博客也应该会了。

连抓了两遍之后发现,这一堆东西不动啊,前面排着的update被锁定,想写还写不进去。select过多,读也读不出来。

干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】
1 mysql> show processlist;
2 +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+

干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】

干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】

3、定位待优化语句

再返回来看后面的查询语句是通过三个条件进行查询的。于是定位了待优化的语句也就是下方的select出现次数最多的语句
干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】
                         ↑↑↑查询语句如上↑↑↑

  随后抓出一条命令explain,多次确认后加SQL_NO_CACHE不让其走缓存再反复确认,最终判断次语句没有建立索引或走索引,共查阅7万3千多条数据耗时惊人。

1 mysql> select SQL_NO_CACHE id from **_**_detail where ader='**_**-jazz_flash' and dateline='**_**' and pos='**_**';

  此时看到可能走的索引和索引都是不存在的。独立奔跑在七万多条语句中

1 possible_keys:NULL
2
3      key:NULL
4
5      rows:71328 #接近全盘扫描

  我记得这台机器是戴尔服务器2850很老的一台服务器,但这很明显不是硬件问题,随问对方的主管,有没有人对这台机器进行优化,一边电话询问一边进行查看,去证实自己的想法,使用show查看表结构show create table **_**_detai\G,果不其然,除了主键索引,一个索引都没有建立(为这台年老失修的服务器感到骄傲,它竟然扛了那么久授小弟一拜)。

干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】

4、解决方案

  扯淡归扯淡我们继续,此时已耗时3分钟,建立索引的规则相信大家也都清楚,此处不过多解释,一会看总结。得到以上结论后,查看哪一字段列的唯一值数量较多。使用select count(distinct XX)from **_**_detai;以上三个语句都使用次等命令查看,最后发现三列的数值为766/531/154都不高,原因是有一列是日期,它的唯一值是最少的,第二列看不懂。。

  再使用select count(*) from **_**_detail;命令查看一下总数量达到了七万多条的数量。

  根据以上的情况,而且查询语句里面也很特殊都是等号。这种情况下建立索引就容易走索引。这种情况下考虑走联合索引。根据以上信息及咨询研发经理其他语句的情况下,创建如下索引:

1 mysql> create index d_a_p on **_**_detail(dateline,daer(20),azz(10),pos(20));

  语句的查询顺序是询问的研发经理,因为联合索引有前缀生效的特性,所以此时确定了索引之后并没有直接创建,而是与研发经理协商,此时需要杀掉几个读的请求。在前面选几个。show proacesslist;update根据业务需求去考虑。谨慎使用至于杀掉的方法..kill+id相信没几个不会的吧。

  索引建立完成再使用explain查看索引是否生效,然后同样还是使用select+SQL_NO_CACHE参数不走缓存查询语句。发现此时仅扫描了12条语句,查询时间更是少之又少。

  再次使用show proacesslist;查看mysql线程,几乎看不到了。说明效果很明显。

干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】

  

5、解决效果

  优化之后的负载,已经从之前的6.x、8.x慢慢下降为2.x,1.72,五分钟后降到了0.07、0.21的正常值

干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】
 1 [lcp@ZCdb01 ~]$ uptime
2 13:59:09 up 120 days, 21:29, 2 users, load average: 2.40, 4.62, 5.09
3 [lcp@ZCdb01 ~]$ uptime
4 13:59:29 up 120 days, 21:29, 2 users, load average: 1.72, 4.32, 4.98
5 [lcp@ZCdb01 ~]$ uptime
6 13:59:30 up 120 days, 21:29, 1 users, load average: 1.66, 4.26, 4.95
7 [lcp@ZCdb01 ~]$ uptime
8 14:05:27 up 120 days, 21:35, 1 users, load average: 0.07, 1.39, 3.42
9 [lcp@ZCdb01 ~]$ uptime
10 14:05:35 up 120 days, 21:36, 1 users, load average: 0.21, 1.38, 3.40
干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】

6、总结

   问题判断+解决时长10分钟以内

优化判断+后期观察15分钟左右

此次问题解决总用时25分钟左右

   此次问题由于对方对mysql数据库优化不到位,此公司并无相关技术人员,日常维护工作无法正常开展,导致突发状况访问异常。为保证以后服务器正常工作,优化完成后在配置文件(my.cnf)下添加如下参数记录慢查询语句。

1   long_query_time =2 #<==超过2秒,记录到LOG里。
2
3   log_queries_not_using_indexes #<==没有走索引的语句,记录到LOG里。
4
5   log-slow-queries = /data/3306/slow.log #<==LOG文件

  但是建立索引的前提是,生产场景,表中数据多的情况下及高峰期不能建立索引,例如:300万记录。由于此次问题解决中使用的是联合索引,联合索引的特性是前缀生效,这也是有别于其他索引,所以创建时更为谨慎,需要与开发共同商议创建规则。否则索引无效。

  关于mysql的优化从此次解决问题的过程中得出以下几个结论:

  1、紧急情况抓慢查询SQL语句:

    登录数据库

    show full prcesslist;

  2、未雨绸缪:重要不紧急:分析慢查询日志。(生成日志方法在上述总结中有具体参数)

    分析慢查询SQL语句,每天定时发邮件给相关工作人员,核心开发、高级运维或DBA

    每天切割慢查询日志,去重分析后发给大家。

         切割方法:

              1)mv ,relaod进程。2)cp,>清空

              2)利用定时任务

  以上分享内容到此结束,如有疑问欢迎发送邮件到lcp779401@cntv.cn探讨交流,希望对大家有所帮助。

干货分享:MySQL之化险为夷的【钻石】抢购风暴【转载】的更多相关文章

  1. 【干货分享】Node&period;js 中文资料导航

    这篇文章与大家分享一批高质量的的 Node.js 中文资料.Node.js 是一个基于 Chrome JavaScript 运行时建立的一个平台, 用来方便地搭建快速的, 易于扩展的网络应用 Node ...

  2. 干货分享:SQLSERVER使用裸设备

    干货分享:SQLSERVER使用裸设备 这篇文章也适合ORACLE DBA和MYSQL DBA 阅读 裸设备适用于Linux和Windows 在ORACLE和MYSQL里也是支持裸设备的!! 介绍 大 ...

  3. 纯干货分享!2020阿里java岗笔试面试题总结(附答案)

    前言 2020金九银十马上结束,现为大家整理了这次金九银十面试阿里的面试题总结,都是我从朋友那拿到的面试真题,话不多说,满满的干货分享给大家! int a=10是原子操作吗? 是的.  注意点: i+ ...

  4. iOS - GitHub干货分享&lpar;APP引导页的高度集成 - DHGuidePageHUD - ②&rpar;

    距上一篇博客"APP引导页的高度集成 - DHGuidePageHUD - ①"的发布有一段时间了, 后来又在SDK中补充了一些新的内容进去但是一直没来得及跟大家分享, 今天来跟大 ...

  5. iOS - GitHub干货分享&lpar;APP引导页的高度集成 - DHGuidePageHUD - ①&rpar;

    好长时间没更新博客, 是时候来一波干货分享了;APP引导页话不多说每一个APP都会用到,分量不重但是不可缺少,不论是APP的首次安装还是版本的更新,首先展现给用户眼前的也就只有它了吧,当然这里讲的不是 ...

  6. 分享MYSQL中的各种高可用技术(源自姜承尧大牛)

    分享MYSQL中的各种高可用技术(源自姜承尧大牛) 图片和资料来源于MYSQL大牛姜承尧老师(MYSQL技术内幕作者) 姜承尧: 网易杭州研究院 技术经理 主导INNOSQL的开发 mysql高可用各 ...

  7. 【干货分享】Google 的设计准则,素材和资源

    在谷歌,他们说, “专注于用户,所有其它的就会水到渠成 ”.他们遵循设计原则,寻求建立让用户惊喜的用户体验.谷歌一直挑战自己,为他们的用户创造一种视觉语言,综合优秀设计的经典原则和创新.谷歌设计规范是 ...

  8. 32位汇编第四讲&comma;干货分享&comma;汇编注入的实现&comma;以及快速定位调用API的数量&lpar;OD查看&rpar;

    32位汇编第四讲,干货分享,汇编注入的实现,以及快速定位调用API的数量(OD查看) 昨天,大家可能都看了代码了,不知道昨天有没有在汇编代码的基础上,实现注入计算器. 如果没有,今天则会讲解,不过建议 ...

  9. 逆向实用干货分享&comma;Hook技术第一讲&comma;之Hook Windows API

    逆向实用干货分享,Hook技术第一讲,之Hook Windows API 作者:IBinary出处:http://www.cnblogs.com/iBinary/版权所有,欢迎保留原文链接进行转载:) ...

随机推荐

  1. Razor速记

    1.语法 @{     int c=2; } @for(int i=0;i<c;i++) {     @i     @:@i     @:select     @:select @i     s ...

  2. java简单日历

    一.使用java的calendar类写一个简单的日历 package com.calendar; import java.util.Calendar; import java.util.Date; i ...

  3. 【BZOJ】【1049】【HAOI2006】数字序列

    DP 第一问比较水……a[i]-=i 以后就变成最长不下降子序列问题了,第二问这个结论好神奇,考试的时候怎么破?大胆猜想,不用证明?TAT 题解:http://pan.baidu.com/share/ ...

  4. Python3向网页POST数据

    还是以我的网页iciba为例 POST数据到www.selflink.cn/iciba/get0.php获取返回的查询结果 #coding:utf8 import urllib.request imp ...

  5. 《转载》使用CSS3 Flexbox布局

    Flexbox(中文版本可以点击这里)的布局是一个用于页面布局的全新CSS3模块功能.它可以把列表放在同一个方向(从左到右或从上到下排列),并且让这些列表能延伸到占用可用的空间.较为复杂的布局可以通过 ...

  6. JMeter—逻辑控制器(六)

    参考<全栈性能测试修炼宝典JMeter实战>第六章 JMeter 元件详解中第一节JMeter逻辑控制器 JMeter逻辑控制器可以对元件的执行逻辑进行控制,除仅一次控制器外,其他可以嵌套 ...

  7. EZ 2018 04 06 NOIP2018 模拟赛(七)

    我是链接 这次是真的惨,码了将近2hours的可持久化线段树炸掉了! 而且本地拍了一万年也没发现哪里炸了. T1 压位的入门题,话说这道题能拿个99分就可以了(100分要FFT) 对于暴力,就是暴力找 ...

  8. (弃)解读Openstack Identity API v2&period;0

    目前OpenStack社区提供了详尽的API文档,并配有丰富的说明和示例,关于Identity API v2查看这里, 关于Identity API v3请查看这里. 尽管现在官方已经不建议OpenS ...

  9. Redis压力测试

    然后依次测试如下命令: 上面第三个命令写错了,要改为:

  10. 解题:APIO 2014 序列分割

    题面 拆开式子我们发现切割顺序不影响答案,所以可以设计出一个$dp[i][j]$表示到$i$为止切了$j$刀的最大收益之类的,然后做个前缀和就可以转移了. $dp[i][j]=min(dp[i][j] ...