MySQL high performance and high security testing

1. Parameter description

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 was greater than 0, the MySQL server synchronizes its binary log to disk (using Fdatasync ()) After Sync_binlog commit groups is 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 Operatin G 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 your lose at the very one commit group from the binary log. However, it's also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast) .

Sync_binlog parameter Description:

When Sync_binlog is a way to control how transactions are written to the binary log. If the setting is greater than 0, the value of the Sync_binlog setting is reached for a set of transactions synchronously written to the binary log, and if set to 0, every time the transaction occurs, the transaction information in memory is not synchronized to the disk, but relies on the operating system to flush to disk frequently, and when set to 1, it is a more secure option. When you are down, you lose up to 1 transaction information, but the performance is the slowest.

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's possible when CO mmit-related I/O operations is rearranged and done in batches. You can achieve better performance by changing the default value, and then you can lose up to one second worth of Transact Ions in a crash.

• The default value of 1 is required to full ACID compliance. With this value, the log buffer was written out to the log file at each transaction commit and the flush to disk operation is performed 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 are performed on the log file . No writes from the log buffer to the log file is 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 are 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 SE Tting.
• InnoDB ' s crash recovery works regardless of the Innodb_flush_log_at_trx_commit setting. Transactions is either applied entirely or erased entirely.

For durability and consistency in a replication setup, the 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 had taken place, even though it had not. Then the durability of transactions are not guaranteed even with the setting 1, and in the worst case a power outage can EV En 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. can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use s ome Other command specific to the

Innodb_flush_log_at_trx_commit parameter Description:

Innodb_flush_log_at_trx_commit=1, the principle of full respect for the week acid transaction, each time a log buffer is flushed to the file cache of log file, and then flushed to disk. This is the worst performance. When the innodb_flush_log_at_trx_commit=0, when the mysqld down, will lose a second of the transaction, every 1 seconds log buffer logs will be written to the log file cache, and then through the operating system scheduling, often flush to disk. When innodb_flush_log_at_trx_commit=2, a second transaction is lost, each commit log buffer is written to the log file cache, and the log in the log file cache is flushed to disk, which occurs every second.

2. Test Information 2.1 High performance

Parameters	Value
Sync_binlog	100
Innodb_flush_log_at_trx_commit	2
Innodb_buffer_pool_size	3.5G
Innodb_log_file_size	300

It took about 244 seconds to insert 4,247,160 records this time.

2.2 High Security

Parameters	Value
Sync_binlog	1
Innodb_flush_log_at_trx_commit	1
Innodb_buffer_pool_size	3.5G
Innodb_log_file_size	300

It took 290 seconds to insert 4,247,160 records. Different parameter configurations insert the same amount of data, which differs by 46 seconds.

2.3 Test Scripts

3. Test Information 3.1 sync_binlog behavior

Related files and functions:

Source file:/sql/binlog.cc

Related functions:

std::p air<bool, bool> sync_binlog_file (bool force);

int Ordered_commit (THD *THD, bool all, bool Skip_commit = false);

MySQL high performance and high security testing