0

I have a MySql server which has 2 active databases which are used for a web application and 3 inactive ones (left for archival purpose but not currently used). The problem is that the server seems to have an abnormal amount of queries being run in it. For example, I would restart the server and within a minute the number if queries asked would jump to 800 - and then it never goes down below 6 queries a second.

To test this further, I removed all the database users from the active and inactive databases, so that web application users cannot connect to the server. Remote connection to the server is also disabled. The only remaining database user associated to the server was the user for PHPMyadmin. I left the system in this state so that no "real queries" can be generated, and I now have the following data:

Network traffic since startup: 535 MiB

This MySQL server has been running for 0 days, 14 hours, 41 minutes and 26 seconds.

Questions since startup: 31,858 Documentation ø per hour: 2,169 ø per minute: 36

Items with red flag (Warning):

Aborted clients 2
Aborted connects 1.1 k
Created tmp disk tables 48
Handler read rnd 17.9 k
Handler read rnd next 3 M
Innodb buffer pool reads 9.1 k
Key reads 2.2 k
Opened tables 757
Slow queries 56
Table locks waited 5

My server variables are attached here

The slow query log shows queries that we never wrote in our web application like:

SELECT /*!4001 SQL_NO_CACHE/* * FROM <tablename> for every table in every database and a bunch of big union queries labeled as "Sends query", "Defers query", "SMTP query" etc.

My question is how is my server generating so many queries with no real user connected? Is this normal? Is there something I can do to Improve this?

0

Are you sure there are no cron jobs performing some database queries? Or that your MySQL is not configured to use its event scheduler?

MySQL Events are tasks that run according to a schedule. Therefore, we sometimes refer to them as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab (also known as a “cron job”) or the Windows Task Scheduler.

  • How do I check the event scheduler? I am sure about cron jobs because all my cron jobs are PHP scripts and they would need to connect to the server first..and I have disassociated the relevant database user from the database. – coderkane Aug 19 '13 at 5:25
  • Try if SHOW EVENTS\G returns something. – Janne Pikkarainen Aug 19 '13 at 5:29
  • mysql> use <dbname>; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW EVENTS\G Empty set (0.00 sec) – coderkane Aug 19 '13 at 5:35
0

It's been a while since this question was asked, but in case it's helpful to anyone coming across it, one approach would be to try turning on the general query log to see all queries that are being executed (see http://dev.mysql.com/doc/refman/5.5/en/query-log.html) and see if any look familiar. If you don't recognize the queries, you could try doing a recursive grep for parts of some of the queries to find the code / script they are located in on the server.

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.