MySQL memory allocation

MySQL memory allocation1. MySQL Memory request formula:1.1per-Thread buffers: Each thread buffers the memory allocated for each thread connection. Each thread's connection buffer and result buffer start at a given net_buffer_length size and can dynamically grow to the size of max_allowed_packet bytes.    Result buffer is shrunk to the net_buffer_length byte size after each statement execution is completed during use. The calculation formula for each connection thread: (Read_buffer_size+read_rnd_buffer_size+sort_buffer_size+thread_stack+join_buffer_size+binlog_cache_size) *max_connections=total Memory forAll connections This memory usage is called: MySQL Thread buffers (MTB)1.2 Globalbuffers:Globalbuffer does not have much to do with the memory allocated by each thread. When the process starts, the memory resource is reserved until the server shuts down. This memory is called MySQL Global buffers (MGB)1.3Total Memory Request Total Memory request size is: MTB+mgb=Total Memory used by MySQL1.4parameter Description: read_buffer_size: Each thread scans the memory requested by the MyISAM table each time, and the value defaults to 131072 bytes. The value should be an integer multiple of 4K, if not an integer multiple of 4K will be taken down to an integer multiple of 4K. This part of the memory is mainly used when the need to read the data sequentially, such as the inability to use the index in the case of full-table scan, full-index scanning and so on. At such times, MySQL reads the data block sequentially according to the data storage order, each time the data reads fast first will be in the read_buffer_size, when the buffer space is full or after all data reads, then returns the data in the buffer to the upper level caller, in order to improve the efficiency    . This option is also used by all the village Coarse engines: (1the index in the temporary file is cached when you use the order by sort. (2) Bulk INSERT into partitions operation. (3caches inline or nested query results. Read_rnd_buffer_size: In contrast to sequential reads, when MySQL makes a non-sequential read (random Read) of a block of data, it uses this buffer to stage the read data.    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 to use the memory buffers set by the Read_rnd_buffer_size parameter when the data blocks are read in a certain order.        Thread_stack: Mainly used to store the identity information of each thread itself, such as thread ID, thread runtime basic information and so on, we can use the Thread_stack parameter to set how much memory is allocated for each thread stack. Join_buffer_size: Applications often come with operational requirements for two tables (or multiple table) joins, when MySQL completes certain join requirements ( all/index join), in order to improve performance by reducing the number of reads of the "driven table" participating in the join, you need to use the join Buffer to assist with the join operation.  When the join buffer is too small, MySQL does not save the buffer to the disk file, but rather first joins the result set in the join buffer with the table that needs the join, then empties the data in the join buffer and continues to write the remaining result set to this In Buffer, so reciprocating.        This inevitably causes the driver table to be read multiple times, increasing IO access and reducing efficiency.    Sort_buffer_size:mysql uses this memory area for the sort operation (Filesort) to complete the ordering request for the client. MySQL writes data to a disk file to complete sorting when the sort area cache size We set does not meet the actual required memory for sorting. Because disk and memory read and write performance is not at all an order of magnitude, the performance impact of the Sort_buffer_size parameter on sort operations is absolutely negligible.        The index is often used to complete the sort operation. Main Library execution: SQL>SelectSUM (a.variable_value)/1024x768Kb fromGlobal_variables awhereA.variable_nameinch    ('read_buffer_size','read_rnd_buffer_size',    'sort_buffer_size','Thread_stack',    'join_buffer_size','binlog_cache_size'); ---------------------------------------------38144SQL>Select* fromGlobal_variables AwhereA.variable_name ='max_connections'; ---------------------------------------------max_connections theSQL>Select 38144* the/1024x768/1024x768; -----------------------------------------54.56542969(This is the amount of memory required by the compute thread)

MySQL memory allocation