Investigating Memory Usage Discrepancies

When there is a large discrepancy between the sum of memory usage from information_schema.TABLE_STATISTICS vs what is reported in alloc_table_memory, review the internal table statistics via information_schema.INTERNAL_TABLE_STATISTICS to see where the rest of memory is being held. For example:

SHOW STATUS EXTENDED LIKE '%Alloc_table_memory%';

+--------------------+-----------------------+
| Variable_name      | Value                 |
+--------------------+-----------------------+
| Alloc_table_memory | 39389.780 (+0.125) MB |
+--------------------+-----------------------+
1 row in set (0.00 sec)

Compare that information with the results of:

SELECT SUM(memory_use)/1024/1024/1024 MEM_GB FROM information_schema.table_statistics
  WHERE 1=1 AND host = 'leaf-1.example.com';

+----------------+
| MEM_GB         |
+----------------+
| 2.392731554806 |
+----------------+
1 row in set (0.76 sec)

The alloc variable is showing 39 GB while table statistics shows just 2.39 GB.

The next step would be to examine the md_<table> information in information_schema.INTERNAL_TABLE_STATISTICS for outliers to see where the rest of the memory is being held. For example, extremely large unlimited storage databases can result in md_columnar_blobs storing large amounts of data for each node.