3

i am fairly new to the role and the technology. we have a Network server that receives the telemetry data from sensors and temeletry data is stored in MYSQL dabatase. we have a front end portal which we use for device management and queries the database.

For for the past few weeks our EC2 instance is experiencing High CPU usage everytime we opens the portal. in normal times the server load is normal and CPL usage is less than moderate

upon going through few sites and other questions in here, i have investigated the issue but couldnt figure how to solve this issue

Please find below the screenshots from TOP and IOTOP commands. seems there are too many read operations happening on mysql

can someone throw me a light on where to start and find a resolution

regards

IOTOP command screenshot: IOTOP command screenshot

TOP command screenshot: TOP command screenshot

SQL SHOW PROCESSLIST screenshotSHOW FULL PROCESSLIST

cjonnala
  • 31
  • 3
  • Not enough info here. MySQL is busy so consider checking mysql `SHOW FULL PROCESSLIST` to get an idea what work it is busy on. Post the results. – Matthew Ife Apr 03 '20 at 13:33
  • Hi thank you for the response, please find the screenshot attached – cjonnala Apr 03 '20 at 13:41
  • Lengthy read operations are often related to table full scan generated from queries on table not properly indexed. This is one the concept you might have to familiarize yourself with. – Francozen Apr 03 '20 at 18:16
  • @cjonnala SHOW FULL PROCESSLIST; will provide the beginning of the query for the process. – Wilson Hauck Apr 03 '20 at 20:08
  • Unless you have `SUPER` privilege, you can only see the tasks run by your login. That is, the `PROCESSLIST` you showed is incomplete. – Rick James Apr 03 '20 at 23:58
  • Please post TEXT results of SHOW FULL PROCESSLIST; when system is BUSY for more meaningful information. – Wilson Hauck Apr 06 '20 at 12:50
  • Hi, Apologies for the delay, i do not have the SUPER previlege i reckon, i get the same out put for the SHOW FULL PROCESSLIST – cjonnala Apr 16 '20 at 12:14

1 Answers1

2

Your database is doing a lot of table scans. You will need to identify what queries are being slow and index them and/or rewrite them to be more efficient. Start by enabling the slow query log: log_slow_queries=1 long_query_time=0

and put the slow log through mysqldumpslow or pt-query-digest. That will tell you what the most problematic queries are then you'll have to figure out whether better indexing is sufficient or whether you will need to rewrite the queries in a more efficient way.

Gordan Bobić
  • 936
  • 4
  • 10
  • Good. But it may take more than just those two settings to get anything from the slowlog. See http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog – Rick James Apr 04 '20 at 00:00
  • @cjonnala long_query_time=0 will skip recording to the slow query log. Try long_query_time=1 to log anything that takes longer than 1 second. – Wilson Hauck Apr 06 '20 at 12:52
  • @Wilson Hauck, not true - long_query_time=0 will record ALL queries to the slow query log as everything is slower than the threshold of 0. – Gordan Bobić Apr 06 '20 at 23:20