MySQL Server Memory Usage general understanding and code

A lot has been said about MySQL server memory usage and methods for calculating or estimating the same.

While going through the MySQL manual and the MySQL Server Memory Usage documents, I noted the following quick points regarding how MySQL memory uses can be computed.

This post will summarise my understanding and, at the end, will provide a code snippet for the same memory usage calculations.

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:




Per thread variables include:



Note that,

If size increases or if table have blob 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 us an idea regarding the MySQL Server memory usage, but hopefully it gave you the basics of the concept!

This article is also available on my blog for further change or updation.

Note: For MySQL 5.1.* it will throw error 1064.
Consider a bug report. associated with the code.

Solution for MySQL 5.1+:
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;
will make stored procedure work fine.

Below is a stored procedure to calculate the same estimates of memory consumption using Global Variables.

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;
                      
                      #If you're using mysql 5.1+ consider using below statement.
                      #comment above cursor declaration and uncomment following line.
                      #For more information refer: http://bugs.mysql.com/bug.php?id=49758
                      #DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.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 ;
                      

Select all Open in new window

#Sample output:
                      mysql> call my_memory();
                      +---------------------+-------------------+
                      | Parameter           | Value             |
                      +---------------------+-------------------+
                      | Global Buffers      | 17.99609375 M     |
                      | Per Thread          | 3.7031173706055 M |
                      | Maximum Connections | 100               |
                      | Total Memory Usage  | 388.30783081055 M |
                      | + Per Heap Table    | 16 M              |
                      | + Per Temp Table    | 32 M              |
                      +---------------------+-------------------+
                      

Select all Open in new window