Mysql – Handle multiple simultaneous requests Mysql

amazon-rdsMySQL

sorry for this noob question.
I do have an application which expects about 5000 users accessing it simultaneously, my current database is running on RDS and for each request, a query is called, it takes about 30 milliseconds to be executed.

The main caveat is when we open multiple connections the CPU database spikes to 100% and the app starts to getting timeout error.

What solution would be possible to handle so many requests?

Architecture:

  • 3 EC2 (db.t3.large) running behind a load balancer
  • 1 RDS (db.r5.16xlarge) MySql 5.7
  • 1 Index Created for the AAA table, this table has about 17K rows, after index created the number of rows read was reduced to 6754 rows.
  • When the connections go to spike it achieves up to 2K connections
  • The Max connections for this instance type is 10K
  • The time registered on profiling to execute is 0.03705 seconds (before the index it was 0.7 seconds)

enter image description here

enter image description here

enter image description here

SELECT Count(*) 
FROM   (SELECT `AAA`.id, 
               Degrees(Acos(-0.55754389032929 * Sin(Radians(ZZZ.latitude)) + 
                                    0.83014746301876 * Cos( 
                                    Radians(ZZZ.latitude)) * 
                                    Cos(Radians(151.211111 - 
                            ZZZ.longitude)))) * 60 * 1.1515 * 1.609344 AS 
               distance 
        FROM   `AAA` 
               LEFT JOIN 'ZZZ' 
                      ON `AAA`.`userid` = 'ZZZ'.`id` 
               LEFT JOIN 'BBB' 
                      ON 'ZZZ'.`id` = 'BBB'.`userid` 
               LEFT JOIN 'CCC' 
                      ON 'ZZZ'.`id` = 'CCC'.`userid` 
               LEFT JOIN 'EEE' 
                      ON 'CCC'.`badgeid` = 'EEE'.`id` 
               LEFT JOIN 'FFF' 
                      ON 'ZZZ'.`id` = 'FFF'.`userid` 
        WHERE           ( ( `AAA`.`active` = 1 ) 
                 AND ( `AAA`.`approved` = 1 ) 
                 AND ( 'ZZZ'.`active` = 1 ) 
                 AND ( 'ZZZ'.`verified` = 1 ) ) 
               AND ( St_contains(St_geomfromtext( 'POLYGON((151.30120109009 -33.97620109009, 151.30120109009 -33.79602090991, 151.12102090991 -33.79602090991, 151.12102090991 -33.97620109009,151.30120109009 -33.97620109009 ))'), AAA.`position`) ) 
 GROUP  BY `AAA`.`id` 
 HAVING distance < 10) `c`;

Best Answer

According to your screenshot, it looks like the CPU is spiking before the connections grow. Not sure if you can conclude they are exactly correlated unless this is the only time you see the spiking occur and it's always reving up exactly just a couple minutes before the connections grow (then maybe something just before the connection is registered in your counter). But even your graph shows the lowest point of CPU utilization was at your highest point of connections, so I don't think that's your issue here.

As Michael Lutz mentioned, you should look into Connection Pooling on AWS to see if helps with the Timeouts you're experiencing. But it's also possible your query is not performant when ran concurrently, at high rates of calls, or for whatever varying predicates that could potentially be used.

Without seeing your query, the EXPLAIN AND ANALYZE, and information about your indexes, there's not much more information we can provide. If you can update your post with this information, I'll update my answer accordingly, should any other potential sources of issues become evident.

Related Question