lol服务器显示40ms,一个简单insert 语句执行 40ms 原因剖析

背景：一个简单的带有主键的insert 语句，居然要 40ms ，开发受不了，要求降低

因此我们要关注的的 数据从插入落地的IO 中间都干了什么

一、MySQL的文件

首先简单介绍一下MySQL的数据文件，MySQL 数据库包含如下几种文件类型：

1)数据文件 (datafile)

存放表中的具体数据的文件。

2)数据字典

记录数据库中所有innodb表的信息。

3)重做日志 (redolog)

记录数据库变更记录的文件，用于系统异常crash(掉电)后的恢复操作，可以配置多个(配置这个参数inodb_log_files_in_group)比如 ib_logfile0、 ib_logfile1。

4)回滚日志 (undolog)

也存在于mysql 的ibdata文件，用户记录事务的回滚操作。注在mysql5.6以上版本可以拆开出来，单独文件夹存在。

5)归档日志 (binlog)

事务提交之后，记录到归档日志中。

6)中继日志 (relaylog)

从master获取到slave的中转日志文件，sql_thread则会应用relay log并重放于从机器。

7)其他日志slowlolg, errorlog, querylog

这里慢日志也经常用。可以结合pt-query-digest工具和anemometer一起展示出来。

对于以上文件的IO访问顺序可以分为顺序访问 比如binlog ，redolog ，relay log是顺序读写，datafile，ibdata file是随机读写，这些IO访问的特点决定了在os 配置磁盘信息时候，如何考虑分区 ，比如顺序写可以的log可以放到SAS盘 ，随机读写的数据文件可以放到ssd或者fio高性能的存储。

二写操作

为了保证数据写入操作的安全性，数据库系统设置了 undo，redo 保护机制，避免因为os或者数据库系统异常导致的数据丢失或者不一致的异常情况发生。

1、先写undo log。

2、在内存更新数据，这步操作就在内存中形成了脏页，如果脏页过多，checkpoint机制进行刷新，innodb_max_dirty_pages_pct决定了刷新脏页比例。innodb_io_capacity参数可以动态调整刷新脏页的数量，innodb_lru_scan_depth这个参数决定了刷新每个innodb_buffer_pool的脏页数量。

3、记录变更到redo log，prepare这里会写事务id。innodb_flush_log_at_trx_commit决定了事务的刷盘方式。为0时，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行。该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下，MySQL会每秒执行一次 flush(刷到磁盘)操作。

4、写入binlog这里会写入一个事务id这里有个sync_binlog参数决定多个事务进行一次性提交。

5、redo log第二阶段，这里会进行判断前2步是否成功，成功则默认commit，否则rollback。刷入磁盘操作。这里是先从脏页数据刷入到内存2M大小的doublewrite buffer，然后是一次性从内存的doublewrite buffer刷新到共享表空间的doublewrite buffer，这里产生了一次IO。然后从内存的内存的doublewrite buffer刷新2m数据到磁盘的ibd文件中，这里需要发生128次io。然后校验，如果不一致，就由共享表空间的副本进行修复。这里有个参数innodb_flush_method决定了数据刷新直接刷新到磁盘，绕过os cache。

6、返回给client。

如果有slave，第4步之后经过slave服务线程io_thread写到从库的relay log ，再由sql thread应用relay log到从库中。

关于性能？

写undo redo log ，binlog的过程中都是顺序写，都会很快的完成，随机写操作，inset_buffer功能。

对于非聚集类索引的插入和更新操作(5.5 版本及以上支持Update/Delete/Purge等操作的buffer功能)，不是每一次都直接插入到索引页中，而是先插入到内存中。具体做法是：如果该索引页在缓冲池中，直接插入；否则，先将其放入插入缓冲区中，再以一定的频率和索引页合并，就可以将同一个索引页中的多个插入合并到一个IO操作中，改随机写为顺序写，大大提高写性能。

关于数据安全，这是数据库写入的重点？

1，2，3过程失败就是事务失败，因为此时还未写入磁盘，对磁盘中的数据无影响，返回事务失败给client，从库也不会受到影响。 4，5过程失败的时候或者已经将写成功返回给客户，可以根据redo log的记录来进行恢复，如果出现部分写失效请参考《double write》。

MySQL的写redo log的第一个阶段会把所有需要做的操作做完，记录数据变更，第二阶段的工作比较简单 ，只做事务提交确认。如果写入binlog成功，而第二阶段失败，MySQL启动时也会将事务进行重做，最终更新到磁盘中。MySQL 5.5+的smei sync可以更好的保障主从的事务一致性。

三、文件访问方式

IO 访问的方式分为两种顺序读写和随机读写， 在MySQL的io过程中可以以此来将数据库文件分类

顺序读写：重做日志ib_logfile*，binlog file。

随机读写：innodb表数据文件，ibdata文件。

根据系统的访问类型，对硬件做如下分类：读多(SSD+RAID)、写多FIO(flashcache)、容量密集(fio + flashcache)。

由于随机io会严重降低系统的性能，在当前的硬件水平下，可以考虑选择奖数据库服务器配置ssd/fusionio。

四、影响IO的参数和策略

影响mysql io的参数有很多个，这里罗列几个重要的参数。

innodb_buffer_pool_size

该参数控制innodb缓存大小，用于缓存应用访问的数据，推荐配置为系统可用内存的80%。

binlog_cache_size

该参数控制二进制日志缓冲大小，当事务还没有提交时，事务日志存放于cache，当遇到大事务cache不够用的时，mysql会把uncommitted的部分写入临时文件,等到committed的时候才会写入正式的持久化日志文件。

innodb_max_dirty_pages_pct

该参数可以直接控制Dirty Page在BP中所占的比率，当dirty page达到了该参数的阈值，就会触发MySQL系统刷新数据到磁盘。

innodb_flush_log_at_trx_commit

该参数确定日志文件何时write、flush。

为0，log buffer将每秒一次地写入log file中，并且log file的flush(刷到磁盘)操作同时进行.该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。

为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去.

为2，每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

注意：由于进程调度策略问题，这个“每秒执行一次flush(刷到磁盘)操作”并不是保证100%的“每秒”。

sync_binlog

sync_binlog的默认值是0，像操作系统刷其他文件的机制一样，MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

当sync_binlog =N (N>0) ，MySQL 在每写 N次 二进制日志binary log时，会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

innodb_flush_method

该参数控制日志或数据文件如何write、flush。可选的值为fsync，o_dsync，o_direct，littlesync，nosync。

数据库的I/O是一个很复杂和细致的知识层面，涉及数据库层和OS层面的IO写入策略，也和硬件的配置有关，

【SQL Sever】将SQL Sever中的一个数据表的数据导出为insert语句

例如:这SQL   Sever中的一张数据表,想要将这张数据表中的数据  转化成一个一个的insert语句存储在txt的文档中,那么不论走到那里这个insert语句一执行,我们就能将这个数据表中的数据 ...

Tomcat剖析(二)：一个简单的Servlet服务器

Tomcat剖析(二):一个简单的Servlet服务器 1. Tomcat剖析(一):一个简单的Web服务器 2. Tomcat剖析(二):一个简单的Servlet服务器 3. Tomcat剖析(三) ...

Tomcat剖析(一)：一个简单的Web服务器

Tomcat剖析(一):一个简单的Web服务器 1. Tomcat剖析(一):一个简单的Web服务器 2. Tomcat剖析(二):一个简单的Servlet服务器 3. Tomcat剖析(三):连接器 ...

40 | insert语句的锁为什么这么多？

在上一篇文章中,我提到 MySQL 对自增主键锁做了优化,尽量在申请到自增 id 以后,就释放自增锁. 因此,insert 语句是一个很轻量的操作.不过,这个结论对于“普通的 insert 语句”才有 ...

使用notepad++/excle快速将cvs文件转换为insert语句技巧以及注意点

使用notepad++/excle快速将cvs文件转换为insert语句技巧以及注意点 业务场景 最近nc项目经理从第三方弄来了一个300w行的csv文件,让导入数据库做处理,出现了下列问题: csv ...

40 insert语句的锁

40 insert语句的锁 上一篇文章中对mysql自增主键锁做了优化,尽量在申请到自增id后,就释放自增锁. 因此,insert语句是一个很轻量的操作,不过,这个结论对于”普通的insert”才生效 ...

如何DIY一个简单的反弹Shell脚本

00起因 之前在一个服务器上做测试的时候需要从本地连到服务器上,但是服务器没有开ssh服务,但是有python环境,想着自己写一个脚本可以从自己本地连接到服务器,然后服务器端可以将处理完的请求结果返回 ...

1020关于mysql一个简单语句的执行流程

MySQL的语句执行顺序 转自http://www.cnblogs.com/rollenholt/p/3776923.html MySQL的语句一共分为11步,如下图所标注的那样,最先执行的总是FRO ...

随机推荐

FineReport关于tomcat集群部署的方案

多台服务器集群后,配置权限.数据连接.模板.定时调度等,只能每台服务器一个个配置,不会自动同步到所有服务器. 针对上述情况,在FineReport中提供新集群部署插件,将xml配置文件.finedb/ ...

win7 x64 vs2010 directShow开发环境配置

近来工作需要,要用dirrectShow写一个视频播放的demo验证自己的想法.开发环境配置了好久都没有成功,最后终于弄完,现在记录下来,以后有同学遇到同样问题,可以以此法解决. windows SD ...

inline-block 和 float 的区别

1.float元素会自动成为一个块元素. 2.float元素,会脱离文档流!   默认脱离文档流的元素的z-index值是比没有脱离文档流的元素高的! 3.float:没有上下哦,  上下用margi ...

Memcached应用总结

Memcached应用总结 memcached是一款高性能的分布式缓存系统,凭借其简单方便的操作,稳定可靠的性能广泛应用于互联网应用中,网上关于memcached介绍的资料也很多,最经典的资料就是&l ...

python学习第十一天 -- 函数式编程

在介绍函数式编程之前,先介绍几个概念性的东西. 什么是函数式编程? 函数式编程的特点: 1.把计算视为函数而非指令; 2.纯函数式编程:不需要变量,没有副作用,测试简单; 3.支持高阶函数,代码简洁. ...

《JavaScript设计模式与开发实践》读书笔记之模板方法模式

1. 模板方法模式 1.1 面向对象方式实现模板方法模式 以泡茶和泡咖啡为例,可以整理为下面四步 把水煮沸 用沸水冲泡饮料 把饮料倒进杯子 加调料 首先创建一个抽象父类来表示泡一杯饮料 var Bev ...

三星note4，微信公众号开发，页面闪退

在做微信公众号开发时,使用三星note4测试,有一个select框闪退的问题出现. 出现该问题的原因是该选择框,设置了appearance:none;属性.查找了官方解释是: 所有主流浏览器都不支持 ...

Java异常处理-----自行处理

自行处理 1.try{//可能发生异常的代码 }catch(异常类 变量名){//处理}. 2.案例除法运算的异常处理. 3.如果没有进行try catch处理,出现异常程序就停止.进行处理后,程序会 ...

【网络】IP子网划分详解

1.IP地址组成                                IP地址组成示意图 IP地址由32位二进制组成,32位二进制分成了4字节,每字节8位,字节之间用符.(点)分隔,为了方便 ...

LoadRunner简单介绍----性能自动化测试工具

在做性能测试中,我认为技术可以说是武功心法,工具则是一把利剑,有一把好的利剑可以帮助自己更好的完成性能测试工作.在这里简单介绍一下LoadRunner,带大家一起来认识一下这把尚方宝剑. 一.性能测试 ...