Overview今天主要分享mysql数据库写压力的性能监控和调优，分为性能监控和相关两个方面调整参数。.参考：http://www.cnblogs.com/geaozhang/一、写压力性能监控1、OS层面的监控：iostat-x#iostat-x描述：rrqm/s：每秒merge的读操作数.wrqm/s：每秒合并写操作的次数。r/s：每秒完成的读I/O设备数。w/s：每秒完成的写入I/O设备的数量。rsec/s：每秒读取的扇区数。wsec/s：每秒写入的扇区数。rkB/s：每秒读取的千字节数。wkB/s：每秒写入K字节。avgrq-sz：每个设备I/O操作的平均数据大小（扇区）。avgqu-sz：平均I/O队列长度。await：每个设备I/O操作的平均等待时间（以毫秒为单位）。svctm：每个设备I/O操作的平均服务时间（毫秒）。%util：一秒内有多少百分比的时间用于I/O操作，或者一秒内有多少时间I/O队列不为空。其实监控系统的io状态主要是通过%util、r/s、w/s来查看的。一般busyness为70%，writepersecond也是比较理想的值；但如果系统当前很忙并且每秒写入量很低，则可以增加Writes。2.DB级别监控：监控各种pending(hanging)mysql>showglobalstatuslike'%pend%';+----------------------------+--------+|Variable_name|Value|+----------------------------+-------+|Innodb_data_pending_fsyncs|0|#Pendingfsync|Innodb_data_pending_reads|0|#Pendingphysicalread|Innodb_data_pending_writes|0|#Pendingwrite|Innodb_os_log_pending_fsyncs|0|#PendingStartedlogfsync|Innodb_os_log_pending_writes|0|#Pendinglogwrite+----------------------------+-------+5rowsinset(0.01sec)数值的个数writesuspends大于0，不管是什么writesuspend，如果有suspend，说明有写压力，所以最好保持为0。监控“suspend”状态值，如果大于0继续增加，报警处理。3、写速度监控：日志写、脏页写3.1、日志写速度监控mysql>showglobalstatuslike'%log%written';从innodb_os_log_written可以知道innodblog的写入量，从而判断innodb_log_buffer_size的大小是否合适，同时也可以估计一个log_file可以支持多长时间。InnoDB当缓冲区满时，每秒提交或刷新一次事务，以先发生者为准。3.2.脏页写速度监控mysql>showglobalstatuslike'%a%written';+----------------------------+---------+|Variable_name|Value|+----------------------------+----------+|Innodb_data_written|1073152|#目前写入的数据总量，单位字节|Innodb_dblwr_pages_written|7||Innodb_pages_written|58|#写入的数据页数+--------------------------+--------+3rowsinset(0.01sec)3.3。关注比例：Innodb_dblwr_pages_written/Innodb_dblwr_writes，表示一次写入多少页mysql>showglobalstatuslike'%dblwr%';+----------------------------+--------+|Variable_name|Value|+--------------------------+-------+|Innodb_dblwr_pages_written|7|#已经写入doublewritebuffer的page的次数|Innodb_dblwr_writes|3??|#doublewrite的次数+--------------------------+--------+2rowsinset(0.00sec)1.如果比例为64:1，表示doublewrite每次都写满，写压力为很高。2.如果系统的double_write比较高，那么iostat看到的wrqm/s（combinedwritevaluepersecond）就会高，因为一个高的double_write意味着每次写基本都是2M，此时会发生更多的merge，但是不要怕wrqm/s高，因为有合并是好事，就看wrqm/s和busyness能不能接受了。4.监控脏页数量mysql>showglobalstatuslike'%dirty%';+----------------------------+------+|Variable_name|Value|+--------------------------------+-------+|Innodb_buffer_pool_pages_dirty|0|#当前bufferpool的脏页数|Innodb_buffer_pool_bytes_dirty|0|#当前bufferpool的脏页总字节数+-------------------------------+--------+2rowsinset(0.01sec)mysql>showglobalstatuslike'i%total%';+-------------------------------+--------+|Variable_name|Value|+--------------------------------+--------+|Innodb_buffer_pool_pages_total|8192|#bufferpool总数据页数+-------------------------------+--------+1rowinset(0.01sec)attentionratio:Innodb_buffer_pool_pages_dirty/Innodb_buffer_pool_pages_total,占比dirtypages使用比率来检查是否有很多脏页。例如，如果脏页数为10%，则可以判断该系统可能不是面向写的系统。5.写入性能瓶颈mysql>showglobalstatuslike'%t_free';+--------------------------------+-------+|Variable_name|Value|+----------------------------+--------+|Innodb_buffer_pool_wait_free|0|+----------------------------+------+1rowinset(0.01sec)mysql>showglobalstatuslike'%g_waits';+----------------+------+|Variable_name|Value|+-------------------+--------+|Innodb_log_waits|0|+----------------+-------+1rowinset(0.00sec)说明：Innodb_buffer_pool_wait_free，如果该值大于0，表示缓冲池中没有可用的pages，等待后台flushdirtypages回来释放可用的数据pages，这会大大影响业务和挂起。innodb_log_waits，如果值大于0，说明写压力很大，有日志等待。如果Innodb_log_waits状态变量（等待日志缓冲区被刷新的次数）的值比较高并且持续增长，则可以增加日志缓冲区或减小事务大小。6.系统真实负载：行增删改查，事务提交，事务回滚mysql>showglobalstatuslike'i%rows%';+----------------------+--------+|Variable_name|Value|+--------------------+------+|Innodb_rows_deleted|0||Innodb_rows_inserted|145||Innodb_rows_read|233||Innodb_rows_updated|5|+--------------------+--------+4rowsinset(0.01sec)mysql>showglobalstatuslike'%commit%';+----------------+--------+|Variable_name|Value|+----------------+--------+|Com_commit|0||Com_xa_commit|0||Handler_commit|16|+----------------+--------+3rowsinset(0.01sec)mysql>showglobalstatuslike'%rollback%';+--------------------------+--------+|Variable_name|Value|+----------------------------+-------+|Com_rollback|0||Com_rollback_to_savepoint|0||Com_xa_rollback|0||Handler_rollback|0||Handler_savepoint_rollback|0|+---------------------------+--------+5rowsinset(0.01sec)通过监控系统的真实负载，如果业务正常，负载上升，在这一次，就要根据商优的具体情况做出相应的调整。2.写压调参在调整参数降低写压时，一定要实时关注iostat系统的各项指标。1.脏页刷新频率mysql>showvariablelike'i%depth%';默认为1024，遍历lru列表刷新脏页，值越大，脏页刷新频率越高。2、磁盘刷新的脏页数量：diskiocapacitymysql>showvariablelike'%io_c%';根据磁盘io容量调整，值越大，每次flush的脏页量越大。3.Redologtuningmysql>showvariableslike'innodb_log%';logfile大小和组数可能会导致写入抖动：需要监控日志切换的频率（文件系统层面的技巧）。4、redologmysql的刷新机制>showvariableslike'%flush%commit';这里我优化了mysql默认的flushingstrategy为1，最安全，但同时自然会带来一定的性能压力。在写入压力巨大的情况下，根据具体业务场景，以牺牲安全为代价，调整为0或2。5.定义每次日志刷新的时间mysql>showvariablelike'innodb_flush_log_at_timeout';默认为1，即每秒Logflushing设置innodb_flush_log_at_trx_commit。为了充分保证数据的一致性，一般innodb_flush_log_at_trx_commit=1，这种情况下innodb_flush_log_at_timeout的设置是没有意义的。所以这个参数的设置只对innodb_flush_log_at_trx_commit0/2有效。6、控制内存中脏页的比例mysql>showvariablelike'%dirty%pct%';在内存缓冲池空间允许的范围内，可以增加内存空间中脏页的比例，可以解决燃眉之急，降低写入压力。7、关闭doublewrite，减少写入压力mysql>showvariableslike'%doub%';双写功能，默认启用，静态参数。以上是写的情况下对mysql相关参数的监控和调优。如何调整（静态参数，动态参数）最好按照官方文档调整。更多devops和DBA内容稍后会分享，感兴趣的朋友可以关注~
