关于近阶段MySQL维护过程所遇问题的学习整理

前奏感触


最近一段时间无证上岗充当 DBA 临时工接触 MySQL 一些维护操作,总体感觉挺好,时常能够遇到’新’问题’挑战’,所以边尝试边学习边查手册,边请教专业的同事朋友边和小伙伴们闲聊分享新发现,有种自己刚毕业入社会那会入门运维的感觉。DBA确实感觉挺好,有意思有挑战,唯一不好的一点就是:个人感觉很多都得在夜里才能操作维护。说来也惭愧自己不是真正计算机类毕业的如今却实打实的干着IT基础服务这行,所以就自己而言,更多涉及广度缺少深度(系统的学习深入的研究),遇到问题时排查原因分析定位基本都还不成问题,至于解决方案措施有时即便有思路有想法却也很容易比较没了底气自信,所以没优势,这样通常也不易被看好看重。

在维护 MySQL 过程中,前前后后遇到几次坑及个人困惑,也此处理过程了解些使用 MySQL 数据库过程可能需要留意的几个地方,简单记录下希望对后来人或像自己这样的临时工有所帮助吧。至于那些关于 MySQL 主从、主主、还是主主各带从的关系这里不多记录,基本就都那样。

MySQL 使用考虑点


一、MySQL平台环境选择


MySQL平台环境:硬件、系统等

硬件:

  1. CPU
  2. 内存:尽可能的大,主要是InnoDB中有个内存池 innodb_buffer_pool ,尽量将热数据预加载保存在内存里以便尽量多的从内存里读取数据。
  3. 磁盘:提供磁盘 I/O 读写速度性能,SSD现在很普遍,有钱的都上FusionIO,SSD可使用Raid 如:系统分区可选Raid1,数据分区则用Raid10的。
  4. 网卡:低延迟,传输/同步

系统环境:

  1. EXT4 / XFS / Btrfs(这个目前应该还不很少用线上吧)
  2. I/O调度算法:deadline

二、MySQL 自身配置


存储引擎选择,目前基本都是InnoDB引擎,或者各大公司基于InnoDB基础按需二次开发,所以就以个人这段时间在InnoDB引擎下遇到的几个问题来描述

1、共享表空间与独享表空间的选择(最好事先选择好,建议独立表空间性能且不说就方便维护)

innodb_file_per_table=1

2、InnoDB 内存池:将数据尽量多的保存在内存里,更多的减少磁盘读写操作,提供性能。

innodb_buffer_pool_size = 80% * Memory

InnoDB 内存池这东西有个需要注意就是:数据库新启动或重启,内存会被自动释放,所以都需要重新进行数据预热,将磁盘上的所有数据缓存到内存中,这个时候数据库压力负载都会异常的飙升,特别如果是在访问请求量高峰时,可能直接引发崩溃,这个自己在最后一次维护没留意就采坑中招了。详见下面两台分别是被重启过和线上的innodb_buffer_pool相关的配置信息区别: Innodb_buffer_pool_pages_free 值便是内存池空闲页面数,第一个是刚重启起来所以很多都尚未预热,第二个是线上服务已经为0即所有的innodb_buffer_pool都已经使用上。

mysql> SHOW GLOBAL STATUS LIKE '%innodb_buffer_pool_pages%';
+---------------------------------------+-------------+
| Variable_name                         | Value       |
+---------------------------------------+-------------+
| Innodb_buffer_pool_pages_data         | 2209585     |
| Innodb_buffer_pool_pages_dirty        | 147528      |
| Innodb_buffer_pool_pages_flushed      | 1953689     |
| Innodb_buffer_pool_pages_free         | 2484453     |
| Innodb_buffer_pool_pages_misc         | 24553       |
| Innodb_buffer_pool_pages_total        | 4718591     |
+---------------------------------------+-------------+
6 rows in set (0.00 sec)

mysql> SHOW GLOBAL STATUS LIKE '%innodb_buffer_pool_pages%';
+---------------------------------------+---------------+
| Variable_name                         | Value         |
+---------------------------------------+---------------+
| Innodb_buffer_pool_pages_data         | 4545938       |
| Innodb_buffer_pool_pages_dirty        | 96512         |
| Innodb_buffer_pool_pages_flushed      | 1609426350    |
| Innodb_buffer_pool_pages_free         | 0             |
| Innodb_buffer_pool_pages_misc         | 172653        |
| Innodb_buffer_pool_pages_total        | 4718591       |
+---------------------------------------+---------------+
6 rows in set (0.00 sec)

至于如何加快预热速度,Google了下有各种方法,要么是限制于XtraDB要么是要基于二次开发的引擎上,文章可见快速预热Innodb Buffer Pool的方法。个人愚见感觉即便模拟访问也不是很靠谱,因为模拟的东西本身和用户访问不太一致,不可能把磁盘数据都写入到内存里,因为这个内存池大小有限。

据说 MySQL5.6 版本支持在正常关闭服务的情况下支持事先做好预热备份的配置,相应配置(摘自网络,个人暂未测试)

innodb_buffer_pool_dump_at_shutdown = 1 #在关闭时把热数据dump到本地磁盘。
innodb_buffer_pool_dump_now = 1     #采用手工方式把热数据dump到本地磁盘。
innodb_buffer_pool_load_at_startup = 1  #在启动时把热数据加载到内存。
innodb_buffer_pool_load_now = 1     #采用手工方式把热数据加载到内存。

在关闭MySQL时,会把内存中的热数据保存在磁盘里ib_buffer_pool文件中,位于数据目录下。


3、磁盘写入操作调节

问题:临时从线上配置从库准备作为导数据用的时遇到同步严重延迟的现象

定位:同步追了一晚上发现 Seconds_Behind_Master 数值还是有点出乎的大,事先对MySQL 不熟所以只能怀疑延迟,网上查了下发现这个值是:SQL thread 与 I/O thread 之间的差值(摘自Seconds_Behind_Master 解析),有两种情况可能会引起这个大差值:网络延迟或执行写入I/O压力;看了下 master 的 binlog 都是很快传递到 slave 上且加上从库服务器磁盘I/O确实超负荷(虽然写入数据不大却过于频繁原因,留一个疑问:不知当时的读写频率次数是多少),为了更确切的确定,在@nettedfish的指点帮助下”在主库的对应db上,临时创建一个heartbeat 表,插入时间戳,在从库上,过一段时间取出来,与当前的时间戳想比较下”,确实如show slave status\G;显示的延时那时长。

解决:反馈给老大,很快指点自己试着将 innodb_flush_log_at_trx_commit 由线上的配置为 1 临时改为0,因为考虑是临时作为迁移导数据用的从库,不是线上生产环境影响不大,果然让其自动定期写入磁盘,效果明显:很快便追上同步且磁盘负荷恢复正常。

查证:这个参数原来对于磁盘读写性能影响甚大啊

innodb_flush_log_at_trx_commit = 0   #不write(),也不fsync()。每秒同时执行一次write()和fsync();
innodb_flush_log_at_trx_commit = 1  #同步IO,确定将redo log同步写入磁盘,即write(),又fsync().
innodb_flush_log_at_trx_commit = 2  #只write(),不fsync(),即不确认写入磁盘,可能还在OS page cache

备注:

  • write():用户态进程mysqld内存空间log buffer pool的数据复制到内核态线程内存空间中(即page cache),此时,日志数据并没有写至磁盘设备,
  • fsync():才会把页面缓存中的日志数据同步到磁盘设备上

由此可见:

  1. 设置为1,易产生大量同步I/O操作。每次同步I/O操作,都会阻止I/O调用,直到数据被真正写回磁盘(写磁盘较写内存慢很多),这样就会显著地降低InnoDB每秒可以提交的事务数。
  2. 设置为0或2,意味着更少的调用fsync(),最多有可能会丢失1s的事务,所以对事务要求不高业务环境下,其实完全可以设置值2,甚至值0来减少事务引起的磁盘I/O。

Ps:后来意识到那些监控 MySQL 主从同步是否延时是不是就是根据查看 Seconds_Behind_Master 这个值来确定的呢?!

其实关于磁盘读写操作相关的还有个参数是:sync_binlog — InnoDB同步bin log至磁盘的频率

sync_binlog = 0     #默认值是0。不调用fsync(),依赖于OS调度。
sync_binlog = 1     #每次commit,都要求进行一次fsync()同步I/O操作。

4、定期分析表,回收空间,间接优化表性能

InnoDB 引擎数据即便删除也不会被自动释放,所以如果用共享表空间那似乎意味需要定期重导数据操作?!对于独立表空间虽然delete操作同样不会回收已用空间,但至少还可以通过定期分析表,实现手动回收。

optimize NO_WRITE_TO_BINLOG table tablename;

需要注意的是:

  1. optimize执行过程会锁表,最好在slave上执行;
  2. optimize表分析实质是先将所有数据导入到新的innodb文件中,所以数据分区空余空间必须要大于该表文件
  3. optimize命令默认会写入binlog同步到其从库,所以需要加参数 NO_WRITE_TO_BINLOG

5、剩下的估计开发比较清楚

其他注意事项:

  1. 定期分析慢查询日志;
  2. 必要选择建立索引;

参考文档:

What to tune in MySQL Server after installation

Choosing innodb_buffer_pool_size

近期工作随记

身心体会


确实啊,这近一个月来就自己而言跟打战似的,各种忙碌、通宵熬夜导数据。前要盯流量看监控,后要顾集群服务存储兼干DBA活;左边答疑技术支持,右边请教开发确认机制;对外细到服务销售/客户上传数据,对内还需受命服务于两边项目;说白基本是“白天作战UpYUN,晚上转移Huaban”、“白天要跑机房,晚上得理配置,半夜上岗无证DBA临时工”的节奏一点不够分。其实领导会上所言的关于老大事情交接,其实不过是岗位的交接罢了,某种程度并不包括工作内容任务。

这样也不算什么坏事…而且也不是说仅仅是忙累而才不爽。小公司就是这么点优势—只要主动多去参与你就可以学习更多积累更多。其实不爽的地方关键在于:那些整天喊着“共同创业,共事”的领导们就只会口头忽悠说着些多好听的话,而实际上对于员工而言基本是看不到自己所创造的价值所在(最基本的业务销售情况无人知晓);做基础服务不舍得投入基础设施,一心只想考虑看要在技术人/程序服务上榨干所有,倒是很舍得投入找来各种摆饰好看撑场面的“展示品”,需要有这样的阶段没错,但关键至少目前还没到吧!如今一定程度上必备基本的容量规划预估是值得考虑,多少次都是被动的等项目迫在眉睫才到处要资源的,适当的冗余备置批资源想必还抵不上一个“展示品”一个月的费用吧?!

身为领导们很正常的一贯都只喜好那些好被自己降服、唯命是从、时刻追随自己等方面的人。可惜自己情商低下,属比较不正常的另类,言论随心、直言不讳、单刀直入、不降服于任何人,不想压抑内心不爽、不想虚假处事,只想做真实自己,哪怕自己知晓这样最终受伤的是自己。

其实就公司目前而言,管理方式及氛围而言都相当令人不敢苟同啊。

最后,只能用朋友曾给的一条共勉评论—“要么忍,要么滚”来终结身心体会,以这样方式记录下自己人生的点滴就好。

无证上岗DBA临时工


毕业至今多年,经历过的几家公司部门貌似都没有专职的DBA这岗位角色,可能自己处过都是小公司吧,这样的好处就是运维可以负责兼顾DBA维护的角色,学习到更多实战东西,之前在厦门就是如此,可惜自从来杭州后新接触更多是CDN的东西,所以后端DB基本就没再碰过,没想到这近一个月来能再次捡起几次熬夜都和数据库维护有关啊。

主要包括是 MySQL 维护及版本升级以及 Redis 服务实例的迁移分离,都是由于业务规模的速度增长而做的完善或扩充:MySQL 之前配置考虑不够周到及业务量增长触发到版本的某个BUG;花瓣 Redis 集群服务内存告警做相应的扩容迁移分离。

MySQL维护:共享表空间–>独享表空间;版本升级5.5.19–>5.5.31


共享表空间—>独享表空间


上次迁移更新数据库还是一年多前,当时自己就只负责安装配置数据库所需的平台环境(回想起那时都是泪啊!),至于 MySQL 的参数配置还是老大来,当时也没有考虑太周到,采取的是共享表空间方式。(时间的推移发现磁盘空间回收释放成一个问题),故决定重整调整配置开启设置使用独享表空间,方便后续对单表做表分析回收释放空间。(采用重新导出导入数据方式来做变更),主要配置修改:

    vi /etc/my.cnf
    innodb_file_per_table =1

至于共享表空间和独享表空间的比较,个人无权评论,因为自己是个无证上岗的DBA临时工,建议可以请教相关专职DBA或也可以看看MySQL 独立表空间 VS 共享表空间

升级 MySQL 数据库版本:5.5.19—>5.5.31


当时主要有2个疑问需事先确认:

  1. 数据需不需要重新导出导入
  2. 升级后同现有的相对较低版本与升级后高版本的同步兼容性问题

为了确认验证上面这两个问题,找了2台之前下线的服务器(事先已经有部署有 MySQL5.5.19版本)做了下基本逻辑的测试:对于第一个问题,测试大致

  • 安装部署新版本 MySQL5.5.31
    tar zxf mysql-5.5.31-linux2.6-x86_64.tar.gz
    /usrl/local/mysql/bin/mysqladmin -uroot -p shutdown #关闭旧版本MySQL服务
    mv /usr/local/mysql /usr/local/mysql5.5.19
    mkdir /usr/local/mysql
    mv mysql-5.5.31-linux2.6-x86_64/* /usr/local/mysql/
    mkdir /usr/local/mysql/log
    chown -R mysql.mysql /usr/local/mysql
  • 启动新版本 MySQL 服务
    /usr/local/mysql/bin/mysqld_safe --user=mysql&

    mysql> select version();
    +------------+
    | version()  |
    +------------+
    | 5.5.31-log |
    +------------+
    1 row in set (0.00 sec)
  • 执行 mysql_upgrade 调整修复数据兼容性 (按规范流程来做事)
    [root@hadoop01 local]# /usr/local/mysql/bin/mysql_upgrade -uroot -p
    Enter password:
    Looking for 'mysql' as: /usr/local/mysql/bin/mysql
    Looking for 'mysqlcheck' as: /usr/local/mysql/bin/mysqlcheck
    Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
    Running 'mysqlcheck' with connection arguments: '--port=3306' '--socket=/tmp/mysql.sock'
    mysql.columns_priv                                 OK
    mysql.db                                           OK
    mysql.event                                        OK
    mysql.func                                         OK
    mysql.general_log                                  OK
    mysql.help_category                                OK
    mysql.help_keyword                                 OK
    mysql.help_relation                                OK
    mysql.help_topic                                   OK
    mysql.host                                         OK
    mysql.ndb_binlog_index                             OK
    mysql.plugin                                       OK
    mysql.proc                                         OK
    mysql.procs_priv                                   OK
    mysql.proxies_priv                                 OK
    mysql.servers                                      OK
    mysql.slow_log                                     OK
    mysql.tables_priv                                  OK
    mysql.time_zone                                    OK
    mysql.time_zone_leap_second                        OK
    mysql.time_zone_name                               OK
    mysql.time_zone_transition                         OK
    mysql.time_zone_transition_type                    OK
    mysql.user                                         OK
    Running 'mysql_fix_privilege_tables'...
    OK

后来了解到 mysql_upgrade 这命令所做的事情主要是包括:

  1. mysqlcheck –check-upgrade –all-databases –auto-repair
  2. mysql_fix_privilege_tables
  3. mysqlcheck –all-databases –check-upgrade –fix-db-names –fix-table-names

故是建议每一次的升级过程中,mysql_upgrade这个命令最好也应该去执行,通过mysqlcheck命令检查表是否兼容新版本的数据库同时作出修复,还有个很重要的作用就是使mysql_fix_privilege_tables命令去升级权限表。

更多详细升级帮助说明详见:MySQL 官网升级文档

对于第二个疑问:升级后同现有的相对较低版本间的同步兼容性问题,做如下测试:

先在刚刚已经升级的 MySQL 版本为5.5.31 server1 和先前下架但未升级版本对应 MySQL 版本为5.5.19 的server2,这 2 台配置为互为主主。测试如下操作:

  1. 在 server1 的 test 库创建一张表 t1 字段为 id / name ,查看 server2 上的 test 库表情况,正常;
  2. 在 server1 刚创建的 t1 表 插入一条记录 ,到 server2 上查询,结果正常;
  3. 在 server2 上删除刚刚在 server1 上插入的记录,到 server1 上查看,结果正常;

查看前后 slave status 变化是否正常,MySQL自身log日志记录情况,可以判断确定同步疑问也是没问题的。

注:随手分享篇关于 Linux performance tuning tips for MySQL


Redis 实例分离迁移


Redis目前还没有成熟的集群方案,虽说2.8版本已经支持且标记为Stable版本,但一直还是处于更新迭代阶段,拿来线上环境使用还是显得不是很靠谱,个人的看法是:适当追新是应该的没错,但感觉还是不要太够于激进追新,那样只会是更冒险且是小白鼠吧。这次迁移分离主要是随Huaban项目redis服务内存占用增长使得之前每台分配的实例数显得紧张,到了必要的扩容分离处理(不大不小阶段吧)。主要备录几点刚接触过程了解到的点:

  1. 迁移分离方式–复制功能,一定要用到内存快照,所以需要充当master库所在服务器相等量的内存容量闲置;
  2. master和slave断开后重新建立连接都得重新完整的将整个快照做一次同步传输,没有类似MySQL的位置标记概念(增量备份);
  3. 从2.6版本后,slave角色默认是开启只读的,这个如果没有事先使用 config set slave-read-only no 关闭的话,在做分离迁移完后应用端切换到新的上面后,会遇到 redis slave read only 的提示,以致新的写入失败丢失;
  4. 确保本地磁盘空间足够(基本不会遇到但是不好说),因为内存快照过程redis进程是会dump一份临时的temp.rdb文件到磁盘再传输到slave服务器上加载进内存中,如果master磁盘不足的话会出现快照恶性循环失败启动新的slave进程;
  5. so,用前需要事先做好业务的预估容量规划及后期的扩容问题。

还是那句话,个人始终只是临时工就只了解些外表的一些皮毛而已,对于看得懂源码的人想了解可以读读源码去,哈哈。这里随手分享一两篇个人觉得写得不错的牛人微博或文章大家可以看看—田琪的微博Redis复制与可扩展集群搭建

最近还尝试接触了个新老玩意—TCPCopy


不忘生活


工作在忙在累,也不能忘周末的休闲骑行生活,工作最终都是为了享受生活,故不能忘。就如自己的一个愿望是:哪一天可以申请回老家乡下远程办公,看似很简单的愿望却至今未能遇到有一家公司肯批准(很多都觉得不放心、会偷懒;成为特例影响他人,但自己要求想法就是如此另类,因为个人觉得完全没任何问题。)所以一直坚持着和小伙伴们一起踩着脚踏车到周边游逛。有骑行随记图文有证据分享,如:

  1. 萧山无人村挑战盘山而上
  2. 奔杨家赏银杏
  3. 甘岭水库,长乐林场

近年来各地污染指数各种爆表,搞得本来要去徒步爬山都不能……

Nginx无缝升级及其自带信号处理

任何一种服务版本或插件升级都需要考虑最佳状态的无缝过渡升级(不影响任何请求服务的中断且无需人员的干涉)

对于 Nginx 其进程管理模式及其自带各种信号处理可以很好的控制管理 master 和 work 进程。之前用得相对比较多的基本只是修改 Nginx 配置文件后使用到 ‘HUP’ 信号处理方式来重新加载配置文件,但其实 Nginx 还有很多其他信号可以用于控制对其进程(不管是 master 或 work)做相应不同的处理的,详见官方 wiki:NginxCommandLine

对于 Master 管理进程而言:

  • ERM, INT(暴力结束进程,当前的请求不执行完成就退出)
  • QUIT (优雅退出,执行完当前的请求后退出)
  • HUP (重新加载配置文件,用新的配置文件启动新worker进程,并优雅的关闭旧的worker进程,可作为 reload 生效新配置)
  • USR1 (重新打开日志文件,可作为日志切割处理)
  • USR2 (平滑的升级nginx二进制文件,可作为无缝平滑升级过渡)
  • WINCH (优雅的关闭worker进程,master进程仍在监听服务)

对于 work 进程而言:(其实 master 进程即可对 work 进行管理,但这里指的是使用信号单独对 work 进程做处理)主要包括:TERM, INT / QUIT / USR1 信号

这里主要测试说明两种之前使用较少的 无缝平滑升级二进制文件 和 升级后如有异常的回滚 操作:

  1. 无缝平滑升级二进制文件方式
    不管对于升级Nginx到一个新的版本,或新增 / 更新 nginx module的时候,都难免需要替换Nginx 的二进制文件,所谓想无缝更新文件而不想对用户或对服务有任何请求失败或丢失的影响,这里可以借助 Nginx 的 USR2 信号处理。

    首先,编译生成新的二进制文件(只需 configure / make 即可),通常编译后默认会在 objs/nginx 直接覆盖旧的二进制文件后,对现有 master 进程发送 USR2 信号,此时现有 master 进程会将自己之前的 pid 文件重命名为 pid.oldbin
    同时执行新的 nginx 二进制文件并启动新的 master 进程,此时会同时存在运行 2 个 Nginx 实例即 2 组的新旧 master / work 进程同时处理请求。截图如下:

    这时只需将旧的 Nginx 实例的 master 直接优雅退出便可完成版本升级,即:kill -quit `ca/usr/local/nginx/logs/nginx.pid.oldbin`

    但建议暂且还是先不要将旧的 master 进程,以防新升级的版本有异常问题可以快速的回滚旧实例上,详见下面的回滚操作

  2. 新服务异常回滚操作
    接上,建议采用 kill -winch `ca/usr/local/nginx/logs/nginx.pid.oldbin` 的方式只先将旧的 Nginx 实例的 work 进程优雅关闭退出,而非直接 quit 连旧的 Nginx 实例的 master 进程也优雅退出。

    因为这样此时旧的 Nginx 实例的 master 进程还在运行着也意味着仍在监听着 socket 服务,这样一旦新版本二进制服务有异常问题时,仍可以快速的回滚旧的 Nginx 实例来提供服务,只需对旧实例的 master 进程发送 HUP 信号,此时它就会再次唤起启动 work 进程来处理请求服务,同时发送 QUIT 信号到新 Nginx 实例的 master 进程关闭退出,一切就又回滚恢复到没有更新升级前的状态。

    当然一切顺利的话,在 1 的情况下便可直接将 旧实例的 master 进程 QUIT 优雅的退出关闭,完全由新实例来提供服务处理请求。