InnoDB provides MySQL with a transaction-safe (ACID compatible) storage engine with the commit, rollback, and crash recovery capabilities. InnoDB locks at the row level and also provides an Oracle-style consistent non-locking read in the SELECT statement. These features increase multi-user deployment and performance. There is no need to expand locking in InnoDB because row-level locking is suitable for very small spaces in InnoDB. InnoDB also supports FOREIGN KEY enforcement. In SQL queries, you can freely mix InnoDB type tables with other MySQL table types, even in the same query.
Why learn Innodb tuning:
InnoDB is designed for maximum performance when Mysql handles huge amounts of data. Its CPU efficiency may be unmatched by any other disk-based relational database engine. Innodb is favored in websites or applications with a large amount of data.
On the other hand, Innodb can also ensure that the master and slave data are consistent in the database replication operation.
How to tune MySQL servers for a heavy InnoDB workload
Memory utilization
First, introduce one of the most important parameters of Innodb: innodb_buffer_pool_size
This parameter is similar to MyISAM’s key_buffer_size, but there are also differences. This parameter mainly caches the index, data of the innodb table, and the buffer when inserting data. Optimize the primary parameters for Innodb acceleration.
The principle of this parameter for allocating memory: the default allocation of this parameter is the only 8M, which can be said to be a very small value. If it is a dedicated DB server, then it can account for 70%-80% of the memory. This parameter cannot be changed dynamically, so the allocation needs to be considered. If the allocation is too large, Swap will take up too much, making the Mysql query extremely slow. If your data is relatively small, you can assign your data size + 10% as the value of this parameter. For example: the data size is 50M, then assign this value to innodb_buffer_pool_size=64M
innodb_buffer_pool_size=4G
Daily value
innodb_log_file_size parameter: Specify the size of the daily value
Allocation principle: the size of several daily value members is almost equal to your innodb_buffer_pool_size. The upper limit is 4G for each daily value. Generally, it is better to control the sum of several LOG files within 2G. The specific situation also depends on your transaction size, data size as a basis.
innodb_log_file_size=256M
innodb_log_files_in_group parameter: Specify how many daily value groups you have. Generally, we can use 2-3 daily value groups. The default is two.
innodb_log_files_in_group=3
innodb_log_buffer_size parameter: the buffering of transactions in memory
innodb_log_buffer_size=3M
innodb_flush_logs_at_trx_commit parameter: control the way transactions are submitted. This parameter has only 3 values, 0, 1, 2, please confirm the level that you can accept. The default is 1, please do not change the main library. Higher performance can be set to 0 or 2 but will lose a second of transactions.
innodb_flush_logs_at_trx_commit=1
File IO allocation, space occupation
innodb_file_per_table: Make each Innodb table have its own independent table space. If you delete the file, you can reclaim that part of the space.
innodb_file_per_table=1
innodb_file_io_threads: file read and write IO number, this parameter only works on Windows. On Linux, it will only equal 4.
innodb_open_files: defines how many files Innodb will keep open while working in innodb_file_per_table mode.
