Skip to content

Analyze MySQL Memory Usage

This guide will help you identify what is causing drops in available memory and how to check the free memory status of your cloud database.

Before checking how much free RAM is available, it is crucial to understand how effectively the current memory is being used. The Hit Ratio indicates how often the database retrieves data directly from the fast RAM (Buffer Pool) rather than reading it from the slow disk.

Run the following command to retrieve the stats:

1
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

The result should look like this: 

1
2
3
4
5
6
7
8
9
+---------------------------------------+----------+
| Variable_name                         | Value    |
+---------------------------------------+----------+
| Innodb_buffer_pool_read_ahead_rnd     | 0        |
| Innodb_buffer_pool_read_ahead         | 152906   |
| Innodb_buffer_pool_read_ahead_evicted | 14       |
| Innodb_buffer_pool_read_requests      | 51016043 |
| Innodb_buffer_pool_reads              | 18293    |
+---------------------------------------+----------+

The most important values are:

  • Innodb_buffer_pool_read_requests: The total amount of logical read requests.
  • Innodb_buffer_pool_reads: The number of times the database could not find data in the cache and had to read from disk.

Calculating the Score

To calculate the efficiency score, use the following formula:

\[Hit Ratio = \left( 1 - \frac{\text{Innodb_buffer_pool_reads}}{\text{Innodb_buffer_pool_read_requests}} \right) \times 100\]
Score Meaning Action
> 99% Excellent. The database runs almost entirely from RAM. No action is required, even if the memory appears to be “full.”
95% - 99% Good/Acceptable. The disk is accessed occasionally. Monitor performance during peak hours.
< 95% Moderate/Poor. Significant delays due to disk usage. The Buffer Pool is likely too small for your dataset.

Check Free Space and Total Amount

Use the following query to see how much space is actually free for new data within the Buffer Pool.

1
2
3
4
5
SELECT
FORMAT(SUM(POOL_SIZE) * 16.0 / 1024, 2) AS 'Total RAM (MB)',
FORMAT(SUM(FREE_BUFFERS) * 16.0 / 1024, 2) AS 'Free RAM (MB)',
CONCAT(FORMAT(SUM(FREE_BUFFERS) * 100.0 / SUM(POOL_SIZE), 2), '%') AS '% Free'
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Note

A low “% Free” percentage (e.g., 5%) is not a problem as long as your Hit Ratio is high (> 99%). It simply means that you are making optimal use of the available memory to cache data.

The result should look like this: 

1
2
3
4
5
+----------------+---------------+--------+
| Total RAM (MB) | Free RAM (MB) | % Free |
+----------------+---------------+--------+
| 255.98         | 16.00         | 6.25%  |
+----------------+---------------+--------+

Which Tables Consume Memory?

If you want to know which specific tables or indexes are filling your Buffer Pool, use the sys schema. This is useful for identifying if old or unused tables are wasting RAM.

1
2
3
4
5
6
7
8
SELECT
object_schema AS 'Database',
object_name AS 'Table/Index',
allocated AS 'Used Memory',
pages AS 'Amount of Pages'
FROM sys.innodb_buffer_stats_by_table
ORDER BY allocated DESC
LIMIT 10;

How to Interpret This Data

  1. Large unknown tables: Do you see tables that are rarely used (e.g., old logs or archives) at the top? This is a waste of RAM; consider archiving them.
  2. Gigantic indexes: If an index takes up more memory than the table itself, this may indicate inefficient queries or over-indexing.

Real-time Memory Usage by Page

If you need a granular view of exactly which table pages are in memory right now, you can query the INNODB_BUFFER_PAGE table.

Performance Impact

Querying information_schema.INNODB_BUFFER_PAGE can be very heavy on large databases. It requires scanning the entire memory buffer and can cause locking or performance degradation (lag) on live production systems. Use with caution.

1
2
3
4
5
6
7
8
SELECT
TABLE_NAME AS 'Table',
FORMAT(COUNT(*) * 16.0 / 1024, 2) AS 'Memory (MB)'
FROM information_schema.INNODB_BUFFER_PAGE
WHERE TABLE_NAME IS NOT NULL
GROUP BY TABLE_NAME
ORDER BY COUNT(*) DESC
LIMIT 10;

The output will display the top memory consumers:

1
2
3
4
5
6
7
8
+----------------------------------------+----------------+
| Table                                  | Memory (MB)    |
+----------------------------------------+----------------+
| `mysql`.`columns`                      | 1.42           |
| `mysql`.`tables`                       | 0.52           |
| `mysql`.`st_spatial_reference_systems` | 0.34           |
| ...                                    | ...            |
+----------------------------------------+----------------+