MySQL Database on RDS with memory issues

amazon-rdsdatabase-tuningMySQLmysql-5.7performanceperformance-tuning

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
============================

Best Answer

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".

Related Solutions

Mysql – Users complain that system runs slow when thesqldump is in progress

There is a known issue in Windows, that when you push a large file to another server all the memory ends up getting allocated to the System cache instead of the user processes. You can look in the Physical Memory (MB) section of task manager to see how much memory is allocated to the system cache.

This can be solved by backing up to a local disk, then having the remote machine pull that file.

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

...even surpassing it's theorically maximum possible allocation.

[OK] Maximum possible memory usage: 7.3G (46% of installed RAM)

There is not actually a way to calculate maximum possible memory usage for MySQL, because there is no cap on the memory it can request from the system.

The calculation done by mysqltuner.pl is only an estimate, based on a formula that doesn't take into account all possible variables, because if all possible variables were taken into account, the answer would always be "infinite." It's unfortunate that it's labeled this way.

Here is my theory on what's contributing to your excessive memory usage:

thread_cache_size       = 128

Given that your max_connections is set to 200, the value of 128 for thread_cache_size seems far too high. Here's what makes me think this might be contributing to your problem:

When a thread is no longer needed, the memory allocated to it is released and returned to the system unless the thread goes back into the thread cache. In that case, the memory remains allocated.

^{http://dev.mysql.com/doc/refman/5.6/en/memory-use.html}

If your workload causes even an occasional client thread to require a large amount of memory, those threads may be holding onto that memory, then going back to the pool and sitting around, continuing to hold on to memory they don't technically "need" any more, on the premise that holding on to the memory is less costly than releasing it if you're likely to need it again.

I think it's worth a try to do the following, after first making a note of how much memory MySQL is using at the moment.

Note how many threads are currently cached:

mysql> show status like 'Threads_cached';
+----------------+-------+
| Variable_name  | Value |
+----------------+-------+
| Threads_cached | 9     |
+----------------+-------+
1 row in set (0.00 sec)

Next, disable the thread cache.

mysql> SET GLOBAL thread_cache_size = 0;

This disables the thread cache, but the cached threads will stay in the pool until they're used one more time. Disconnect from the server, then reconnect and repeat.

mysql> show status like 'Threads_cached';

Continue disconnecting, reconnecting, and checking until the counter reaches 0.

Then, see how much memory MySQL is holding.

You may see a decrease, possibly significant, and then again you may not. I tested this on one of my systems, which had 9 threads in the cache. Once those threads had all been cleared out of the cache, the total memory held by MySQL did decrease... not by much, but it does illustrate that threads in the cache do release at least some memory when they are destroyed.

If you see a significant decrease, you may have found your problem. If you don't, then there's one more thing that needs to happen, and how quickly it can happen depends on your environment.

If the theory holds that the other threads -- the ones currently servicing active client connections -- have significant memory allocated to them, either because of recent work in their current client session or because of work requiring a lot of memory that was done by another connection prior to them languishing in the pool, then you won't see all of the potential reduction in memory consumption until those threads are allowed to die and be destroyed. Presumably your application doesn't hold them forever, but how long it will take to know for sure whether there's a difference will depend on whether you have the option of cycling your application (dropping and reconnecting the client threads) or if you'll have to just wait for them to be dropped and reconnected over time on their own.

But... it seems like a worthwhile test. You should not see a substantial performance penalty by setting thread_cache_size to 0. Fortunately, thread_cache_size is a dynamic variable, so you can freely change it with the server running.

Best Answer

Related Solutions

Mysql – Users complain that system runs slow when thesqldump is in progress

Mysql – How to debug a db memory-leak causing thesql to go before it’s own limits

Related Question