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

This article introduces the relevant knowledge of "what is the method of MySQL system variable optimization". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

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%'

+-+

| | Variable_name | Value |

+-+

| | Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |

| | Innodb_buffer_pool_load_status | Buffer pool (s) load completed at 180330 16:27:30 |

| | Innodb_buffer_pool_resize_status |

| | Innodb_buffer_pool_pages_data | 51679 | |

| | Innodb_buffer_pool_bytes_data | 846708736 | |

| | Innodb_buffer_pool_pages_dirty | 0 | |

| | Innodb_buffer_pool_bytes_dirty | 0 | |

| | Innodb_buffer_pool_pages_flushed | 116888 | |

| | Innodb_buffer_pool_pages_free | 1024 | |

| | Innodb_buffer_pool_pages_misc | 4641 | |

| | Innodb_buffer_pool_pages_total | 57344 | |

| | Innodb_buffer_pool_read_ahead_rnd | 0 | |

| | Innodb_buffer_pool_read_ahead | 0 | |

| | Innodb_buffer_pool_read_ahead_evicted | 0 | |

| | Innodb_buffer_pool_read_requests | 91375799 | |

| | Innodb_buffer_pool_reads | 380 |

| | Innodb_buffer_pool_wait_free | 0 | |

| | Innodb_buffer_pool_write_requests | 41378543 | |

+-+

Mysql > SHOW ENGINE INNODB STATUS\ G

* * 1. Row *

Type: InnoDB

Name:

Status:

= =

2018-04-10 14:29:36 0x7f6777c37700 INNODB MONITOR OUTPUT

= =

Per second averages calculated from the last 4 seconds

-

BACKGROUND THREAD

-

Srv_master_thread loops: 327 srv_active, 0 srv_shutdown, 942463 srv_idle

Srv_master_thread log flush and writes: 942790

-

SEMAPHORES

-

OS WAIT ARRAY INFO: reservation count 2095

OS WAIT ARRAY INFO: signal count 2039

RW-shared spins 0, rounds 469, OS waits 237

RW-excl spins 0, rounds 9654, OS waits 322

RW-sx spins 148, rounds 4422, OS waits 145

Spin rounds per wait: 469.00 RW-shared, 9654.00 RW-excl, 29.88 RW-sx

-

TRANSACTIONS

-

Trx id counter 10166

Purge done for trx's n:o

< 10166 undo n:o < 0 state: running but idle History list length 56 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 421558371456848, not started 0 lock struct(s), heap size 1136, 0 row lock(s) -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (read thread) I/O thread 4 state: waiting for i/o request (read thread) I/O thread 5 state: waiting for i/o request (read thread) I/O thread 6 state: waiting for i/o request (write thread) I/O thread 7 state: waiting for i/o request (write thread) I/O thread 8 state: waiting for i/o request (write thread) I/O thread 9 state: waiting for i/o request (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 894 OS file reads, 125124 OS file writes, 6256 OS fsyncs 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 0, seg size 2, 0 merges merged operations: insert 0, delete mark 0, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 232523, node heap has 0 buffer(s) Hash table size 232523, node heap has 0 buffer(s) Hash table size 232523, node heap has 0 buffer(s) Hash table size 232523, node heap has 3093 buffer(s) Hash table size 232523, node heap has 1 buffer(s) Hash table size 232523, node heap has 1545 buffer(s) Hash table size 232523, node heap has 1 buffer(s) Hash table size 232523, node heap has 1 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1258938797 Log flushed up to 1258938797 Pages flushed up to 1258938797 Last checkpoint at 1258938788 0 pending log flushes, 0 pending chkp writes 1286 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 962002944 Dictionary memory allocated 320881 Buffer pool size 57344 Free buffers 1024 Database pages 51679 Old database pages 19056 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 335, not young 124 0.00 youngs/s, 0.00 non-youngs/s Pages read 379, created 60479, written 118447 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 51679, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 0 read views open inside InnoDB Process ID=10779, Main thread ID=140081746663168, state: sleeping Number of rows inserted 10501644, updated 2100327, deleted 0, read 21003388 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 3）innodb_additional_mem_pool_size innodb_additional_mem_pool_size变量为InnoDB特定数据字典信息定义了内存池。对于这个变量，没有什么好的方法来确定它的最优值，一般将其设置为10M。 4）query_cache_size query_cache_size变量是一个用来存储经常缓存过的查询全局内存缓冲区。使用query_cache_type变量可以总体启用和禁用查询缓存。启用时query_cache_size的值可能为0，这表示没有查询需要被缓存。而MySQL实例可以通过动态的改变query_cache_size的值在某个时间仍然可以支持缓存。 2、全局/会话内存缓冲区 1）max_heap_table_size 这个变量定义了MySQL MEMORY存储引擎表的最大容量。当某个表容量超过最大值时，应用程序会收到下面的信息。 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.

This is the end of the content of "what is the method of optimizing MySQL system variables". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

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.