3

A coworker of mine just migrated our server and DB to AWS and since then a handful of queries are going extremely slow. Our code is largely PHP and we're using a MySQL database. Based on some research I've done, it seems likely that the problem could be one of two things:

1) Aurora isn't optimized to deal with older single threaded code and it might work better using MySQL RDS? (Click here to see related article)

2) All of the indexes need to be rebuilt

As I have no idea how to test or deal with either of these things (I'm a DB noob) I was hoping someone could help point me in the right direction or have some suggestions as to what's going on.

EDIT:

Here is a pastebin to some of the error logs if this is helpful: https://pastebin.com/fudCSTux, I'm trying to get that script working but no luck so far. I'm happy to paste the values from those variables, but some of them seem to be security risks, I'll have to go through and edit out the sensitive info.

EDIT 2:

Here is the pastebin for all of the database config information.

Here is the pastebin for the explain query and show create table information.

EDIT 3:

The main server is a t2 small. As far as the database goes there is a master, a reader and a writer. The reader and writers are both currently t2 mediums, vCPU 2, RAM 4GB, as far as I can tell there are no SSD/NVME devices. I'm looking at the AWS console services > rds > databases > clicked on instances > configuration > instance class to assess the hardware.

I changed the queries and removed the quotes around driverId and queries are still slow. I'm going to wait to index until traffic is lower this evening just in case something crashes.

12
  • 1
    A great thing about AWS is you can try things out easily and cheaply. I suggest you spin up a spot instance and install MySQL, or set up MySQL on RDS, to try it out.
    – Tim
    Dec 17 '19 at 18:47
  • 2
    Aurora and RDS both provide the ability to log slow queries and analyze them. You should also define "extremely slow" - to some, that means 200ms. To others, that means 30 seconds.
    – ceejayoz
    Dec 17 '19 at 20:22
  • 1
    Additional information request from your AWS instance. RAM size, # cores, any SSD devices on MySQL Host server? Post on pastebin.com and share the links. From SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; F) SHOW ENGINE INNODB STATUS; for server workload tuning analysis to provide suggestions. Dec 17 '19 at 23:23
  • 1
    Rebuilding indexes is not useful. Locate the "slowest" queries; let's look at them.
    – Rick James
    Dec 18 '19 at 0:54
  • 1
    @WilsonHauck sorry for the delay, we had a meltdown yesterday, I've provided (I think) all of the information you've requested minus the script, which for some reason I'm still struggling to get working. Hopefully this will provide a bit of insight into the problem! Dec 20 '19 at 16:23
2

Your query - select * from driver_events where driverId = '923' and FROM_UNIXTIME(time, '%m-%d-%Y') = '12-18-2019' order by time desc limit 0,5; - does NOT NEED the quotes around the driverID. The quotes cause datatype manipulation for every row. The column driverID is defined as INT - making the quotes unnecessary.

Your jobs query runs so long because there is no index for jobs.time. Suggestion: ALTER TABLE jobs ADD INDEX idx_jobs_time (time);

To reduce time required to complete queries. View my profile, Network profile for downloadable FREE Utility Scripts to improve performance.

0

Rate Per Second = RPS

Suggestions to consider for your AWS Aurora Parameters group

thread_cache_size=32  # from 2 to reduce threads_created
innodb_lru_scan_depth=100  # from 1024 to conserve 90% of CPU cycles used by function
innodb_flushing_avg_loops=5  # from 30 to reduce loop delay
read_rnd_buffer_size=192K  # from 512K to reduce handler_read_rnd_next RPS of 12,322

You will find these configuration changes will REDUCE CPU busy significantly and improve speed of query completion.

0

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.