MySQL: a convenient stored procedure for memory usage reporting

If you’ve ever been troubleshooting on the MySQL command line and needed to quickly see how much memory is being used then you’ve probably noticed that there are no built in commands to give you this data. Unlike other enterprise databases MySQL doesn’t have a very robust management system built in to help make the DBA’s life easier. It doesn’t come with built in Stored Procedures to report on usage statistics or generate handy reports; so we have to code them and import them to MySQL — no relying on Oracle to help us out here.

So, here’s a stored procedure that can be imported to MySQL and run whenever you need to see the memory usage statistics. Installation and usage info is built into the SP below. The SP can also be downloaded from the repo: https://bitbucket.org/themattreid/generic-sql-scripts/src/15c75632f1af/mysql-memory-report-storedproc.sql

#####################################################################                                                                                                                                                           
## NAME: memory_report_sp.sql                                                                                                                                                                                                   
## AUTHOR: Matt Reid                                                                                                                                                                                                            
## SITE: http://themattreid.com                                                                                                                                                                                                 
## DATE: 2012-09-02                                                                                                                                                                                                             
## LICENSE: GPL v3                                                                                                                                                                                                              
##                                                                                                                                                                                                                              
## INSTALL METHODS:                                                                                                                                                                                                             
##  a) install via linux shell                                                                                                                                                                                                  
##     $> mysql --user=root -p mysql < memory_report_sp.sql                                                                                                                                                                     
##  b) install via mysql command line                                                                                                                                                                                           
##     mysql> use mysql; import memory_report_sp.sql                                                                                                                                                                            
##                                                                                                                                                                                                                              
## USAGE:                                                                                                                                                                                                                       
##  execute the stored procedure to generate the report                                                                                                                                                                         
##     mysql> use mysql;                                                                                                                                                                                                        
##     mysql> call memory_report();                                                                                                                                                                                             
##                                                                                                                                                                                                                              
## EXAMPLE OUTPUT:                                                                                                                                                                                                              
##  [localhost mysql://root@localhost/mysql > call memory_report();                                                                                                                                                             
##  +-----------------------------+----------+                                                                                                                                                                                  
##  | VARIABLE                    | VALUE    |                                                                                                                                                                                  
##  +-----------------------------+----------+                                                                                                                                                                                  
##  | TOTAL_BUFFERS_GLOBAL        | 420.00 M |                                                                                                                                                                                  
##  | TOTAL_BUFFERS_PER_THREAD    | 32.72 M  |                                                                                                                                                                                  
##  | MAX_CONNECTIONS_LIMIT       | 151      |                                                                                                                                                                                  
##  | MAX_CONNECTIONS_USED        | 2        |                                                                                                                                                                                  
##  | MAX_CONNECTION_USED_PERCENT | 1.32 %   |                                                                                                                                                                                  
##  | TOTAL_MEMORY_LIMIT          | 452.53 M |                                                                                                                                                                                  
##  | TOTAL_MEMORY_ACTIVE         | 47.44 M  |                                                                                                                                                                                  
##  | TOTAL_MEMORY_ACTIVE_PERCENT | 10.48 %  |                                                                                                                                                                                  
##  | HEAP_TABLE_LIMIT            | 16.00 M  |                                                                                                                                                                                  
##  | TEMP_TABLE_LIMIT            | 16.00 M  |                                                                                                                                                                                  
##  +-----------------------------+----------+                                                                                                                                                                                  
##  10 rows in set (0.02 sec)                                                                                                                                                                                                   
#####################################################################                                                                                                                                                           
                                                                                                                                                                                                                                
DELIMITER $$                                                                                                                                                                                                                    
DROP PROCEDURE IF EXISTS `memory_report` $$                                                                                                                                                                                     
CREATE PROCEDURE `memory_report` ()                                                                                                                                                                                             
BEGIN                                                                                                                                                                                                                           
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Declare some variables                                                                                                                                                                                                       
#####################################################################                                                                                                                                                           
DECLARE SUM_SGA BIGINT UNSIGNED;                                                                                                                                                                                                
DECLARE SUM_PGA BIGINT UNSIGNED;                                                                                                                                                                                                
DECLARE MAX_CONNECTIONS_LIMIT INT;                                                                                                                                                                                              
DECLARE MAX_CONNECTIONS_USED INT;                                                                                                                                                                                               
DECLARE CONNECTION_RATIO FLOAT;                                                                                                                                                                                                 
DECLARE TOTAL_HEAP BIGINT UNSIGNED;                                                                                                                                                                                             
DECLARE TOTAL_TEMPTABLE BIGINT UNSIGNED;                                                                                                                                                                                        
DECLARE k VARCHAR(255);                                                                                                                                                                                                         
DECLARE v BIGINT UNSIGNED;                                                                                                                                                                                                      
DECLARE TICK BOOL;                                                                                                                                                                                                              
                                                                                                                                                                                                                                
DECLARE MEM_LIMIT BIGINT UNSIGNED;                                                                                                                                                                                              
DECLARE MEM_USED BIGINT UNSIGNED;                                                                                                                                                                                               
DECLARE MEM_PERC FLOAT;                                                                                                                                                                                                         
DECLARE MAX_CONNECTION_USED_PERCENT FLOAT;                                                                                                                                                                                      
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Declare the queries                                                                                                                                                                                                          
#####################################################################                                                                                                                                                           
DECLARE GLOBALS CURSOR FOR SELECT                                                                                                                                                                                               
        VARIABLE_NAME, VARIABLE_VALUE                                                                                                                                                                                           
        FROM INFORMATION_SCHEMA.GLOBAL_VARIABLES                                                                                                                                                                                
        WHERE VARIABLE_NAME IN                                                                                                                                                                                                  
                ('query_cache_size',                                                                                                                                                                                            
                'key_buffer_size',                                                                                                                                                                                              
                'innodb_buffer_pool_size',                                                                                                                                                                                      
                'innodb_additional_mem_pool_size',                                                                                                                                                                              
                'innodb_log_buffer_size',                                                                                                                                                                                       
                'read_buffer_size',                                                                                                                                                                                             
                'read_rnd_buffer_size',                                                                                                                                                                                         
                'sort_buffer_size',                                                                                                                                                                                             
                'thread_stack',                                                                                                                                                                                                 
                'join_buffer_size',                                                                                                                                                                                             
                'binlog_cache_size',                                                                                                                                                                                            
                'max_connections',                                                                                                                                                                                              
                'max_heap_table_size',                                                                                                                                                                                          
                'tmp_table_size')                                                                                                                                                                                               
        UNION                                                                                                                                                                                                                   
        SELECT VARIABLE_NAME, VARIABLE_VALUE                                                                                                                                                                                    
        FROM INFORMATION_SCHEMA.GLOBAL_STATUS                                                                                                                                                                                   
        WHERE VARIABLE_NAME IN                                                                                                                                                                                                  
                ('max_used_connections');                                                                                                                                                                                       
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
DECLARE CONTINUE HANDLER FOR NOT FOUND SET TICK = 1;                                                                                                                                                                            
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Set default values                                                                                                                                                                                                           
#####################################################################                                                                                                                                                           
SET SUM_SGA = 0;                                                                                                                                                                                                                
SET SUM_PGA = 0;                                                                                                                                                                                                                
SET MAX_CONNECTIONS_LIMIT = 0;                                                                                                                                                                                                  
SET MAX_CONNECTIONS_USED = 0;                                                                                                                                                                                                   
SET CONNECTION_RATIO = 0;                                                                                                                                                                                                       
SET TOTAL_HEAP = 0;                                                                                                                                                                                                             
SET TOTAL_TEMPTABLE = 0;                                                                                                                                                                                                        
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Query global variables                                                                                                                                                                                                       
#####################################################################                                                                                                                                                           
SET TICK = 0;                                                                                                                                                                                                                   
OPEN GLOBALS;                                                                                                                                                                                                                   
looper:LOOP                                                                                                                                                                                                                     
      FETCH GLOBALS INTO k,v;                                                                                                                                                                                                   
  IF TICK = 1 THEN                                                                                                                                                                                                              
    LEAVE looper;                                                                                                                                                                                                               
  END IF;                                                                                                                                                                                                                       
                                                                                                                                                                                                                                
    IF k in ('query_cache_size',
       'key_buffer_size',
       'innodb_buffer_pool_size',
       'innodb_additional_mem_pool_size',
       'innodb_log_buffer_size')                                                                                         
       THEN SET SUM_SGA = SUM_SGA + v;                                                                                                                                                                                          
    ELSEIF k in ('read_buffer_size',
       'read_rnd_buffer_size',
       'sort_buffer_size',
       'thread_stack',
       'join_buffer_size',
       'binlog_cache_size')                                                                                            
       THEN SET SUM_PGA = SUM_PGA + v;                                                                                                                                                                                          
    ELSEIF k in ('max_connections') THEN SET MAX_CONNECTIONS_LIMIT = v;                                                                                                                                                         
    ELSEIF k in ('max_heap_table_size') THEN SET TOTAL_HEAP = v;                                                                                                                                                                
    ELSEIF k in ('tmp_table_size','max_heap_table_size')                                                                                                                                                                        
     THEN SET TOTAL_TEMPTABLE = IF ((TOTAL_TEMPTABLE > v), TOTAL_TEMPTABLE, v);                                                                                                                                                 
    ELSEIF k in ('max_used_connections') THEN SET MAX_CONNECTIONS_USED = v;                                                                                                                                                     
                                                                                                                                                                                                                                
    END IF;                                                                                                                                                                                                                     
                                                                                                                                                                                                                                
END LOOP;                                                                                                                                                                                                                       
CLOSE GLOBALS;                                                                                                                                                                                                                  
                                                                                                                                                                                                                                
                                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
## Output report                                                                                                                                                                                                                
#####################################################################                                                                                                                                                           
SET MEM_LIMIT = ROUND((SUM_SGA + (MAX_CONNECTIONS_LIMIT * SUM_PGA))/POW(1024,2),2);                                                                                                                                             
SET MEM_USED = ROUND((SUM_SGA + (MAX_CONNECTIONS_USED * SUM_PGA))/POW(1024,2),2);                                                                                                                                               
SET MEM_PERC = ROUND((MEM_USED * 100)/MEM_LIMIT,2);                                                                                                                                                                             
SET MAX_CONNECTION_USED_PERCENT = ROUND((MAX_CONNECTIONS_USED * 100)/MAX_CONNECTIONS_LIMIT,2);                                                                                                                                  
                                                                                                                                                                                                                                
SELECT "GLOBAL_BUFFERS_TOTAL" AS VARIABLE, CONCAT(ROUND(SUM_SGA/POW(1024,2),2),' M') AS VALUE UNION                                                                                                                             
SELECT "THREAD_BUFFERS_TOTAL", CONCAT(ROUND((SUM_PGA * MAX_CONNECTIONS_LIMIT)/POW(1024,2),2),' M') UNION                                                                                                                        
SELECT "THREAD_BUFFERS_EACH", CONCAT(ROUND(SUM_PGA/POW(1024,2),2),' M') UNION                                                                                                                                                   
                                                                                                                                                                                                                                
SELECT "MAX_CONNECTIONS_LIMIT", MAX_CONNECTIONS_LIMIT UNION                                                                                                                                                                     
SELECT "MAX_CONNECTIONS_USED", MAX_CONNECTIONS_USED UNION                                                                                                                                                                       
SELECT "MAX_CONNECTION_USED_PERCENT", CONCAT(MAX_CONNECTION_USED_PERCENT, ' %') UNION                                                                                                                                           
                                                                                                                                                                                                                                
SELECT "MEMORY_UTILIZATION_LIMIT", CONCAT(MEM_LIMIT,' M') UNION                                                                                                                                                                 
SELECT "MEMORY_UTILIZATION_ACTIVE", CONCAT(MEM_USED,' M') UNION                                                                                                                                                                 
SELECT "MEMORY_UTILIZATION_RATIO", CONCAT(MEM_PERC,' %') UNION                                                                                                                                                                  
                                                                                                                                                                                                                                
SELECT "HEAP_TABLE_LIMIT", CONCAT(ROUND(TOTAL_HEAP / POW(1024,2),2),' M') UNION                                                                                                                                                 
SELECT "TEMP_TABLE_LIMIT", CONCAT(ROUND(TOTAL_TEMPTABLE / POW(1024,2),2),' M') ;                                                                                                                                                
                                                                                                                                                                                                                                
END $$                                                                                                                                                                                                                          
DELIMITER ; 
Tagged , ,

3 thoughts on “MySQL: a convenient stored procedure for memory usage reporting

  1. It’s very nice to have this information in the database.

    However the result is not 100% correct:
    - innodb_additional_mem_pool_size is only used if innodb_use_sys_malloc=OFF
    - It doesn’t calculate usage for Aria (MariaDB) PBXT and TokuDB.
    - It probably would return incorrect values if there is more than 1 MyISAM key cache.
    - It probably would return incorrect values if query_cache_type=0 and query_cache_size > 0

  2. admin says:

    Good points. I’ll make changes to reflect those concerns.

  3. Shawn Green says:

    This is a good estimate of how much RAM your allocations are going to need. Some additional things that go into the memory usage formula are:

    * max_allowed_packet is an upper limit and is generally much larger than the average query
    * there will be one join_buffer allocated for each JOIN in the query
    * the variables max_heap_table_size and tmp_table_size also represent upper limits. Whenever an in-process temporary table exceeds the lesser of these, it is converted to an on-disk temporary table.
    * most queries will only need one of read_buffer_size or read_rnd_buffer_size not both.

    Again, your formula is an excellent tool for setup and server tuning as long as the user is clear that it is only an estimate of how much RAM is in use. Other uses such as space allocated for the parsed command or the space allocated for any prepared statements are not as visible as these other values so we cannot account for those allocations in this type of analysis. It should not be used as an exact measurement of memory usage

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>