SQL query took too long

All Installation / Update / Upgrade Issues here.
Becks
.
Posts: 87
Joined: Wed May 07, 2008 5:33 pm
Contact:

SQL query took too long

Postby Becks » Wed May 23, 2012 11:37 am

I got warning from my hosting that query from my Mysql took too long:

Code: Select all

SELECT SQL_NO_CACHE pse.person_id, COUNT(pse.mid) AS played, 0 AS started, 0 AS sub_in, 0 AS sub_out FROM ( SELECT DISTINCT m.id as mid, tp.id as tpid, tp.person_id FROM jos_joomleague_match AS m INNER JOIN jos_joomleague_round r ON m.round_id=r.id INNER JOIN jos_joomleague_project AS p ON p.id=r.project_id LEFT JOIN ( SELECT DISTINCT m.id AS mid, tp.id AS tpid FROM jos_joomleague_match_player AS md INNER JOIN jos_joomleague_match AS m ON m.id = md.match_id INNER JOIN jos_joomleague_team_player AS tp ON tp.id = md.teamplayer_id INNER JOIN jos_joomleague_project_team AS pt ON pt.id=tp.projectteam_id WHERE pt.id='130' AND (md.came_in = 0 || md.came_in = 1)) AS mp ON mp.mid = m.id LEFT JOIN ( SELECT DISTINCT m.id AS mid, tp.id AS tpid FROM jos_joomleague_match_statistic AS md INNER JOIN jos_joomleague_match AS m ON m.id = md.match_id INNER JOIN jos_joomleague_team_player AS tp ON tp.id = md.teamplayer_id INNER JOIN jos_joomleague_project_team AS pt ON pt.id=tp.projectteam_id WHERE pt.id='130') AS ms ON ms.mid = m.id LEFT JOIN ( SELECT DISTINCT m.id AS mid, tp.id AS tpid FROM jos_joomleague_match_event AS md INNER JOIN jos_joomleague_match AS m ON m.id = md.match_id INNER JOIN jos_joomleague_team_player AS tp ON tp.id = md.teamplayer_id INNER JOIN jos_joomleague_project_team AS pt ON pt.id=tp.projectteam_id WHERE pt.id='130') AS me ON me.mid = m.id INNER JOIN jos_joomleague_team_player AS tp ON (tp.id = mp.tpid OR tp.id = ms.tpid OR tp.id = me.tpid) WHERE tp.projectteam_id = '130' AND m.published = 1 AND p.published = 1 ) AS pse GROUP BY pse.tpid;


This query is from joomleague sql. It takes nearly 6 seconds to run the query. It should be less than one second. The sql need to be optimized to work better.
Joomla 2.5.11
JoomLeague - v2.0.84.f90c771
http://www.premierleaguebg.com

User avatar
Trubadix
.
Posts: 339
Joined: Fri Oct 24, 2008 4:35 pm
Joomleague version: 2.0.x
Location: 31542
Contact:

Re: SQL query took too long

Postby Trubadix » Thu May 24, 2012 11:24 am

mostly a reorganice of db tables will reolve that.
Login on your providers page.
Went to MySql
Select your db
Mark all tables of the database
Select reorganice all selected tables.
Life-System : Joomla 2.5.28 auf Domainfactory, Joomleague 2.0.x
http://www.shootingleague.shg24.com/
Test-System: Joomla 3.6.x auf Strato, Joomleague 3.0.x
http://ligatest.ksv-nesselblatt.de/

And_One
Joomleague Project Manager, Donator
Joomleague Project Manager, Donator
Posts: 3613
Joined: Fri Sep 11, 2009 4:00 pm
Joomleague version: 2.0.x
Location: Graz
Contact:

Re: SQL query took too long

Postby And_One » Sun May 27, 2012 9:59 pm

i suggest to do what trubadix gave as hint. have you tried that?

Becks
.
Posts: 87
Joined: Wed May 07, 2008 5:33 pm
Contact:

Re: SQL query took too long

Postby Becks » Sun May 27, 2012 10:23 pm

I don't have this option "reorganice" in phpmyadmin. I got only repair and optimise, but what I've done was to delete all persons from my DB and my host said it's ok now. I'm not using this so I'm ok but those who are going to use it should do somethink because it's important of speed point of view
Joomla 2.5.11
JoomLeague - v2.0.84.f90c771
http://www.premierleaguebg.com

User avatar
Trubadix
.
Posts: 339
Joined: Fri Oct 24, 2008 4:35 pm
Joomleague version: 2.0.x
Location: 31542
Contact:

Re: SQL query took too long

Postby Trubadix » Sat Jun 02, 2012 1:02 pm

What a unlucky resolving :confused:
I can't delete my 2500 persons, I need them. :p
If I get a slow system, a table-optimizing brings up a fast system back.
If not, there is another problem, you have to find out!
Life-System : Joomla 2.5.28 auf Domainfactory, Joomleague 2.0.x
http://www.shootingleague.shg24.com/
Test-System: Joomla 3.6.x auf Strato, Joomleague 3.0.x
http://ligatest.ksv-nesselblatt.de/

testmonkey
.
Posts: 40
Joined: Sat Mar 17, 2007 3:40 am

Re: SQL query took too long

Postby testmonkey » Sun Jul 08, 2012 3:32 am

Hi,

I had the same problem. I have about 1000 persons in the person table and when I click on the Persons link in the Database section in the backend, it takes about 20 seconds and then I get a blank page.

I checked the error logs and I found the following error:
PHP Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 17029121 bytes) in /public_html/administrator/components/com_joomleague/views/persons/tmpl/default.php on line 212

I already tried to optimize and repair the database but that didn't make a difference.

It seems like this query uses a lot of memory and causes the server to go over the allocated limit. To resolve the issue I increased the memory allocated in php.ini. In my case i set it to 128M by adding the following line in php.ini:

php_value memory_limit = 128M;

I don't know why the Person query takes 17MB, that seems like a lot. Can this be improved somehow?

I'm using v1.6.0.9826ddb with joomla 1.5.26.

User avatar
Trubadix
.
Posts: 339
Joined: Fri Oct 24, 2008 4:35 pm
Joomleague version: 2.0.x
Location: 31542
Contact:

Re: SQL query took too long

Postby Trubadix » Sun Jul 08, 2012 8:46 am

let me guess, localhost!?
- server settings
- versions does not corresponding
Check the system requirements for Joomla and Joomleague.
Like PHP, MySQL and so on.

I've got now over 3000 players in my db live and I don't get a error like that.
I'm working on 3 domains with the same data and get no error about spacing like you.
1. domain on domaingo (live joomla 1.5.26 joomleague 1.6.x)
2. domain strato (developement joomla 1.5.26 joomleague 1.6.x)
3. localhost (crashtest joomla 2.5.x joomleague 2.0.0 .)
Life-System : Joomla 2.5.28 auf Domainfactory, Joomleague 2.0.x
http://www.shootingleague.shg24.com/
Test-System: Joomla 3.6.x auf Strato, Joomleague 3.0.x
http://ligatest.ksv-nesselblatt.de/

testmonkey
.
Posts: 40
Joined: Sat Mar 17, 2007 3:40 am

Re: SQL query took too long

Postby testmonkey » Sun Jul 08, 2012 3:41 pm

This is on a shared host, not the most generous system resources.

This my system info:
Joomla: 1.5.26
PHP: 5.2.17
MySQL: 5.1.63
Joomleague: 1.6.0.9826ddb

This is my php.ini:
upload_max_filesize = 20M ;
max_execution_time = 180;
post_max_size = 20M ;
php_value memory_limit = 128M;
----------------------
I added the php_value memory_limit to resolve this issue and it seems to help, or maybe it's just coincidence.

Are your live and development sites on shared hosting?

17MB for used by one query seems like a lot to me. Is this normal? If my host is only allocating me 64MB total, 17MB is 25% of my entire memory allocation and that seems like a lot for 1 function.

testmonkey
.
Posts: 40
Joined: Sat Mar 17, 2007 3:40 am

Re: SQL query took too long

Postby testmonkey » Sun Jul 08, 2012 3:45 pm

Another observation...not sure if this is part of the problem I was having:

A lot of my persons had no nationality assigned and it showed "Select Nation". I updated all these to have a valid country and now when I click on the Persons link it is returning the results much faster.

User avatar
Trubadix
.
Posts: 339
Joined: Fri Oct 24, 2008 4:35 pm
Joomleague version: 2.0.x
Location: 31542
Contact:

Re: SQL query took too long

Postby Trubadix » Sun Jul 08, 2012 9:09 pm

Are your live and development sites on shared hosting?

Have a look to my Signature, both are online on net.

A lot of my persons had no nationality assigned and it showed "Select Nation". I updated all these to have a valid country and now when I click on the Persons link it is returning the results much faster.


Absolutly possible that the errorhandling of empty required fields slows the system down.
All incorrectly filled fields are colored different at overview and should be corrected.

First it's your mistake to ignore requested fields.
To check the errorhandling working faster and better, is a part for Developers.
Please ask developers to check it.
Last edited by Trubadix on Mon Jul 09, 2012 12:00 am, edited 1 time in total.
Life-System : Joomla 2.5.28 auf Domainfactory, Joomleague 2.0.x
http://www.shootingleague.shg24.com/
Test-System: Joomla 3.6.x auf Strato, Joomleague 3.0.x
http://ligatest.ksv-nesselblatt.de/

And_One
Joomleague Project Manager, Donator
Joomleague Project Manager, Donator
Posts: 3613
Joined: Fri Sep 11, 2009 4:00 pm
Joomleague version: 2.0.x
Location: Graz
Contact:

Re: SQL query took too long

Postby And_One » Sun Jul 08, 2012 10:38 pm

testmonkey wrote:Hi,

I had the same problem. I have about 1000 persons in the person table and when I click on the Persons link in the Database section in the backend, it takes about 20 seconds and then I get a blank page.

I checked the error logs and I found the following error:
PHP Fatal error: Allowed memory size of 67108864 bytes exhausted (tried to allocate 17029121 bytes) in /public_html/administrator/components/com_joomleague/views/persons/tmpl/default.php on line 212

I already tried to optimize and repair the database but that didn't make a difference.

It seems like this query uses a lot of memory and causes the server to go over the allocated limit. To resolve the issue I increased the memory allocated in php.ini. In my case i set it to 128M by adding the following line in php.ini:

php_value memory_limit = 128M;

I don't know why the Person query takes 17MB, that seems like a lot. Can this be improved somehow?

I'm using v1.6.0.9826ddb with joomla 1.5.26.



yes, disable server side thumbnail generation in joomleague general settings

testmonkey
.
Posts: 40
Joined: Sat Mar 17, 2007 3:40 am

Re: SQL query took too long

Postby testmonkey » Mon Jul 09, 2012 2:30 am

Thank you for your help And_One and Trubadix, everything looks good now. Migration from 0.93 to 1.6 has been very smooth, thank you Joomleague dev team :thx:


Return to “Installation / Update”

Who is online

Users browsing this forum: No registered users and 2 guests

cron