Mysql binlog log optimization and ideas

Mysql binlog log optimization and ideas are completed after the database is installed. For binlog parameter settings, some parameter adjustments are made to meet business needs or maximize performance. Mysql logs mainly affect I/O performance. This time, we mainly focus on binlog logs. Check the binary log related parameter mysql> show variables like '% binlog % '; + metric + | Variable_name | Value | + metric + -------------------- + | binlog_cache_size | 1048576 | metric | OFF | metric | 18446744073709547520 | max_binlog_size | 1073741824 | sync_binlog | 0 | + ------------------------------ -- + ---------------------- + 5 rows in set (0.14 sec) www.2cto.com where the innodb_locks_unsafe_for_binlog parameter is a unique binlog-related parameter of the innodb Storage engine. Binlog_cache_size is disabled by default: the default size is 37268, that is, 32 K. we can calculate the binlog_cache_size parameter above, which is 1048576/1024/1024 = 1 M and adjusted according to the transaction needs. This parameter indicates the cache size of the SQL statement containing binary logs in the transaction. How to Understand binary log cache is the memory allocated to each client on the premise that the server supports the transaction storage engine and the server enables the binary log (-log-bin option, yes. Each client can allocate binlog cache space of the set size. We can use the following two state variables to determine the usage of binlog_cache_size: 1 binlog_cache_use: Thenumber of transactions that used the temporary binary log cache. (number of transactions cached using binary logs) www.2cto.com 2 Binlog_cache_disk_use: the number oftransactions that used the binary log cache but that exceeded the value of binlog_cache_size andused a temporary file to store changes from the transaction. use binary log cache and the value reaches the value set by binlog_cache_size. Use temporary files to store data from transactions. Max_binlog_cache_size: The default value is 18446744073709547520. This value is large enough. This parameter corresponds to binlog_cache_size, which indicates the Maximum cache size that binlog can use. If there are too many transactions in the system and the value of this parameter is set to small, an error is returned. Max_binlog_size: 1073741824 = the maximum value of 1G binlog. Generally, it is set to m or 1G. Generally, it cannot exceed 1G. This parameter cannot strictly control the binlog size, especially when a large transaction is encountered, and the binlog reaches the end again. To ensure transaction integrity, do not switch logs, write All SQL statements to the current log. This oracle redo log is a bit different (You can google it if you are interested ). Mysql records the logic changes of the mysql database, which is called an event. It is actually a query statement that causes database changes. Sync_binlog: parameters not only affect database performance, but also affect database data integrity. The Sync_binlog Parameter options include www.2cto.com 1 Sync_binlog = 0, which means that after the transaction is committed, mysql will not refresh the information in binlog_cache to the disk using Disk Synchronization commands such as fsync (file system synchronization, the filesystem determines when to synchronize data, or when the cache is full before synchronizing data to the disk. 2 sync_binlog = n. After each n commits, mysql runs a Disk Synchronization command such as fsync to forcibly write data in binlog_cache to the disk. Sync_binlog = 0, which is the default database setting and has the best performance and is also the most dangerous. Once the system crash is enabled, all binlog information in binlog_cache will be lost. When synn_binlog is set to 1, synn_binlog consumes the most security performance and completes a transaction and synchronizes it once. The system crash only discards the unfinished transaction information. I did not perform tests in this regard. The performance gap between 1 and 0 has been set. Some people have said that in high-concurrency systems, the performance of the two sets can be as high as 5 times or more. This requires us to make a choice, whether it is the best performance or the safest. MATERIALS: mysql performance tuning and Architecture Design www.2cto.com http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.htmlhttp://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html Author: aeolus_pu