导读
本文由茹憶老师按照5月2日早上的直播内容整理而成,我直接转载,以下是正文内容。
2022年5月2日,在青岛面临抗疫关键时刻的国庆黄金周末,非常约请了OracleMySQLACEDirector叶金荣(叶老师),联合为你们带来了MySQL运维防坑的视频号在线直播,了解了这种坑可以让你们在日常运维愈发平稳,全场300多人参与,直播持续了一个半小时,约1小时的分享和半小时的互动答疑,堪称干货满满。直播无回放,我们将直播涉及的关键内容进行了整理,产生了这份MySQLDBA防坑手册,建议你们可以收藏出来,类似问题参考解决(文末有下一场直播,欢迎预约)。
1.MySQL联接数问题
2.MySQL文件句柄设置
3.注意SQL隐式转换的坑
在开发规范中,我们常常会要求研制防止在where条件中出现隐式类型转换,哪些是隐式转换:即在where句子中条件的值和条件对应的列的数据类型不一致。如whereid=‘123’,而id的类型为bigint,或则wherecode=100,而code的类型为varchar,隐式转换会形成以下两个问题:
1.隐式类型转换可能造成索引失效。
2.隐式类型转换可能形成非预期的结果。
4.SQL为何一会可以走到索引,一会走不到索引
有些时侯开发同学会找到DBA,反馈有一条SQL有索引,之前也能走到索引,查询性能十分的高,忽然收到慢查询报案,查了十多秒,这些情况形成的缘由通常和数据内容有关。
MySQL假如通过索引选择扫描行数过多(大概20%以上,这个不是确定的),优化器会觉得使用全表扫描更佳,因而会走全表扫描,其实好多时侯虽然还是走索引性能会更好一点,MySQL选择优化器并不是这么智能。
举个反例,微博的用户有一张评论表,查询我们这种冷门用户的评论会走索引,查询特别的快,假如刚好查询的是一个大V帐号,有可能涉及数百万条数据造成没有走到索引。
5.自增键重启后回溯问题
InnoDB引擎的自增值,虽然是保存在了显存里,但是到了MySQL8.0版本后,才有了“自增值持久化”的能力,也就是才实现了“如果发生重启,表的自增值可以恢复为MySQL重启前的值”。
在MySQL5.7及之前的版本,自增值保存在显存里,并没有持久化。每次重启后,第一次打开表的时侯,还会去找自增值的最大值max(id),之后将max(id)+1作为这个表当前的自增值。
举例来说,假如一个表当前数据行里最大的id是10,AUTO_INCREMENT=11。这时侯,我们删掉id=10的行,AUTO_INCREMENT还是11。但若果马上重启实例,重启后这个表的AUTO_INCREMENT都会弄成10。也就是说,MySQL重启可能会更改一个表的AUTO_INCREMENT的值。
6.自增键用完怎样办
假如你用过或了解过MySQL,那你一定晓得自增字段了。每位自增id都是定义了初始值,之后根据指定步长下降(默认步长是1)。
其实,自然数是没有上限的,并且我们在设计表结构的时侯,一般还会指定主键宽度,这么,这时侯id就有上限了。
在插入数据时有可能惟一字段冲、sql事务回滚、批量插入的时侯,批量申请自增值等诱因引起自增id是不连续的。
我们通常会将自增键的类型设置为int,数据范围为负21亿到正21亿,对于一个频繁插入删掉数据的表来说,21亿是可能会被用完的,可能引起业务难以正常写入。
因而在建表的时侯你须要考察你的表是否有可能达到这个上限,假如有可能linux命令vi,就应当创建成8个字节的bigintunsigned。
另外可以将表的自增键做好监控,例如抵达使用率的80%,就可以报案下来。
7.大表删掉hang的问题
MySQL上面直接对大表执行droptable删掉有可能造成MySQLHang住,对业务导致影响。删掉超大表的前提是该表是独立表空间,之后根据如下步骤删掉能够防止造成业务故障。
一、表创建一个硬链接
# du -sh pay_bills.ibd
175G pay_bills.ibd
# 创建硬链接
# ln pay_bills.ibd pay_bills.ibd_hdlk
二、执行表删掉
在Linux中,每位储存文件就会有指向该文件的InodeIndex,多个文件名可以通过相同InodeIndex指向相同一个储存文件。
假如该文件名引用的InodeIndex上还被其他文件名引用,则只会删掉该文件名和InodeIndex之间的引用
假如该文件名引用的InodeIndex上没有被其他文件名引用,则删掉该文件名和InodeIndex之间的引用并删掉InodeIndex指向的储存文件。
实际上只是删掉了对pay_bills.ibd的一个文件引用,我们pay_bills.ibd_hdlk对化学文件的引用还是存在的,就不会执行OS级别的删掉操作鸟哥的linux私房菜,IO波动不大,增加对MySQL的影响。
mysql> drop table pay_bills;
Query OK, 0 rows affected (3.24 sec)
三、执行文件删掉
安装truncate工具
# yum install coreutils -y
执行删掉脚本
#!/bin/bash
TRUNCATE=/usr/bin/truncate
for i in `seq 175 -2 1`; do
$TRUNCATE -s ${i}G pay_bills.ibd_hdlk
sleep 1
done
最后删掉硬联接
rm -f pay_bills.ibd_hdlk
8.AdaptiveHashIndex引起的问题
哈希(hash)是一种十分快的查找方式,在通常情况下这些查找的时间复杂度为O(1),即通常仅须要一次查找能够定位数据。而B+树的查找次数,取决于B+树的高度,在生产环境中,B+树的高度通常为3~4层,故须要3~4次的查询。
InnodB储存引擎会监控对表上各索引页的查询。假如观察到构建哈希索引可以带来速率提高,则构建哈希索引,称之为自适应哈希索引(AdaptiveHashIndex,AHI)AHI是通过缓冲池的B+树页构造而至,因而构建的速率很快,并且不须要对整张表建立哈希索引。InnoDB储存引擎会手动按照访问的频度和模式来手动地为个别热点页构建哈希索引。
然而在DROPTABLE过程中,发觉存在大量的线程处于OPENINGTABLES的状态,DROP时间十分的长。缘由是当DROPTABLE时,InnoDB引擎都会删掉表对应0的AHI(自适应哈希索引)。而这个过程须要持有一把数据字典的互斥锁、读写锁。
对于这个问题,可以在DROPTABLE的时侯关掉AHI功能,甚至可以永久关掉AHI功能。
9.MHA切换VIP的问题
MHA是业界精典并且好多公司在用的HA切换工具,并且默认开源的MHA基于VIP飘移来进行切换,通过VIP甩尾可能存在由于网卡问题甩尾失败的问题,因而引起切换失败。
目前好多运维能力比较强的公司通过自建DNS,之后将MHA的VIP甩尾的代码二次开发改建为切换DNS,业务联接DNS域名来解决这个问题。
10.pt-archiver迁移为何少了一条数据
pt-archiver属于大名鼎鼎的percona工具集的一员,是归档和清除MySQL大表数据的最佳轻量级工具之一。并且使用过程中发觉迁移的数据少了一条(最后一条数据),缘由是和工具的safe-auto-increment参数有关。
--[no]safe-auto-increment默认值:yes,指定不使用自增列(AUTO_INCREMENT)最大值对应的行进行归档。该选项在进行归档消除时会额外添加一条WHERE谓词以避免工具删掉单列倒序数组具有的具有AUTO_INCREMENT属性最大值的数据行,为了在数据库重启以后能够使用到AUTO_INCREMENT对应的值,但这会导致难以归档或去除数组对应最大值的行。
11.pt-osc和ghost变更丢数据的问题
我们在使用pt-osc和gh-ost的过程中,发觉了变更完丢数据的情况,晓得了这个坑,就降低了删库跑路的风险。
pt-osc和gh-ost执行方法关于数据处理的区别,我们做了一个测试
测试表如下:
CREATE TABLE`ddltest` (
`id` int(11)NOT NULL AUTO_INCREMENT,
`name`varchar(10) DEFAULT NULL,
PRIMARY KEY(`id`)
)ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8
mysql>select * from ddltest;
+—-+——+
| id | name|
+—-+——+
| 1| a |
| 2| b |
+—-+——+
pt-osc和gh-ost执行方法关于数据处理的区别,大约总结一下:
最终最终总结:
针对此问题可以改进的地方:
建立SQL军规,新增数组时假如设置notnull属性则必须带上default值。假如新增数组设置notnulllinux 查看文件句柄数,严禁加惟一索引。
建立SQL初审流程,假若系统发觉开发朋友递交了新增惟一索引的SQL,则不容许开发手动执行,流转到DBA处理。
提升自我初审从严意识,同时在手动执行平台DBA初审界面,针对添加惟一索引,做强提醒功能。
针对加惟一索引的SQL,最好单独递交处理,走onlineddl模式执行,尽量不用pt-osc和gh-ost。
假如使用新版OSC:Perconatoolkil3.0.13,参数–check-unique-key-change–check-alter可以事先检测是否降低uniquekey,并报错。
12.数据库分拆引起的删库风波
讲一个真实案例,以前,有一名前朋友在做一次数据库分拆,分拆是通过搭建同步备库和切换进行的(备库只同步了须要分拆的数据库),操作完成后,原先服务器实例早已拆走的数据库须要删掉,删掉后发觉新的数据库也没有了,一次删掉风波由此形成。
形成的缘由是因为新库分拆完成后,忘掉断掉和老库和同步,进而于在老库删掉后新库也跟随没有了。
这个完全是操作和流程问题,也是希望你们就能造成重要,运维操作建议写好操作步骤,但是关键步骤(类似删库和删表)最好有doublecheck,可以防止类似故障发生。
13.HA没有切换/监控没有正常报案
你们有没有碰到过这些情况linux 查看文件句柄数,数据库故障了,却没有按预期的进行故障切换,监控报案也常常失效,我想说的是我们使用了大量的工具来让我们的运维愈发稳定,而且本身工具是不是也是单点?是不是也要做好监控呢?
例如HA平台、备份平台、监控平台,这种平台的自身服务或则Agent进行我们也要做好对应的监控,防治上述问题发生。
14.df看空间越来越少,du却没有发觉大文件
有时侯在Linux主机用df-hT查看c盘占用早已满了,而且用du-sh*又找不到是什么文件目录占满了c盘。
缘由是当我们使用rm在linux上删掉了大文件,并且假如有进程打开了这个大文件,却没有关掉这个文件的句柄,这么linux内核还是不会释放这个文件的c盘空间
解决方式:
lsof-n|grepdeleted查看到占用的进程
之后用kill-9进程号杀害进程就可以释放对应占用的空间了
批量处理命令:
lsof |awk '/deleted/{print $2}'|xargs kill -9
另外也看下有没有vim进程,假如使用vim打开了一个大文件,也会导致该问题。
15.死锁要紧么,须要注意哪些
假如是时常低频的死锁,没哪些太大影响,假如是出现频度比较高,对业务影响比较大。剖析死锁的缘由最好还能复现死锁。
剖析死锁可以使用showengineinnodbstatus命令得到锁的信息结合业务场景和业务代码进行剖析。
16.text等大对象类型有哪些风险
text/blob大数组会引起页的分裂,影响性能,具体可以参考
text数组会导致表占用更多的化学c盘空间,不合理的text浪费大量c盘空间
17.CPU%user为何非常的高
通常MySQL服务器的CPU%user假如比较高,通常95%以上都是索引使用不当造成的,我们须要重点关注慢SQL,其中重点关注执行状态为senddata/creatingsortindex/copyingtotmptable/creatingtmptable的SQL,并跟踪优化。
18.查询被hang住了,哪些缘由
假如查询hang住了,通常可以从以下几个方面进行排查:
可能的诱因有:
19.mysqlcrash了,如何办
mysqlcrash,可以从以下几个方面去排查剖析:
这么假如mysqlcrash了该如何办呢?
欢迎加入陌陌群一起交流阐述(加作者陌陌备注加入技术交流群):
国庆劳动节的最后一场直播
本次为你们带来MySQL从5.6升级到8.0的流程和注意的坑
欢迎你们关注视频号,获取定期免费技术直播信息
《深入浅出MGR》视频课程
戳此小程序即可直达B站