5

We have an issue with our AWS Aurora MySQL instance running out of freeable memory and crashing as a result. AWS's response was to upgrade to a bigger instance, but I don't feel that will necessarily solve the problem.

We are on a db.r5.large instance which is 15GB of RAM.

After a reboot it drops to 5GB of RAM which is pretty much expected, but then gradually declines over the course of a week to 0GB RAM and then reboots - and sometimes fails to do so and requires a manual reboot of the instance.

Graph of freeable memory declining to 0

CPU usage generally hovers around 15% which spikes when we do heavy late-night processing.

From How large should be mysql innodb_buffer_pool_size?

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM
(SELECT SUM(data_length+index_length) Total_InnoDB_Bytes
FROM information_schema.tables WHERE engine='InnoDB') A;

gives 10GB as required to hold all data and indexes in memory.

SELECT (PagesData*PageSize)/POWER(1024,3) DataGB FROM
(SELECT variable_value PagesData
FROM information_schema.global_status
WHERE variable_name='Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize
FROM information_schema.global_status
WHERE variable_name='Innodb_page_size') B;

gives 4.6GB in the InnoDB Buffer Pool

SHOW FULL PROCESSLIST

shows only a couple of processes and no hung threads.

My question is, is there a way of ensuring the freeable memory never goes past a certain point and is released back for usage e.g it should never fall below 2GB so if something is memory heavy, it won't exhaust memory.

I understand there might be a performance hit for this, but before I scale up to a bigger instance (at double the cost) and experience the same slow decline to 0, I'd like to see how it performs as it's better than a production machine crashing at random times.


Additonial information:

I've added the various MySQL outputs to https://pastebin.com/CkRxqL04

Not really sure how to run the Unix commands on the RDS instance as there's no access to the filesystem.

I checked other logs and it seems the CPU is steadily climbing too when it should burst and drop back to a normal level.

CPU usage keeps increasing

This is not a high traffic site, though it does do some heavy lifting behind the scenes.

DB connections

While doing further digging, I noticed there's an attempt at synching to a slave still active from when the initial migration was done. Even as root I can't remove it, but could this be eating memory / CPU?

2019-08-29 00:30:04 7112 [Note] Error reading relay log event: slave SQL thread was killed
2019-08-29 00:30:04 7112 [Note] Slave I/O thread killed while connecting to master
2019-08-29 00:30:04 7112 [Note] Slave I/O thread exiting, read up to log 'mysql-bin-changelog.000016', position 904410367

UPDATE 19/09/09

The pattern still seems to be the same - CPU is going up and up - albeit gradually.

Slow log is empty (I know it's turned on as there was previously entries in it).

I also installed an APM tool and there is nothing suspicious coming up there - all script execute in the time they should as do the Db queries. We have some long-running CRON jobs (3 minutes), but they shouldn't affect the DB as they're on the webserver box.

Memory since reboot CPU since reboot SHOW GLOBAL STATUS LIKE 'com_stmt_%';

12
  • Did you make any changes to the mysql/aurora configuration; if so, what? Do you have applications other than Aurora running on that server? What is the setting of innodb_buffer_pool_size?
    – Rick James
    Aug 28, 2019 at 6:07
  • 1
    A few weeks ago I upgraded from db.r4.large instance and applied the default AWS / MySQL config to the new generation instance to see if that was the cause, but the issue persisted. The only changes since that clean config have been timezone and lowering both interactive_timeout and wait_timeout to 180 seconds in case there were threads getting stuck. And no, Aurora is it's own service - we have an EC2 instance to run the website. Aug 28, 2019 at 6:13
  • You seem to have a memory leakage- those are not common without reason- you need to enable profiling of your queries (possibly on application side) to understand what is going on- unlike a normal MySQL instance, SHOW FULL PROCESSLIST may not be showing the full story as it may be hiding other users. This is such a traffic-dependent question that needs more debugging before giving a definitive answer.
    – jynus
    Aug 28, 2019 at 8:33
  • How many connections do you see when the memory is at zero? Is the number of connections steady over time?
    – Aaron
    Aug 28, 2019 at 14:04
  • 1
    The size of the innodb_buffer_pool is {DBInstanceClassMemory*3/4} which should be around 11.25GB on this instance. Aug 29, 2019 at 5:25

1 Answer 1

1

Rate Per Second=RPS - Suggestions to consider for your AWS Aurora Parameters Group

log_output=TABLE,FILE  # from TABLE so you can review your Error Log FILE after crash
log_warnings=2  # from 1 to include aborted_connects and other info in your Error Log
query_cache_min_res_unit=512  # from 4096 to conserve QC RAM used
query_cache_size=64M  # from ~ 443M to reduce CPU cycles used for QC management
innodb_lru_scan_depth=100  # from 1024 to conserve 90% CPU cycles used for function
thread_cache_size=24  # from 11 to reduce threads_created count
read_rnd_buffer_size=256K  # from 512K to reduce Handler_read_rnd_next RPS of 2,558
read_buffer_size=512K  # from 256K to reduce Handler_read_next RPS of 29,608
tmp_table_size=64M  # from 16M to expand capacity
max_heap_table_size=64M  # from 16M to reduce created_tmp_disk_tables RPHr of 1,134

if ALLOWED,

innodb_io_capacity=1900  # from 200 to enable higher IOPS to SSD devices

OBSERVATIONS, innodb_buffer_pool_size adjustment to be considered when innodb_buffer_pool_reads > 50 RPS (you are less than 1 RPS today) innodb_change_buffering=none to be reconsidered, review refman details

These Suggestions may help stabilize your instance.

Disclaimer: I am the content author of website mentioned in my profile, Network profile where we offer FREE downloadable Utility Scripts to assist with improving performance, more suggestions, contact info.

4
  • 1
    thanks for thanks for those suggestions - I checked each of the parameters and then applied them - free memory immediately increased. These are the sort of suggestions I had hoped AWS would give :-) I managed to delete the redundant slave thread MySQL entry and will see what impact that has when I reboot the server (we are not multizone so I have to wait until out of hours). Sep 1, 2019 at 23:32
  • Nigel, Did AWS allow you to increase innodb_io_capacity? These were all Dynamic Global Variables. Is your response time using a Browser significantly improved? Do you have url I could use to 'view' your site? Sep 2, 2019 at 6:27
  • 1
    I was unable to edit the innodb_io_capacity parameter. Did a reboot this morning and CPU dropped down to 5% (it had climbed to 30%), so I'll continue to monitor and see if there's another "leak" somewhere. Unfortunately the "slave" process has returned even though I deleted it from slave_master_info table. Our site is agentselect.com.au Sep 2, 2019 at 22:26
  • Do you have access to Skype? Id is in view profile, Network profile. I am in UTC -6 hrs. Sep 2, 2019 at 22:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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