About parameters net_buffer_length how MySQL Uses Memory

Http://dev.mysql.com/doc/refman/5.6/en/memory-use.html

The following list indicates some of the ways that the mysqld server uses memory. Where applicable, the name of the system variable relevant to the memory use is given:

• All threads share the MyISAM key buffer, its size is determined by the key_buffer_size variable. Other buffers used by the server is allocated as needed. See section 8.11.2, "Tuning Server Parameters".

• Each thread, which is a used to manage client connections uses some thread-specific space. The following list indicates these and which variables control their size:

  • Stack A (variable thread_stack )

  • A connection buffer (variable net_buffer_length )

  • A result buffer (variable net_buffer_length )

  The connection buffer and result buffer each begin with a size equal net_buffer_length to bytes, but is dynamically enlarged up to bytes as needed. The result of buffer shrinks to bytes after each net_buffer_length SQL statement. While a statement are running, a copy of the current statement string is also allocated.

  Each connection thread uses memory for computing statement digests (see section 22.7, "Performance Schema statement Digest S "): in MySQL 5.6.24 and up, max_digest_length bytes per session.

• All threads share the same base memory.

• When a thread was no longer needed, the memory allocated to it was released and returned to the system unless the thread GOE s back into the thread cache. In this case, the memory remains allocated.

• The myisam_use_mmap system variable can is set to 1 and enable memory-mapping for all MyISAM tables.

• Each request is performs a sequential scan of a table allocates a read buffer (variable read_buffer_size ).

• When reading rows in an arbitrary sequence (for example, following a sort), a random-read buffer (variable read_rnd_buffer_size ) May is allocated to avoid disk seeks.

• All joins is executed in a single pass, and the most joins can is done without even using a temporary table. Most temporary tables is memory-based hash tables. Temporary tables with a large row length (calculated as the sum of any column lengths) or that contain BLOB columns is s tored on disk.

  If an internal in-memory temporary table becomes too large, MySQL handles this automatically by changing the table from -memory to On-disk format, to is handled by the MyISAM storage engine. You can increase the permissible temporary table size as described inch 8.4.4, "How MySQL Uses Internal temporary Ta Bles ".

• Most requests this perform a sort allocate a sort buffer and zero to the other temporary files depending on the result set size. See section b.5.4.4, "Where MySQL Stores temporary Files".

• Almost all parsing and calculating are do in thread-local and reusable memory pools. No memory overhead is needed for small items, so the normal slow memory allocation and freeing are avoided. Memory is allocated only for unexpectedly large strings.

• For each myisam table. Is opened, the index file is opened once; The data file is opened once for each concurrently running thread. For each concurrent thread, a table structure, column structures for each column, and a buffer of size 3 * n is allocated (where N is the maximum row length, not counting blob columns). A blob column requires five to eight bytes plus the length of the blob< /code> data. The myisam storage engine maintains one extra row buffer for internal use.

• For each table BLOB has columns, a buffer is enlarged dynamically to read in larger BLOB values. If you scan a table, a buffer as large as of the largest BLOB value is allocated.

• Handler structures for all in-use tables is saved in a cache and managed as a FIFO. The initial cache size is taken from the value of the table_open_cache system variable. If A table has been used by and running threads at the same time, the cache contains both entries for the table. See sections 8.4.3.1, "How MySQL Opens and closes Tables".

• A flush TABLES statement or Mysqladmin flush-tables command Closes all tables this is not in use at once and marks all in-use tables To is closed when the currently executing thread finishes. This effectively frees most in-use memory. flush TABLES does not return until all TABLES has been closed.

• The server caches information in memory as a result of grant , create USER , create SERVER , and install PLUGIN statements. This memory isn't released by the corresponding revoke , drop user , drop SERVER , and uninstall PLUGIN statements, so for a Server that executes many instances of the statements that cause caching, there'll be a increase in memory use. This cached memory can is freed with flush privileges .

PS and other system status programs could report this mysqld uses a lot of memory. This is caused by thread stacks on different memory addresses. For example, the Solaris version of PS counts the unused memory between stacks as used memory. To verify the check available swap with swap -s . We Test mysqld with several memory-leakage detectors (both commercial and Open Source), so there should is no mem Ory leaks.

Previous/next/up/table of Contents

User Comments

Posted by Kelly Campbell on May 2005 5:03pm

[Delete] [Edit]

Tmp_table_size is isn't the only variable this determines when a TMP table was written to disk. Max_heap_table_size also applies.

Posted by Sheila Yao on October 2 6:01pm

[Delete] [Edit]

I got this formula from MySQL error log complaining it doesn ' t has enough memory to start mysqld:
Key_buffer_size + (read_buffer_size + sort_buffer_size) * max_connections = K bytes of memory

I hope this document could is straight forward by providing a formula-calculate the memory usage for mysqld.

Sheila

Posted by Guy Baconniere on May 6 3:50pm

[Delete] [Edit]

I Use the following SQL query to guess MySQL memory usage
of MySQL unfortunately innodb_* and Thread_stack is not
Part of the MySQL system variables so need to fill them
manually.

Best regards,
Guy Baconniere

--

SHOW VARIABLES like ' innodb_buffer_pool_size ';
SHOW VARIABLES like ' innodb_additional_mem_pool_size ';
SHOW VARIABLES like ' innodb_log_buffer_size ';
SHOW VARIABLES like ' thread_stack ';
SET @kilo_bytes = 1024;
SET @mega_bytes = @kilo_bytes * 1024;
SET @giga_bytes = @mega_bytes * 1024;
SET @innodb_buffer_pool_size = 2 * @giga_bytes;
SET @innodb_additional_mem_pool_size = * @mega_bytes;
SET @innodb_log_buffer_size = 8 * @mega_bytes;
SET @thread_stack = 192 * @kilo_bytes;
SELECT
(@ @key_buffer_size + @ @query_cache_size + @ @tmp_table_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
)/@giga_bytes as MAX_MEMORY_GB;

+---------------+
| MAX_MEMORY_GB |
+---------------+
|        3.7002 |
+---------------+

1 row in Set (0.00 sec)

Posted by Not_found Not_found on March 7:06pm

[Delete] [Edit]

I disagree with how the previous comment handles the tmp_table_size value. They treat it as a single allocation on the global scope when for memory consumption purposes it's more in line with a PE R thread buffer.

A single connection/query can use a single or multiple temporary tables in the duration of its processing. The connections does not use a single temporary table ' area ' reserved just for that purpose.

If you is going to use a formula for memory consumption, the tmp_table-size should is located with the other per thread B Uffers-not in the allocation listing.

Posted by Christopher Schultz on January 8:50pm

[Delete] [Edit]

Based upon the previous II comments, I re-worked the max-mem-usage query and made it work (there weren ' t enough @ Me variables and @giga_bytes isn ' t defined). This returns a Non-null value on MySQL 5.5.29:

SELECT (@ @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)
)/(1024x768 * 1024x768 * 1024x768) as MAX_MEMORY_GB;

Posted by Eduardo Franceschi on December 10:32pm

[Delete] [Edit]

I wrote a shell script based on above examples. I ' ve added a Min/max memory suggestions also. The minimum memory is estimated using the max_used_connections variable from SHOW STATUS.

#!/bin/sh

MYSQL-E "Show variables; Show status "| awk '
{
Var[$1]=$2
}
END {
Max_conn = var["Max_connections"]
Max_used_conn = var["Max_used_connections"]
base_mem=var["Key_buffer_size"] + var["query_cache_size"] + var["innodb_buffer_pool_size"] + VAR["Innodb_additional_ Mem_pool_size "] + var[" Innodb_log_buffer_size "]
mem_per_conn=var["Read_buffer_size"] + var["read_rnd_buffer_size"] + var["sort_buffer_size"] + VAR["join_buffer_size "] + var[" binlog_cache_size "] + var[" Thread_stack "] + var[" Tmp_table_size "]
Mem_total_min=base_mem + mem_per_conn*max_used_conn
Mem_total_max=base_mem + mem_per_conn*max_conn

printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n "," Key_buffer_size ", var[" Key_buffer_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Query_cache_size ", var[" Query_cache_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Innodb_buffer_pool_size ", var[" Innodb_buffer_pool_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Innodb_additional_mem_pool_size ", var[" Innodb_additional_mem_pool_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Innodb_log_buffer_size ", var[" Innodb_log_buffer_size "]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n "," BASE MEMORY ", base_mem/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n "," Sort_buffer_size ", var[" Sort_buffer_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Read_buffer_size ", var[" Read_buffer_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Read_rnd_buffer_size ", var[" Read_rnd_buffer_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Join_buffer_size ", var[" Join_buffer_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Thread_stack ", var[" Thread_stack "]/1048576
printf "| %40s | %15.3f MB |\n "," Binlog_cache_size ", var[" Binlog_cache_size "]/1048576
printf "| %40s | %15.3f MB |\n "," Tmp_table_size ", var[" Tmp_table_size "]/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n "," MEMORY PER CONNECTION ", mem_per_conn/1048576
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %18d |\n "," Max_used_connections ", Max_used_conn
printf "| %40s | %18d |\n "," Max_connections ", Max_conn
printf "+------------------------------------------+--------------------+\n"
printf "| %40s | %15.3f MB |\n "," Total (MIN) ", mem_total_min/1048576
printf "| %40s | %15.3f MB |\n "," Total (MAX) ", mem_total_max/1048576
printf "+------------------------------------------+--------------------+\n"
}‘

Posted by Shane Bester on September 2 5:59am

[Delete] [Edit]

Folks above using their own formulas, don ' t forget about memory used by performance schema (SHOW ENGINE Performance_schema STATUS) and global variable innodb_ft_total_cache_size.

Posted by Kedar Vaijanapurkar on February 9:12am

[Delete] [Edit]

Long back I had created a stored procedure which resides in MySQL and should is as easy as giving a call to the procedure To estimate the memory usage based on Global and Per Thread Variables.

Mysql> call My_memory ();

+---------------------+------------+
| Parameter           | Value (M)  |
+---------------------+------------+
| Global Buffers      | 531 M      |
| Per Thread          | 1.890625 M |
| Maximum Connections |        |
| Total Memory Usage  | 833.5 M    |
| + Per Heap Table    |       
| + Per Temp Table    |       
+---------------------+------------+

Source:http://kedar.nitty-witty.com/blog/calculte-mysql-memory-usage-quick-stored-proc

(Do read the cursor declaration note at the post which may cause a error due to bug)

DELIMITER $$

DROP PROCEDURE IF EXISTS ' my_memory ' $$
CREATE PROCEDURE ' my_memory ' ()
BEGIN

DECLARE var VARCHAR (100);
DECLARE Val VARCHAR (100);
DECLARE done INT;

#Variables for storing calculations
DECLARE Global_sum DOUBLE;
DECLARE Per_thread_sum DOUBLE;
DECLARE Max_conn DOUBLE;
DECLARE heap_table DOUBLE;
DECLARE temp_table DOUBLE;

#Cursor for Global Variables

# # # for < MySQL 5.1
# # # # DECLARE Cur_gblvar CURSOR for SHOW GLOBAL VARIABLES;

# # # for MySQL 5.1+
DECLARE Cur_gblvar CURSOR for SELECT * from INFORMATION_SCHEMA. Global_variables;
# # # ref:http://bugs.mysql.com/bug.php?id=49758

DECLARE CONTINUE HANDLER for not FOUND SET done=1;

SET global_sum=0;
SET per_thread_sum=0;
SET max_conn=0;
SET heap_table=0;
SET temp_table=0;

OPEN Cur_gblvar;

Mylp:loop
FETCH Cur_gblvar into Var,val;
IF Done=1 Then
LEAVE MYLP;
END IF;
IF var in (' Key_buffer_size ', ' innodb_buffer_pool_size ', ' innodb_additional_mem_pool_size ', ' innodb_log_buffer_size ' , ' query_cache_size ') then
#Summing up Global Memory Usage
SET Global_sum=global_sum+val;
ELSEIF var in (' Read_buffer_size ', ' read_rnd_buffer_size ', ' sort_buffer_size ', ' join_buffer_size ', ' thread_stack ', ' Max_allowed_packet ', ' net_buffer_length ') then
#Summing up Per Thread Memory Variables
SET Per_thread_sum=per_thread_sum+val;
ELSEIF var in (' Max_connections ') then
#Maximum Allowed connections
SET Max_conn=val;
ELSEIF var in (' Max_heap_table_size ') then
#Size of Max Heap tables created
SET Heap_table=val;
#Size of possible temporary Table = Maximum of tmp_table_size/max_heap_table_size.
ELSEIF var in (' Tmp_table_size ', ' max_heap_table_size ') then
SET temp_table=if ((temp_table>val), temp_table,val);
END IF;

END LOOP;
CLOSE Cur_gblvar;
#Summerizing:
Select "Global buffers" as "Parameter", CONCAT (global_sum/(1024*1024), ' M ') as "Value" union
Select "Per Thread", CONCAT (per_thread_sum/(1024*1024), ' M ') union
Select "Maximum Connections", Max_conn Union
Select "Total Memory Usage", CONCAT ((Global_sum + (Max_conn * per_thread_sum))/(1024*1024), ' M ') union
Select "+ Per Heap Table", CONCAT (heap_table/(1024*1024), ' M ') union
Select "+ Per Temp Table", CONCAT (temp_table/(1024*1024), ' M ');

END $$
DELIMITER;

About parameters net_buffer_length how MySQL Uses Memory