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:

ps and other system status programs may report that mysqld uses a lot of memory. This may be 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 this, check available swap with swap -s. We test mysqld with several memory-leakage detectors (both commercial and Open Source), so there should be no memory leaks.

User Comments

tmp_table_size is not the only variable that determines when a tmp table is written to disk. max_heap_table_size also applies.

I got this formula from mysql error log complaining it doesn‘t have 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 be straight forward by providing a formula to calculate the memory usage for mysqld.

Sheila

I use the following SQL query to guess MySQL memory usage
of MySQL unfortunately innodb_* and thread_stack are not
part of MySQL system variables so you 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 = 16 * @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)

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 is more in line with a per thread buffer.

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

If you are going to use a formula for memory consumption, the tmp_table-size should be located with the other per thread buffers - not in the single allocation listing.

Based upon the previous two comments, I re-worked the max-mem-usage query and made it work (there weren‘t enough @‘s on some 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 )
) / (1024 * 1024 * 1024) AS MAX_MEMORY_GB;

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"
}‘

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.

Long back I had created a stored procedure which resides in mysql and should be 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 | 160        |
| Total Memory Usage  | 833.5 M    |
| + Per Heap Table    | 16 M       |
| + Per Temp Table    | 26 M       |
+---------------------+------------+

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

(Do read the cursor declaration note in the post which may cause an 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 ;