1

I have my Mysql DB instance in RDS, My CPU is jumping from 50% to 100%.. Later i checked my DB threads..

I was surprised with my thread count.

 SHOW STATUS WHERE variable_name LIKE "Threads_%" OR variable_name =
 "Connections"

The output for the above query as follow

Threads connected 21
Threads created 1642
Threads running 18446744073709515083

What is happening here..! Any help appreciated..

Also attached image for your ref

enter image description here

Manikandan Ram
  • 389
  • 1
  • 14
  • Please post results of SELECT @@version; You are probably looking at needing to post a 'bug report'. It is unlikely in your lifetime you will see a CPU with enough TB of RAM to support this count of Threads running. Maybe this would work better for you, SHOW GLOBAL STATUS WHERE variable_name LIKE 'Threads_%' OR variable_name = 'Connections'; – Wilson Hauck Mar 10 '20 at 14:54
  • 1
    5.7.12-log -- version.. Added the image – Manikandan Ram Mar 11 '20 at 11:51
  • Your 5.7.12 became General Availability 2016-04-11. Newer changelogs do not mention threads_running that included looking through 5.7.27 which became GA on 2019-07-22. With 26087 Connections, you have an obviously busy system. My next comment will request additional information to allow workload analysis. This specific question about Threads_running with such a large count should be reported as a bug, in my opinion. – Wilson Hauck Mar 11 '20 at 12:38
  • Additional information request. RAM size, # cores, any SSD or NVME devices on MySQL Host server? Post on pastebin.com and share the links. From your SSH login root, Text results of: B) SHOW GLOBAL STATUS; after minimum 24 hours UPTIME C) SHOW GLOBAL VARIABLES; D) SHOW FULL PROCESSLIST; E) complete MySQLTuner report AND Optional helpful information, if available includes - htop OR top for most active apps, ulimit -a for a Linux/Unix list of limits, iostat -xm 5 3 for IOPS by device and core/cpu count, for server workload tuning analysis to provide suggestions. – Wilson Hauck Mar 11 '20 at 12:40
  • If you could post requested information, will provide some tips for improving performance of your instance. – Wilson Hauck Mar 21 '20 at 19:02

1 Answers1

2

18446744073709515083 -- Sounds like the actual number is -1 (or some other slightly negative number). This would be a fluke, possibly caused by some threads going away while your SHOW STATUS was running. Try simply SHOW STATUS LIKE 'Threads_running'.

What version of MySQL are you using? Some major changes happened in 8.0. If that is the version, please file a bug report ( bugs.mysql.com ).

Use SHOW FULL PROCESSLIST to see what queries are running. Ignore the "Sleep" ones. Check out the ones with large "Time".

High CPU usually comes from an inefficient SELECT. Find it, then show us EXPLAIN SELECT ... and SHOW CREATE TABLE.

Rick James
  • 2,058
  • 5
  • 11
  • Sound good.. I contacted the AWS team, They had some bugs in the Aurora engine version 2.04.5, I updated the Aurora version to 2.07 the issue is fixed now..Also the DB CPU is still going high even for lower number of users, – Manikandan Ram Mar 16 '20 at 16:56
  • Is there any 3rd party tools to check the inefficient queries..and montior the DB effectively – Manikandan Ram Mar 16 '20 at 16:57
  • 1
    @ManikandanRam - I like the slowlog for finding inefficient queries. – Rick James Mar 16 '20 at 18:25
  • Sometimes under loaded conditions,Chances were there for normal optimal queries to be listed under slow query..! In that situations what can we do – Manikandan Ram Mar 17 '20 at 04:01
  • 1
    @ManikandanRam - Certain settings lead to unnecessary entries, see this for my recommendations: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog – Rick James Mar 17 '20 at 21:46
  • @ManikandanRam - Also, use `pt-query-digest` (as mentioned there) to summarize the log, thereby making it easier to ignore the noise. – Rick James Mar 17 '20 at 21:47
  • Interesting, Let me go through the docx – Manikandan Ram Mar 18 '20 at 07:30