Cesar Bielich Cesar Bielich -4 years ago 65
MySQL Question

Excessive resource usage on a very simple query

I am not sure if I need to be worried about this, but I want to make sure that my script is not bugging mysql or something.

I get an email from lfd about every 30 seconds for a script that I run in php using a query in mysql.

session_start();
include('connect.php');

$sql = "SELECT * FROM table WHERE (dest_id = '".$_SESSION['session_user_id']."' OR dest_id = '0') AND user_id != '".$_SESSION['session_user_id']."' AND `read` = 0 AND org_code = '".$_SESSION['session_org_code']."'";
$result = $GLOBALS['db']->query($sql);
echo $result->num_rows;


This query when I run it manually seems to run very quickly.

The email from the lfd says

Time: Fri Jun 9 01:20:55 2017 -0700
Account: ********
Resource: Process Time
Exceeded: 719621 > 1800 (seconds)
Executable: /usr/bin/php
Command Line: /usr/bin/php /home/myname/public_html/example/includes/inbox_total.php
PID: 17579 (Parent PID:16310)
Killed: No


Is it my understanding that
Exceeded: 719621 > 1800 (seconds)
means that my script is taking
719621
seconds to run?

Is there something need to worry about and if so are there some trouble shooting tips I can use to find the issue?

Answer Source

Best way to find out if this is accurate: turn on the slow query log (if you have access to your MySQL configuration). That will tell you more details for sure. It isn't crazy for a query that seems fast to you to take a long time. If this is a table that also gets written to frequently, then you can end up hanging up MySQL (or at least freezing up one particular transaction) due to an inability to read while waiting to write. It depends very much on what MySQL storage engine you are using, the variability of your load, and some other factors which can be hard to predict. I have certainly had this problem before, and definitively had issues where queries worked fine for me but were bogging down during high load times, unexpected traffic spikes, or other things outside of my control. The latter is especially likely if you are running on a poorly controlled VPS (in this case, poorly controlled by the hosting company: with a poorly configured virtual host a VPS can suck up CPU resources "dedicated" to another VPS, to your detriment).

So is this possible? Absolutely. What do you do about it? Depends on what the root issue is: traffic spikes, poor VPS allocation, etc. Sometimes a lot of digging can be needed to get to the root of the issue.

One immediate issue could be a largish table without proper indexing. It is actually impossible to index an OR condition, so I can actually say without seeing anything else that your query is not using an index. If this table has even a few thousand records, under the wrong load conditions, it could very easily turn into a super slow query, especially if you commonly write to the table and are using MyISAM.

That's just a shot in the dark though without more details.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download