MySQL 8 and MySQL 5.7 Memory Consumption on Small Devices

This looks like a good opportunity to use performance_schema 🙂

The bottom of this manual page has an example: https://dev.mysql.com/doc/refman/8.0/en/memory-use.html

I loaded your sample config, and just made one change to 5.7 since it doesn’t instrument memory by default (performance-schema-instrument=’memory/%=COUNTED’).

With Percona Server 8.0.15 vs Percona Server 5.7.26:

mysql [localhost:8015] {msandbox} ((none)) > SELECT SUBSTRING_INDEX(event_name,’/’,2) AS
-> code_area, sys.format_bytes(SUM(current_alloc))
-> AS current_alloc
-> FROM sys.x$memory_global_by_current_bytes
-> GROUP BY SUBSTRING_INDEX(event_name,’/’,2)
-> ORDER BY SUM(current_alloc) DESC;
+—————————+—————+
| code_area | current_alloc |
+—————————+—————+
| memory/innodb | 328.51 MiB |
| memory/performance_schema | 270.73 MiB |
| memory/mysys | 8.57 MiB |
| memory/sql | 4.46 MiB |
| memory/temptable | 1.00 MiB |
| memory/mysqld_openssl | 208.01 KiB |
| memory/mysqlx | 3.62 KiB |
| memory/myisam | 696 bytes |
| memory/vio | 632 bytes |
| memory/csv | 88 bytes |
| memory/blackhole | 88 bytes |
+—————————+—————+
11 rows in set (0.02 sec)

mysql [localhost:5726] {msandbox} ((none)) > SELECT SUBSTRING_INDEX(event_name,’/’,2) AS code_area, sys.format_bytes(SUM(current_alloc)) AS current_alloc FROM sys.x$memory_global_by_current_bytes GROUP BY SUBSTRING_INDEX(event_name,’/’,2) ORDER BY SUM(current_alloc) DESC;
+—————————+—————+
| code_area | current_alloc |
+—————————+—————+
| memory/innodb | 320.68 MiB |
| memory/performance_schema | 137.16 MiB |
| memory/mysys | 8.22 MiB |
| memory/sql | 3.40 MiB |
| memory/memory | 174.18 KiB |
| memory/myisam | 106.14 KiB |
| memory/vio | 512 bytes |
| memory/blackhole | 512 bytes |
| memory/csv | 512 bytes |
+—————————+—————+
9 rows in set (0.01 sec)

So the extra memory is mainly performance_schema. But with that you get memory instrumentation, mdl, etc.

I wonder if there is anyway to know the usage of each part of mysql.
as you can see blow, operation system tells me that mysql uses 46.5GB of RAM,
but performance_schema tells me that mysql uses 37.7GB of RAM,
it is nearly 10GB gap!!!
here is several questions:
1. Is it caused by innodb_flush_log_at_trx_commit=2 and sync_binlog=0?
2. if yes, why these memory is not freed even in the light workload?
3. is it caused by connection thread?
4. if yes, why when i killed all connection, the memory usage does not get down?

welcome for any help,
thanks in advance.

free -m
total used free shared buff/cache available
Mem: 64145 48544 271 3227 15328 11925
Swap: 0 0 0

mysql> show variables like ‘innodb_buffer_pool_size’;
+————————-+————-+
| Variable_name | Value |
+————————-+————-+
| innodb_buffer_pool_size | 37580963840 |
+————————-+————-+
1 row in set (0.00 sec)

mysql> show variables like ‘innodb_flush_log_at_trx_commit’;
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 2 |
+——————————–+——-+
1 row in set (0.00 sec)

mysql> show variables like ‘sync_binlog’;
+—————+——-+
| Variable_name | Value |
+—————+——-+
| sync_binlog | 0 |
+—————+——-+
1 row in set (0.00 sec)

mysql> SELECT sum(current_number_of_bytes_used)/1024/1024/1024 FROM performance_schema.memory_summary_global_by_event_name;
+————————————————–+
| sum(current_number_of_bytes_used)/1024/1024/1024 |
+————————————————–+
| 37.710148520768 |
+————————————————–+
1 row in set (0.01 sec)

mysql> exit
Bye
[root@10-42-106-194 ~]# pidstat -r -p 2932 1 1
Linux 3.10.0-229.14.1.el7.x86_64 (10-42-106-194) 08/15/2019 _x86_64_ (32 CPU)

11:57:36 PM UID PID minflt/s majflt/s VSZ RSS %MEM Command
11:57:37 PM 700 2932 224.00 0.00 57718944 48764400 74.24 mysqld
Average: 700 2932 224.00 0.00 57718944 48764400 74.24 mysqld