Memory usage for MySQL

Question

I have created the following procedure:

CREATE DATABASE memory
USE memory

DELIMITER $$

DROP PROCEDURE IF EXISTS `my_memory` $$
CREATE PROCEDURE `my_memory` ()
BEGIN
DECLARE var LONGTEXT; 
DECLARE val LONGTEXT; 
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;
#Cursor for Global Variables
#### For MySQL 5.1+
DECLARE CUR_GBLVAR CURSOR FOR SELECT * FROM information_schema.GLOBAL_VARIABLES;
#### Ref: http://bugs.mysql.com/bug.php?id=49758
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 ;

When I run the procedure, I get the following:

mysql>  call my_memory();
+---------------------+------------+
| Parameter          | Value      |
+---------------------+------------+
| Global Buffers      | 1131 M    |
| Per Thread          | 5.078125 M |
| Maximum Connections | 200        |
| Total Memory Usage  | 2146.625 M |
| + Per Heap Table    | 16 M      |
| + Per Temp Table    | 134 M      |
+---------------------+------------+
6 rows in set (0.02 sec)
Query OK, 0 rows affected (0.06 sec)

From that information, we know the following: Total Memory Usage = Global buffer + (Memory Per Thread x Maximum Connections). = 1131 + (5.078125 x 200) = 1131 + 1015.625 = 2,146.625

When I list all the threads, there are around 50 of them:

mysql> SHOW FULL PROCESSLIST;
+--------+------+-----------------+------+---------+------+-------+-----------------------+
| Id    | User | Host            | db  | Command | Time | State | Info                  |
+--------+------+-----------------+------+---------+------+-------+-----------------------+
| 226874 | root | localhost:58812 | NULL | Query  |    0 | init  | SHOW FULL PROCESSLIST |
| 226903 | wms  | localhost:59532 | wms  | Sleep  |    0 |      | NULL                  |
| 226904 | wms  | localhost:59533 | wms  | Sleep  |    0 |      | NULL                  |
| 226908 | wms  | localhost:59574 | wms  | Sleep  |  592 |      | NULL                  |
| 226911 | wms  | localhost:59603 | wms  | Sleep  |    1 |      | NULL                  |
| 226926 | wms  | localhost:59976 | wms  | Sleep  |    0 |      | NULL                  |
| 226927 | wms  | localhost:60070 | wms  | Sleep  |    1 |      | NULL                  |
| 226945 | wms  | localhost:60395 | wms  | Sleep  |    6 |      | NULL                  |
| 226970 | wms  | localhost:61265 | wms  | Sleep  |    1 |      | NULL                  |
| 226975 | wms  | localhost:61378 | wms  | Sleep  |  147 |      | NULL                  |
| 226984 | wms  | localhost:61990 | wms  | Sleep  |  628 |      | NULL                  |
| 226985 | wms  | localhost:61991 | wms  | Sleep  |  628 |      | NULL                  |
| 226986 | wms  | localhost:61992 | wms  | Sleep  |  628 |      | NULL                  |
| 226987 | wms  | localhost:62087 | wms  | Sleep  |  600 |      | NULL                  |
| 226988 | wms  | localhost:62088 | wms  | Sleep  |  600 |      | NULL                  |
| 226989 | wms  | localhost:62145 | wms  | Sleep  |  568 |      | NULL                  |
| 226990 | wms  | localhost:62227 | wms  | Sleep  |  540 |      | NULL                  |
| 226991 | wms  | localhost:62333 | wms  | Sleep  |  508 |      | NULL                  |
| 226992 | wms  | localhost:62334 | wms  | Sleep  |  508 |      | NULL                  |
| 226993 | wms  | localhost:62335 | wms  | Sleep  |  508 |      | NULL                  |
| 226994 | wms  | localhost:62366 | wms  | Sleep  |  478 |      | NULL                  |
| 226995 | wms  | localhost:62367 | wms  | Sleep  |  478 |      | NULL                  |
| 226996 | wms  | localhost:62371 | wms  | Sleep  |  450 |      | NULL                  |
| 226997 | wms  | localhost:62372 | wms  | Sleep  |  448 |      | NULL                  |
| 226998 | wms  | localhost:62431 | wms  | Sleep  |  388 |      | NULL                  |
| 226999 | wms  | localhost:62432 | wms  | Sleep  |  388 |      | NULL                  |
| 227000 | wms  | localhost:62507 | wms  | Sleep  |  360 |      | NULL                  |
| 227001 | wms  | localhost:62508 | wms  | Sleep  |  360 |      | NULL                  |
| 227002 | wms  | localhost:62731 | wms  | Sleep  |  270 |      | NULL                  |
| 227003 | wms  | localhost:62732 | wms  | Sleep  |  270 |      | NULL                  |
| 227004 | wms  | localhost:62733 | wms  | Sleep  |  270 |      | NULL                  |
| 227005 | wms  | localhost:62734 | wms  | Sleep  |  270 |      | NULL                  |
| 227006 | wms  | localhost:62735 | wms  | Sleep  |  270 |      | NULL                  |
| 227007 | wms  | localhost:62736 | wms  | Sleep  |  270 |      | NULL                  |
| 227008 | wms  | localhost:62737 | wms  | Sleep  |  270 |      | NULL                  |
| 227009 | wms  | localhost:62741 | wms  | Sleep  |  270 |      | NULL                  |
| 227010 | wms  | localhost:62742 | wms  | Sleep  |  270 |      | NULL                  |
| 227011 | wms  | localhost:62743 | wms  | Sleep  |  270 |      | NULL                  |
| 227012 | wms  | localhost:62869 | wms  | Sleep  |  210 |      | NULL                  |
| 227013 | wms  | localhost:62870 | wms  | Sleep  |  208 |      | NULL                  |
| 227014 | wms  | localhost:62901 | wms  | Sleep  |  180 |      | NULL                  |
| 227015 | wms  | localhost:62902 | wms  | Sleep  |  180 |      | NULL                  |
| 227016 | wms  | localhost:62904 | wms  | Sleep  |  150 |      | NULL                  |
| 227017 | wms  | localhost:62945 | wms  | Sleep  |  90 |      | NULL                  |
| 227018 | wms  | localhost:62946 | wms  | Sleep  |  88 |      | NULL                  |
| 227019 | wms  | localhost:62974 | wms  | Sleep  |  58 |      | NULL                  |
| 227020 | wms  | localhost:62975 | wms  | Sleep  |  58 |      | NULL                  |
| 227021 | wms  | localhost:63020 | wms  | Sleep  |  30 |      | NULL                  |
| 227022 | wms  | localhost:63021 | wms  | Sleep  |  30 |      | NULL                  |
| 227023 | wms  | localhost:63022 | wms  | Sleep  |  30 |      | NULL                  |
| 227024 | wms  | localhost:63027 | wms  | Sleep  |  28 |      | NULL                  |
+--------+------+-----------------+------+---------+------+-------+-----------------------+

Why does the my_memory() procedure add 1015.625 MB (200 x 5.078125) as one of the values in total memory used? Shouldn't it be 50 x 5.078125 MB, since there is only 50 threads active?

In Task Manager, it shows MySQL using 1011 MB of memory. Why is this different from the MySQL procedure showing total usage as 2146 MB?

With process explorer, it show the private bytes as 1,696.440 KB and working set as 741,956K. The working sets should be the physical memory used by the process included memory-mapped files.

Why is this lower than what task manager displays?

Answer 1

The so-called formula for memory usage popularized by tools like MySQL Tuner is actually misleading. You shouldn't rely on that as an estimate for memory use.

The theoretical maximum memory never happens in practice.

Many buffers called per-thread buffers are only allocated as needed by some queries. Your processlist shows no queries actually running, so there are no such buffers in use.

It's nearly impossible, for every processlist slot up to max_connections to be running a query at the same instant, and every query allocating every type of buffer to its maximum size.

Likewise temp tables. Why would it allocate the maximum RAM for a temp table if you aren't running a query that creates temp tables?

Also keep in mind that some figures like the join buffer and the temp table size can in theory be allocated multiple times by the same query. But that's also vanishingly rare to happen at the same time as allocating all other buffers.

If you treat the formulas of theoretical max memory allocation as a typical memory allocation, then every MySQL Server instance I've ever analyzed would be using many times the amount of physical RAM on the server. But that just doesn't happen.

A better way to tell what memory is in use is to query the sys views like:

select event_name, current_alloc 
from sys.x$memory_global_by_current_bytes;

Answer 2

If innodb_buffer_pool_size is about 70% of available memory (after accounting for Java, etc), and if you have not changed much in my.cnf, you should be OK.

Swapping is a big performance problem -- avoid it.

I'm with Bill in saying that every formula for MySQL's memory usage is bogus. And I think Task Manager is often providing bogus info.