MySQL Architecture--memory

Http://www.bitscn.com/pdb/mysql/201405/227583.html
http://blog.csdn.net/wyzxg/article/details/7268122
http://blog.csdn.net/wyzxg/article/details/7268175

From the way memory is used, the memory usage of MySQL database is divided into the following two categories

1. Thread Exclusive Memory 2. Global Shared Memory

1. Thread Exclusive Memory

In MySQL, thread-exclusive memory is used primarily for each client connection thread to store exclusive data for various operations,
such as thread stack information, packet sorting operations, data read/write buffering, result set staging, and so on, and most can
To control the amount of memory used by the relevant parameters.

thread stack information using memory (Thread_stack)

Mainly used to store the identity information of each thread itself, such as thread ID, thread run-time basic information, etc.
We can use the Thread_stack parameter to set how much memory to allocate for each thread stack.

sorting using memory (sort_buffer_size)

MySQL uses this memory area for sorting operations (Filesort) to complete the ordering request of the client. When we
When you set the sort area cache size to not meet the actual required memory for sorting, MySQL writes the data to the magnetic
The disk file to complete the sorting. Since the read and write performance of disk and memory is not at all an order of magnitude,
The performance impact of the Sort_buffer_size parameter on the sort operation is absolutely not negligible. The implementation principle of the sort operation
Please refer to: MySQL Order by implementation analysis.

JOIN operation uses memory (join_buffer_size)

Applications often appear with the operational requirements of some two tables (or multiple tables) join, and MySQL completes certain join
Demand (All/index join), in order to reduce the number of reads of the "driven table" participating in the join to
High performance, you need to use the join buffer to assist with the join operation (for a specific join implementation algorithm, refer to:
The basic implementation principle of Join in MySQL). When the join buffer is too small, MySQL will not save the buffer
Into the disk file, the result set in the join buffer is first join with the table that needs the join, and then
Empties the data in the Join buffer and continues to write the remaining result set to this Buffer, so that it is reciprocating.
This inevitably causes the driver table to be read multiple times, increasing IO access and reducing efficiency.

sequential read data buffers using memory (read_buffer_size)

This part of memory is primarily used when you need to read the data sequentially, such as the full table without using the index
Scan, full index scan, etc. At this point, MySQL reads the data blocks sequentially in the order in which they are stored,
Every time the data is read, it will be temporarily present in read_buffer_size, when buffer space is full or all
After the data read is finished, the data in buffer is returned to the upper caller for efficiency.

Random read data buffers using memory (read_rnd_buffer_size)

In contrast to sequential reads, this buffer is used when MySQL makes non-sequential reads (random reads) of data blocks
The data that the zone is temporarily reading. If you read the table data based on the index information, join the table based on the sorted result set, and so on.
In general, MySQL needs to generate random reads when data blocks are read in a certain order.
Use the memory buffer set to the Read_rnd_buffer_size parameter.

connection information and return to the client before the result set is staged using memory (net_buffer_size)

This section is used to hold the connection information for the client connection thread and the result set returned to the client. When MySQL starts to produce
A result set that can be returned will be persisted before being returned to the client request thread over the network.
Net_buffer_size is set in the buffer, and so on to meet a certain size of time to start sending to the client to improve the network
Network transmission efficiency. However, the Net_buffer_size parameter only sets the initial size of the buffer, MySQL
Additional memory will be applied to meet the requirements as needed, but the maximum size of the Max_allowed_packet parameter is not exceeded.

BULK Insert staging using memory (bulk_insert_buffer_size)

When we use values such as Insert ..., (...), (...) ... The way to BULK INSERT, MySQL will first
The submitted data is placed in a cache space, and when the cache space is filled out or all data is submitted, MySQL
Writes the data in this cache space once to the database and empties the cache. Also, when we carry out the LOAD DATA INFILE
Operation to Load data from a text file into a database, this buffer is also used.

temporary tables using memory (tmp_table_size)

When we do some special operations such as the need to use temporary tables to complete the order By,group by and so on, MySQL
You may need to use a temporary table. When our temp table is smaller (less than the size set by the Tmp_table_size parameter)
, MySQL creates temporary tables as memory temporary tables, only if the size set by the tmp_table_size cannot be
When the entire temporary table is loaded, MySQL will store the table built into the MyISAM storage engine on disk.
However, when the size of another system parameter max_heap_table_size is less than tmp_table_size,
MySQL will use the Max_heap_table_size parameter to set the size as the largest memory temp table size, and ignore
The value set by the Tmp_table_size. And the Tmp_table_size parameter starts with MySQL 5.1.2.
Always use max_heap_table_size before.

The MySQL threads listed above only enjoy the memory of all threads alone, not all of them.
Only these may have a greater impact on MySQL performance and can be adjusted by system parameters.

Since all of the above memory is thread-exclusive, the overall amount of memory used in extreme cases will be the total multiple of all connected threads.
So in the setup process must be cautious, not to improve the performance of blindly increase the parameters of the values, to avoid because
Not enough memory to produce an out of a memory anomaly or a serious swap switch reduces overall performance.

2. Global Shared Memory

The global share is mainly the MySQL Instance (mysqld process), and the underlying storage engine is used to stage various global operations and shareable staging information, such as query cache to store queries cached, thread cache of the connection threads, cache table file handle information Table cache, caches the binary log BinLog buffer, caches the key buffer MyISAM the index key of the storage engine, and stores InnoDB data and Indexes InnoDB Buffer Pool, and so on. The following is a simple analysis of MySQL's main shared memory.

Query Cache

The query cache is a unique cache area for MySQL that caches the result set information for a particular query and shares it with all clients. By making a specific Hash calculation of the query statement, it is stored in the query Cache with the result set to increase the corresponding speed of the exact same query statement. When we open MySQL's query cache, MySQL receives each SELECT type of query and then first obtains the hash value of the query through a fixed hash algorithm, and then goes to the query cache to find out if there is a corresponding Query Cache. If so, the result set of the Cache is returned directly to the client. If not, the result set is cached to the Query cache and returned to the client after the corresponding result set is obtained. When any of the table data changes, all query caches associated with that table are invalidated, so the query cache is not very useful for tables that change more frequently, but is well suited for tables with fewer changes, which can greatly improve query efficiency, such as those of static resource tables , configuration tables, and so on. In order to use query Cache,mysql as efficiently as possible, multiple query_cache_type values and two query Hint:sql_cache and Sql_no_cache are designed for query Cache. When the Query_cache_type is set to 0 (or OFF), the query cache is not used, when set to 1 (or on), MySQL ignores the query cache when the Sql_no_cache is used in query, and when When the Query_cache_type is set to 2 (or demand), MySQL uses query cache for that query only when the Sql_cache hint is used in query. You can use Query_cache_size to set the maximum memory space that can be used.

connection thread caching ( thread cache)

Connection thread is MySQL in order to improve the efficiency of creating connection threads, keep some idle connection threads in one buffer for new incoming connection requests, which can greatly improve the efficiency of creating a connection for applications that use short connections. After we set the size of the connection thread that the connection thread cache pool can cache through Thread_cache_size, you can calculate the hit ratio of the connection thread cache by (connections-threads_created)/Connections * 100%. Note that this is set to the number of connection threads that can be cached, not the size of the memory space.

Tables Cache (table caches)

Table buffers are primarily used to cache file handle information for table files, and versions prior to MySQL5.1.3 are set by the Table_cache parameter, but are changed from MySQL5.1.3 to Table_open_cache to set their size. When our client program submits a query to MySQL, MySQL needs to get a table file handle information for each table involved in query, and if there is no table Cache, then MySQL will have to open and close the file frequently, without doubt, the system will produce A certain impact, Table Cache is to solve this problem arises. After the table cache is available, MySQL will first look in the table cache to find out if there are any tables file handles for an idle state when it needs to fetch the handle information of the file. If there is, then remove the direct use, if not, you can only open the file operation to obtain file handle information. After use, MySQL will then put the file handle information back into the Table Cache pool for other threads to use. Note that this is set to the number of table file handle information that can be cached, not the size of the memory space.

table Definition information cache (table definition cache)

The table definition information cache is a new buffer that is introduced from the MySQL5.1.3 version to hold table definition information. When more tables are used in MySQL, this cache will undoubtedly increase the efficiency of access to table-defined information. MySQL provides the Table_definition_cache parameter to let us set the number of tables that can be cached. In versions prior to MySQL5.1.25, the default value was 128, starting with the MySQL5.1.25 version, the default value was adjusted to 256, and the maximum setting was 524288. Note that this is set to the number of table definition information that can be cached, not the size of the memory space.

binary Log buffers (Binlog buffer)

Binary log buffers are primarily used to cache binary logs generated by various data change operations. To improve the performance of the system, MySQL does not always write the binary log directly to the log file, but rather writes the information to Binlog Buffer before writing to the log file once the specific conditions (such as the Sync_binlog parameter settings) are met. We can set the amount of memory that can be used by binlog_cache_size and limit its maximum size by max_binlog_cache_size (MySQL will request more memory when a single transaction is too Large). When the required memory is greater than the Max_binlog_cache_size parameter setting, MySQL will error: "Multi-statement transaction required more than ' max_binlog_cache_ Size ' bytes of storage '.

MyISAM Index Cache (Key buffer)

The MyISAM index cache caches the index information of the MyISAM table in memory to improve its access performance. This cache can be said to be one of the most important factors affecting the performance of the MyISAM storage engine by setting the maximum memory space that can be used with key_buffere_size.

InnoDB Log buffer (InnoDB)

This is the buffer used by the transaction log of the InnoDB storage engine. Similar to Binlog Buffer,innodb when writing transaction logs, in order to improve performance, the information is first written to the INNOFB log Buffer, when it satisfies the Innodb_flush_log_trx_commit The log is written to a file (or to a disk) after the corresponding condition set by the parameter (or the log buffer is full). You can set the maximum memory space that can be used by the Innodb_log_buffer_size parameter.
Note: The Innodb_flush_log_trx_commit parameter has a very critical impact on the write performance of InnoDB log. This parameter can be set to 0,1,2, as explained below:

The data in the 0:log buffer is written to log file at a frequency of once per second, and the file system-to-disk synchronization is performed, but the commit of each transaction does not trigger any flush of log buffer to log file or file system-to-disk refresh operations;
1: The data in log buffer will be written to log file each time the transaction commits, and the file system to disk synchronization will also be triggered;
2: Transaction commit triggers a flush of log buffer to log file, but does not trigger a disk file system-to-disk synchronization. In addition, there is a file system-to-disk synchronization operation every second.
In addition, the MySQL documentation mentions that the mechanism of synchronizing each second in these settings may not completely ensure that a very accurate synchronization occurs every second and depends on the process scheduling problem. In fact, whether InnoDB can really meet the value set by this parameter means normal Recovery is still under the limitations of the file system and the disk itself in the different OS, there may be times when the disk synchronization is not actually complete and will tell Mysqld that the disk synchronization has been completed.

InnoDB data and Index cache (InnoDB buffer Pool)

The InnoDB buffer pool acts on the InnoDB storage engine similar to the impact of the key Buffer cache on the MyISAM storage engine, except that the InnoDB buffer pool caches not only the index data, but also the table's data, and Cached in accordance with the data fast structure information in the data file, which is very similar to the database buffer cache in the Oracle SGA. So the InnoDB Buffer Pool has a great impact on the performance of the InnoDB storage engine. can be calculated by (innodb_buffer_pool_read_requests-innodb_buffer_pool_reads)/innodb_buffer_pool_read_requests * 100% InnoDB the hit rate of the Buffer Pool.

InnoDB Dictionary information cache (InnoDB Additional Memory Pool)

InnoDB dictionary information cache is mainly used to store the dictionary information of InnoDB storage engine and some internal shared data structure information. So its size is also more related to the number of InnoDB storage engine tables used in the system. However, if the amount of memory that we set through the Innodb_additional_mem_pool_size parameter is not enough, InnoDB will automatically request more memory and log the warning message in MySQL's Error log.

The various shared memory listed here is the primary shared memory that has a significant impact on MySQL performance. In fact, in addition to these shared memory, MySQL also has a lot of other shared memory information, such as when the simultaneous request for too many connections to hold the connection request information Back_log queue.

MySQL Architecture--memory