Source

generic-bash-scripts / 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        | 42.00 M  |
##  | TOTAL_BUFFERS_PER_THREAD    | 2.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 ;
Tip: Filter by directory path e.g. /media app.js to search for public/media/app.js.
Tip: Use camelCasing e.g. ProjME to search for ProjectModifiedEvent.java.
Tip: Filter by extension type e.g. /repo .js to search for all .js files in the /repo directory.
Tip: Separate your search with spaces e.g. /ssh pom.xml to search for src/ssh/pom.xml.
Tip: Use ↑ and ↓ arrow keys to navigate and return to view the file.
Tip: You can also navigate files with Ctrl+j (next) and Ctrl+k (previous) and view the file with Ctrl+o.
Tip: You can also navigate files with Alt+j (next) and Alt+k (previous) and view the file with Alt+o.