The following article mainly introduces the five configuration parameters that have a close relationship with MySQL performance.I saw information on the five configuration parameters that have a close relationship with MySQL performance on related websites a few days ago.Share it with everyone, and hope you can gain something.

(一)Connect

The connection usually comes from a web server.Some parameters related to the connection are listed below and how to set them.

1, max_connections

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

2, max_packet_allowed

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

3, aborted_connects

Check the system status counter and make sure it has not increased.If the number increases, it means that the client has encountered an error while connecting.

4, thread_cache_size

Inbound connections will create a new thread in MySQL, because opening and closing connections in MySQL are cheap and fast.It does not have as many continuous connections as other databases, such as Oracle, but the thread is created in advance.It does not save time, which is why MySQL thread caching is required.

If you are growing, please pay close attention to the threads created to make your thread cache larger.For thread_cache_size of 2550 or 100, the memory usage is not much.

(2) Query cache

(3) Temporary table

Memory speed is quite fast, so we hope that all sorting operations are performed in memory.We can adjust the query to make the result set smaller to achieve memory sorting, or set the variable to be 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 temporary tables on disk, it will also create many sessions, which will seize limited resources., So 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.In-depth understanding of MySQL two-way replication technology

(4) Session memory

Each session in MySQL has its own memory.This memory is the memory allocated for 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 bit of black art here is that MySQL allocates caches on demand, so you can't just add them and multiply by the number of sessions, which is estimated to be much larger than the typical usage of MySQL.

The best practice is to start MySQL, connect to all sessions, and then continue to focus on the VIRT column of the top session, mysql The number of d rows usually remains relatively stable.This is the actual total memory usage.After subtracting all static MySQL memory areas, you get the actual memory for 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 sorting buffer is preferably set to 1M-2M, and then set in the session to set a higher value for a specific query.

3, join_buffer_size

The size of the buffer area allocated by the joint query is set to 1M-2M, and then set 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 it can be set to a larger value as a session variable in the session.

(5) Slow query log

Slow query log is a very useful feature of MySQL.

1, log_slow_queries

Set the log_slow_queries parameter in the MySQL parameter in the my.cnf file and set it to on.By default, MySQL will put the file in the data directory, and the file will be 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 set it dynamically, the value is from 1 to set it to on, if the database is started, the log will be closed by default.As of 5.1.21 and the version with the Go ogle patch installed, this option can be set in microseconds.This is a A great feature, 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 problematic SQL before the problem becomes larger.

3, log_queries_not_using_indexes

Enable this option is a good idea, it actually records the query that returns all rows.

Summary

We introduced the five types of MySQL parameter settings.We usually rarely touch them.These parameters are still very useful when performing MySQL performance tuning and fault diagnosis.

The cache query in MySQL includes two analytical query plans and the returned data set.If the underlying table data or structure changes, the items in the query cache will be invalidated.

1, query_cache_min_res_unit

The blocks in the query_cache_min_res_unit query cache in the MySQL parameters are allocated at this size.Use the following formula to calculate the average size of the query cache.Set this variable according to the calculation result, and MySQL will use the query cache more effectively, and the cache will be more efficient.More queries reduce the waste of memory.

2, query_cache_size

This parameter sets the total size of the query cache.

3, query_cache_limit

This parameter tells MySQL to discard queries larger than this size.Generally, large queries are relatively rare, such as running a batch to perform statistics on a large report, so those large result sets should not fill up the query cache.

Use

Find these variables.