<

MySQL Server Memory Usage general understanding and code

Published on
11,272 Points
4,972 Views
3 Endorsements
Last Modified:
Approved
Community Pick
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 ;

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

Open in new window

3
Comment
Author:theGhost_k8
  • 2
4 Comments
LVL 61

Expert Comment

by:Kevin Cross
Nice article, theGhost_k8.
Voted yes above!
0

Expert Comment

by:BToTheAToTheBABA
Hi,

 What does the output to call my_memory() implies ?

Or

 How to interpret the Results ?


ForEx)
This is my output. Is this Normal Or Fine ??


+---------------------+--------------+
| Parameter           | Value        |
+---------------------+--------------+
| Global Buffers      | 639 M        |
| Per Thread          | 701.265625 M |
| Maximum Connections | 100          |
| Total Memory Usage  | 70765.5625 M |
| + Per Heap Table    | 500 M        |
| + Per Temp Table    | 500 M        |
+---------------------+--------------+

Open in new window

0
LVL 22

Author Comment

by:theGhost_k8
As said the stored procedure I wrote to estimate memory usage of mysql... Mean while yours are high values!!
0

Expert Comment

by:BToTheAToTheBABA

Is there any RECOMMENDED rule or Logic like ?????

for 1GB RAM
- Global Buffers Should Not be Lower Than 700MB
- Total Memory Usage Should Never exceed 1GB
0

Featured Post

Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Join & Write a Comment

In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month