0

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?

Cesar Bielich
  • 4,754
  • 9
  • 39
  • 81

1 Answers1

2

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.

Conor Mancone
  • 1,940
  • 16
  • 22
  • This particular table has 62 records and 10 columns so it's not large at all. There are no LONGTEXT colums with tons of data. Just half int and half varchar 255 – Cesar Bielich Jun 11 '17 at 23:01
  • Interesting. You are probably going to have to do some digging. Again, the slow query log will at least help you to identify for sure that there is a problem (consider it a second opinion on your SQL performance). It can be configured to warn you about queries without indexes: turn that part off because we already know your query doesn't use indexes. Then set the actual time part to something greater than 1800 seconds. If your slow query log agrees, then MySQL is definitely having trouble. Why it is having trouble will be the next problem. – Conor Mancone Jun 11 '17 at 23:12
  • So because I am using `OR` in my statement that makes me not using an INDEX? – Cesar Bielich Jun 11 '17 at 23:13
  • For 62 rows you don't need indexes. – zerkms Jun 11 '17 at 23:14
  • I turned on slow queries log and I am not seeing that database listed at all in the log let alone that query – Cesar Bielich Jun 11 '17 at 23:24
  • @CesarBielich: Yes, an `OR` means you are not using an index. There are other ways to get an index involved though: https://stackoverflow.com/questions/2829544/mysql-how-to-index-an-or-clause If you are familiar with the `EXPLAIN` query, that can show that an index is not being used. @zerkms is also right though: you don't normally need indexes for 62 records. However, all bets are off under the worst case scenario (i.e. lots of table locking updates, high CPU load, etc). I think your first step is to verify if you have an actual problem, or faulty reporting. – Conor Mancone Jun 11 '17 at 23:24
  • @Terminus has a good point: 720K seconds is an awfully long time, even for a slow query. Unless you have specifically turned off your PHP time limit, your script shouldn't be running that long. Unless you are ending up with a zombie process, your problem may just be faulty reporting. – Conor Mancone Jun 11 '17 at 23:25
  • yeah based on turning on mysql slow I dont see anything related to that table or db so I am going to excuse this issue as a miss read from lfd. Thanks all for your help. I was able to target some other issues as well in lfd from looking at the logs so thanks @ConorMancone. – Cesar Bielich Jun 12 '17 at 01:03