MySQL parameters related to innodb

Source: Internet
Author: User
Some colleagues asked a few MySQL parameter settings. Here we will record the following: innodb_thread_concurrency, which is used to limit the number of threads that can enter the innodb layer when they enter innod.

Some colleagues asked a few MySQL parameter settings. Here we will record the following: innodb_thread_concurrency, which is used to limit the number of threads that can enter the innodb layer when they enter innod.

Some colleagues asked a few MySQL parameter settings. Here is a simple record:

Innodb_thread_concurrency, used to limit the number of threads that can enter the innodb Layer

When read_row/write_row/update_row/delete_row is called at the innodb layer, innodb_srv_conc_enter_innodb is checked.

If it is full, it will wait for innodb_thread_sleep_delay to try it once in milliseconds.

If it fails again, it will enter a FIFO queue sleep

After the innodb layer completes the operation, innodb_srv_conc_exit_innodb is called to exit the innodb layer.

When the thread enters, it obtains a period of time slice innodb_concurrency_tickets. Within the time slice range, this thread does not need to be detected and directly enters innodb

Theoretically, we can set innodb_thread_concurrency to (number of CPUs + number of disks) * 2, but this depends on the specific application scenario.

Innodb_commit_concurrency, used to limit the number of threads in the innodb layer commit stage. In most cases, the default value is sufficient.

--------------------------

Innodb_write_io_threads

Number of threads used to write dirty pages

Innodb_read_io_threads

Number of threads used to read file blocks from a disk

Thread_cache_size

After the thread is used, it is not exited, but stored in the list, which reduces the overhead of repeatedly creating threads,

The setting of this value should be related to memory: mem = (thread_cache_size) * (thread_stack)

Max_connections

The maximum number of connection requests allowed by the MySQL layer. Note that setting too large may cause a large overhead: thread switching overhead. Currently, a popular optimization solution is to use a thread pool to store request queues and establish a limited number of work threads to process connections. In other words, even if max_connections is set to a large value, innodb_thread_concurrency is also applied.

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.