MySQL高性能以及高安全测试

1.  参数描述

 sync_binlog

Command-Line Format

--sync-binlog=#

Option-File Format

sync_binlog

System Variable Name

sync_binlog

Variable Scope

Global

Dynamic Variable

Yes

 

Permitted Values

Platform Bit Size

32

Type

numeric

Default

0

Range

0 .. 4294967295

 

Permitted Values

Platform Bit Size

64

Type

numeric

Default

0

Range

0 .. 18446744073709547520

  1. If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after sync_binlog commit groups are written to the binary log. The default value of sync_binlog is 0, which does no synchronizing to disk—in this case, the server relies on the operating system to flush the binary log‘s contents from time to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one commit group from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

 

 

sync_binlog参数说明:

当sync_binlog是控制事务写入二进制日志的方式。如果设置大于0,则达到sync_binlog设置的值一组事务同步写入到二进制日志;如果设置为0,则每当事务发生,在内存中的事务信息,不是同步刷到磁盘,而是依赖于操作系统时常刷新到磁盘;当设置为1,则是更安全的选项,当宕机后,最多失去1个事务的信息,但是性能确实最慢的。

 

 

 innodb_flush_log_at_trx_commit

Command-Line Format

--innodb_flush_log_at_trx_commit[=#]

Option-File Format

innodb_flush_log_at_trx_commit

System Variable Name

innodb_flush_log_at_trx_commit

Variable Scope

Global

Dynamic Variable

Yes

 

Permitted Values

Type

enumeration

Default

1

Valid Values

0

1

2

Controls the balance between strict ACID compliance for commit operations, and higher performance that is possible when commit-related I/O operations are rearranged and done in batches. You can achieve better performance by changing the default value, but then you can lose up to one second worth of transactions in a crash.

  • The default value of 1 is required for full ACID compliance. With this value, the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file.
  • With a value of 0, any mysqld process crash can erase up to a second of transactions. The log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file. No writes from the log buffer to the log file are performed at transaction commit. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
  • With a value of 2, any mysqld process crash can erase up to a second of transactions. The log buffer is written out to the log file at each commit. The flush to disk operation is performed on the log file once per second. Once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.
  • As of MySQL 5.6.6, InnoDB log flushing frequency is controlled by innodb_flush_log_at_timeout, which allows you to set log flushing frequency to N seconds (where Nis 1 ... 2700, with a default value of 1). However, any mysqld process crash can erase up to N seconds of transactions.
  • DDL changes and other internal InnoDB activities flush the InnoDB log independent of the innodb_flush_log_at_trx_commit setting.
  • InnoDB‘s crash recovery works regardless of the innodb_flush_log_at_trx_commit setting. Transactions are either applied entirely or erased entirely.

For durability and consistency in a replication setup that uses InnoDB with transactions:

  • If binary logging is enabled, set sync_binlog=1.
  • Always set innodb_flush_log_at_trx_commit=1.

Caution

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt InnoDBdata. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the 

 

 innodb_flush_log_at_trx_commit参数说明:

innodb_flush_log_at_trx_commit=1,完全尊周ACID事务的原则,每提交一次,log buffer中的日志刷新到log file的文件缓存,然后在刷新到磁盘。这种的性能最差。当innodb_flush_log_at_trx_commit=0的时候,当mysqld宕掉的时候,会丢失一秒的事务,每1秒log buffer中的日志会先写到log file的文件缓存,然后通过操作系统调度,时常刷新到磁盘。当为innodb_flush_log_at_trx_commit=2,会丢失一秒的事务,每次提交log buffer的日志会写到日志文件缓存,日志文件缓存中的日志刷新到磁盘则是每秒钟发生。

 

2. 测试信息

2.1高性能

 

 

 

 

 

 

参数

Sync_binlog

100

Innodb_flush_log_at_trx_commit

2

Innodb_buffer_pool_size

3.5G

Innodb_log_file_size

300

 

 

 

此次插入4247160条记录,花了时间大概为244秒。

2.2高安全

参数

Sync_binlog

1

Innodb_flush_log_at_trx_commit

1

Innodb_buffer_pool_size

3.5G

Innodb_log_file_size

300

 

 

插入4247160条记录花了290秒。不同的参数配置插入相同数据量,相差了46秒的时间。

2.3     测试脚本

 

 

3.  测试信息

3.1 sync_binlog行为

   相关的文件以及函数:

     源文件:/sql/binlog.cc

 

相关函数: 

std::pair<bool, bool> sync_binlog_file(bool force);

int ordered_commit(THD *thd, bool all, bool skip_commit = false);

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。