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

This article mainly explains the "MySQL parameter adjustment method", the content of the article is simple and clear, easy to learn and understand, now please follow the editor's train of thought slowly in depth, together to study and learn "MySQL parameter adjustment method"!

1 MySQL parameter adjustment 1.1 tx_isolation transaction parameters 1.1.1 transaction characteristics

Transactions have four characteristics: Atomiocity, Consistency, Isolation and Durability, which are referred to as ACID features.

Atomicity: a transaction is the logical unit of work of a database, and all operations included in the transaction are either done or not done.

Consistency: the result of transaction execution must be to change the database from one consistent state to another.

Isolation: the execution of one transaction cannot be interfered with by other transactions. That is, the operations and the data used within a transaction are isolated from other transactions, and the transactions executed concurrently do not interfere with each other.

Persistence: once a transaction is successfully committed, the modification of the data in the database is persistent. And then the rest.

1.1.2 four transaction isolation levels

Dirty reading is allowed when READ UNCOMMITED:SELECT, that is, SELECT reads data modified by other transactions that has not yet been committed.

READ COMMITED:SELECT cannot be read repeatedly, that is, the same query statement is executed twice in the same transaction. If the query data is modified and submitted by other transactions during the period between the first query and the second query, the data read is inconsistent.

REPEATABLE READ:SELECT can be read repeatedly, that is, the same query statement is executed twice in the same transaction, and the data is always consistent.

SERIALIZABLE: the only difference from repeatable readability is that ordinary SELECT statements are changed to SELECT by default. . LOCK IN SHARE MODE . That is, to add sharing trivial to the data involved in the query statement, blocking other transactions to modify the real data.

1.1.3 isolation level selection

Considering the application and performance of the system, it is recommended to use the default REPEATABLE READ level, or READ COMMITED level.

1.2 innodb_buffer_pool_size

This is the most important setting for InnoDB and has a decisive impact on InnoDB performance. The default setting is only 8m, so InnoDB performance is poor under the default database settings. On a database server with only the InnoDB storage engine, you can set up 60-80% memory. To be more precise, set the memory size by 10% larger than InnoDB tablespaces if the memory capacity allows. It is used to set the size of the memory area used to cache InnoDB indexes and data blocks, similar to the key_buffer_size parameters of the MyISAM storage engine and, of course, more like Oracle's db_cache_size. To put it simply, when we manipulate an InnoDB table, all the data returned or any index block used in the de-data process will walk through this area of memory.

Like key_buffer_size for MyISAM engine, innodb_buffer_pool_size sets the size of the largest memory area required by InnoDB storage engine, which is directly related to the performance of InnoDB storage engine, so if we have enough memory, we can set this parameter to hit enough to put as many InnoDB indexes and data as possible into the cache area until all.

We can calculate the cache hit ratio through (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%, and adjust the size of the innodb_buffer_pool_size parameter according to the hit rate for optimization.

1.3 innodb_flush_log_at_trx_commit

This parameter sets the refresh rate of log information in memory when the transaction is committed. There are 3 values. The default setting is 1, that is, synchronous refresh:

At 0, the log buffer is written to the log file once a second, and the log file is refreshed on disk. The crash of any mysqld process deletes the last second transaction before the crash

= 1, when each transaction commits, the log buffer is written to the log file, and the log file is refreshed on disk. Truly ACID . Slow speed

At = 2, when each transaction commits, the log buffer is written to the file, but the log file is not refreshed to disk. Only when the operating system crashes or power goes down will the last second transaction be deleted, otherwise the transaction will not be lost.

1.4 innodb_log_buffer_size

INNODB log cache size, disk speed is very slow, directly write log to disk will affect the performance of InnoDB, this parameter sets the size of log buffer, generally 4m. If you have a large blob operation, you can increase it appropriately.

1.5 innodb_log_file_size

The size of the INNODB log file, which determines the recovery speed. If the recovery is too large, it will be slow, and if it is too small, it will affect the query performance. Generally, using 1GB can strike a balance between performance and recovery speed.

1.6 innodb_data_file_path

This parameter specifies the space for table data and index storage, which can be one or more files. The last data file must be automatically expanded, and only the last file allows automatic expansion. In this way, when the space is used up, the auto-extension data file will automatically grow (in 8MB) to accommodate the additional data. For example: innodb_data_file_path=/disk1 / ibdata1:900M;/disk2/ibdata2:50M:autoextend two data files on different disks (). The data is first placed in ibdata1, and when it reaches 900m, the data is placed in ibdata2. Once 50 MB of 8MB is reached, ibdata 2 will automatically grow in units. If the disk is full, you need to add a data file to the other disk.

1.7 innodb_data_home_dir

This parameter sets the directory where the tablespace data is placed, by default in the data directory of mysql, specified by the MySQL parameter datadir. Setting to a different partition from the MySQL installation file can improve performance. You can use it with innodb_data_file_path to specify a common location with innodb_data_home_dir, and then specify a file name through inndo_data_file_path.

1.8 innodb_file_io_threads

This parameter specifies the number of file I / O threads available to the InnoDB table, which is recommended to be set to 4 on non-Windows platforms.

1.9 innodb_flush_logs_at_trx_commit

This parameter sets the processing of log information in memory when the transaction is committed.

1) = 1, when each transaction commits, the log buffer is written to the log file, and the log file is refreshed on disk. Truly ACID . Slow. The default is this value.

2) at 2, when each transaction commits, the log buffer is written to the file, but the log file is not refreshed to disk. Only when the operating system crashes or power goes down will the last second transaction be deleted, otherwise the transaction will not be lost.

3) when 0, the log buffer is written to the log file once a second, and the log file is refreshed on disk. The crash of any mysqld process deletes the last second transaction before the crash.

1.10 innodb_flush_method

Affects the method of server flush data or log files. Set how InnoDB synchronizes IO

1) Default-use fsync ().

2) O_SYNC opens files in sync mode, which is usually slow.

3) O_DIRECT, using Direct IO on Linux. Skipping the operating system's file system Disk Cache and letting MySQL read and write disks directly can significantly improve speed, especially on RAID systems. Avoid additional data replication and double buffering (mysql buffering and OS buffering). Avoid double buffering (double buffering) and reduce the pressure of swap. Performance can be improved in most cases. Note, however, that if there is not enough RAID cache, there will be trouble writing IO.

1.11 tmp_table_size

Tmp_table_size, which specifies the maximum value of the memory temporary table, which is allocated by each thread. (it is the minimum values of tmp_table_size and max_heap_table_size that actually limit. If the memory temporary table exceeds the limit, MySQL automatically converts it to a disk-based MyISAM table. This parameter does not limit the memory table created by create tables and is stored in the specified tmpdir directory. Default:

Mysql > show variables like "tmpdir"

1.12 max_heap_table_size

This variable defines the size of the memory table (memory table) that the user can create. This value is used to calculate the maximum row value of the memory table. If the temporary table in memory exceeds this value, MySQL automatically converts it to a MyISAM table on the hard disk. This variable supports dynamic changes, that is, set @ max_heap_table_size=#, but it is of no use for existing memory tables unless the table is recreated (create table) or modified (alter table) or truncate table. Service restart will also set the existing memory table to the value of global max_heap_table_size. This variable, along with tmp_table_size, limits the size of internal memory tables.

1.13 long_query_time

Long_query_time, set a time limit for SQL execution in seconds. Beyond this time, the test returns an error. This parameter can be set dynamically: set global long_query_time=1800

1.14 join_buffer_size

If two tables are associated with a query, but the associated field does not have an index, the memory space allocated using this parameter.

1.15 key_buffer_size

The index block of the MyISAM table allocates a buffer that is shared by all threads. Key_buffer_size is the size of the index block buffer. The key buffer is the key cache. Using key_buffer_size combined with the Key_blocks_unused state variable and the buffer block size, you can determine the proportion of the key buffer used. The buffer block size can be obtained from the key_cache_block_size server variable. The proportion of buffers used is:

1-(Key_blocks_unused * key_cache_block_size) / key_buffer_size)

Note: MyISAM data file reading depends on the operating system's own IO cache, if there is a MyISAM table, reserve more memory for the operating system.

1.16 wait_timeout

Wait_timeout, which sets the idle timeout for non-interconnected sessions in s.

1.17 interactive_timeout

Interactive_timeout, which sets the idle timeout for interconnected sessions in s.

Thank you for your reading, the above is the content of "MySQL parameter adjustment method", after the study of this article, I believe you have a deeper understanding of the MySQL parameter adjustment method, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

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.