Five configuration parameters closely related to MySQL Performance _ MySQL

The following articles mainly introduce the five configuration parameters that closely affect MySQL Performance. I saw the five configuration parameters that closely affect MySQL performance on the relevant website a few days ago, I think it's good. I just want to share it with you. I hope you will have some gains. (1) connection the following articles mainly introduce the five configuration parameters that closely affect MySQL Performance, I saw the five configuration parameters closely related to MySQL performance on the relevant website a few days ago. I think they are good and I will share them with you. I hope you will have some gains.

(1) connection

The connection usually comes from the Web server. The following lists 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 the session memory for each connection (this article will cover the session memory ).

2. max_packet_allowed

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

3. aborted_connects

Check the counters in the system status to make sure they do not grow. if the number increases, it indicates that the client encountered an error when connecting.

4. thread_cache_size

An inbound connection creates a new thread in MySQL, because it is cheap and fast to open and close the connection in MySQL, and it is not like other databases, for example, there are so many persistent connections in Oracle, but the pre-creation of threads will not save time, which is why MySQL thread cache is required.

If you are growing, pay close attention to the created thread so that your thread cache is larger. for thread_cache_size of 2550 or 100, the memory usage is not large.

(2) query cache

(3) temporary tables

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

Tmp_table_size

Max_heap_table_size

Whenever you create a temporary table in MySQL, it uses the minimum values of these two variables as the critical value. in addition to creating a temporary table on the disk, it also creates many sessions, these sessions will seize limited resources, so it is best to adjust the query instead of setting these parameters higher. at the same time, note that tables with BLOB or TEXT fields will be directly written to the disk. In-depth introduction to MySQL two-way replication technology

(4) session memory

Each session in MySQL has its own memory, which is the memory allocated to the SQL query, so you want to make it as large as possible to meet your needs. However, you have to balance the number of consistent sessions in the data base at the same time. The black art here is that MySQL allocates cache on demand. Therefore, you cannot just add them and multiply them by the number of sessions. this is estimated to be much larger than the typical MySQL usage.

The best practice is to start MySQL, connect all sessions, and continue to pay attention to the VIRT column of top-level sessions. the number of mysqld rows is usually relatively stable, which is the actual total memory usage, after all the static MySQL memory areas are subtracted, the actual memory of all sessions is obtained. then, the average value is obtained by dividing the number of sessions.

1. read_buffer_size

Cached continuously scanned blocks. this cache is cross-storage engine, not just a MyISAM table.

2. sort_buffer_size

The size of the cache for sorting. it is best to set it to 1 M-2 M, 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 area allocated for execution of the joint query is set to 1-2 MB, and then separately set as needed in each session.

4. read_rnd_buffer_size

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

(5) slow query logs

Slow log query is a useful feature of MySQL.

1. log_slow_queries

In MySQL parameters, the log_slow_queries parameter is in my. set it in the cnf file and set it to on. by default, MySQL puts the file in the data directory, and the file uses "host name-slow. log, but you can also specify a name for this option.

2. long_query_time

The default value is 10 seconds. you can set it dynamically. The value ranges from 1 to on. if the database is started, the log is disabled by default. As of 5.1.21 and the version with the Google patch installed, this option can be set in microseconds. this is a great feature, because once you have eliminated all queries whose query time exceeds 1 second, the adjustment is very successful, which helps you eliminate the problem SQL before the problem increases.

3. log_queries_not_using_indexes

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

Summary

We have introduced five categories of MySQL parameters. we usually seldom touch them. These parameters are very useful for MySQL Performance Tuning and fault diagnosis.

Cache queries in MySQL include two resolution query plans and returned datasets. if the basic table data or structure changes, the items in the query cache will be invalid.

1. query_cache_min_res_unit

In the MySQL parameter, the query_cache_min_res_unit query cache blocks are allocated in this size. use the following formula to calculate the average size of the query cache, and set this variable according to the calculation result, mySQL uses the query cache more effectively, caches more queries, and reduces memory waste.

2. query_cache_size

This parameter sets the total size of the query cache.

3. query_cache_limit

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

 
 

  
  
qcache hit ratio = qcache_hits / (qcache_hits + com_select) 
 
 

Use

 
 

  
  
SQL> show status like ‘qcache%’; 
  
  
SQL> show status like ‘com_%’; 
 
 

Find these variables.

 
 

  
  
average query size = (query_cache_size – qcache_free_memory)/qcache_queries_in_cache 
 
 

Use

 
 

  
  
SQL> show variables like ‘query%’; 
  
  
qcache_* status variables you can get with: 
  
  
SQL> show status like ‘qcache%’; 
 
 

Obtain the value of query_cache_size.

The above content is an introduction to the five important configuration parameters that closely affect MySQL Performance.