CODEWITHSUNDEEP
phpmysql
Cesar Bielich
Cesar Bielich

Reputation: 4945

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?

Upvotes: 0

Views: 242

Answers (1)

Conor Mancone
Conor Mancone

Reputation: 2030

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.

Upvotes: 2

Related Questions