国内用不了的网站,vue 做自适应网站,网站策划用培训吗,树莓派安装wordpress作者#xff1a;叶金荣#xff0c;知数堂联合创始人#xff0c;3306pai社区联合创始人前言是什么原因导致线上数据库服务器磁盘I/O的util和iowait持续飚高#xff1f;1. 问题描述朋友小明的线上数据库突发严重告警#xff0c;业务方反馈写入数据一直堵住#xff0c;很多锁…作者叶金荣知数堂联合创始人3306pai社区联合创始人前言是什么原因导致线上数据库服务器磁盘I/O的util和iowait持续飚高1. 问题描述朋友小明的线上数据库突发严重告警业务方反馈写入数据一直堵住很多锁超时回滚了不知道怎么回事就找到我了。不管3721先采集现场的必要信息再说。a. 系统负载主要是磁盘I/O的负载数据该服务器的磁盘是由6块2T SSD硬盘组成的RAID-5阵列。从上面的截图来看I/O %util已经基本跑满了iowait也非常高很明显磁盘I/O压力太大了。那就再查查什么原因导致的这么高压力。b. 活跃事务列表可以看到有几个活跃的事务代价很高锁定了很多行。其中有两个因为太久超时被回滚了。再看一次活跃事务列表发现有个事务锁定的行更多了说明活跃业务SQL的效率不太好需要进行优化。这个算是原因之一先记下。c. 查看InnoDB状态执行 SHOW ENGINE INNODB STATUS\G 查看InnoDB状态这里只展示了几个比较关键的地方...0x7f8f700e9700 INNODB MONITOR OUTPUT...LATEST DETECTED DEADLOCK------------------------...*** (2) TRANSACTION:TRANSACTION 52970892097, ACTIVE 1 sec starting index readmysql tables in use 2, locked 280249 lock struct(s), heap size 9691344, 351414 row lock(s),undo log entries 30005### 这里很明显发生死锁的事务之一持有很多行锁需要优化SQL...update a inner join b on a.uidb.uid set a.kidif(b.okid0,b.kid,b.okid),a.aidb.aid where a.date2020-02-10...TRANSACTIONS------------Trx id counter 52971738624Purge done for trxs n:o 52971738461 undo n:o 0state: running but idleHistory list length 81...---TRANSACTION 52971738602, ACTIVE 0 sec insertingmysql tables in use 1, locked 11 lock struct(s), heap size 1136, 0 row lock(s),undo log entries 348### 同样滴也是有很多行锁...LOG---Log sequence number 565123876918590Log flushed up to 565123858946703Pages flushed up to 565121518602442Last checkpoint at 565121518602442...### 注意到Last checkpoint和LSN之间的差距非常大约为2249MB### 说明redo log的checkpoint有延迟比较厉害有可能是因为磁盘I/O太慢### 也有可能是因为产生的脏页太多太快来不及刷新----------------------BUFFER POOL AND MEMORY----------------------Total large memory allocated 201200762880Dictionary memory allocated 130361859Internal hash tables (constant factor variable factor) Adaptive hash index 3930999872 (3059599552 871400320)Page hash 23903912 (buffer pool 0 only)Dictionary cache 895261747 (764899888 130361859)File system 16261960 (812272 15449688)Lock system 478143288 (478120568 22720)Recovery system 0 (0 0)Buffer pool size 11795040Buffer pool size, bytes 193249935360Free buffers 7035886Database pages 4705977Old database pages 1737005Modified db pages 238613### 脏页比例约为2%看着还好嘛而且还有挺多free page的...d. 查看MySQL的线程状态*--------------------------------------------------| Command | Time | State | Info |--------------------------------------------------| Query | 1 | update | insert xxx| Query | 0 | updating | update xxx| Query | 0 | updating | update xxx| Query | 0 | updating | update xxx| Query | 0 | updating | update xxx--------------------------------------------------可以看到几个事务都处于updating状态。意思是正在扫描数据并准备更新肉眼可见这些事务状态时一般是因为系统负载比较高所以事务执行起来慢或者该事务正等待行锁释放。2. 问题分析及优化工作分析上面的各种现场信息我们可以得到以下几点结论a. 磁盘I/O压力很大。先把阵列卡的cache策略改成WB不过由于已经是SSD盘这个作用并不大只能申请更换成RAID-10阵列的新机器了还需等待资源调配。b. 需要优化活跃SQL降低加锁代价[rootyejr.me] desc select * from a inner join b on a.uidb.uid where a.date2020-02-10;--------------------------------------------------------------------------------| table | type | key | key_len | ref | rows | filtered | Extra |--------------------------------------------------------------------------------| a | ref | date | 3 | const | 95890 | 100.00 | NULL || b | eq_ref | uid | 4 | db.a.uid | 1 | 100.00 | Using index condition |--------------------------------------------------------------------------------[rootyejr.me] select count(*) from a inner join b on a.uidb.uid where a.date2020-02-10;----------| count(*) |----------| 40435 |----------1 row in set (0.22 sec)执行计划看起来虽然能用到索引但效率还是不高。检查了下发现a表的uid列竟然没加索引我汗。。。c. InnoDB的redo log checkpoint延迟比较大有2249MB之巨。先检查redo log的设置innodb_log_file_size 2Ginnodb_log_files_in_group 2这个问题就大了redo log明显太小等待被checkpoint的redo都超过2G了那肯定要疯狂刷脏页所以磁盘I/O的写入才那么高I/O %util和iowait也很高。建议把redo log size调整成4G、3组。innodb_log_file_size 4Ginnodb_log_files_in_group 2此外也顺便检查了InnoDB其他几个重要选项innodb_thread_concurrency 0# 建议维持设置0不变innodb_max_dirty_pages_pct 50# 由于这个实例每秒写入量较大建议先调整到75降低刷脏页的频率# 顺便缓解redo log checkpoint的压力。# 在本案例最后我们把这个值调整到了90。特别提醒从MySQL 5.6版本起修改redo log设置后实例重启时会自动完成redo log的再次初始化不过前提是要先干净关闭实例。因此建议在第一次关闭时修改以下两个选项innodb_max_dirty_pages_pct 0innodb_fast_shutdown 0并且再加上一个新选项防止实例启动后会有外部应用连接进来继续写数据skip-networking在确保所有脏页(上面看到的Modified db pages为0)都刷盘完毕后并且redo log也都checkpoint完毕(上面看到的Log sequence number和Last checkpoint at**值相等)此时才能放心的修改 innodb_log_file_size 选项配置并重启。确认生效后再关闭 skip-networking 选项对业务提供服务。经过一番优化调整后再来看下服务器和数据库的负载。可以看到服务器的磁盘I/O压力再也不会那么大了数据库中也不会频繁出现大量行锁等待或回滚的事务了。[rootyejr.me] SHOW ENGINE INNODB STATUS\gLog sequence number 565749866400449Log flushed up to 565749866400449Pages flushed up to 565749866318224Last checkpoint at 565749866315740[rootyejr.me] SHOW ENGINE INNODB STATUS\gLog sequence number 565749866414660Log flushed up to 565749866400449Pages flushed up to 565749866320827Last checkpoint at 565749866315740[rootyejr.me] SHOW ENGINE INNODB STATUS\gLog sequence number 565749866414660Log flushed up to 565749866414660Pages flushed up to 565749866322135Last checkpoint at 565749866315740[rootyejr.me] select (565749866400449-565749866315740)/1024;----------------------------------------| (565749866400449-565749866315740)/1024 |----------------------------------------| 82.7236 |----------------------------------------1 row in set (0.00 sec)[rootyejr.me] select (565749866414660-565749866315740)/1024;----------------------------------------| (565749866414660-565749866315740)/1024 |----------------------------------------| 96.6016 |----------------------------------------很明显redo log checkpoint lag几乎没有了。完美搞定写在最后遇到数据库性能瓶颈负载飚高这类问题我们只需要根据一套完整的方法论延伸阅读
