slow mysql queries via php5-mysql

I upgraded my old Fedora Core 5 server running mysql and php-mysql
from 5.022 and 5.1.6 to 5.075 and 5.2.6 respectively and performance
problems with accessing the databases appeared. I am not sure what
is the source but it seem to be rather related to php5-mysql than
the mysql engine itself.
This is how it shows up for a database of ~ 50k records:

Query: SELECT * FROM `zdjecie` WHERE 1 LIMIT 15000, 20000

1. The query executed via phpMyAdmin
FC5:
Takes ~0.4s to execute and ~4s to start displaying the records

U9.04:
Takes ~0.6s to execute and ~40s to start displaying the records.
Repeating the query SOMETIMES shorten this time to similar values
as for FC5 so it looks like it is sometimes buffered.

2. Same query from the shell level:
mysql -u user --password=xxx db < query > /dev/null

takes a fraction of the second for both setups.

Anybody encountered anything similar or have some tuning suggestions?

--
Marcin

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

Re: slow mysql queries via php5-mysql

Marcin Debowski wrote:
> I upgraded my old Fedora Core 5 server running mysql and php-mysql
> from 5.022 and 5.1.6 to 5.075 and 5.2.6 respectively and performance
> problems with accessing the databases appeared. I am not sure what
> is the source but it seem to be rather related to php5-mysql than
> the mysql engine itself.
> This is how it shows up for a database of ~ 50k records:
>
> Query: SELECT * FROM `zdjecie` WHERE 1 LIMIT 15000, 20000
>
> 1. The query executed via phpMyAdmin
> FC5:
> Takes ~0.4s to execute and ~4s to start displaying the records
>
> U9.04:
> Takes ~0.6s to execute and ~40s to start displaying the records.
> Repeating the query SOMETIMES shorten this time to similar values
> as for FC5 so it looks like it is sometimes buffered.
>
> 2. Same query from the shell level:
> mysql -u user --password=xxx db < query > /dev/null
>
> takes a fraction of the second for both setups.

So all the time is spent travelling back to the 'client' (phpmyadmin)
and processing those results.

The php-mysql bridge isn't very complicated, I doubt it's going to cause
these sort of delays. I would suspect phpmyadmin purely because it has
to process 50,000 rows, work out paging, display them, and so on.

Is phpmyadmin on the same server as mysql?

--
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: slow mysql queries via php5-mysql

On 2009-07-30, Chris wrote:
> So all the time is spent travelling back to the 'client' (phpmyadmin)
> and processing those results.
>
> The php-mysql bridge isn't very complicated, I doubt it's going to cause
> these sort of delays. I would suspect phpmyadmin purely because it has
> to process 50,000 rows, work out paging, display them, and so on.
>
> Is phpmyadmin on the same server as mysql?

Yes, but I daubt phpmyadmin is the culprit - a dedicated web interface I
have written for the database has exactly the same problem. This
interface works fast under the old version of php-/mysql and slow under
the new one. The php code is the same.

--
Marcin

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

Re: slow mysql queries via php5-mysql

Marcin Debowski wrote:
> On 2009-07-30, Chris wrote:
>> So all the time is spent travelling back to the 'client' (phpmyadmin)
>> and processing those results.
>>
>> The php-mysql bridge isn't very complicated, I doubt it's going to cause
>> these sort of delays. I would suspect phpmyadmin purely because it has
>> to process 50,000 rows, work out paging, display them, and so on.
>>
>> Is phpmyadmin on the same server as mysql?
>
> Yes, but I daubt phpmyadmin is the culprit - a dedicated web interface I
> have written for the database has exactly the same problem. This
> interface works fast under the old version of php-/mysql and slow under
> the new one. The php code is the same.

Can you run it on the same server as a test? That will rule out whether
it's network related at least.

Or do you have a cli script you can run to do the same thing?
--
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: slow mysql queries via php5-mysql

On 2009-07-30, Chris wrote:
> Marcin Debowski wrote:
>> Yes, but I daubt phpmyadmin is the culprit - a dedicated web interface I
>> have written for the database has exactly the same problem. This
>> interface works fast under the old version of php-/mysql and slow under
>> the new one. The php code is the same.
>
> Can you run it on the same server as a test? That will rule out whether
> it's network related at least.

Mysql and apache are on the same physical server but it connects via
127.0.0.1.

> Or do you have a cli script you can run to do the same thing?

I just tried to connect via the socket and I am not sure if this helped
- seems slightly better but this could be sheer effect of the different
connection route. I will try to run some more quantitative tests.

--
Marcin


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

Re: slow mysql queries via php5-mysql

Marcin Debowski wrote:
> On 2009-07-30, Chris wrote:
>> Marcin Debowski wrote:
>>> Yes, but I daubt phpmyadmin is the culprit - a dedicated web interface I
>>> have written for the database has exactly the same problem. This
>>> interface works fast under the old version of php-/mysql and slow under
>>> the new one. The php code is the same.
>> Can you run it on the same server as a test? That will rule out whether
>> it's network related at least.
>
> Mysql and apache are on the same physical server but it connects via
> 127.0.0.1.

So it's not network related, I thought they may have been different
physical machines.

>> Or do you have a cli script you can run to do the same thing?
>
> I just tried to connect via the socket and I am not sure if this helped
> - seems slightly better but this could be sheer effect of the different
> connection route. I will try to run some more quantitative tests.

That suggestion was to see if cutting phpmyadmin out of the picture made
a difference rather than testing the network theory.

Plus, mysql_query buffers the results before sending them back to the
client - which means mysql loads 50,000 records in to memory, then sends
it to php.

Are there any differences in (say) memory_limit settings for the two php
setups, or any of the mysql specific settings in php.ini ?

If you use an unbuffered query, does it make a difference
(http://www.php.net/manual/en/function.mysql-unbuffered-quer y.php) ?

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


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