Understanding MySQL Memory Usage

Question

I’m trying to understand MySQL’s memory usage on a Linux server. Here's the memory information from my server:

• Total Memory: 123GB
• Memory Used: 57GB

Output from free -h :

              total        used        free      shared  buff/cache   available
Mem:           123G         57G        897M        332K         64G         64G
Swap:          8.0G        6.0M        8.0G

When running the top command, I see MySQL using 56.6GB of memory:

Output from top:

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU %MEM     TIME+ COMMAND
 4768 mysql     20   0   59.9g  56.6g  20188 S  86.0 45.9  60389:58 mysqld

I understand that MySQL’s memory usage can be broken down into two main components:

1. Global-Level Memory Usage
2. Thread-Level Memory Usage

For global memory usage, I can query MySQL using the following:

SELECT * FROM sys.memory_global_total;

This shows:

+-----------------+
| total_allocated |
+-----------------+
| 51.88 GiB       |
+-----------------+

However, when I check thread-level memory usage, I notice some significant discrepancies.

Here’s what I see when I query thread-level memory usage:

SELECT * FROM sys.memory_by_thread_by_current_bytes;

Sample output:

+-----------+--------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
| thread_id | user                                 | current_count_used | current_allocated | current_avg_alloc | current_max_alloc | total_allocated |
+-----------+--------------------------------------+--------------------+-------------------+-------------------+-------------------+-----------------+
|    500239 | sql/replica_sql                      |       163255904835 | 60.51 TiB         |  407 bytes        | 58.96 TiB         | 60.70 TiB       |
|    500240 | sql/replica_worker                   |          367874443 | 164.61 GiB        |  480 bytes        | 136.54 GiB        | 218.97 TiB      |
|    500241 | sql/replica_worker                   |           46792912 | 13.40 GiB         |  307 bytes        | 5.96 GiB          | 37.91 TiB       |
|    500242 | sql/replica_worker                   |           33729827 | 4.45 GiB          |  141 bytes        | 1.81 GiB          | 6.19 TiB        |
|    500243 | sql/replica_worker                   |           14609027 | 1.88 GiB          |  138 bytes        | 807.92 MiB        | 2.33 TiB        |
|        38 | innodb/dict_stats_thread             |             209426 | 63.83 MiB         |  319 bytes        | 40.52 MiB         | 321.42 GiB      |
|    500238 | sql/replica_io                       |             126155 | 41.43 MiB         |  344 bytes        | 41.37 MiB         | 75.33 GiB       |
|        49 | innodb/srv_worker_thread             |             314167 | 37.36 MiB         |  124 bytes        | 13.38 MiB         | 71.73 TiB       |
|        48 | innodb/srv_purge_thread              |             357706 | 37.26 MiB         |  109 bytes        | 15.47 MiB         | 27.50 TiB       |
|        27 | innodb/log_writer_thread             |             156264 | 19.08 MiB         |  128 bytes        | 19.08 MiB         | 19.08 MiB       |
|        37 | innodb/srv_master_thread             |              87333 | 8.44 MiB          |  101 bytes        | 3.77 MiB          | 7.23 TiB        |
|   1407905 | admin_user_te@localhost              |                102 | 8.20 MiB          | 82.33 KiB         | 8.00 MiB          | 126.27 MiB      |
|        47 | innodb/clone_gtid_thread             |               5139 | 3.96 MiB          |  807 bytes        | 2.61 MiB          | 5.85 GiB        |
|         1 | sql/main                             |               6948 | 1.27 MiB          |  192 bytes        | 402.89 KiB        | 13.18 GiB       |
|   1407897 | [email protected]            |                 10 | 1.05 MiB          | 107.48 KiB        | 1.00 MiB          | 8.38 MiB        |
|   1407891 | [email protected]            |                  9 | 1.03 MiB          | 117.41 KiB        | 1.00 MiB          | 9.46 MiB        |
|   1407890 | [email protected]            |                  9 | 1.02 MiB          | 115.68 KiB        | 1.00 MiB          | 8.72 MiB        |
|        64 | [email protected]            |                823 | 931.25 KiB        | 1.13 KiB          | 563.80 KiB        | 45.15 GiB       |
|        46 | innodb/buf_dump_thread               |                  6 | 352.48 KiB        | 58.75 KiB         | 352.48 KiB        | 264.78 MiB      |
|         7 | innodb/io_read_thread                |                  3 | 48.23 KiB         | 16.08 KiB         | 48.23 KiB         | 6.68 TiB        |

If you look at the current_allocated column, it shows that replica threads are using 60 TiB of memory in a single thread, which is far beyond the total RAM (123GB).

This seems to indicate a huge discrepancy between actual memory usage and what is reported in MySQL.

My Questions:

1. Why is there such a large discrepancy in the current_allocated memory values? Is this an issue with how MySQL tracks memory usage?
2. What’s the proper way to calculate total MySQL memory usage, considering both global and thread-level usage?
3. How do I correctly interpret the memory metrics to match the actual memory usage shown by the OS?

Any guidance on how to better track or understand MySQL’s memory usage would be appreciated. Thanks!