Calculate Mysql Memory Usage – Quick Stored Proc

While going through mysql doc and MySQL Server Memory Usage, I noted following quick points regarding how mysql uses memory.

We have global buffers which are allocated irrespective of connections as and when mysql server is started. Along with that mysql server allocates memory to each thread to perform respective tasks.

So the formula goes:

Mysql Server Memory Usage = Sum of Global Buffers + (number of Connection * Per thread memory variables).

Global buffers include:

  • key_buffer_size: key_buffer_size is the size of the buffer used for index blocks.
  • innodb_buffer_pool_size: The size in bytes of the memory buffer InnoDB uses to cache data and indexes of its tables.
  • innodb_additional_mem_pool_size: The size in bytes of a memory pool InnoDB uses to store data dictionary information and other internal data structures.
  • innodb_log_buffer_size: The size in bytes of the buffer that InnoDB uses to write to the log files on disk.
  • query_cache_size: The amount of memory allocated for caching query results.

Each thread for client connection uses:

thread_stack – The stack size for each thread.

net_buffer_length – conncetion buffer

max_allowed_packet – up to this size net_buffer_length can grow

read_buffer_size – used for sequential table scan

rean_rnd_buffer_size – used for random read buffer / sorting

tmp_table_size – temporary / hash tables in mysql

sort_buffer_size – for sorting

Per thread variables include:

  • read_buffer_size: Buffer memory used for sequential table scan.
  • read_rnd_buffer_size: Memory used for random read buffer / sorting.
  • sort_buffer_size: Memory allocated for sorting, Group By, Order By.
  • join_buffer_size: The size of the buffer that is used for plain index scans, range index scans, and joins that do not use indexes and thus perform full table scans.
  • thread_stack: The stack size for each thread.
  • net_buffer_length: Conncetion buffer
  • max_allowed_packet: Up to this size net_buffer_length can grow.

Note that,

If size increases or if table have blog columns, instead of heap tables on-disk tables created.

Memories for variables read_buffer_size, sort_buffer_size, read_rnd_buffer_size, tmp_table_size are allocated as & when required. They are also de-allocated once the task is accomplished.

Of course this barely gives you an idea regarding MySQL Server memory usage!

Below is a stored procedure to calculate the same estimates of memory consuption using Global Varibles.

Download Stored Procedure.
[ad#ad-2-468-x-60]

Note: This will work for MySQL 5.0.*. For MySQL 5.1.* it will throw error 1064.

Consider following bug report: http://bugs.mysql.com/bug.php?id=49758 ]

Solution for MySQL 5.1+:

Consider changing the cursor declaration from

DECLARE CUR_GBLVAR CURSOR FOR SHOW GLOBAL VARIABLES;

to

DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;

And the stored procedure will work fine.

For calculating MySQL Memory Usage:

Create Following Stored Procedure and execute.

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 |

+———————+————+

##my_memory.sql

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;

#Cusor for Global Variables

DECLARE CUR_GBLVAR CURSOR FOR

SHOW GLOBAL VARIABLES;

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 ;