大清早,看到关于1024程序员节的消息,心想今天会不会有什么惊喜呢?小期待~

1024节来自阿里的问候 - 图1

看客们且耐心听我讲故事,有干货,血淋淋的教训换来的经验。

吃完饭,打开邮箱看看

1024节来自阿里的问候 - 图2
心想: 呆胶布,百分之八十而已,问题不大,数据库里百分之80%的数据都是从没分析过的日志,只写不读不更新,估计500万行了吧,删了前400万行就好了,今天又是成就感满满的充实一天呢。
1024节来自阿里的问候 - 图3

于是打算开始操作,流利的打开了Navicat Premium

1. 看了看有多少数据

  1. SELECT max(id),min(id) FROM tb_log1;

1024节来自阿里的问候 - 图4
不出所料,接近500W条日志记录,久远的日志不需要调取了,删个400万条就能出来大量空间了。

2. 开始删

DELETE FROM tb_log1 WHERE id< 40000000;

忽然一想,delete操作不会释放空间,truncate才会嘛,那万一删这些数据导致磁盘空间直接从80%涨到100%,那不就坑大发了,占到100%会不会崩溃?。于是我研究了一下下,得知我使用的InnoDB引擎,在进行删除是是标记为删除,然后在写入数据时才会利用删掉的空间。
标记400W条数据为删除,剩余的20%空间等于8G,不可能让用8G,减去2G和内存一样的体积,再除以二,假设用3G空间用来标记这400W条记录为删除,相当于 3_1024_1024*1024 byte空间,存4000000个删除标志,每个删除标志能有800byte大,不至于吧,稳得很。
对于这样的删除,需要加一句optimize table table_name就能立刻释放空间。

3. 继续开始删

DELETE FROM tb_log1 WHERE id< 40000000;
OPTIMIZE TABLE tb_log1;

业务早高峰期,狗命要紧啊,这万一删这400万条数据,能把tb_log表锁定一两个小时,那又该怎么办???于是谨慎的我又研究了一下:

  • 网上别人删除十几万条数据20G需要十几秒,心想,那还好,十几秒而已,40W条也就一分钟,以为是网络卡顿就过去了。
  • 谨慎的我再看了看目前CPU和RAM的利用率,还好还好,也就百分之几和百分之30,几十个连接,性能占用百分之10都不到,应该卡不了多久。
  • 还是感觉哪里有问题,再想想,这样的删除要写入400W次,好像还蛮恐怖的呢,SSD硬盘一秒钟能写入1000次的话,那要写4000秒,再快一个量级400秒也不行啊,投诉电话肯定要被打爆。…emmm还真是一两个小时,看来网上说法不一定靠谱。

1024节来自阿里的问候 - 图5

4. 那怎么办?

  • 方案一:这些日志反正从没分析过,也没用到过,要不troncate一下全删了算了,万一发现有用,阿里云还有镜像,恢复一个新的实例来分析就行了,不过备份的镜像存不了几天,恢复来研究太麻烦了。
  • 方案二:把有用的少量数据写到一个新的表里,然后删除旧表,再把新的表名字改回旧的表,这样应该会很快,也比较保险。
  • 那就这么定了,采取方案二,于是流利的写出了sql语句,我果然是专业的呀。

1024节来自阿里的问候 - 图6

5. 继续开始删

-- 复制部分数据并创建新表
INSERT INTO tb_log2 SELECT * FROM tb_log1 WHERE id>4500000 ; -- 十分之一的日志应该不会导致数据库炸掉
-- 同时给两个表命名
RENAME TABLE tb_log1 TO tb_log2, tb_log2 TO tb_log1; 
-- 删除数据多的表
DROP TABLE tb_log2;

这下应该完美了吧,就算慢也应该不会超过一分钟,客户来不及打投诉电话就恢复了,用户体验都被狗吃了,滑稽笑
不,万一代码有错误呢!!于是建了小表,弄了点数据进去,这么执行没问题。

6. 墨迹了半天,终于要执行代码了

紧张又鸡冻。
1024节来自阿里的问候 - 图7
然鹅,艾迪魔力转圈圈….圈圈转了几圈后,提醒连接断掉了。

1024节来自阿里的问候 - 图8
执行查询时失去了连接。。。,那到底删成功没删成功?
按照第一步看了看,数据还是那些,没删成功啊。

7. 退而求其次,全部删了算了

TRUNCATE TABLE tb_log1;

1024节来自阿里的问候 - 图9
然鹅,艾迪魔力转圈圈….圈圈转了几圈后,提醒连接断掉了。

1024节来自阿里的问候 - 图10
执行查询时失去了连接。。。,那到底删成功没删成功?
按照第一步看了看,数据还是那些,没删成功啊。

8. 有种不祥的感觉

于是打开了我们的软件,登录不上了,抓包看一眼,妈耶,请求没反应,大事不好了,用户端估计要疯了,市场部这回估计得炸了。
急忙登录阿里云RDS后台,看看咱的数据库咋地啦。
这时老板的电话打来了,喜闻乐见,意料之中,肉牛满面,果断挂了没接。

  • 打开RDS控制台,满眼的喜庆色(当时忘了截图了),就是说IO上天了,每秒钟2000:
    1024节来自阿里的问候 - 图11
    WTF? truncate需要如此多的IO操作?
  • 连接总数也上天了,1500个连接。
    1024节来自阿里的问候 - 图12
    这就更理解不了了,我的PHP没那么高的并发啊,难道是数据库阻塞导致的?一定是!!
  • 存储空间当时达到了120%(这是后来恢复正常后的状况,当时只有40G容量)。

1024节来自阿里的问候 - 图13

这就更出乎意料了,存储空间怎么会激增十几G呢???我在做梦???还是我学了假的数据库???

  • 再看RDS控制面板,红色字显示[磁盘使用率超出限制,锁定中],锁定什么意思???
    那Navicat点看一个表,能打开呢,没锁啊,能用啊。
  • 再去登录我们的网站并抓包,等不了,错误变成了500,切换测试状态,数据库报错提示锁定中,只读状态。
  • 为什么截断表磁盘使用率会激增??而不会释放容量??想不通,没办法,只好花钱买扩量,流利的加了几百块钱扩容。

1024节来自阿里的问候 - 图14

  • 扩容操作进行十几分钟了还没反应,一直显示[锁定中],没有任何有效信息显示扩容请求已受理或者正在进行扩容,这个真让人头大。

1024节来自阿里的问候 - 图15

  • 只好发起工单,工单发起大概过了十分钟
    电话来了,熟悉的号码95187,熟悉的声音,阿里云客户经理告诉我我的工单已经第一时间转发给相关工程师了,然后……
    开始推销数据库了,我说时态紧急,没空听你的推销,然后他说不是推销,聊聊数据库的情况。
    我心想:“哦,你个市场部的人能懂数据库运维?”
    果不其然,对方没说几句,就说道:“您的业务应该是处于增长期,数据量会变得比较大,有没有考虑用关系型数据库,我们平台提供…”
    然后我就挂了电话,心想: 这个问题不解决好,不是公司倒闭,就是我损失大笔的钱,没心思理会“灵堂卖片”行为。

1024节来自阿里的问候 - 图16

  • 我注意到在数据库面板[锁定中]红字附近,有个小小的问号1024节来自阿里的问候 - 图17
    把鼠标放上去,会提醒:升级mysql8.0可以通过truncate、delete from table等操作自行释放空间
    言外之意阿里云RDS mysql8.0之前的版本,像我前面那样的想法来释放空间,就是个笑话?
  • 什么也操作不了,没法删数据,没法重启实例,只能等,就算老板现在冲到我面前,把刀驾到我的脖子上,让我解决问题,我也只能等了。

9. 最后怎么好的?

  • 挂了客服的电话,大概又过了十来分钟,恢复了,不知道是工单那边的工程师给解除了锁定,还是我扩容操作完成了,所以好了。
    从登不上,到再次能登陆上,大概过了能有一个小时,这一个小时市场部的兄弟估计被骂的不是人了。
  • 升级8.0一来要钱,二来不兼容,感觉被阿里云安排的明明白白,不加钱就别想有空间用的意思。
    比如8.0的mysql一个表里不能用两处current_timestamp,我的表大多数都是:
create_time timestamp default current_timestamp,
update_time timestamp on update current_timestamp

升级了就运行不了了
1024节来自阿里的问候 - 图18

血淋淋的教训和经验

  1. 风险大的操作,一定要舍得花钱,做好万全准备。
  2. 风险大的操作,一定要等在线用户少的时候,比如半夜三四点时。
  3. 数据量一大或者用户一多,不确定因素就会多,比如连接过程中阻塞个十几秒后,主要业务的请求就堆积起来了。
  4. 要深入理解理解数据库的缓存、日志、磁盘等机制,知其然不知其所以然会出问题。
  5. 不要盲目自信,不要想的太理想。
  6. 日志在实现时就考虑好定期清理,比如超过三个月的定时删除,在操作时先关掉日志的写,以避免阻塞。

以上就是1024程序员节我收到的惊喜,啊呸,惊吓!

1024节来自阿里的问候 - 图19

另外,祝广大的程序员1024节日快乐!