Detailed description of InnoDB parameters,

Detailed description of InnoDB parameters,

1. query InnoDB parameters of MySQL 5.5 and comment on the following:
[Root @ localhost etc] # grep-I innodb my. cnf;

T_innodb; otherwise, slaves may diverge from the master.

Uncomment the following if you are using InnoDB tables#If you want to use InnoDB, the following configuration is required.

Innodb_data_home_dir =/usr/local/mysql/data#Directory sharing settings for InnoDB tables. If the settings are not performed in my. cnf, InnoDB uses the datadir directory of MySQL as the default directory.

Innodb_data_file_path = ibdata1: 10 M: autoextend#Specify the path and size of the data file separately.

Innodb_log_group_home_dir =/usr/local/mysql/data# Path of the InnoDB log file.

Innodb_buffer_pool_size = 16 M# InnoDB is used to buffer data and index memory at high speed. Larger settings can reduce disk I/O when accessing data. You can set it to 80% of the physical memory on a dedicated database server. Do not set it too large, because competition for physical memory usage may affect page calls of the operating system.

Innodb_additional_mem_pool_size = 2 M# The size of the memory combination (memory pool) That InnoDB uses to store data dictionary information and other internal data structures. The ideal value is 2 M and is removed from MySQL 5.7.4.

Innodb_log_file_size = 5 M# The size (in MB) of each log file in the log group ).

Innodb_log_buffer_size = 8 M# The buffer size before InnoDB writes logs to log disk files. The ideal value is 1 M to 8 M.

Innodb_flush_log_at_trx_commit = 1# Usually set to 1, which means that the log has been written to the disk before the transaction is committed, the transaction can run longer, and the repair capability after the service crashes. If you are willing to compromise this security, or if you are running a relatively small transaction, you can set it to 0 to reduce the disk I/O of log files. This option is set to 0 by default.

Innodb_lock_wait_timeout = 50# Before rooled back, InnoDB transactions will wait for the timeout time (in seconds ).

2.Some parameter changes and explanations.

Lower_case_table_names = 1#1: All table names are converted to lowercase and stored on the disk. The names are case-insensitive. Other values: 0: the name of the table saved by the create table statement, which is case sensitive. 2: The original value is saved and case-insensitive.

Try number of CPU's * 2 for thread_concurrency cpu# Total number of threads. 5.7. In earlier versions, this parameter is valid and can be viewed through show variables like '% thread %.

Thread_concurrency = 8

Innodb_additional_mem_pool_size and innodb_use_sys_malloc are removed from MySQL 5.7.4. Innodb_additional_mem_pool_size# Store data dictionaries. If no data dictionary exists, obtain the bin record from the system to the err. log. After the linux system memory management maturity, mysql gave up its own management of this part of memory allocation. URL: http://mysql.taobao.org/monthly/2016/04/01/

Explicit_defaults_for_timestamp = true # Specify the default value for the timestap field. (True: displays the specified not null attribute. If the value is null when the attribute is added, an error is returned)

The http://www.jb51.net/article/71052.htm may report an SQL error if a running database adds this field. We recommend that you add this parameter to the new database to improve the data logic.

Skip-name-resolve ##*Skip domain name resolution to resolve ip to host nameHttp://www.45fan.com/a/question/21253.html)

After adding:

[Warning] IP address

'192. 168.1.17' cocould not be resolved: Name or service not known avoid the above warning and improve connection Performance

. It is recommended to add.

Log_timestamps = SYSTEM # Time Zone settings for http://www.ttlsa.com/mysql/mysql-5-7-kengdieparam-log_timestamps/ logs, such as Error Log date time zone error, recommended to add fix

3. Detailed explanation of InnoDB parameters of MySQL

Mysql InnoDB Parameters

Innodb_data_home_dir	This isDirectory sharing settings for InnoDB tables.If the settings are not performed in my. cnf, InnoDB uses the datadir directory of MySQL as the default directory.. If you set an empty string, you can set the absolute path in innodb_data_file_path.
Innodb_data_file_path	Specify the path and size of the data file separately.The complete path of the data file is composed of innodb_data_home_dir and the set value. The file size is specified in MB. Therefore, "M" is required after the file size is specified ". InnoDB also supports the abbreviation "G", 1G = 1024 M. Starting from 3.23.44, you can set the data file size to be greater than 4 GB on operating systems that support large files. In other operating systems, data files must be smaller than 2 GB. The total data file size must be at least 10 MB. In the MySQL-3.23, this parameter must be explicitly specified in my. cnf. This is not required in MySQL-4.0.2 and later versions, and the system creates a 16 MB auto-extended data file ibdata1 under the datadir directory of MySQL by default. You can also use a RAW raw disk partition (raw devices) as a data file. For details about how to specify them in my. cnf, see section 12.1.
Innodb_mirrored_log_groups	Number of copies of Log File groups set to protect dataThe default value is 1. Set it in numeric format in my. cnf.
Innodb_log_group_home_dir	IThe path of the nnoDB log file.It must be set with the same value as innodb_log_arch_dir. If it is not explicitly specified, two 5 MB ib_logfile... files will be created in the datadir directory of MySQL by default.
Innodb_log_files_in_group	Number of log files in the log Group.InnoDB writes files in a circular mode (circular fashion. Value 3 is recommended. Set it in numeric format in my. cnf.
Innodb_log_file_size	The size (in MB) of each log file in the log group ).If n is the number of log files in the log group, the ideal value is 1 MB to 1/n of the buffer pool (buffer pool) size set below. A large value can reduce the number of times the buffer pool is refreshed, thus reducing disk I/O. However, a large log file means that it takes a longer time to recover data during the crash. The total log files must be less than 2 GB, and 3.23.55 and 4.0.9 must be less than 4 GB. Set it in numeric format in my. cnf.
Innodb_log_buffer_size	The buffer size before InnoDB writes logs to log disk files. The ideal value is 1 M to 8 M.Large log buffering allows transactions to be committed only to transactions without saving logs into the disk ). Therefore, if a large transaction is processed, setting a large log buffer can reduce disk I/O. Set it in numeric format in my. cnf.
Innodb_flush_log_at_trx_commit	Usually set to 1, which means that the log has been written to the disk before the transaction is committed, the transaction can run longer, and the repair capability after the service crashes.If you are willing to compromise this security, or if you are running a relatively small transaction, you can set it to 0,To reduce the disk I/O of log files.This option is set to 0 by default.
Innodb_log_arch_dir	The directory where fully written log files wocould be archived if we used log archiving. The parameters set here must be The same as innodb_log_group_home_dir.This parameter can be ignored from 4.0.6.
Innodb_log_archive	This value is always 0. Since the recovery from the backup is suitable for MySQL to use its own log filesNo longer neededArchive InnoDB log files. This option is set to 0 by default.
Innodb_buffer_pool_size	InnoDB is used to buffer data and index memory at high speed. Larger settings can reduce disk I/O when accessing data. You can set it to 80% of the physical memory on a dedicated database server. Do not set it too large, because competition for physical memory usage may affect page calls of the operating system.Set it in numeric format in my. cnf.
Innodb_additional_mem_pool_size	InnoDB is used to store the size of data dictionary information and memory pools of other internal data structures. The ideal value is 2 M,If more tables exist, you need to reassign them here. If InnoDB uses up all the memory in this pool, it allocates memory from the operating system and writes error messages to the MySQL error log. Set it in numeric format in my. cnf.
Innodb_file_io_threads	IThe file I/O thread in nnoDB. Usually set to 4,However, in Windows, you can set a larger value to increase disk I/O. Set it in numeric format in my. cnf.
Innodb_lock_wait_timeout	Before rolling back, InnoDB transactions will wait for the timeout time (in seconds ).InnoDB automatically checks its own transaction deadlocks when the table is locked and the transaction is rolled back. If you use the lock tables command or use another transaction-safe table processor (transaction safe table handlers than InnoDB) in the same transaction, a deadlock that InnoDB cannot notice may occur. In this case, timeout will be used to solve this problem.The default value of this parameter is 50 seconds.. Set it in numeric format in my. cnf.
Innodb_flush_method	This parameter is only related to Unix. The default value of this parameter is fdatasync. Another setting item is O_DSYNC. This only affects the log file dump,Dump data with fsync in Unix. InnoDB versions start from 3.23.40b. in Unix, fdatasync is specified as fsync and O_DSYNC is specified as O_SYNC. Because there are still some problems in some Unix environments, 'data' versions is not used.
Innodb_force_recovery	Warning: this parameter can only be used when you want to dump data from a corrupted database!The value range may be 1-6. View the following section 'forcing recovery 'to understand the specific meaning of this parameter. A value greater than 0 indicates the security of InnoDB to prevent users from modifying data. This parameter is available starting from 3.23.44. Set it in numeric format in my. cnf.
Innodb_fast_shutdown	InnoDB does not clear the insert buffer before it is disabled.This operation may take several minutes, but in extreme cases it may take several hours.If this parameter is set to 1, InnoDB will skip this process and close it directly.This parameter is available starting from 3.23.44 and 4.0.1. Starting from 3.23.50, the default value of this parameter is 1.
Innodb_thread_concurrency	INnoDB tries to make the operating system process used by the InnoDB service less than or equal to the value set here. The default value of this parameter is 8.If the computer system performance is low or innodb_monitor shows a multi-thread waiting signal, set this value to a smaller value. If your computer system has a very high processor and disk system, you can set this value a little higher to make full use of your system resources. We recommend that you set the value to the number of processors + number of disks. This parameter is available starting from 3.23.44 and 4.0.1. Set it in numeric format in my. cnf.