High performance MySQL (8) Optimizing server configuration: Concurrency and load

When MySQL encounters high concurrency, it may encounter bottlenecks that have never been encountered.

First, InnoDB concurrent configuration

InnoDB is designed for high performance, and in recent years his ascension is obvious, but still imperfect.

InnoDB has its own thread scheduler that controls how threads access data in the kernel, and what they can do at once in the kernel. The most basic way to limit concurrency is to use the innodb_thread_concurrency variable, which limits how many threads can enter the kernel at once, and 0 indicates no limit.

Theoretically, the following formula can give you a value like this

Concurrent value = CPU quantity * Disk Count * 2

In practice, however, it would be better to use a smaller value, and an experiment must be done to find the best value for the appropriate system.

If the thread that has entered the kernel exceeds the allowable number, the new thread will not be able to enter the kernel.

InnoDB uses two-segment processing to try to get threads into the kernel as high as possible. The two-segment strategy reduces the switch caused by operating system scheduling. The thread sleeps innodb_thread_sleep_delay for the first time, then retries into the kernel, and if it is still unable to enter the kernel, put in a waiting thread queue for the operating system to process.

The default hibernation time for the first phase is 10000 microseconds, and when the CPU has a large number of threads in the "Hibernate before queue" state and is not fully utilized, changing this value may be helpful in a high concurrency environment, especially when there are large numbers of small queries.

Once the thread enters the kernel, he will have a certain number of "bills (tickets)" That can be "free" to return to the kernel without further concurrent checking. The Innodb_concurrency_tickets option controls the number of bills and rarely changes, unless there are a lot of very long-running queries.

There is another concurrency bottleneck for the commit phase, where I/O is very intensive because of the need for a refresh operation. The innodb_commit_concurrency variable controls how many threads can be committed at the same time, and if there are more thread collisions, this value can be modified.

A new approach is to use line pool to limit concurrency, MARIADB has been implemented, and Oracle a business plug-in can provide thread pool functionality for MySQL5.5.

Second, MyISAM concurrent configuration

Under certain conditions MyISAM also allows concurrent inserts and reads, but the first thing to understand is how MyISAM deletes and inserts rows.

MyISAM deletes do not rearrange the entire table, they simply mark the row as deleted and leave a "hole" in the table. MyISAM fills these holes at the time of insertion and, if there is no hole, inserts the new line at the end of the table.

Although MyISAM is a table-level lock, it can still read while appending new rows, which can only read all the data at the start of the query, and the newly inserted data is not visible, thus avoiding inconsistent reads.

However, if some of the data in the middle of the table changes, it is still difficult to provide consistent reading. MVCC solves this problem by using the version number to verify, but MyISAM is not supported, so unless the insert operation is at the end of the table, concurrent inserts cannot be supported.

By setting Concurrent_insert This variable, you can configure MyISAM to open concurrent inserts, which can be matched with the following values

0: Concurrent inserts are not allowed, and all inserts have mutexes on the table.

1: Default value, as long as there are no holes in the table, MyISAM allows concurrent inserts.

2:mysql5.0 is valid later, forcing concurrent inserts to the end of the table, even if there is a hole in the table. If no thread reads the data from the table, MySQL will put the new line in the hole, and using this setting usually makes the table more fragmented.

Third, the load configuration

When the server is running with a full load, try to log all the query statements, as this is the best way to view the resource usage.

Optimizing blob and text scene blobs and text are special types for MySQL, and one of the most important things is that the server cannot store blob and text values in the memory temp table, Percona server does not have this restriction, So if a query involves blobs and text, and you need to use a temporary table, the temporary table is created on disk immediately.

It is usually better to convert the value to varchar using the substring () function.

For InnoDB, if there are lots of big characters, it's best to put them together in a single column and share an extended space better than each field with its own space. It can also be compressed with compress and then stored as BLOBs and text.

When MySQL must sort the blob and the text field, it only uses prefixes, and max_sort_length can set how big the prefix is.

This article comes from "phper-every day a little ~" blog, please be sure to keep this source http://janephp.blog.51cto.com/4439680/1321073