0

I am facing high memory utilization issue on RDS MySQL server. We are having other issues too, can someone give me a help?

My database has 120GB, including indexes.

Can anyone help me out to understand, RDS memory consumption and freeing behavior?

How we can able to know what causing high memory utilization? How we can avoid it?

RDS Configuration:- db.m5.4xlarge (vCPU = 16 and RAM = 64 GB)

Here are some variables:

bulk_insert_buffer_size - 8388608
innodb_buffer_pool_chunk_size - 134217728
innodb_buffer_pool_dump_at_shutdown - ON
innodb_buffer_pool_dump_now - OFF
innodb_buffer_pool_dump_pct - 25
innodb_buffer_pool_filename - ib_buffer_pool
innodb_buffer_pool_instances8
innodb_buffer_pool_load_abort - OFF
innodb_buffer_pool_load_at_startupON
innodb_buffer_pool_load_now - OFF
innodb_buffer_pool_size - 47244640256
innodb_change_buffer_max_size - 25
innodb_change_buffering - all
innodb_log_buffer_size16777216
innodb_sort_buffer_size - 2097152
join_buffer_size262144
key_buffer_size - 16777216
myisam_sort_buffer_size - 8388608
net_buffer_length - 16384
preload_buffer_size - 32768
read_buffer_size131072
read_rnd_buffer_size262144
sort_buffer_size262144
sql_buffer_result - OFF
max_tmp_tables32
tmp_table_size6316621824
have_query_cacheYES
query_cache_limit - 0
query_cache_min_res_unit4096
query_cache_size0
query_cache_typeON
query_cache_wlock_invalidateOFF
innodb_page_cleaners4
max_connections - 2500
max_user_connections0
thread_stack196608
binlog_cache_size - 32768
binlog_stmt_cache_size32768
have_query_cacheYES
host_cache_size - 728
innodb_disable_sort_file_cacheOFF
innodb_ft_cache_size8000000
innodb_ft_result_cache_limit2000000000
innodb_ft_total_cache_size640000000
key_cache_age_threshold - 300
key_cache_block_size1024
key_cache_division_limit100
max_binlog_cache_size - 18446744073709547520
max_binlog_stmt_cache_size18446744073709547520
metadata_locks_cache_size - 1024
query_cache_limit - 0
query_cache_min_res_unit4096
query_cache_size0
query_cache_typeON
query_cache_wlock_invalidateOFF
stored_program_cache256
table_definition_cache2000
table_open_cache31512
table_open_cache_instances16
thread_cache_size - 33
connect_timeout - 60
delayed_insert_timeout300
have_statement_timeoutYES
innodb_flush_log_at_timeout - 1
innodb_lock_wait_timeout50
innodb_rollback_on_timeoutOFF
interactive_timeout - 30
lock_wait_timeout - 31536000
net_read_timeout30
net_write_timeout - 60
rpl_stop_slave_timeout31536000
slave_net_timeout - 60
wait_timeout10
+-------------------------------------+----------------------+

**Part of Innodb Status**

-------------------------------------+
InnoDB
=====================================
2019-10-02 14:21:22 0x2b94c2f40700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 33 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 63575 srv_active, 0 srv_shutdown, 11740 srv_idle
srv_master_thread log flush and writes: 75314
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 55391770
OS WAIT ARRAY INFO: signal count 26184149
RW-shared spins 0, rounds 86600920, OS waits 42401698
RW-excl spins 0, rounds 462913252, OS waits 12003058
RW-sx spins 23109, rounds 530146, OS waits 16312
Spin rounds per wait: 86600920.00 RW-shared, 462913252.00 RW-excl, 22.94 RW-sx
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (write thread)
I/O thread 7 state: waiting for completed aio requests (write thread)
I/O thread 8 state: waiting for completed aio requests (write thread)
I/O thread 9 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] ,
ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 1; buffer pool: 0
32457638 OS file reads, 4421652 OS file writes, 2294794 OS fsyncs
2 pending preads, 0 pending pwrites
782.52 reads/s, 16384 avg bytes/read, 82.57 writes/s, 69.24 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 13566, seg size 13568, 371078 merges
merged operations:
insert 454633, delete mark 124835, delete 12938
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 10093739, node heap has 4264 buffer(s)
Hash table size 10093739, node heap has 135474 buffer(s)
Hash table size 10093739, node heap has 1471 buffer(s)
Hash table size 10093739, node heap has 59948 buffer(s)
Hash table size 10093739, node heap has 19650 buffer(s)
Hash table size 10093739, node heap has 1081 buffer(s)
Hash table size 10093739, node heap has 7542 buffer(s)
Hash table size 10093739, node heap has 47145 buffer(s)
168373.38 hash searches/s, 14089.63 non-hash searches/s
---
LOG
---
Log sequence number 804344096126
Log flushed up to 804344095294
Pages flushed up to 803851900229
Last checkpoint at803851900229
1 pending log flushes, 0 pending chkp writes
1251611 log i/o's done, 38.88 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 48375005184
Dictionary memory allocated 2461293
Buffer pool size 2883584
Free buffers 7704
Database pages 2599305
Old database pages 959347
Modified db pages104790
Pending reads2
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 215190608, not young 1218564644
3804.95 youngs/s, 45715.22 non-youngs/s
Pages read 32426535, created 1349697, written 2650097
783.43 reads/s, 2.97 creates/s, 33.67 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 32 / 1000
Pages read ahead 97.03/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2599305, unzip_LRU len: 0
I/O sum[251384]:cur[3936], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 360448
Free buffers 923
Database pages 324971
Old database pages 119939
Modified db pages13414
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 26775448, not young 149344221
483.59 youngs/s, 5762.25 non-youngs/s
Pages read 4020738, created 170517, written 337016
103.39 reads/s, 0.67 creates/s, 2.18 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 32 / 1000
Pages read ahead 12.88/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324971, unzip_LRU len: 0
I/O sum[31423]:cur[492], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 360448
Free buffers 961
Database pages 324942
Old database pages 119929
Modified db pages13177
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 25415387, not young 141982022
425.65 youngs/s, 4656.50 non-youngs/s
Pages read 3816124, created 167340, written 316273
79.30 reads/s, 0.03 creates/s, 4.48 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 32 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324942, unzip_LRU len: 0
I/O sum[31423]:cur[492], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 360448
Free buffers 927
Database pages 324869
Old database pages 119903
Modified db pages12944
Pending reads1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 27297719, not young 150838953
481.65 youngs/s, 6947.94 non-youngs/s
Pages read 4077648, created 168214, written 330297
101.91 reads/s, 0.09 creates/s, 5.15 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 31 / 1000
Pages read ahead 15.73/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324869, unzip_LRU len: 0
I/O sum[31423]:cur[492], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 360448
Free buffers 957
Database pages 324872
Old database pages 119903
Modified db pages13846
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 27241158, not young 152596490
508.68 youngs/s, 5102.88 non-youngs/s
Pages read 4079040, created 165287, written 332920
101.63 reads/s, 0.06 creates/s, 4.33 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 1 / 1000 not 16 / 1000
Pages read ahead 10.12/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324872, unzip_LRU len: 0
I/O sum[31423]:cur[492], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 360448
Free buffers 970
Database pages 324930
Old database pages 119924
Modified db pages13306
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 27071975, not young 156302791
495.53 youngs/s, 4981.52 non-youngs/s
Pages read 4202662, created 168923, written 335712
106.24 reads/s, 0.61 creates/s, 4.76 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 3 / 1000 not 36 / 1000
Pages read ahead 12.76/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324930, unzip_LRU len: 0
I/O sum[31423]:cur[492], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 360448
Free buffers 1000
Database pages 324927
Old database pages 119923
Modified db pages12453
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 29017652, not young 165174087
520.92 youngs/s, 6319.20 non-youngs/s
Pages read 4381293, created 171171, written 331047
104.78 reads/s, 0.58 creates/s, 7.24 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 3 / 1000 not 46 / 1000
Pages read ahead 15.91/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324927, unzip_LRU len: 0
I/O sum[31423]:cur[492], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 360448
Free buffers 973
Database pages 324822
Old database pages 119884
Modified db pages13399
Pending reads0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 25561540, not young 148607714
404.81 youngs/s, 5283.75 non-youngs/s
Pages read 3835345, created 169647, written 334254
89.85 reads/s, 0.42 creates/s, 3.21 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 2 / 1000 not 34 / 1000
Pages read ahead 11.21/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324822, unzip_LRU len: 0
I/O sum[31423]:cur[492], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 360448
Free buffers 993
Database pages 324972
Old database pages 119942
Modified db pages12251
Pending reads1
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 26809729, not young 153718366
484.11 youngs/s, 6661.19 non-youngs/s
Pages read 4013685, created 168598, written 332578
96.33 reads/s, 0.52 creates/s, 2.30 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 3 / 1000 not 47 / 1000
Pages read ahead 18.42/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 324972, unzip_LRU len: 0
I/O sum[31423]:cur[492], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
4 read views open inside InnoDB
Process ID=14412, Main thread ID=47944612980480, state: sleeping
Number of rows inserted 76778123, updated 1282408, deleted 102505, read 277730269604
1496.74 inserts/s, 47.06 updates/s, 1.30 deletes/s, 3574606.95 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

0

innodb_buffer_pool_size is 47G -- this is good, and the main user of RAM.

tmp_table_size seems to be set to 6G -- This is dangerously high. If you happen to run a few complex queries at the same time, you could run out of RAM, thereby leading to swapping, which is very bad for MySQL performance. Set it to no more than 600M.

If that does not suffice, let's see all of SHOW VARIABLES; and SHOW GLOBAL STATUS;. Also, show us the evidence that proclaims "memory issues".

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.