Search code examples
mysqlmemory-leaksinnodbamazon-rds

AWS RDS low freeable memory and swap being used

I have a t2.medium RDS (4G Memory & MySQL 5.7.22) with the following global variables:

  • innodb_buffer_pool_chunk_size - 0.125G
  • innodb_buffer_pool_instances - 8
  • innodb_buffer_pool_size - 3G

And this is the result of SHOW ENGINE INNODB STATUS;:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 3298295808
Dictionary memory allocated 1021071
Buffer pool size   196608
Free buffers       8192
Database pages     182510
Old database pages 67208
Modified db pages  46
Pending reads      0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11507859, not young 812666330 1.12 youngs/s, 5324.32 non-youngs/s
Pages read 8720629, created 157094, written 2516637 5.27 reads/s, 0.00 creates/s, 12.40 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 22 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 182510, unzip_LRU len: 0
I/O sum[6344]:cur[0], unzip sum[0]:cur[0]

The freeable memory is always between 85Mb and 105MB only and around 70MB is being used from the Swap. This is constant even when there are nearly no processes running on RDS.

Can anyone guide me if we need to either update the buffer_pool variables, or if there is any other way to debug this memory issue?

Solution

  • With only 4G of RAM, make innodb_buffer_pool_size no bigger than 2G. This is probably low enough to get rid of the swap problem.

    I hope innodb_buffer_pool_instances - 8G is a typo. I would recommend 1 or 2, not thousands, not millions, and certainly not billions.

    In general, RDS should have set all the tunables at reasonable values.

    Cron

    Don't use a cron-like mechanism if there is any chance of one iteration not finishing before the next is launched. Instead, have a continuously-running script (or program) that does a chunk, sleeps for a while, then repeats. This will (1) finish the task sooner, and (2) not cause a meltdown (or partial meltdown) due to instances stumbling over each other (it will happen).