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

This article mainly shows you "what are the relevant parameter settings for MySQL InnoDB", the content is simple and clear, and I hope it can help you solve your doubts. Let me lead you to study and learn this article "what are the relevant parameter settings for MySQL InnoDB".

Innodb_buffer_pool_size

This parameter is similar to the SGA configuration of oracle. When the host is used as a mysql database server, it is generally configured as 60% / 80% of the total memory.

Innodb_buffer_pool_instances

This parameter is used to set the number of memory buffer pool instances and divide the memory configured by innodb_buffer_pool_size into N parts. This parameter takes effect only when the configured memory is less than 1G. When the database has multiple sessions for database operations, it is used to process tasks in multiple memory blocks in parallel. The general configuration value is less than or equal to the number of server CPU.

Max_connections

This parameter is used to set the maximum number of connections for MySQL. When the database is faced with high concurrency, this value needs to be adjusted to a reasonable value to meet the concurrency requirements of the business and prevent the database from rejecting connections.

Max_user_connections

This parameter is used to set the number of connections for a single user.

Innodb_log_buffer_size

This parameter is used to set the log buffer size. Generally, you do not need to set it too large. You only need to save the data log for 1 second operation. By default, mysql writes the log to disk once per second by polling.

Innodb_stats_on_metadata

This parameter is used to set whether to collect statistics dynamically. When enabled, it will affect the performance of the database (usually shut down, refresh manually sometime, or refresh regularly) if it is closed, you need to configure the database to schedule tasks and refresh the statistics of the database regularly.

Innodb_lock_wait_timeout

This parameter is used to control the timeout of the lock. The default is 50. This value should be noted that if a special service really takes a long time, it cannot be configured too short.

Innodb_thread_concurrency

This parameter is used to set a limit on the number of threads that can enter the innodb layer

It is recommended to set it to twice the number of cpu cores of the machine, but in most cases, the default value is sufficient.

Innodb_write_io_threads

This parameter is used for the number of threads writing dirty pages (the number of threads for database writes, for concurrency)

Innodb_read_io_threads

This parameter is used for the number of threads reading file blocks from disk (the number of threads during database read operations, for concurrency)

If the CPU has 2 8 cores, you can set it as follows:

Innodb_read_io_threads = 8

Innodb_write_io_threads = 8

If the database has more reads than writes, you can set:

Innodb_read_io_threads = 10

Innodb_write_io_threads = 6

These are all the contents of the article "what are the relevant parameter settings for MySQL InnoDB". Thank you for reading! I believe we all have a certain understanding, hope to share the content to help you, if you want to learn more knowledge, welcome to follow the industry information channel!

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.