Mysql memory usage analysis (1)

Author: skate
Time: 2012/02/16
 
Mysql memory usage analysis
 
In terms of memory usage, the memory usage of the MySQL database is divided into the following two types:
 
1. Dedicated thread memory
2. Global shared memory
 
1. Dedicated thread memory
 
In MySQL, the thread exclusive memory is mainly used for each client connection thread to store exclusive data for various operations,
Such as thread stack information, grouping and sorting operations, data read/write buffering, and result set temporary storage.
To control the memory usage through relevant parameters.
 
Thread stack information uses memory (thread_stack ):
It is mainly used to store the identification information of each thread, such as the thread id and basic information during the thread running,
We can use the thread_stack parameter to set the size of memory allocated to each thread stack.
 
Memory Used for sorting (sort_buffer_size ):
MySQL uses this memory area for sorting (filesort) to complete client sorting requests. When we
When the cache size in the Set sorting area cannot meet the actual memory size required for sorting, MySQL writes data to the disk
Disk Files to complete sorting. Since 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. How sort operations work
See Implementation Analysis of MySQL Order.
 
Memory Used by the Join Operation (join_buffer_size ):
Applications often have two or more tables to Join. MySQL performs some Join Operations.
When needed (all/index join), in order to reduce the number of reads from the "driven table" involved in the Join operation
High performance. You need to use the Join Buffer to complete the Join operation. For more information about the Join implementation algorithm, see:
Basic implementation principle of Join in MySQL ). MySQL does not store the Buffer when the Join Buffer is too small.
First, Join the result set in the Join Buffer with the table to be joined. Then
Clear the data in the Join Buffer and continue to write the remaining result set to this Buffer.
This will inevitably cause the driver table to be read multiple times, doubling IO access and reducing efficiency.
 
Memory Used for sequential data buffer reading (read_buffer_size ):
This part of memory is mainly used to read data in sequence, such as the entire table if the index cannot be used.
Scan, full index scan, etc. In this case, MySQL reads data blocks sequentially according to the data storage order,
Each read data is saved to read_buffer_size first. When the buffer space is full or all
After the data is read, the data in the buffer is returned to the upper-layer caller to Improve the efficiency.
 
Memory Used for random data buffer reading (read_rnd_buffer_size ):
In contrast to sequential read, this buffer is used when MySQL performs unordered read (random read) data blocks.
Save the read data. 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 read.
And then use the memory buffer set by the read_rnd_buffer_size parameter.
 
Connection information and temporary memory usage (net_buffer_size) for the result set before returning 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 generate
The result set that can be returned will be saved in
In the buffer set by net_buffer_size, It is not sent to the client until it reaches a certain size to improve the network
Network transmission efficiency. However, the net_buffer_size parameter only sets the initialization size of the cache area.
You can apply for more memory as needed, but the maximum size of the max_allowed_packet parameter is not greater.
 
Bulk_insert_buffer_size ):
When we use insert... Values (...), (...), (...)... MySQL will first insert
If the submitted data is stored in a cache space, MySQL will
Write Data in the cache space to the database at one time and clear the cache. In addition, when we LOAD DATA INFILE
This buffer is also used when the data in the text file is loaded into the database.
 
Memory usage for temporary tables (tmp_table_size ):
When we perform some special operations such as Order By, Group By, and so on which temporary tables are required, MySQL
You may need to use a temporary table. When our temporary table is small (smaller than the size set by the tmp_table_size parameter)
MySQL will create a temporary table as a temporary memory table, only when the size set by tmp_table_size cannot
When the entire temporary table is installed, MySQL will store the table created as the MyISAM storage engine 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.
Max_heap_table_size has been used before.
 
The MySQL thread exclusive memory listed above is only part of the exclusive memory of all threads, not all,
However, these may have a major impact on MySQL performance and can be adjusted through system parameters.
 
Because the above memory is dedicated to threads, in extreme cases, the total memory usage will be a multiple of all connection threads.
Therefore, you must be cautious when setting the parameter. Do not blindly increase the parameter values to improve performance.
The Out Of Memory exception or severe Swap switching caused by insufficient Memory reduces the overall performance.
 
 
 
 
 
----- End -----