Memory usage and allocation in mysql _ MySQL

Mysql memory allocation is the top priority of optimization, so you must understand how the memory is allocated mysqlshowglobalvariableslike #39; % buffer % #39 ;; + ------------------------- + ------------ + | Variable_name... mysql memory allocation is the top priority of optimization, so you must understand how the memory is allocated.

mysql> show global variables like '%buffer%';+-------------------------+------------+| Variable_name           | Value      |+-------------------------+------------+| bulk_insert_buffer_size | 4194304    || innodb_buffer_pool_size | 2013265920 || innodb_change_buffering | inserts    || innodb_log_buffer_size  | 8388608    || join_buffer_size        | 1048576    || key_buffer_size         | 16777216   || myisam_sort_buffer_size | 262144     || net_buffer_length       | 16384      || preload_buffer_size     | 32768      || read_buffer_size        | 1048576    || read_rnd_buffer_size    | 1048576    || sort_buffer_size        | 1048576    || sql_buffer_result       | OFF        |+-------------------------+------------+13 rows in set (0.01 sec)mysql> show global variables like '%cache%';+------------------------------+----------------------+| Variable_name                | Value                |+------------------------------+----------------------+| binlog_cache_size            | 1048576              || have_query_cache             | YES                  || key_cache_age_threshold      | 300                  || key_cache_block_size         | 1024                 || key_cache_pision_limit     | 100                  || max_binlog_cache_size        | 18446744073709547520 || query_cache_limit            | 1048576              || query_cache_min_res_unit     | 4096                 || query_cache_size             | 0                    || query_cache_type             | ON                   || query_cache_wlock_invalidate | OFF                  || table_definition_cache       | 256                  || table_open_cache             | 100                  || thread_cache_size            | 100                  |+------------------------------+----------------------+14 rows in set (0.00 sec)

Some configuration information is displayed.

Memory composition

1. thread shared memory 2. dedicated thread memory

used_Mem =+ key_buffer_size+ query_cache_size+ innodb_buffer_pool_size+ innodb_additional_mem_pool_size+ innodb_log_buffer_size+ max_connections *(    + read_buffer_size    + read_rnd_buffer_size    + sort_buffer_size    + join_buffer_size    + binlog_cache_size    + thread_stack    + tmp_table_size    + bulk_insert_buffer_size)

Dedicated thread memory

1. read_buffer_size: memory used for sequential data buffer reading

This part of memory is mainly used for full table scanning and full index scanning when data needs to be read in sequence, such as without using indexes. In this case, MySQL reads data blocks sequentially according to the data storage order. The data read is saved in read_buffer_size first. when the buffer space is full or all data is read, then, the data in the buffer is returned to the upper-layer caller to improve the efficiency.

2. read_rnd_buffer_size: memory used for random data buffer reading

In contrast to sequential reading, MySQL uses this buffer zone to temporarily read unordered (random read) data blocks. For example, read table data based on index information, and Join the table based on the sorted result set. In general, when the reading of data blocks needs to meet certain sequence, MySQL needs to generate random reading and then use the memory buffer set by the read_rnd_buffer_size parameter.

3. sort_buffer_size: memory used for sorting

MySQL uses this memory area for sorting (filesort) to complete client sorting requests. When the cache size in the sorting area cannot meet the actual memory size required for sorting, MySQL writes data to disk files for sorting. Because the read/write performance of disks and memory is not an order of magnitude, the effect of sort_buffer_size on the performance of sorting operations cannot be underestimated.

4. join_buffer_size: memory used for connection

Applications often require two or more tables to Join. when MySQL completes some Join requests (all/index join ), to reduce the number of reads from the "driven table" involved in the Join operation to improve performance, you need to use the Join Buffer to assist in the Join operation. When the Join Buffer is too small, MySQL will not store the Buffer into a disk file. Instead, it first performs the Join operation on the result set in the Join Buffer and the table to be joined, and then clears the data in the Join Buffer, continue to write the remaining result set into this Buffer. This will inevitably cause the driver table to be read multiple times, doubling IO access and reducing efficiency.

5. thread_stack: memory used for thread stack information

It is mainly used to store the identity information of each thread, such as the thread id and basic information during thread running. we can use the thread_stack parameter to set the size of memory allocated to each thread stack.

6. tmp_table_size: memory used by the temporary table

When we perform some special operations such as Order By and Group By which temporary tables are required, MySQL may need to use temporary tables. When the temporary table is small (smaller than the size set by the tmp_table_size parameter), MySQL creates the temporary table as a memory temporary table, only when the size set by tmp_table_size cannot be attached to the entire temporary table can MySQL create the table as the MyISAM storage engine and store it on the 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, while ignoring the value set by tmp_table_size. The tmp_table_size parameter is available only from MySQL 5.1.2, and max_heap_table_size has been used before.

7. bulk_insert_buffer_size: Batch insert temporary memory

When we use insert... Values (...), (...), (...)... MySQL first places the submitted data in a cache space. when the cache space is full or all data is submitted, mySQL writes data in the cache space to the database at a time and clears the cache. In addition, when we load data infile to Load the DATA in text files into the database, this buffer zone will also be used.

8. binlog_cache_size: memory used for binary log Buffering

We know that the InnoDB storage engine supports transactions and the implementation of transactions depends on the log technology. for the sake of performance, the log encoding adopts the binary format. So, how do we remember logs? When there is a log, it is directly written to the disk? However, the disk efficiency is very low. if you have used Nginx, Nginx usually outputs access logs to buffer the output. Therefore, do we also need to consider Cache when recording binary logs? The answer is yes, but the Cache is not directly persistent, so it is facing security issues-because when the system goes down, there may be residual data in the Cache that cannot be written to the disk. Therefore, the Cache should be balanced to be the right one: reducing disk I/O to meet performance requirements; ensuring no Cache residue and timely persistence to meet security requirements

If the value is too large, memory resources will be consumed. if the value is too small, you can submit a "long transaction (long_transaction)", for example, batch import data. This transaction will inevitably generate many binlogs, so the cache may not be enough (the default binlog_cache_size is 32 K ), when it is not enough, mysql will write uncommitted parts into a temporary file (the cache efficiency of the temporary file must not be high in the memory cache), and will write the formal persistent log file only when it is committed.

Thread shared memory

1. query_cache_size: Query cache

Query cache is a unique cache region of MySQL. it is used to cache the Result Set information of a specific Query and share it with all clients.

After a specific Hash calculation is performed on a Query statement, it is stored in the Query Cache corresponding to the result set to improve the speed of the same Query statement.

After MySQL's Query Cache is enabled, MySQL receives a SELECT Query and obtains the Hash value of the Query through a fixed Hash algorithm, then, go to the Query Cache to check whether there is a corresponding Query Cache. If yes, the Cache result set is directly returned to the client. If no, perform subsequent operations. after obtaining the corresponding result set, Cache the result set to the Query Cache and return it to the client.

When the data of any table changes, all Query Cache related to the table will be invalid. Therefore, Query Cache is not very suitable for tables with frequent changes, however, it is very suitable for tables with fewer changes and can greatly improve the query efficiency, such as static resource tables and configuration tables. To use Query Cache as efficiently as possible, MySQL designs multiple query_cache_type values and two Query hints: SQL _CACHE and SQL _NO_CACHE for Query Cache.

A. When query_cache_type is set to 0 (or OFF), Query Cache is not used.

B. When query_cache_type is set to 1 (or ON), MySQL ignores the Query Cache only when SQL _NO_CACHE is used in the Query.

C. when query_cache_type is set to 2 (or DEMAND), MySQL uses Query Cache for the Query only after the SQL _CACHE prompt is used in the Query.

You can use query_cache_size to set the maximum memory space that can be used.

2. binlog_cache_size: binary log buffer

The Binary Log buffer is mainly used to cache Binary Log information generated by various data changes.

To improve system performance, MySQL does not directly write binary logs to Log files every time, but first writes information to Binlog Buffer. when certain conditions are met (such as sync_binlog parameter settings) and then write it into the Log File again. We can use binlog_cache_size to set the memory size that can be used, and use max_binlog_cache_size to limit its maximum size (MySQL will apply for more memory when a single transaction is too large ). When the required memory is greater than the max_binlog_cache_size parameter, MySQL reports the error "Multi-statement transaction required more than 'max _ binlog_cache_size 'bytes of storage ".

3. key_buffer_size: MyISAM index cache

The key_buffer_size indicates the size of the key cache that MySQL uses to store indexes in memory. the cache stores index blocks in memory to avoid reading the disk repeatedly. the key_buffer_size is one of the most important variables to tune to improve MySQL database performance. the index blocks of MyISAM tables are stored in the key cache and are accessible to all processes which use MySQL globally.

The maximum size of the key_buffer_size variable is 4 GB on 32 bit machines, and larger for 64 bit machines. mySQL recommends that you keep the key_buffer_size less than or equal to 25% of the RAM on your machine. this also depends on the other processes that use memory on the machine and it is wise to check if you consistently have 25% of free memory using the Linux command free. more on this later.

4. innodb_log_buffer_size: InnoDB log buffer

This is the buffer used by the transaction logs of the InnoDB storage engine. Similar to Binlog Buffer, InnoDB writes information to Innofb Log Buffer to improve performance when writing transaction logs, when the corresponding conditions set by the innodb_flush_log_trx_commit parameter are met (or the log buffer is full), the log will be written to a file (or synchronized to the disk. You can use the innodb_log_buffer_size parameter to set the maximum memory space that can be used.

Note: The innodb_flush_log_trx_commit parameter has a critical impact on the InnoDB Log write performance. This parameter can be set to 0, 1, and 2. the explanation is as follows:

Mysql log operation steps: log_buffer --- mysql write ---> log_file --- OS refresh (flush) ---> disk

0: data in the log buffer is written to the log file at a frequency every second, and the file system is synchronized to the disk at the same time, however, the commit of each transaction does not trigger any refresh from log buffer to log file or from the file system to disk;

1: Data in the log buffer will be written to the log file during each transaction commit, and synchronization from the file system to the disk will also be triggered;

2: transaction commit triggers refresh from log buffer to log file, but does not trigger disk file system to disk synchronization. In addition, a file system is synchronized to the disk every second.

Specific display

0 (delayed write): log_buffer -- every 1 second --> log_file-real-time-> disk

1 (real-time writing, real-time refreshing): log_buffer-real-time-> log_file-real-time-> disk

2 (real-time write, latency brush): log_buffer-real-> log_file -- every 1 second --> disk

The recommended method is innodb_flush_log_at_trx_commit = 2, sync_binlog = N (N is 500 or 1000), and cache with battery backup power is used to prevent system power failure.

Note: sync_binlog = N (N> 0). MySQL uses fdatasync () for every N times of writing binary logs () the function synchronizes binary logs written to binary logs to the disk.

In addition, the MySQL documentation also mentions that the mechanism for synchronizing these settings once per second may not completely ensure that synchronization will occur every second very accurately, but also depends on the process scheduling problem. In fact, whether InnoDB can really meet the meaning of the value set by this parameter indicates that normal Recovery is still restricted by file systems and disks in different operating systems, sometimes, if the disk synchronization is not completed, mysqld is also notified that the disk synchronization has been completed.

5. innodb_buffer_pool_size: InnoDB data and index cache

The role of the InnoDB Buffer Pool on the InnoDB storage engine is similar to that of the Key Buffer Cache on the MyISAM storage engine. The main difference is that the InnoDB Buffer Pool not only caches index data, but also caches table data, the cache is based entirely on the fast data structure information in the data file, which is very similar to the database buffer cache in Oracle SGA. Therefore, the InnoDB Buffer Pool has a big impact on the performance of the InnoDB storage engine. You can use (Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests * 100% to calculate the InnoDB Buffer Pool hit rate.

6. innodb_additional_mem_pool_size: InnoDB Dictionary information cache

The InnoDB Dictionary information cache is mainly used to store the dictionary information of the InnoDB storage engine and some internal shared data structure information. Therefore, the size of the table depends on 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 will automatically apply for more memory and record warning information in the MySQL Error Log.

The various shared memories listed here are the main shared memory that I personally think has a great impact on MySQL Performance. In fact, in addition to the shared memory, MySQL also has many other shared memory information, such as the back_log queue used to store connection request information when too many connections are requested at the same time.