Shulou(Shulou.com)05/31 Report--

This article will explain in detail the example analysis of the configuration and use of the storage engine InnoDB in MySQL. The editor thinks it is very practical, so I share it with you for reference. I hope you can get something after reading this article.

Innodb achieves high concurrency through multi-version concurrency control (MVCC) and implements four isolation levels of the SQL standard, default to the REPEATABLE level. At the same time, a strategy called next-key locking is used to avoid phantom. In addition, the InnoDB storage engine also provides high performance and high availability functions such as insert buffering (insert buffer), second read and write (double write), adaptive hash indexing (adaptive hash index), pre-read (read ahead) and so on.

How to set InnoDB as the default engine:

1. Check out the mysql storage engine: mysql > show engines. InnoDB | YES, indicating that this mysql database server supports the InnoDB engine.

two。 Set InnoDB as the default engine: add default-storage-engine=INNODB under [mysqld] in the configuration file my.ini

3. Restart the mysql server

4. Log in to the mysql database, mysql > show engines. If InnoDB | DEFAULT appears, it means that InnoDB is set to the default engine successfully.

Common configuration parameters for InnoDB:

# InnoDB stores data dictionaries and buffer pools for internal data structures. 16MB is large enough. Innodb_additional_mem_pool_size = 16M#InnoDB is used to cache data, index, lock, insert buffer, data dictionary, etc. # if it is a dedicated DB server and is based on InnoDB engine, you can usually set 50% of the physical memory. # if it is a non-dedicated DB server, you can first try to set it to 1ram 4 of memory, and then adjust if there is a problem. The default value is 8m, which is very lame. This is also the reason why many people think that InnoDB is not as easy to use as MyISAM. Innodb_buffer_pool_size = 4G#InnoDB shared tablespace initialization size, default is 10MB, also very crappy X, changed to 1GB, and automatic extension innodb_data_file_path = ibdata1:1G:autoextend#. If you do not understand this option, it is recommended to set it to 1, which can better protect data reliability and have a certain impact on performance. But the log buffer of controllable innodb_flush_log_at_trx_commit = 1#InnoDB is usually set to 64MB to suffice the size of innodb_log_buffer_size = 64M#InnoDB redo log, and the setting of 256MB is usually sufficient for innodb_log_file_size = 256M#InnoDB redo log filegroup, and usually set to 2 is sufficient for innodb_log_files_in_group = independent tablespace mode with InnoDB enabled, so it is easy to manage innodb_file_per_table = status file with InnoDB enabled Easy for administrators to view and monitor innodb_status_file = set transaction isolation level to READ-COMMITED to improve transaction efficiency, usually meet transaction consistency requirements transaction_isolation = READ-COMMITTED other configuration options also need to note: # set the maximum number of concurrent connections, if the front-end program is PHP, it can be increased appropriately, but not too large # if the front-end program uses connection pooling, it can be appropriately reduced To avoid excessive number of connections max_connections = 6 seconds, the maximum number of connection errors can be appropriately increased to prevent frequent connection errors, the front-end host is rejected by mysql max_connect_errors = 10000 seconds to set the slow query threshold. It is recommended to set the minimum 1 second long_query_time = limit to set the maximum temporary table value, which is assigned to each connection. Sometimes it is a kind of damage to performance query_cache_size = "if it is an InnoDB engine-based DB, the key_buffer_size dedicated to the MyISAM engine can be set small, 8MB is enough # if the MyISAM engine is the main engine, it can be set larger, but not more than 4G#. Here, it is strongly recommended not to use the MyISAM engine. The default is to use the InnoDB engine key_buffer_size = 8M# to set the connection timeout threshold.

Several suggestions related to the design of InnoDB engine data tables:

1. All InnoDB data tables create a business-independent self-increasing number as the primary key, which is very helpful to ensure performance.

two。 Do not use text/blob. If you really need to use it, split it out into a separate table as much as possible.

3. TIMESTAMP type storage is recommended for timestamps

4. It is recommended that IPV4 addresses be stored in INT UNSIGNED type.

5. It is recommended to use TINYINT storage instead of CHAR (1) for gender and other yes-or-no logic

6. When storing long text content, it is recommended to use JSON/BSON format.

InnoDB lock type

The InnoDB storage engine implements the following two standard row-level locks

A shared lock (S Lock) that allows a transaction to read a row of data

An exclusive lock (X Lock) that allows a transaction to delete or update a row of data

Compatibility of shared locks and exclusive locks

Note:

(1) S lock and X lock are row locks, compatibility refers to the compatibility with the same record (row) lock.

(2) transaction T1 has acquired a shared lock for row R, and another transaction T2 can acquire a shared lock for row R immediately, which is called lock compatibility. If transaction T3 wants to obtain the exclusive lock of row R, it must wait for transaction T1 and T2 to release the shared lock on row R, which becomes lock incompatibility.

This is the end of this article on "sample analysis of the configuration and use of storage engine InnoDB in MySQL". I hope the above content can be of some help to you, so that you can learn more knowledge. if you think the article is good, please share it for more people to see.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.