MySQL Development Advanced article series MySQL Server (Innodb_lock_wait_timeout,innodb_support_xa,innodb _log_*)

1. Innodb_lock_wait_timeout

MySQL can automatically monitor the deadlock caused by the row lock and handle the corresponding, but the deadlock caused by the table lock cannot be automatically monitored, so this parameter is mainly used to wait for a specified time to roll back after a similar situation occurs. The system default value is 50 seconds. Users can set their own according to the business. The production environment does not recommend the use of oversized innodb_lock_wait_timeout parameter values.

-- View transaction time-out time  like ' Innodb_lock_wait_timeout ';

　　
You can also set a timeout for the current session, such as set innodb_lock_wait_timeout=1000. For the cause of the deadlock, how to view the analysis deadlock problem, how to optimize to avoid deadlocks, please check the "MySQL development advanced Article lock Problem series."

2. Innodb_support_xa

This parameter is set to support distributed transactions. The default value is on or 1, which means that distributed transactions are supported.
Distribution matters are divided into two categories:

(1) is an external XA transaction (supports multi-instance distributed transactions).

(2) is to support internal XA transactions (supporting data consistency between Binlog and Redo_log).

If this parameter is closed, according to predecessors, it may affect: 1 is the master-slave replication Binlog and Redo_log inconsistent, 2 is binlog and Redo_log transaction order inconsistencies.

--   See if distributed transactions are supported like 'innodb_support_xa';

　　

3. InnoDB _log_buffer_size

This parameter refers to the size of the log cache. The default settings are generally sufficient to meet the performance requirements of the server in the case of medium-strength write loads and a shorter transaction. If there is a peak update operation or a large load, you should consider increasing its value. If the value is set too high, memory may be wasted because it refreshes every second, so there is no need to set the amount of memory required for more than 1 seconds (it is understood that the log cache is emptied every 1 seconds after it is refreshed). Usually set to 8~16MB is sufficient. The system defaults to 16M.

--   View Log cache space size like 'innodb_log_buffer_size';

　　

16777216.0/1024.0/1024.0=16m

4. Innodb_log_file_size
This parameter is the size of each log file in a log group, also known as the transaction log file size. This parameter is important in the case of high write loads, especially large datasets . The larger the value, the higher the performance, but the side effect is that the recovery time increases when a system disaster occurs. The system defaults to 48M.
(1) Small log files make writing slower and crash recovery faster. The reason is that because the transaction log is equivalent to a write buffer, and the small log file is quickly filled, this time it needs to be flushed frequently to the hard disk, which slows down. If you generate a lot of writes, you increase the number of checkpoint writes, and if you do not refresh the data fast enough, write performance will degrade. Instead, the file space is large, giving you plenty of room to use before the refresh operation occurs.
(2) Large log files make writing faster and crash recovery slower.

--   View the size of each log file like 'innodb_log_file_size';

　　
268435456.0/1024.0/1024.0=256m

5. Innodb_log_compressed_pages

This parameter refers to: The log File page stores compression. The system defaults to on, which reduces the amount of redo log writes.

6. Innodb_log_checksums

This parameter refers to: Before writing redo log to the file, each block of redo log needs to be added checksum check bit to prevent the apply damage redo log.

7. Innodb_log_write_ahead_size

This parameter refers to the block size before the redo log is written. The system defaults to 8192 bytes.

8. Innodb_log_files_in_group

This parameter refers to: The variable controls the number of log files. The default value is 3. The log is written in a sequential fashion. Combine innodb_buffer_pool_size to set its size. Generally not set.

9. Innodb_log_group_home_dir

This parameter refers to the path where the log group resides.

--   All log parameters are as follows like 'innodb_log%';

　　

MySQL Development Advanced article series MySQL Server (Innodb_lock_wait_timeout,innodb_support_xa,innodb _log_*)