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

This article introduces you to MySQL performance-related parameters, the content is very detailed, interested friends can refer to, I hope to help you.

The common performance parameters of MySQL are as follows:

general_log

Record all executed statements. Open them when you need to analyze problems. Do not open them during normal service, so as not to affect io performance.

query_cache_size

Cache sql text and query results, if the corresponding table has not changed, the next time you encounter the same SQL, skip parsing and querying, and return the results directly.

However, the table changes very frequently, SQL is also dynamically produced, due to the need to constantly update the cache content, then the lock is very strong, but as a bottleneck. At this time, it is best to turn off this function and set the parameter to 0.

sort_buffer_size

Parameters for a single session,

When sorting, if index is not used, the session will request such a large memory space for sorting. If the value of this parameter is too small, the sorting results will be written to the hard disk, which will affect efficiency.

If it is too large, it may lead to physical memory exhaustion, resulting in OOM.

join_buffer_size

Buffer used when join cannot use index, similar to sort_buffer_size

tmp_table_size

In group by and distinct, if SQL does not use an index, an internal temporary table is used to record intermediate state. If the value is not large enough, use a physical hard disk

Innodb_buffer_pool_size

InnoDB's most important cache is used to cache innodb index pages, undo pages, and other auxiliary data. Generally set physical memory 50%~75%

Innodb_buffer_pool_instances

With this parameter, you can divide the entire buffer pool into multiple instance memory spaces, each of which independently manages its own memory and linked list to improve the concurrency capability of MySQL request processing.

Because the buffer pool is managed through a linked list, and in order to protect the page, it is necessary to lock the linked list when accessing it. In the case of multithreading, concurrent reading and writing of the buffer pool cache will have lock competition and waiting.

Officials say Innodb_buffer_pool_size over 1G Consider setting instances to split memory

Innodb_log_file_size,innodb_log_files_in_group

Two parameters determine the size of redo space, set the storage update redo larger, effectively reduce the speed of buffer pool dirty pages being eliminated, reduce checkpoint book, reduce disk I/O

However, if the setting is too large, the recovery time will be longer in case of abnormal database downtime.

Innodb_old_blocks_pct,innodb_old_blocks_time

innodb_old_blocks_pct：

Global, dynamic variable, default value 37, value range 5~95. Used to determine the proportion of old sublist in LRU list

innodb_old_blocks_time：

Global, dynamic variable, default value 1000, value range 0~2**32-1, unit ms.

Used to control the transfer policy of pages in old sublist. When a new page enters the LRU list, it will be inserted into the head of old sublist first, and then the page needs to stay innodb_old_blocks_time in old sublist for so long that the next visit to the page will move it to the head of new sublist.

This parameter is set to protect the new sublist from being filled by pages that are referenced only for a brief period.

By default, pages in the buffer are moved to the head of a new page subtable the first time they are read (i.e., hit the cache), meaning that they will remain in the buffer pool for a long time and will not be eliminated. There is a problem with this, because a table scan (such as with a select query) may put a large amount of data into the cache and eliminate a corresponding amount of old data, but it may be used only once and never used again; likewise, because read-ahead is also placed in the head of a new page child table the next time the page is accessed. These situations move pages that should be used frequently into the old pages subtable.

So 3/8 position. Pages that are first hit (when accessed) at the back are moved to the head of the list. Therefore, pages that are read into the cache but never accessed later are never placed at the head of the list and are later eliminated from the buffer pool.

MySQL provides the configuration parameter,milliseconds) reads are not identified as young, i.e. they are not moved to the head of the list. Parameter 1000, increasing this parameter will cause more pages to be eliminated from the buffer pool faster.

Innodb_flush_method

Innodb brush data and logs to disk way, this value is empty by default, in fact:

Linux default fsync

Windows default async_unbuffered

SSD and PCIE storage can use o_direct to improve performance

Innodb_doublewrite

MySQL defaults to a page size of 16k, while OS usually has a minimum I/O unit of 4k, so it may take 4 OS I/O calls to complete a page. Suppose DB crashes twice, when only a part of the page is written, resulting in a partial write.

MySQL double write is configured to ensure that committed data is not lost and data files are not corrupted when a partial write occurs.

However, if the underlying storage supports atomicity, it can be turned off twice, mainly depending on the relationship between OS page size and DB page size.

Innodb_io_capacity

Control the background to continuously flush the memory (dirty data) data of the hard disk operation, in case of periodic IO QPS drop can consider increasing the parameter settings to speed up the flush frequency

Refer to the experiment to improve Innodb_io_capacity settings, QPS has been improved

Innodb_thread_concurrency

Increasing this value when concurrency is high reduces innodb switching overhead between concurrent threads to increase concurrency throughput of the system.

innodb_flush_log_at_trx_commit

Control redo log brushing mechanism

innodb_flush_log_at_trx_commit=0

When the transaction is committed, the contents of the log buffer will not be processed, nor will the disk flushing operation of the log file in the OS cache be processed. The MySQL background master thread will flush the log buffer to the log file on the disk every 1 second.

When MySQL service is down, server is up or down:

Since transaction commit does not flush logbuffer, logbuffer is lost even if the transaction commits, but only the last 1 second transaction is lost

innodb_flush_log_at_trx_commit=1

When a transaction commits, the contents of the log buffer are written to the OS cache file, and the OS cache is flushed to the disk log file.

When MySQL service is down, server is up or down:

Since transaction commits are flushed to disk log files, no data is lost

innodb_flush_log_at_trx_commit=2

When the transaction is committed, the contents of the log buffer will be written to the OS cache file, and the MySQL background master thread will flush the log file of the OS cache to disk every 1 second.

MySQL service down, server normal:

Since transactions have been flushed to OS cache, but the server is not down, the log will still be flushed to disk, and the data will not be lost

When MySQL service goes down, server goes down:

Because transactions are only flushed to OS cache, if the server goes down and the log is not flushed to disk, transactions will be lost for 1 second.

sync_binlog

Controls how binlog synchronizes to disk

sync_binlog=0, MySQL Binlog information is written to OS cache Binlog when the transaction is committed, and the OS itself will refresh its cache. If the server goes down, all binlogs in the binlog cache will be lost.

sync_binlog=1, MySQL flushes Binlog to physical disk on each transaction commit. This is the highest security, performance loss is the largest. Especially in multi-transaction peer commit, I/O performance can have a significant impact.

But group commit can relieve stress:

binlog_group_commit_sync_delay=N, default is 0, executed regularly, wait N microseconds after commit, binlog disk brushing operation is performed

binlog_group_commit_sync_no_delay_count=N. After commit, wait for the maximum number of transactions to wait N. Ignore the binlog_group_commit_sync_delay setting and start brushing directly. Note that if binlog_group_commit_sync_delay is set to 0, this option is invalid.

However, the setting of group commit may affect the execution speed of commit. Please refer to https://www.cnblogs.com/ziroro/p/9600359.html

sync_binlog=N, which means MySQL will do a disk swipe every N transaction commits. If DB services or servers go down, some transactions will be lost

Note: After opening Binlog, MySQL will automatically treat the transaction as an XA transaction. During the transaction submission process, a unique XID will be automatically assigned, and the XID will be recorded in Binlog and redo log. The transaction commits automatically into two phases, Prepare and Commit.

Prepare phase: tell InnoDB to prepare, InnoDB changes the transaction state, and redolog to disk

Commit phase: record Binlog first, then tell InnoDB commit

binlog_format

binlog_format=STATEMENT

Write executed SQL statements to binlog, read them from the library and execute them

Advantages:

technology is mature

Reduce binlog writes

binlog contains all modified statements, not easy to audit

Disadvantages:

Some functions can no longer be slave complex, such as sleep(),last_insert_id(),udf, etc. will eliminate problems

Compared to row-based replication, insert... Select needs more locks

The isolation level must be repeatable-read, which is one of the culprits of deadlocks

binlog_format=MIXED

By default, STATEMENT logs are used. In certain cases, ROW logs are converted.

binlog_format=ROW

Default values since MySQL 5.7.7

Advantages:

Replication is the safest.

Slave also requires the fewest locks

Disadvantages:

binlog records more data

Cannot see statements fetched on master on slave because they are all events. However, you can turn on the binlog_rows_query_log_events parameter, so that binlog records events as well as the original SQL statement.

(It is recommended to use row mode for replication. Other modes may have inconsistent master-slave data)

tx_isolation

MySQL isolation level, default is repeatable-read

Read Uncommitted

Read Committed

Repeatable Read

Serializable

These four levels are getting stricter, but the performance is getting worse.

It is recommended to use Read Committed and binlog_format=ROW to confirm the consistency of binlog synchronous database master and slave database, taking into account security and meeting most businesses.

slave_parallel_workers

In MySQL 5.6, set slave_parallel_workers = 4 to have four SQL threads (coordinator threads) for parallel replication, and its status is: Waiting for an evant from Coordinator. But its parallelism is only database based. If there are multiple databases in the database instance, this setting can have a large increase in the speed of Slave replication.

The core idea is that the data submitted concurrently by tables under different databases will not affect each other, that is, the slave node can allocate a thread similar to SQL function to different schemas in the relay log to replay the transactions that have been submitted by the main database in the relay log, and keep the data consistent with the main database.

MySQL 5.7 introduces Enhanced Multi-threaded Slaves,

Set slave_parallel_workers>0 and global.slave_parallel_type='LOGICAL_CLOCK', to support concurrent execution of transactions submitted by the master library in the relay log by slave_parallel_workers worker threads in a database.

Its core idea: transactions submitted by a group can be replayed in parallel (with binary log group commit);

Transactions with the same last_committed (but different sequence_num) in the relay log of a slave machine can be executed concurrently.

The slave_parallel_type parameter can have two values:

DATABASE default, library-based parallel replication

LOGICAL_CLOCK: Parallel replication based on group commit

What are the parameters related to MySQL performance to share here, I hope the above content can be of some help to everyone, you can learn more knowledge. If you think the article is good, you can share it so that more people can see it.

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.