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

This article mainly introduces "optimizing SQL system variable analysis". In daily operation, I believe many people have doubts about optimizing SQL system variable analysis. The editor consulted all kinds of data and sorted out simple and easy-to-use operation methods. I hope it will be helpful for you to answer the doubts of "optimizing SQL system variable analysis". Next, please follow the editor to study!

1. Global memory buffer

1) key_buffer_size

This variable is a global memory buffer that stores only MyISAM index information. The index data in the corresponding .MYI file is read from disk and stored in this buffer. To resize the key_buffer_size, simply count the size of the total index in all MyISAM tables, and then resize the data over time.

When there is not enough space in this index code buffer to store new index data, the old page will be overwritten with the least recently used method.

2) innodb_buffer_pool_size

Innodb_buffer_pool_size is a global memory buffer used to store all InnoDB data and indexes. This is an important buffer for databases that fully use InnoDB and must be allocated correctly. Incorrect allocation of this buffer may result in additional disk IO overhead and reduced query performance.

The common method is to set innodb_buffer_pool_size to 80% of RAM, but in many cases this setting is unreasonable. For example, the size of RAM is 50G, while the total amount of database is only 2G.

You can use the SHOW GLOBAL STATUS or SHOW ENGINE INNODB STATUS command to monitor the usage of the InnoDB buffer pool.

Mysql > SHOW GLOBAL STATUS LIKE 'innodb_buffer%'

3) innodb_additional_mem_pool_size

The innodb_additional_mem_pool_size variable defines a memory pool for InnoDB-specific data dictionary information. For this variable, there is no good way to determine its optimal value, it is generally set to 10m.

4) query_cache_size

The query_cache_size variable is a global memory buffer used to store queries that are often cached. Use the query_cache_type variable to enable and disable query caching in general. When enabled, the value of query_cache_size may be 0, which means that there are no queries to be cached. MySQL instances can still support caching at a certain time by dynamically changing the value of query_cache_size.

2. Global / session memory buffer

1) max_heap_table_size

This variable defines the maximum capacity of the MySQL MEMORY storage engine table. When the capacity of a table exceeds the maximum value, the application receives the following information.

Mysql > set session max_heap_table_size=1024*1024

Query OK, 0 rows affected (0.02 sec)

Mysql > create table test (id int (10), user_type varchar (20), code int (10)) engine=memory

Query OK, 0 rows affected (0.02 sec)

Mysql > insert into test select * from t_user_info_tmp_bak

ERROR 1114 (HY000): The table 'test' is full

This variable has a global default value, and the value of this variable can also be specified on each thread in the above example. MySQL does not impose any restrictions on the total capacity of all MEMORY tables. This variable is used only for a single table.

The total size of the MEMORY storage engine table can be determined by the SHOW TABLE STATUS command and the INFORMATION_ schema. Tables table.

2) tmp_table_size

The minimum values in the max_heap_table_size and tmp_table_size variables define the maximum capacity of the internal table, which is used for query execution stored in memory. If using temporary appears in the extra column in the result of the explain select, you can judge that the internal temporary table was used during query execution.

MySQL uses the memory storage engine to support these internal temporary tables, but the capacity of the internal temporary tables exceeds the minimum in max_heap_table_size and tmp_table_size, and MySQL creates a MyISAM disk-based table in a temporary location.

3. Session buffer

1) join_buffer_size

Join_buffer_size defines the memory buffer for each thread, which is used when the query must join the datasets of two tables and cannot use indexes. This buffer is reserved for each thread's index join operation. You can prove that this buffer is used by querying that the value of the Extra column in the plan is Using join buffer. It is recommended that this buffer be set to the default size. Increasing the size of this buffer does not speed up the connection operation.

2) sort_buffer_size

This variable defines the per-thread buffer that each thread uses to sort the result set. You can determine that this buffer is used by querying that the value of the extra column in the plan is Using file-sort. Increasing the size of this buffer is not recommended because it is fully allocated to each request and may slow down the execution of the query when the default value is too large.

3) read_buffer_size

This buffer is used when SQL queries perform consecutive table data scans. Increasing the size of this buffer is recommended only if a large amount of continuous table data is scanned.

4) read_rnd_buffer_size

This buffer is used to store data that is read as the result of a sort operation. This buffer differs from read_buffer_size in that the contiguous data he reads is related to how the data is stored on disk. Increasing the size of this buffer is recommended only when executing large ORDER BY statements.

4. Variables related to basic tools

1) slow_query_log

This Boolean variable enables logging of slow-executing queries, which will report queries whose index execution time exceeds the value of the long_query_time variable.

2) slow_query_log_file

This variable defines the file name that saves all recorded query files when the slow query log function is turned on. This is a global variable and its value can be changed dynamically.

3) general_log

This variable is used to enable a comprehensive query log that records the execution of each query. This variable can only be enabled or disabled on each server instance value. This is a global variable and its value can be changed dynamically.

4) general_log_file

This variable defines the file name that records all SQL queries when full logging is enabled, which is a global variable whose value can be changed dynamically.

5) long_query_time

This variable specifies a time limit for query execution, and when the slow log feature is enabled, queries that exceed this limit are recorded in the slow log.

6) log_output

This variable defines the output location of the slow query log and the comprehensive query log. Valid options are file,table,none. When the output location is defined as file, the output file of the log is defined by the slow_query_log_file and general_log_file system variables, respectively. If this variable is table, the log output will be recorded in the mysql.slow_log and mysql.general_ tables, respectively. These two tables are defined internally by the CSV storage engine, so no indexes are supported. This is a global variable that can be defined dynamically.

5. Other optimization variables

1) optimizer_switch

This variable defines a series of advanced switches for MySQL query optimizer features that can be used to turn off (active by default) three different index merge conditions as well as engine push-down conditions.

2) default_storage_engine

When the parameter value is not specified, this variable is used to specify the storage engine for the create table command.

3) max_allowed_packet

You can use the max_allowed_packet variable to define the maximum value of the SQL query result set. Increasing this value will run the query to return a larger result set.

At this point, the study of "optimizing the analysis of SQL system variables" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

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.