Calculate Mysql Memory Usage – Quick Stored Procedure

Calculate Mysql Memory Usage – Quick Stored Procedure

In this post we will look into the MySQL memory utilization estimation or calculation based on the global variables. Using a simple stored procedure call you can get the memory usage estimation for the present MySQL instance.

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 MySQL Memory Utilization Stored Procedure.

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       |

+---------------------+------------+

# Code MySQL Memory Utilization – Stored procedure.

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 ;

I referred mysql doc and MySQL Server Memory Usage and scripted it for ease.