0

I have a bit of an odd problem. I have a number of application user accounts. One account per application rather than one account per user.

I have one application that seems to be hammering the systems resources. CPU usage is at 99% for MySQL from this single user. If I kill the application then usage for the server goes back down to about 20%.

This user account appears to be continuously running the "show variables" command... as far as I can tell, there is absolutely no reference in the application code to "show variables"... though I'm not sure even that should slow down the machine that much... there are also a handful of queries that appear multiple times in the connections list.

So I have two questions/problems:

  1. Is there any reason the "show variables" command would be running constantly like this? Any other sql commands that would cause "show variables" to fire?

AND

  1. Why would I be seeing so many queries getting duplicated for this user? If I have it running from a single PC, that PC generates somewhere around 30-50 connections and many of them are for the same query. So is it running the same query multiple times when it does this?

I'm trying to pinpoint the source of these oddities, so if anyone can point me in some general direction I'd appreciate it.

4
  • Some applications do a lot of stupid things, such as informational queries before 'real' queries. What 3rd party software is involved? – Rick James Sep 27 '16 at 5:45
  • Turn on the "general log" to capture specifics of who is issuing what. – Rick James Sep 27 '16 at 5:45
  • @Rick James, the general log is going to have to be my best bet at this point. Though I've never figured out a good way of organizing it so the data is somewhat clean to sort through. Are there any good resources you know of to filter and organize a general log? Is there any way to pre-filter it so I am only logging a specific user? – eerick Sep 28 '16 at 16:10
  • In Unix, grep. In Windows findstr. They are crude, but might help. – Rick James Sep 28 '16 at 20:30
0

For home-grown monitoring, you can do several things.

I like the slowlog for finding which queries are resource hogs; it also provides some info to jumpstart figuring out what to do with them. A manual SHOW PROCESSLIST is good if you can catch someone in the act.

Cacti, Nagios, RRD, and several others will assist in continual monitoring of dozens of things. Some are useful; most are rather useless. They are likely to repeatedly do SHOW GLOBAL STATUS. (But, as I have said, that should not be too invasive.)

Simply knowing if the computer is up or not is also important.

If you are using Replication, there are a number of other things to check on. SHOW SLAVE STATUS and its Seconds_behind_master is perhaps the most important.

0

This specific command, "SHOW VARIABLES" is usually run a lot by "monitoring tools". Check if your monitoring tools are using this account.

Some other symptoms to tell if it is a monitoring system:

  • Using INFORMATION_SCHEMA related queries
  • SHOW STATUS command

HTH

4
  • Before 8.0 (with the Data Dictionary), MySQL scales poorly with the number of tables. Perhaps there are thousands of tables? – Rick James Sep 27 '16 at 5:43
  • I know this is going to sound silly but at the moment there are no monitoring tools running. The database is fairly young and I'm still convincing management to pay for it. I run a few manual tools to check statuses, but nothing running constantly, and definitely not in that user. – eerick Sep 28 '16 at 16:07
  • @Rick James, there are less than 200 tables. – eerick Sep 28 '16 at 16:08
  • 200 should not be too bad. – Rick James Sep 28 '16 at 20:33

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.