关于Mysql数据库性能分析点

关于Mysql数据库性能分析点

 

 

1    性能分析点

1.1    日志模式

mysql中innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数。

MYSQL的BINLOG是逻辑日志，其记录是对应的SQL语句, 记录了所有的DDL和DML语句（除了数据查询语句select），以事件形式记录，还包含语句所执行的消耗的时间，MySQL的二进制日志是事务安全型的。

INNODB存储引擎层面的重做日志是物理日志，INNODB存储引擎的重做日志在事务进行中不断地被写入，日志不是随事务提交的顺序进行写入的。

二进制日志仅在事务提交时记录，并且对于每一个事务，仅在事务提交时记录，并且对于每一个事务，仅包含对应事务的一个日志。

INNODB存储引擎的重做日志，由于其记录是物理操作日志，每个事务对应多个日志条目，并且事务的重做日志写入是并发的，并非在事务提交时写入，做其在文件中记录的顺序并非是事务开始的顺序。

1.1.1 innodb_flush_log_at_trx_commit

innodb_flush_log_at_trx_commit是 InnoDB 引擎特有的，ib_logfile的刷新方式（ib_logfile：记录的是redo log和undo log的信息）

查看参数如下：

showvariables like 'innodb_flush%';

|innodb_flush_log_at_trx_commit | 1       |

    innodb_flush_log_at_trx_commit设置为0，log buffer将每秒一次地写入logfile中，并且log file的flush(刷到磁盘)操作同时进行. 该模式下，在事务提交的时候，不会主动触发写入磁盘的操作。如果innodb_flush_log_at_trx_commit设置为1，每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去。如果innodb_flush_log_at_trx_commit设置为2，每次事务提交时MySQL会把log buffer的数据写入logfile.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

0：当mysql挂了之后，可能会损失前一秒的事务信息

2：当mysql挂了之后，如果系统文件系统没挂，不会有事务丢失

1.1.2    sync_binlog

sync_binlog：是MySQL 的二进制日志（binarylog）同步到磁盘的频率。

sync_binlog=0，当事务提交之后，MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘，而让Filesystem自行决定什么时候来做同步，或者cache满了之后才同步到磁盘。等待操作系统的fdatasync从内存flush到磁盘。

sync_binlog=1，当每进行1次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

sync_binlog=n，当每进行n次事务提交之后，MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

 

2    日志文件参数

innodb_log_file_size调整日志文件大小。

innodb_log_files_in_group调整日志文件数量。

innodb_log_group_home_dir调整日志文件位于目录。

innodb_log_file_size会影响性能，默认设置为5M，难以满足生产环境下的需求。日志文件在mysql实例第一次启动时初始化，该文件是旋转的和Oracle数据库是类似的，可以根据文件修改时间来判断日志文件的旋转频率，如果太频繁，说明日志文件太小需要扩大。innodb_log_file_size设置大小通常视innodb_buffer_pool_size而定。

参数innodb_log_buffer_size确保有足够大的日志缓冲区来保存脏数据在被写入到日志文件之前。该变量将数据存导入到内存中，可以减少大量的IO资源消耗。当事务提交时，保存脏数据，后续在刷新到磁盘。调整innodb_buffer_pool_size大小时，innodb_log_buffer_size和innodb_log_file_size也应该做出相应的调整。

innodb_log_file_size是静态的变量，需要以“干净”的方式更改并重新启动

 

3    关于Linux IO缓存

UNIX实现在内核中设有缓冲区高速缓存或页面高速缓存，大多数磁盘I/O都通过缓冲进行。

当将数据写入文件时，内核通常先将该数据复制到其中一个缓冲区中，如果该缓冲区尚未写满，则并不将其排入输出队列，而是等待其写满或者当内核需要重用该缓冲区以便存放其他磁盘块数据时，再将该缓冲排入输出队列，这种输出方式被称为延迟写（delayed write）。

为了保证磁盘上实际文件系统与缓冲区高速缓存中内容的一致性，UNIX系统提供了sync、fsync和fdatasync三个函数。

sync函数只是将所有修改过的块缓冲区排入写队列，然后返回，它并不等待实际写磁盘操作结束。

通常称为update的系统守护进程会周期性地（一般每隔30秒）调用sync函数。这就保证了定期冲洗内核的块缓冲区。命令sync(1)也调用sync函数。

fsync函数只对由文件描述符filedes指定的单一文件起作用，并且等待写磁盘操作结束，然后返回。fsync可用于数据库这样的应用程序。fsync的功能是确保文件fd所有已修改的内容已经正确同步到硬盘上，该调用会阻塞等待直到设备报告IO完成。除了同步文件的修改内容（脏页），fsync还会同步文件的描述信息（metadata，包括size、访问时间st_atime & st_mtime等等），因为文件的数据和metadata通常存在硬盘的不同地方，因此fsync至少需要两次IO写操作。

fdatasync函数类似于fsync，但它只影响文件的数据部分。而所以从性能上来看fdatasync是要优于fsync的。

 

4    mysql性能

4.1    基本配置

a)        innodb_buffer_pool_size:缓冲池是数据和索引缓存的地方，保证你在大多数的读取操作时使用的是内存。查看命令：show variables like '%buffer_pool_size%'

b)       innodb_log_file_size：这是redo日志的大小。redo日志被用于确保写操作快速而可靠并且在崩溃时恢复。可以一开始就把innodb_log_file_size设置成512M(这样有1GB的redo日志)会使你有充裕的写操作空间。如果你知道你的应用程序需要频繁的写入数据并且你使用的时MySQL 5.6，你可以一开始就把它这是成4G。

c)       max_connections:经常看到‘Too many connections'错误，是因为max_connections的值太低了。因为应用程序没有正确的关闭数据库连接，需要比默认的151连接数更大的值。在应用程序里使用连接池或者在MySQL里使用进程池有助于解决这一问题。

d)      binlog_cache_size :表示binlog能够使用的最大cache内存大小,当执行多语句事务的时候所有session的使用的内存超过max_binlog_cache_size的值时,就会报错：“Multi-statementtransaction required more than 'max_binlog_cache_size' bytes ofstorage”,设置太大的话，会比较消耗内存资源;设置太小又会使用到临时文件即磁盘IO。

e)       innodb_log_buffer_size控制在2-8M。不用太多的，里面的内存数据最慢一秒钟写到磁盘一次。具体写入方式和你的事务提交方式有关。

4.2    InnoDB配置

MySQL 5.5版本开始，InnoDB是默认的存储引擎。

a)       innodb_file_per_table：这项设置告知InnoDB是否需要将所有表的数据和索引存放在共享表空间里（innodb_file_per_table= OFF） 或者为每张表的数据单独放在一个.ibd文件（innodb_file_per_table =ON）。每张表一个文件允许你在drop、truncate或者rebuild表时回收磁盘空间。这对于一些高级特性也是有必要的，比如数据压缩。但是它不会带来任何性能收益。有非常多的表时候可以OFF掉。MySQL 5.6中，这个属性默认值是ON。

b)      innodb_flush_log_at_trx_commit：默认值为1，表示InnoDB完全支持ACID特性。

c)       innodb_flush_method: 这项配置决定了数据和日志写入硬盘的方式。一般来说，如果你有硬件RAID控制器，并且其独立缓存采用write-back机制，并有着电池断电保护，那么应该设置配置为O_DIRECT, 禁止系统cache。

d)      innodb_log_buffer_size: 这项配置决定了为尚未执行的事务分配的缓存。其默认值（1MB）一般来说已经够用了，但是如果你的事务中包含有二进制大对象或者大文本字段的话，这点缓存很快就会被填满并触发额外的I/O操作。看看Innodb_log_waits状态变量，如果它不是0，增加innodb_log_buffer_size。