Mysql completing Query fast but mysql->query() takes long time to return even single selects/upda

------=_NextPart_000_007F_01CAD7C9.199AE3A0
Content-Type: text/plain;
charset="US-ASCII"

As you can see PHP claims it took 20 seconds for mysql->query() to return
but mysql think is took around 1.0s


This is from our application
I enabled profile in mysql to determine why an update took 20seconds. As
you can see MySQL reported no where near that amount of duration took
place.
Is there any way I can dig into php and determine why mysql client libs are
so slow (this is not using mysqlnd but mysql-client-libs on CentOS using
5.3.2)


04/06/2010 14:54:54 20.6899s UPDATE `calls` SET `Result`='Busy' WHERE
`CallID`='144786'
| Status | Duration | CPU_user | CPU_system |
Context_voluntary | Context_involuntary | Block_ops_in | Block_ops_out |
Messages_sent | Messages_received | Page_faults_major | Page_faults_minor |
Swaps |

------------------------------------------------------------ ----------------
------------------------------------------------------------ ----------------
------------------------------------------------------------ ------
| starting | 0.000039 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| checking permissions | 0.000008 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| Opening tables | 0.000010 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| System lock | 0.000005 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| Table lock | 0.000006 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| init | 0.000036 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| Updating | 0.000099 | 0.001000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| end | 0.000023 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| query end | 0.000004 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| freeing items | 0.007410 | 0.000000 | 0.000000 | 4
| 1 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| logging slow query | 0.000004 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |
| cleaning up | 0.000004 | 0.000000 | 0.000000 | 0
| 0 | 0 | 0 | 0 | 0
| 0 | 0 | 0 |





This is to a remote system ( but on same GigE switch), however mysql
profiling would log transit type if this was a select .



Thanks

David Murphy


------=_NextPart_000_007F_01CAD7C9.199AE3A0--

Re: Mysql completing Query fast but mysql->query() takeslong time to return even single selects/u

David Murphy wrote:
> As you can see PHP claims it took 20 seconds for mysql->query() to return
> but mysql think is took around 1.0s
>
>
> This is from our application
> I enabled profile in mysql to determine why an update took 20seconds. As
> you can see MySQL reported no where near that amount of duration took
> place.
> Is there any way I can dig into php and determine why mysql client libs are
> so slow (this is not using mysqlnd but mysql-client-libs on CentOS using
> 5.3.2)

Is this a one-off thing or is it happening all the time?

If it's a one-off thing it could be a spurious result (maybe someone
else was doing a mysqldump when your query ran, the dump blocks your
query)..

What sort of mysql table is it? if it's innodb you can try it in a
transaction and roll it back:

begin;
update blah;
rollback;

see how long it takes.

if it takes a short time in the mysql client, then try it in a php
script from your other server.

--
Postgresql & php tutorials
http://www.designmagick.com/


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

Re: Mysql completing Query fast but mysql->query() takes

just a pointer.. have you enabled php profiling to see if it actually
mysql->query() that takes 20 secs ?

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

RE: Mysql completing Query fast but mysql->query() takes long time to return even single selects/

I see this occurring randomly on different quries with different
indexes/tables.

It appears like php is taking a lot longer than mysql's transmission time to
cache the result be it bool TRUE/FALSE or a select of 1 - 50 records.
While it does not happen all the time, since from the MySQL side I can see
how long each step was and how long it took to even transmit the results to
the php server. I am thinking it a small memory hole we do not normaly see
for some reason but there are no sqlng parameters even that seem to be
able to help in this situation.

Also since this is is not really repeatable as is occurs only sometimes and
not with a predictable frequency it would be very hard to do the roll
back.
Also since its in the mysql class of functions I cant even debug into it to
try to see much of anything (since its c++ code not userland functions).

David

-----Original Message-----
From: Chris [mailto:dmagick@gmail.com]
Sent: Sunday, April 11, 2010 6:26 PM
To: David Murphy
Cc: php-db@lists.php.net
Subject: Re: [PHP-DB] Mysql completing Query fast but mysql->query() takes
long time to return even single selects/updates

David Murphy wrote:
> As you can see PHP claims it took 20 seconds for mysql->query() to
return
> but mysql think is took around 1.0s
>
>
> This is from our application
> I enabled profile in mysql to determine why an update took 20seconds. As
> you can see MySQL reported no where near that amount of duration took
> place.
> Is there any way I can dig into php and determine why mysql client libs
are
> so slow (this is not using mysqlnd but mysql-client-libs on CentOS using
> 5.3.2)

Is this a one-off thing or is it happening all the time?

If it's a one-off thing it could be a spurious result (maybe someone
else was doing a mysqldump when your query ran, the dump blocks your
query)..

What sort of mysql table is it? if it's innodb you can try it in a
transaction and roll it back:

begin;
update blah;
rollback;

see how long it takes.

if it takes a short time in the mysql client, then try it in a php
script from your other server.

--
Postgresql & php tutorials
http://www.designmagick.com/


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