Shulou(Shulou.com)06/01 Report--

Memory parameters:

Storage engine / sharing

Log buffer pool

Innodb_buffer_pool_size

Innodb_additional_mem_pool_size

Innodb_log_buffer_size Server / Shar

Query adjustment cache

Thread high-speed network cache

Query_cache

Table_cahce

Table_definition_cache

Connection / session

Sort buffer, read buffer, temporary table

Binlog_cache_size

Read_buffer_size

Read_rnd_buffer_size

Join_buffer_size

Sort_buffer_size

Tmp_table_size

Thread_cache_size

Bulk_insert_buffer_size

Net_buffer_length

Thread_stack

The following is reproduced from: http://www.bitscn.com/pdb/mysql/201405/227583.html

*。 Thread exclusive memory

*。 Global shared memory

Global shared memory similar to ORACLE system global area SGA, thread exclusive memory similar to ORACLE process global area PGA

Thread exclusive memory

In MySQL, thread exclusive memory is mainly used for each client connection thread to store exclusive data of various operations, such as thread stack information, grouping sorting operations, data read and write buffering, result set temporary storage and so on, and most of them can control memory usage through related parameters.

* Thread stack information uses memory (thread_stack):

It is mainly used to store each thread's own identification information, such as thread id, thread runtime basic information and so on. We can set how much memory is allocated for each thread stack through the thread_stack parameter.

Global,No Dynamic,Default 19K (32bit), 256K (32bit)

Recommended configuration: default

* sort using memory (sort_buffer_size):

MySQL uses this memory area for sorting operations (filesort) to complete client-side sort requests. When the sort area cache size we set does not meet the actual memory required for sorting, MySQL writes the data to the disk file to complete the sort. Because the read and write performance of disk and memory is not in the same order of magnitude.

Therefore, the impact of sort_buffer_size parameters on the performance of sorting operations must not be underestimated. For the implementation principle of sorting operation, please refer to the implementation analysis of MySQL Order By.

When will you need it?

When sorting result sets

Use confirmation:

You can verify the use of and this buffer by querying that the value of the Extra column in the plan is Using file-sort.

> explain select * from user1

Global Session,Dynamic,Default 2m (32bit), 2m (32bit)

Recommended configuration: 8m (when there is enough memory), default (when memory is tight)

Optimization suggestion: one argument is that increasing can improve order by,group by performance and prevent data writing to disk from taking up IO resources, and another is that increasing the size of this buffer is not recommended because it may slow down query execution when the value is too large. At present, I have no experimental proof.

* memory (join_buffer_size) is used for Join operations:

Applications often have some operational requirements of two-table (or multi-table) Join. When MySQL completes some Join requirements (all/index join), in order to reduce the number of reads of "driven tables" participating in Join to improve performance, Join Buffer needs to be used to help complete Join operations.

For the specific Join implementation algorithm, please refer to the basic implementation principle of Join in MySQL. When the Join Buffer is too small, MySQL does not save the Buffer to the disk file, but first Join the result set in the Join Buffer with the table that requires Join, and then empty the data in the Join Buffer

Continue to write the remaining result sets to this Buffer, and so on. This will inevitably cause the driven table to be read many times, which will multiply the IO access and reduce the efficiency.

When will you need it?

This buffer is used when the query must join the datasets of two tables (or more) and cannot use the index. This buffer is reserved for indexed link operations per thread.

Use confirmation:

You can verify the use of and this buffer by querying that the value of the Extra column in the plan is Using join bufer.

> explain select * from user1

+-+

| | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

+-+

| | 1 | SIMPLE | user1 | index | NULL | name | 78 | NULL | 3 | Using index |

+-+

The maximum value of Global Session,Dynamic,Default 128K platform varies from version to version.

Recommended configuration: 8m (when there is enough memory), default (when memory is tight)

Optimization suggestion: there is a saying that increasing the size of this buffer will not speed up the full connection operation. At present, I have no experimental proof.

* sequentially read data buffers use memory (read_buffer_size):

This part of the memory is mainly used when the data needs to be read sequentially, such as full table scan, full index scan and so on. In this case, the MySQL reads the data blocks in turn according to the order in which the data is stored, and the data read each time will first be temporarily stored in the read_buffer_size.

When the buffer space is full or all data is read, the data in buffer is returned to the upper caller to improve efficiency.

Global Session,Dynamic,Default 128K

Recommended configuration: 4M/8M * Random read data buffer using memory (read_rnd_buffer_size):

In contrast to sequential reads, this buffer is used to temporarily store read data when MySQL does a non-sequential read (random read) of data blocks. Such as reading table data according to index information, Join according to the sorted result set and table, and so on.

In general, when data blocks need to be read in a certain order, MySQL needs to generate random reads, which in turn uses the memory buffer set by the read_rnd_buffer_size parameter.

Global Session,Dynamic,Default 256K

Recommended configuration: 8m * connection information and result set temporary memory (net_buffer_lenth) before returning to the client:

This part is used to store the connection information of the client connection thread and return the result set of the client. When MySQL starts to produce a result set that can be returned, it will be temporarily stored in the buffer set through net_buffer_lenth before it is returned to the client request thread over the network.

However, the net_buffer_lenth parameter only sets the initialization size of the cache. MySQL will apply for more memory according to the actual needs to meet the requirements.

However, the maximum does not exceed the max_allowed_packet parameter size.

Global Session,Dynamic,Default 16K

Recommended configuration: default 16K

* insert temporary memory in bulk (bulk_insert_buffer_size):

When we use things like insert... Values (…) (...) (...) ... MySQL will first put the submitted data in a cache space, and when the cache space is full or all the data has been submitted, MySQL will write the data in the cache space to the database and empty the cache at one time.

In addition, this buffer is also used when we perform a LOAD DATA INFILE operation to Load the data in a text file into the database.

Global Session,Dynamic,Default 8M

Recommended configuration: default 8m

* temporary tables use memory (tmp_table_size):

MySQL may need to use temporary tables when we do some special operations, such as Order By,Group By that requires temporary tables to complete, and so on. When our temporary table is small (less than the size set by the tmp_table_size parameter), MySQL creates the temporary table into a memory temporary table.

Only when the size set by tmp_table_size cannot hold the entire temporary table will MySQL store the table that was created into the MyISAM storage engine on disk. However, when the size of another system parameter, max_heap_table_size, is smaller than tmp_table_size

MySQL uses the size set by the max_heap_table_size parameter as the maximum temporary memory table size, ignoring the value set by tmp_table_size. And the tmp_table_size parameter has been available since MySQL 5.1.2, and max_heap_table_size has been used until now.

Whoever is young will be effective. There is another parameter, max_tmp_tables, which is not used

Tmp_table_size

Global Session,Dynamic,Default 16M

Recommended configuration: 64m

Max_heap_table_size

Global Session,Dynamic,Default 8M

This variable sets the maximum size to which user-created MEMORY tables are permitted to grow

This variable defines the maximum capacity of the MEMORY storage engine table.

This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory tables. See

This variable is also used with tmp_table_size to limit the size of internal memory tables. Please see

Http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html

Recommended configuration: 64m

Mainly according to the business and server memory to adjust, if necessary, can be adjusted to. GB actually uses 2G configuration, Khan.

There is currently no easy way to determine the total capacity of internal temporary tables. You can use the MySQL state variables created_tmp_tables and created_tmp_disk_tables to determine that temporary tables and disk-based temporary tables are created

Mysql > show global status like 'create%tables'

+-+ +

| | Variable_name | Value |

+-+ +

| | Created_tmp_disk_tables | 0 | |

| | Created_tmp_tables | 0 | |

+-+ +

PERFORMANCE-SCHEMA can be used to help count the total size of disk-based temporary tables

Note: the MySQL thread exclusive memory listed above is only part of the exclusive memory of all threads, not all, but these may have a greater impact on the performance of MySQL, and can be adjusted by system parameters.

Since the above memory is exclusive to threads, in extreme cases, the overall memory usage will be the total multiple of all connected threads. Therefore, in the process of setting up, we must be careful not to blindly increase the parameter values in order to improve performance.

Avoid Out Of Memory exceptions or severe Swap swapping due to insufficient memory, which can degrade overall performance.

2. Global shared memory

Within global sharing, MySQL Instance and the underlying storage engine are used to temporarily store all kinds of global operations and shareable temporary information, such as

Query Cache that stores the query cache

Cache the Thread Cache of the connection thread

Table Cache for caching table file handle information

BinLog Buffer that caches binary logs

Cache the Key Buffer of the MyISAM storage engine index key

InnoDB Buffer Pool that stores InnoDB data and indexes

Wait. The following is a simple analysis of the main shared memory of MySQL.

* MyISAM index cache Key Buffer (key_buffer_size):

The MyISAM index cache caches the index information (. MYI files) of the MyISAM table in memory to improve its access performance. This cache is arguably one of the most important factors affecting the performance of the MyISAM storage engine, setting the maximum memory space that can be used through key_buffere_size.

Note: even if you run an all-innodb schema, you still need to define an index code buffer because the MYSQL meta-information is the same as the MyISAM definition.

Global, Dynamic,Default 8M

Recommended configuration: default 8m

How to confirm that key_buffer_size is not enough?

In the State column using show full proceslist, the value Repairing the keycache is an obvious indicator that the current index code buffer size is not large enough to execute the currently running SQL statement. This will result in additional disk Ihop O overhead.

* query cache Query Cache (query_cache_size):

Http://dev.mysql.com/doc/refman/5.5/en/query-cache-configuration.html

Http://dev.mysql.com/doc/refman/5.5/en/query-cache-status-and-maintenance.html

Query cache is a unique cache area of MySQL, which is used to cache the result set (Result Set) information of a particular Query and share it with all clients. Through the specific Hash calculation of the Query statement, it is stored in the Query Cache corresponding to the result set to improve the corresponding speed of the identical Query statement.

When we open the Query Cache of MySQL, after receiving each Query of type SELECT, MySQL will first get the hash value of the Query through the fixed Hash algorithm, and then go to the Query Cache to find out if there is a corresponding Query Cache. If so, the result set of Cache is returned directly to the client.

If not, follow up, get the corresponding result set, cache the result set in Query Cache, and then return it to the client. When there is any change in the data of any table, all Query Cache related to that table will be invalidated, so Query Cache is not very suitable for tables that change frequently.

But it is very suitable for those tables with few changes, which can greatly improve the query efficiency, such as static resource tables, configuration tables, and so on. In order to use Query Cache,MySQL as efficiently as possible, multiple query_cache_ type values are designed for Query Cache.

And two Query Hint:SQL_CACHE and SQL_NO_CACHE. When query_cache_type is set to 0 (or OFF), Query Cache is not used, when set to 1 (or ON), MySQL ignores Query Cache when and only if SQL_NO_CACHE is used in Query

When query_cache_type is set to 2 (or DEMAND), MySQL uses Query Cache for that Query only if and only if the SQL_CACHE prompt is used in the Query. You can set the maximum memory space that can be used through query_cache_size.

Global Dynamic,Default 0

Recommended configuration: 16m

How to determine the situation of the system query cache?

Show global status like 'Qcache%'; or

Select * from information_schema.GLOBAL_STATUS where VARIABLE_NAME like 'Qcache%'

Formula:

(Qcache_hits/Qcache_hits+Com_select+1) * 100 to determine the validity of the query cache

Mysql > show variables like 'query_cache_size'

+-+ +

| | Variable_name | Value |

+-+ +

| | query_cache_size | 16777216 | |

+-+ +

1 row in set (0.00 sec)

Mysql > show global status like 'Qcache%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Qcache_free_blocks | 535 | |

| | Qcache_free_memory | 4885448 | |

| | Qcache_hits | 1858574835 | |

| | Qcache_inserts | 1619931831 | |

| | Qcache_lowmem_prunes | 802889469 | |

| | Qcache_not_cached | 825000679 | |

| | Qcache_queries_in_cache | 4411 | |

| | Qcache_total_blocks | 9554 | |

+-+ +

8 rows in set (0.00 sec)

Mysql > show global status like 'Com_select'

+-+ +

| | Variable_name | Value |

+-+ +

| | Com_select | 2445037535 | |

+-+ +

1 row in set (0.00 sec) * connection thread cache Thread Cache (thread_cache_size):

Connection threads are used by MySQL to improve the efficiency of creating connection threads, keeping some idle connection threads in a buffer area for new connection requests, which can greatly improve the efficiency of creating connections, especially for applications that use short connections.

After we have set the size of the connection thread that can be cached by the connection thread cache pool through thread_cache_size, we can calculate the hit ratio of the connection thread cache by (Connections-Threads_created) / Connections * 100%.

Note that what is set here is the number of connection threads that can be cached, not the size of the memory space.

Global,Dynamic,Default 0

Recommended configuration: 8

How to determine the situation of the system Thread Cache?

Mysql > show global status like 'Threads_created'

+-+ +

| | Variable_name | Value |

+-+ +

| | Threads_created | 506 |

+-+ +

1 row in set (0.00 sec)

Mysql > show global status like 'connections'

+-+ +

| | Variable_name | Value |

+-+ +

| | Connections | 16513711 | |

+-+ +

1 row in set (0.00 sec)

16513711-506Compact 16513711100% = 99.9938% A very high hit rate. This read-only slave * table caches Table Cache (table_open_cache):

The table cache is mainly used to cache the file handle information of table files. Before MySQL5.1.3, it was set by the table_cache parameter, but changed from MySQL5.1.3 to table_open_cache to set its size. When our client program submits Query to MySQL

MySQL needs to get a table file handle information for every table involved in Query. If there is no Table Cache, MySQL will have to open and close files frequently, which will undoubtedly have a certain impact on system performance. Table Cache is produced to solve this problem.

With Table Cache, every time MySQL needs to get handle information about a table file, it will first look in Table Cache to see if there is a table file handle in an idle state. If so, take it out and use it directly, and if not, you can only open the file to get the file handle information.

After using it, MySQL puts the file handle information back into the Table Cache pool for use by other threads. Note that what is set here is the number of table file handle information that can be cached, not the size of the memory space.

Global,Dynamic,Default 400

Recommended configuration: 4G 2048 is greater than the maximum Opened_tables according to memory configuration

How to determine the situation of the system table_open_cache?

Mysql > show variables like 'table_open_cache'

+-+ +

| | Variable_name | Value |

+-+ +

| | table_open_cache | 512 | |

+-+ +

1 row in set (0.00 sec)

Mysql > show global status like 'open%_tables'

+-+ +

| | Variable_name | Value |

+-+ +

| | Open_tables | 512 | |

| | Opened_tables | 6841 | |

+-+ +

2 rows in set (0.00 sec)

Tuning reference:

Http://blog.zfcms.com/article/282

Http://www.kuqin.com/database/20120815/328904.html

The values of the two parameters. Where Open_tables is the number of tables currently being opened and Opened_tables is the number of all tables that have been opened.

If the value of Open_tables is already close to the value of table_cache, and the Opened_tables is still growing, then mysql is releasing the cached table to accommodate the new table, and you may need to increase the value of table_cache. For most cases, the more appropriate values are:

Open_tables / Opened_tables > = 0.85

Open_tables / table_cache show variables like'% innodb_buffer%'

+-+ +

| | Variable_name | Value |

+-+ +

| | innodb_buffer_pool_instances | 1 | |

| | innodb_buffer_pool_size | 268435456 | |

+-+ +

2 rows in set (0.00 sec)

BUFFER POOL AND MEMORY through show global status and show engine innodb status/G

Mysql > show global status like'% innodb_buffer%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Innodb_buffer_pool_pages_data | 15684 | |

| | Innodb_buffer_pool_bytes_data | 256966656 | |

| | Innodb_buffer_pool_pages_dirty | 210 | |

| | Innodb_buffer_pool_bytes_dirty | 3440640 | |

| | Innodb_buffer_pool_pages_flushed | 372378403 | |

| | Innodb_buffer_pool_pages_free | 1 | |

| | Innodb_buffer_pool_pages_misc | 698 |

| | Innodb_buffer_pool_pages_total | 16383 | |

| | Innodb_buffer_pool_read_ahead_rnd | 0 | |

| | Innodb_buffer_pool_read_ahead | 691803 | |

| | Innodb_buffer_pool_read_ahead_evicted | 41350 | |

| | Innodb_buffer_pool_read_requests | 170965099291 | |

| | Innodb_buffer_pool_reads | 5392513 | |

| | Innodb_buffer_pool_wait_free | 0 | |

| | Innodb_buffer_pool_write_requests | 5825388207 | |

+-+ +

15 rows in set (0.01 sec)

Mysql > show engine innodb status/G

BUFFER POOL AND MEMORY

--

Total memory allocated 274726912; in additional pool allocated 0

Dictionary memory allocated 4055091

Buffer pool size 16383

Free buffers 1

Database pages 15673

Old database pages 5765

Modified db pages 521

Pending reads 0

Pending writes: LRU 0, flush list 0, single page 0

Pages made young 27497746, not young 0

0.00 youngs/s, 0.00 non-youngs/s

Pages read 6346456, created 1902566, written 372381712

0.00 reads/s, 0.37 creates/s, 27.75 writes/s

Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s

LRU len: 15673, unzip_LRU len: 0

I/O sum [1107]: cur [0], unzip sum [0]: cur [0]

Hit rate Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100%

170965099291-5392513 Universe 170965099291 × 100% = 99.99%

* InnoDB dictionary information cache InnoDB Additional Memory Pool (innodb_additional_mem_pool_size):

InnoDB dictionary information cache is mainly used to store dictionary information of InnoDB storage engine and some shared data structure information of internal. So its size also has a lot to do with the number of InnoDB storage engine tables used in the system. However, if the memory size we set through the innodb_additional_mem_pool_size parameter is not enough, InnoDB automatically requests more memory and logs a warning message in the Error Log of MySQL.

Global level, not dynamically changeable Default 8m

Sets the Buffer size for InnoDB to store database dictionary information

Recommended configuration: 50m

Third, check statistics

1. View the memory configuration of each parameter

# 9 variables in global shared memory

Show variables like 'innodb_buffer_pool_size'; / * InnoDB data and Index Cache (InnoDB Buffer Pool) * /

Show variables like 'innodb_additional_mem_pool_size'; / * InnoDB Dictionary Information Cache (InnoDB Additional Memory Pool) * /

Show variables like 'innodb_log_buffer_size'; / * InnoDB log buffer (InnoDB Log Buffer) * /

Show variables like 'binlog_cache_size'; / * binary log buffer (Binlog Buffer) * /

Show variables like 'thread_cache_size'; / * connection thread cache (Thread Cache) * /

Show variables like 'query_cache_size'; / * query cache (Query Cache) * /

Show variables like 'table_open_cache'; / * Table cache (Table Cache) * /

Show variables like 'table_definition_cache'; / * Table definition Information Cache (Table definition Cache) * /

Show variables like 'key_buffer_size'; / * MyISAM Index Cache (Key Buffer) * /

# maximum number of threads

Show variables like 'max_connections'

# 6 variables of thread exclusive memory

Show variables like 'thread_stack'; / * thread stack information using memory (thread_stack) * /

Show variables like 'sort_buffer_size'; / * sort using memory (sort_buffer_size) * /

Show variables like 'join_buffer_size'; / * Join operation uses memory (join_buffer_size) * /

Show variables like 'read_buffer_size'; / * sequentially read data buffers use memory (read_buffer_size) * /

Show variables like 'read_rnd_buffer_size'; / * Random read data buffer uses memory (read_rnd_buffer_size) * /

Show variables like 'tmp_table_size'; / * temporary tables use memory (tmp_table_size). I actually calculate to put tmp_table_size into the global share * /

It can also be obtained directly through the system variable

Select @ @ key_buffer_size

Select @ @ max_connections 2.mysql memory calculation formula

Memory used by mysql = global shared memory + maximum number of threads x thread exclusive memory

Mysql used mem=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+binlog_cache_size+thread_cache_size+query_cache_size+table_open_cache+table_definition_cache+key_buffer_size

+ max_connections* (

Thread_stack+sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size+tmp_table_size)

SET @ kilo_bytes=1024

SET @ mega_bytes=@kilo_bytes*1024

SET @ giga_bytes=@mega_bytes*1024

SELECT (@ innodb_buffer_pool_size+@@innodb_additional_mem_pool_size+@@innodb_log_buffer_size+@@binlog_cache_size+@@thread_cache_size+@@query_cache_size+@@table_open_cache+@@table_definition_cache+@@key_buffer_size+@@max_connections* (@ @ thread_stack+@@sort_buffer_size+@@join_buffer_size+@@read_buffer_size+@@read_rnd_buffer_size+@@tmp_table) _ size)) / @ giga_bytes AS MAX_MEMORY_GB

The maximum memory usage of this theory is 16m by default in version 5.5. calculated according to the default u from Dalian, the space occupied by the temporary table exclusive to the ray path is 2416m. I actually calculated to put tmp_table_size into the global share.

My calculation formula

Memory used by mysql = global shared memory + maximum number of threads x thread exclusive memory

Mysql used mem=innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+binlog_cache_size+thread_cache_size+query_cache_size+table_open_cache+table_definition_cache+key_buffer_size+tmp_table_size

+ max_connections* (

Thread_stack+sort_buffer_size+join_buffer_size+read_buffer_size+read_rnd_buffer_size)

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.