Databases MySQL

Monitor MySQL memory usage – Part 1 (Performance Schema)

Vinnie November 25, 2019

On my day-to-day activities, one of the most classic cases that I get is:

I think that my database has a memory leak. Where MySQL is spending so much memory?

So, saying that, I intend to launch a blog post series that might help tackle this issue. For those who are using MySQL 5.7 things are much easier. MySQL brings memory instruments where it is possible to monitor memory usage in detail.

In my experience, most cases are bad parametrization or heavy queries running. Only a tiny percentage are memory leak bugs de facto. However, they do exist and they might require a deeper analysis. For now, let’s focus on how to track memory usage with Performance Schema.

It is possible to see that MySQL 5.7 brings a huge improvement in the number of memory instruments:

master [localhost:45008] {msandbox} ((none)) &gt; select @@version;
+---------------+
| @@version     |
+---------------+
| 5.7.24-26-log |
+---------------+
1 row in set (0.00 sec)

master [localhost:45008] {msandbox} ((none)) &gt; select count(*) from performance_schema.setup_instruments where name like '%mem%';
+----------+
| count(*) |
+----------+
|      392 |
+----------+
1 row in set (0.00 sec)

				
					
				1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

						master [localhost:45008] {msandbox} ((none)) &gt; select @@version;
+---------------+
| @@version     |
+---------------+
| 5.7.24-26-log |
+---------------+
1 row in set (0.00 sec)
 
master [localhost:45008] {msandbox} ((none)) &gt; select count(*) from performance_schema.setup_instruments where name like '%mem%';
+----------+
| count(*) |
+----------+
|      392 |
+----------+
1 row in set (0.00 sec)

					

			

Comparing with MySQL 5.6:

master [localhost:45008] {msandbox} ((none)) &gt; select @@version;
+-----------------+
| @@version       |
+-----------------+
| 5.6.43-84.3-log |
+-----------------+
1 row in set (0.00 sec)

master [localhost:45008] {msandbox} ((none)) &gt; select count(*) from performance_schema.setup_instruments where name like '%mem%';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

				
					
				1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

						master [localhost:45008] {msandbox} ((none)) &gt; select @@version;
+-----------------+
| @@version       |
+-----------------+
| 5.6.43-84.3-log |
+-----------------+
1 row in set (0.00 sec)
 
master [localhost:45008] {msandbox} ((none)) &gt; select count(*) from performance_schema.setup_instruments where name like '%mem%';
+----------+
| count(*) |
+----------+
|        3 |
+----------+
1 row in set (0.00 sec)

					

			

The next set of queries is designed to provide four different views of MySQL memory usage: global, per host, per thread and finally, per user. First, to start monitoring memory usage, it is necessary to enable the instrumentation:

UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'memory/%';
UPDATE setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE '%memory%';

				
					
				1
2
3
4

						UPDATE setup_instruments SET ENABLED = 'YES', TIMED = 'YES'
WHERE NAME LIKE 'memory/%';
UPDATE setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE '%memory%';

					

			

Next, the queries to monitor memory usage:

SELECT NOW(6) AS &quot;Global Summary&quot;;
 SELECT current_count_used AS curr_count,
        sys.format_bytes(current_number_of_bytes_used) curr_alloc,
        count_alloc,
        sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
        count_free,
        sys.format_bytes(sum_number_of_bytes_free) total_free
   FROM performance_schema.memory_summary_global_by_event_name;

				
					
				1
2
3
4
5
6
7
8

						SELECT NOW(6) AS &quot;Global Summary&quot;;
 SELECT current_count_used AS curr_count,
        sys.format_bytes(current_number_of_bytes_used) curr_alloc,
        count_alloc,
        sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
        count_free,
        sys.format_bytes(sum_number_of_bytes_free) total_free
   FROM performance_schema.memory_summary_global_by_event_name;

					

			

 SELECT NOW(6) AS &quot;Per User Summary&quot;;
 SELECT IFNULL(user, 'mysqld_background') AS user,
        current_count_used AS curr_count,
        sys.format_bytes(current_number_of_bytes_used) curr_alloc,
        count_alloc,
        sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
        count_free,
        sys.format_bytes(sum_number_of_bytes_free) total_free
   FROM performance_schema.memory_summary_by_user_by_event_name
  ORDER BY current_number_of_bytes_used DESC;

				
					
				1
2
3
4
5
6
7
8
9
10

						 SELECT NOW(6) AS &quot;Per User Summary&quot;;
 SELECT IFNULL(user, 'mysqld_background') AS user,
        current_count_used AS curr_count,
        sys.format_bytes(current_number_of_bytes_used) curr_alloc,
        count_alloc,
        sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
        count_free,
        sys.format_bytes(sum_number_of_bytes_free) total_free
   FROM performance_schema.memory_summary_by_user_by_event_name
  ORDER BY current_number_of_bytes_used DESC;

					

			

SELECT NOW(6) AS &quot;Per Host Summary&quot;;
 SELECT IFNULL(host, 'mysqld_background') AS host,
        current_count_used AS curr_count,
        sys.format_bytes(current_number_of_bytes_used) curr_alloc,
        count_alloc,
        sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
        count_free,
        sys.format_bytes(sum_number_of_bytes_free) total_free
   FROM performance_schema.memory_summary_by_host_by_event_name
  ORDER BY current_number_of_bytes_used DESC;

				
					
				1
2
3
4
5
6
7
8
9
10

						SELECT NOW(6) AS &quot;Per Host Summary&quot;;
 SELECT IFNULL(host, 'mysqld_background') AS host,
        current_count_used AS curr_count,
        sys.format_bytes(current_number_of_bytes_used) curr_alloc,
        count_alloc,
        sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
        count_free,
        sys.format_bytes(sum_number_of_bytes_free) total_free
   FROM performance_schema.memory_summary_by_host_by_event_name
  ORDER BY current_number_of_bytes_used DESC;

					

			

 SELECT NOW(6) AS &quot;Per Thread Summary&quot;;
 SELECT if(processlist_user is null,
           substring_index(t.name, '/', -2),
           processlist_user) AS user,
        t.processlist_db AS db,
        m.current_count_used AS curr_count,
        sys.format_bytes(current_number_of_bytes_used) curr_alloc,
        count_alloc,
        sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
        count_free,
        sys.format_bytes(sum_number_of_bytes_free) total_free
   FROM performance_schema.threads t
   JOIN performance_schema.memory_summary_by_thread_by_event_name m using (thread_id)
  ORDER BY current_number_of_bytes_used DESC;

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

						 SELECT NOW(6) AS &quot;Per Thread Summary&quot;;
 SELECT if(processlist_user is null,
           substring_index(t.name, '/', -2),
           processlist_user) AS user,
        t.processlist_db AS db,
        m.current_count_used AS curr_count,
        sys.format_bytes(current_number_of_bytes_used) curr_alloc,
        count_alloc,
        sys.format_bytes(sum_number_of_bytes_alloc) total_alloc,
        count_free,
        sys.format_bytes(sum_number_of_bytes_free) total_free
   FROM performance_schema.threads t
   JOIN performance_schema.memory_summary_by_thread_by_event_name m using (thread_id)
  ORDER BY current_number_of_bytes_used DESC;

					

			