Configuration parameters affecting MySQL performance

Time:2021-9-7

This article will introduce the five categories of MySQL parameter settings, which we rarely touch at ordinary times. These parameters are still very useful in MySQL performance tuning and fault diagnosis.

(1) Connect

The connection usually comes from the web server. Here are some connection related parameters and how to set them.

  1、max_connections

This is the maximum number of connections allowed by the web server. Remember to use session memory for each connection (about session memory, which will be covered later in the article).

  2、max_packet_allowed

The maximum packet size is usually equal to the size of the maximum data set you need to return in a large block. If you are using remote mysqldump, its value needs to be larger.

  3、aborted_connects

Check the counter of the system status to ensure that it does not increase. If the number increases, it indicates that the client has encountered an error during connection.

  4、thread_cache_size

An inbound connection will create a new thread in MySQL. Because opening and closing connections in MySQL are cheap and fast, it is not like other databases,

There are as many persistent connections as Oracle, but pre creation of threads will not save time, which is why MySQL thread caching is needed.

If you are growing, please pay close attention to the threads created to make your thread cache larger. For 2550 or 100 www.sangpi.comthread_ cache_ Size, memory consumption is not much.

(2) Query cache

(3) Temporary table

The memory speed is quite fast, so we want all sorting operations to be carried out in memory. We can adjust the query to make thegameThe result set is smaller for memory sorting, or the variable is set larger.

  tmp_table_size

  max_heap_table_size

Whenever a temporary table is created in mysql, it will use the minimum value of these two variables as the critical value. In addition to building a temporary table on disk, it will also create many sessions, which will seize limited resources,

Therefore, it is better to adjust the query rather than set these parameters higher. At the same time, it should be noted that tables with blob or text field types will be written directly to disk.

(4) Session memory

Each session in MySQL has its own memory, which is the memory allocated to SQL queries, so you want to make it as large as possible to meet your needs. But you have to balance the number of consistent sessions in the database at the same time.

What seems a little black art here is that MySQL allocates caches on demand, so you can’t just add them and multiply them by the number of sessions, which is much larger than the typical use of MySQL.

A very good way is to start mysql, connect all sessions, and then continue to focus on the virt column of the top-level session. The number of mysqld rows is usually relatively stable, which is the actual total memory consumption,

Subtract all static MySQL memory areas to get the actual memory of all sessions, and then divide by the number of sessions to get the average value.

  1、read_buffer_size

Cache continuously scanned blocks. This cache is across storage engines, not just MyISAM tables.

  2、sort_buffer_size

The size of the execution sort cache is preferably set to 1m-2m, and then set it in the session to set a higher value for a specific query.

  3、join_buffer_size

The size of the cache allocated for executing the joint query, set it to 1m-2m, and then set it separately on demand in each session.

  4、read_rnd_buffer_size

For sorting and order by operations, it is best to set it to 1m, and then set it as a session variable to a larger value in the session.

(5) Slow query log

Slow query log is a very useful feature of MySQL.

  1、log_slow_queries

Log in MySQL parameters_ slow_ The queries parameter is set in the my.cnf file and set it to on. By default, MySQL will put the file in the data directory, and the file is named in the form of “hostname – slow. Log”, but you can also specify a name when setting this option.

  2、long_query_time

The default value is 10 seconds. You can dynamically set it from 1 to on. If the database is started, the log will be closed by default.

As of 5.1.21 and the version with Google patch installed, this option can be set in microseconds, which is a great function, because once you eliminate all queries with a query time of more than 1 second, the adjustment is very successful, which can help you eliminate the SQL problem before the problem becomes larger.

  3、log_queries_not_using_indexes

It’s a good idea to turn on this option, which actually records the query that returns all rows.

Recommended Today

OC basis

IOS development interview essential skills chart.png What are objects and what are the objects in OC? An object is an instance of a class; Is an instance created through a class, which is generally called an instance object; Common objects in OC include instance objects, class objects, and metaclass objects; What is a class? What […]