Limit query/function time

Hi all.

I'm running into a random issue where sometimes it take several
minutes (up to 10 or 15) to complete a query. According to 1 or 2
references, this may be a mysql bug. These links explain the similar
problem I'm experiencing:

http://forums.mysql.com/read.php?24,57257
http://forum.percona.com/s/m/790/

I did some testing yesterday and this 1 sql statement successfully
queried (in milliseconds) approximately 50,000 times before it took
several minutes to query, which hung all the other processes on the
database. Because it occurs successfully so many times, I know the
query is good.

Is it possible to set a time limit on a single function in PHP? E.g.,
when I call mysql_query() and it takes 30 seconds, then quit
processing that function and continue on. I know I could set_time_limit
() to a specific time, but that would cause a fatal error... and I
want to be able to catch it.

Any thoughts on what direction I should go? Also, I'm going to do some
research to see if a newer version of mysql has fixed this bug. We're
running 5.0.45.

Thanks,
~Philip

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Limit query/function time

On Thu, Oct 22, 2009 at 4:14 PM, Philip Thompson wrote:
> Hi all.
>
> I'm running into a random issue where sometimes it take several minutes (up
> to 10 or 15) to complete a query. According to 1 or 2 references, this may
> be a mysql bug. These links explain the similar problem I'm experiencing:
>
> http://forums.mysql.com/read.php?24,57257
> http://forum.percona.com/s/m/790/
>
> I did some testing yesterday and this 1 sql statement successfully queried
> (in milliseconds) approximately 50,000 times before it took several minutes
> to query, which hung all the other processes on the database. Because it
> occurs successfully so many times, I know the query is good.
>
> Is it possible to set a time limit on a single function in PHP? E.g., when I
> call mysql_query() and it takes 30 seconds, then quit processing that
> function and continue on. I know I could set_time_limit() to a specific
> time, but that would cause a fatal error... and I want to be able to catch
> it.
>
> Any thoughts on what direction I should go? Also, I'm going to do some
> research to see if a newer version of mysql has fixed this bug. We're
> running 5.0.45.
>
> Thanks,
> ~Philip
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>

You could use ErrorException and catch that:
http://www.php.net/manual/en/class.errorexception.php
Or you could just set your own error handler:
http://www.php.net/manual/en/function.set-error-handler.php

Something I don't know if you've thought of that came to mind, though,
is the MySQL query cache. It's entirely likely that you have a query
that takes several minutes to run, then is stored in query cache for
the next 50,000 iterations of the query. Have you observed this same
behavior without the query cache enabled (it also looks like the bug
mentioned in the second link - the one that pertains to the 5.0.x
branch of mysql - has to do with an overly large query cache)? Based
on that post, I'd guess that your next action would be to disable the
query cache and determine from there if it's a slow query or actually
the bug in MySQL.

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Limit query/function time

Have you run an explain plan on the query?

Bastien

Sent from my iPod

On Oct 22, 2009, at 4:14 PM, Philip Thompson
wrote:

> Hi all.
>
> I'm running into a random issue where sometimes it take several
> minutes (up to 10 or 15) to complete a query. According to 1 or 2
> references, this may be a mysql bug. These links explain the similar
> problem I'm experiencing:
>
> http://forums.mysql.com/read.php?24,57257
> http://forum.percona.com/s/m/790/
>
> I did some testing yesterday and this 1 sql statement successfully
> queried (in milliseconds) approximately 50,000 times before it took
> several minutes to query, which hung all the other processes on the
> database. Because it occurs successfully so many times, I know the
> query is good.
>
> Is it possible to set a time limit on a single function in PHP?
> E.g., when I call mysql_query() and it takes 30 seconds, then quit
> processing that function and continue on. I know I could
> set_time_limit() to a specific time, but that would cause a fatal
> error... and I want to be able to catch it.
>
> Any thoughts on what direction I should go? Also, I'm going to do
> some research to see if a newer version of mysql has fixed this bug.
> We're running 5.0.45.
>
> Thanks,
> ~Philip
>
> --
> PHP General Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Limit query/function time

--Apple-Mail-1879-910964068
Content-Transfer-Encoding: 7bit
Content-Type: text/plain;
charset=us-ascii;
format=flowed;
delsp=yes

On Oct 22, 2009, at 3:26 PM, Eddie Drapkin wrote:

> On Thu, Oct 22, 2009 at 4:14 PM, Philip Thompson > > wrote:
>> Hi all.
>>
>> I'm running into a random issue where sometimes it take several
>> minutes (up
>> to 10 or 15) to complete a query. According to 1 or 2 references,
>> this may
>> be a mysql bug. These links explain the similar problem I'm
>> experiencing:
>>
>> http://forums.mysql.com/read.php?24,57257
>> http://forum.percona.com/s/m/790/
>>
>> I did some testing yesterday and this 1 sql statement successfully
>> queried
>> (in milliseconds) approximately 50,000 times before it took several
>> minutes
>> to query, which hung all the other processes on the database.
>> Because it
>> occurs successfully so many times, I know the query is good.
>>
>> Is it possible to set a time limit on a single function in PHP?
>> E.g., when I
>> call mysql_query() and it takes 30 seconds, then quit processing that
>> function and continue on. I know I could set_time_limit() to a
>> specific
>> time, but that would cause a fatal error... and I want to be able
>> to catch
>> it.
>>
>> Any thoughts on what direction I should go? Also, I'm going to do
>> some
>> research to see if a newer version of mysql has fixed this bug. We're
>> running 5.0.45.
>>
>> Thanks,
>> ~Philip
>
> You could use ErrorException and catch that:
> http://www.php.net/manual/en/class.errorexception.php
> Or you could just set your own error handler:
> http://www.php.net/manual/en/function.set-error-handler.php
>
> Something I don't know if you've thought of that came to mind, though,
> is the MySQL query cache. It's entirely likely that you have a query
> that takes several minutes to run, then is stored in query cache for
> the next 50,000 iterations of the query. Have you observed this same
> behavior without the query cache enabled (it also looks like the bug
> mentioned in the second link - the one that pertains to the 5.0.x
> branch of mysql - has to do with an overly large query cache)? Based
> on that post, I'd guess that your next action would be to disable the
> query cache and determine from there if it's a slow query or actually
> the bug in MySQL.

I didn't think you could catch fatal errors - isn't that why they're
fatal?

I will check into the query cache thing. However, the query that I'm
running isn't exactly the same each time. Each iteration has a
different ID value in the WHERE clause.

Thank you,
~Philip


--Apple-Mail-1879-910964068--

Re: Limit query/function time

On Oct 22, 2009, at 3:30 PM, Phpster wrote:

> Have you run an explain plan on the query?
>
> Bastien
>
> Sent from my iPod

Yes, I have. For giggles, I wanted to run it again. Here it is
(slightly stripped down) and it looks good to me.

mysql> EXPLAIN SELECT
-> `p`.`patient_id`,
-> `p`.`address_id`,
-> `p`.`patient_sub_id`,
-> AES_DECRYPT(`p`.`patient_first_name`, 'hidden_key') AS
`patient_first_name`,
-> AES_DECRYPT(`p`.`patient_last_name`, 'hidden_key') AS
`patient_last_name`,
-> AES_DECRYPT(`p`.`patient_middle_name`, 'hidden_key') AS
`patient_middle_name`,
-> AES_DECRYPT(`p`.`patient_dob`, 'hidden_key') AS `patient_dob`
-> FROM `patient` `p`
-> INNER JOIN `center_patient` `cp` ON `p`.`patient_id` =
`cp`.`patient_id`
-> WHERE ((`p`.`patient_id` = '256783' OR `p`.`patient_sub_id` =
'256783') AND `cp`.`center_id` = '109')
-> ORDER BY `patient_id` DESC;
+----+---------+--------------------+------
+----------------------------------------------------------- -------+
| id | key_len | ref | rows |
Extra |
+----+---------+--------------------+------
+----------------------------------------------------------- -------+
| 1 | 4,4 | NULL | 2 | Using union
(PRIMARY,patient_sub_id); Using where; Using filesort |
| 1 | 8 | p.patient_id,const | 1 | Using
index |
+----+---------+--------------------+------
+----------------------------------------------------------- -------+
2 rows in set (0.00 sec)

There are about 350,000 records in `patient` and `center_patient`
tables. The average number of rows returned from this query is less
than 5.

~Philip


> On Oct 22, 2009, at 4:14 PM, Philip Thompson
> wrote:
>
>> Hi all.
>>
>> I'm running into a random issue where sometimes it take several
>> minutes (up to 10 or 15) to complete a query. According to 1 or 2
>> references, this may be a mysql bug. These links explain the
>> similar problem I'm experiencing:
>>
>> http://forums.mysql.com/read.php?24,57257
>> http://forum.percona.com/s/m/790/
>>
>> I did some testing yesterday and this 1 sql statement successfully
>> queried (in milliseconds) approximately 50,000 times before it took
>> several minutes to query, which hung all the other processes on the
>> database. Because it occurs successfully so many times, I know the
>> query is good.
>>
>> Is it possible to set a time limit on a single function in PHP?
>> E.g., when I call mysql_query() and it takes 30 seconds, then quit
>> processing that function and continue on. I know I could
>> set_time_limit() to a specific time, but that would cause a fatal
>> error... and I want to be able to catch it.
>>
>> Any thoughts on what direction I should go? Also, I'm going to do
>> some research to see if a newer version of mysql has fixed this
>> bug. We're running 5.0.45.
>>
>> Thanks,
>> ~Philip

--
PHP General Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php