MySQL Server Memory Usage general understanding and code: Mysql server, memory usage

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:

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.

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: Connection buffer

max_allowed_packet: Up to this size net_buffer_length can grow.

tmp_table_size - temporary / hash tables in MySQL

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 ;

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:

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              |
+---------------------+-------------------+

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

Select all Open in new window

MySQL Server Memory Usage general understanding and code

Mysql server

memory usage

MySQL Server

About The Author

More Articles From theGhost_k8

Comments

Add your Comment

Join Experts Exchange Today

Gain Access to all our Tech Resources

Get personalized answers

Access Proven Solutions

Read In-Depth How-To Guides

Watch Step by Step Tutorials

And Much More!

Top MySQL Server Experts