MySQL not behaving Asynchronous with Amazon RDS Instance?

1
votes

I'm encountering a very strange issue with our MySQL RDS deployment. When a complex Stored procedure that can take 10+ seconds to complete is called, all other calls to the database are bogged down and hung up. This includes any call to SHOW FULL PROCESSLIST. Note the calls are from external/other sessions. For example the Stored Procedures that are taking 10-20 seconds are called by our Web Service but my attempt at executing any queries or SHOW FULL PROCESSLIST are from the IDE on my system, so a completely different connection/session.

Yet my query hangs until the other process is complete, and Amazon RDS reports just 2.3% CPU usage for MySQL.

Heck, even opening the connection to RDS while these stored procedures are running takes forever, so something is very wrong - it's as if MySQL isn't operating in any asynchronous capacity.

Any ideas what's going on here? Am I missing a single simple default flag in RDS that's turned off asynchronous processing?

1
mysqlamazon-web-servicesasynchronousamazon-rds
Can you enable RDS performance insights: aws.amazon.com/rds/performance-insights? It might help to show you what resource is constrained. – Chris Williams
@mokugo-devops unfortunately, we're on a micro.t2 instance, and performance insights isn't available for this class. Any other recommendations? – Floobinator
What is the complete model of your micro.t2 in use? Are you aware t2 is not meant to be used for a 'production' environment? aws.amazon.com/ec2/instance-types/t2 – Wilson Hauck
Could you post TEXT results of A) SHOW GLOBAL VARIABLES LIKE '%thread%'; and B) SHOW GLOBAL STATUS LIKE '%thread%'; and D) SHOW GLOBAL VARIABLES LIKE '%conn%; and SHOW GLOBAL STATUS LIKE '%conn%; for analysis – Wilson Hauck

1 Answers

0
votes

The issue was the class of instance we were using with AWS; it was just too small. Once we updated it to t2.medium, the problems disappeared. The unusual thing is what we were running really was nothing intensive with the database; however, it appears the t2.micro class is really designed to not be used in any real capacity. One of the issues is price starts compounding very quickly in AWS, even for a sandbox system. A small company can quickly find fees in excess of $1,000 just by running test environments. This is not reasonable given the service and performance level provided by AWS for the cost.