Massive performace problem

[striker8472]

Hello there. I'm Marcel.

I have a really big problem with the phoca gallery perfomance and hope to get a solution here.

On my website teamninjagames.de I'm running joomla with a jfusion bridged phbb and phpbb gallery. It is massively customized and takes a lot of time to maintain when I need to update joomla, phpbb etc. So I decided to migrate everything to joomla. For the gallery I though phoca gallery would be the best solution so I wrote some scripts to migrate my data and create the thumbs. Everything is working and I got all data migrated successfully to phoca gallery but I is very slow.

Here some stats after my migration from phpbb gallery:
#__phocagallery -> 6691
#__phocagallery_categories -> 145
#__phocagallery_img_votes -> 961
#__phocagallery_img_votes_statistics -> 6708
#__phocagallery_tags -> 78
#__phocagallery_tags_ref -> 8732

I really want to use and support phoca but at the moment I'm pretty desperate. I have activated the debug mode and hope the following details will explain my problem more clearly.

Cause of post limitation I need another post for the details:

[striker8472]

Open our most popular category (more than 900 images / 20 images per page) in frontend takes more than 11349.10 ms:
I found out that not the thumbs are the problem. This query takes the major amount of time:

Code: Select all

SELECT a.*, cc.alias AS catalias, cc.accessuserid AS cataccessuserid, cc.access AS cataccess, 
  CASE WHEN CHAR_LENGTH(cc.alias) THEN CONCAT_WS(':', cc.id, cc.alias) ELSE cc.id END as catslug 
  FROM doag_phocagallery AS a 
  LEFT JOIN doag_phocagallery_img_votes_statistics AS r 
  ON r.imgid = a.id 
  LEFT JOIN doag_phocagallery_categories AS cc 
  ON cc.id = a.catid 
  WHERE  a.catid= 139 
  AND a.published = 1 
  AND a.approved = 1 
  ORDER BY a.date DESC 
  LIMIT 0, 20

Open the image tab inside of phoca gallery in the backend takes more than 3 minutes. We have more than 6000 images. Edit and save one image takes more than 6 minutes.
Debug mode shows me two queries that takes a lot of time:

Code: Select all

SELECT a.*,l.title AS language_title,uc.name AS editor,uua.id AS uploaduserid, uua.username AS uploadusername, uua.name AS uploadname,c.title AS category_title, c.id AS category_id, c.owner_id AS category_owner_id,ua.id AS userid, ua.username AS username, ua.name AS usernameno,v.average AS ratingavg
  FROM `doag_phocagallery` AS a
  LEFT JOIN `doag_languages` AS l 
  ON l.lang_code = a.language
  LEFT JOIN doag_users AS uc 
  ON uc.id=a.checked_out
  LEFT JOIN doag_users AS uua 
  ON uua.id=a.userid
  LEFT JOIN doag_phocagallery_categories AS c 
  ON c.id = a.catid
  LEFT JOIN doag_users AS ua 
  ON ua.id = c.owner_id
  LEFT JOIN doag_phocagallery_img_votes_statistics AS v 
  ON v.imgid = a.id
  WHERE (a.published IN (0, 1))
  GROUP BY a.id
  ORDER BY a.title asc

Code: Select all

SELECT a.*,l.title AS language_title,uc.name AS editor,uua.id AS uploaduserid, uua.username AS uploadusername, uua.name AS uploadname,c.title AS category_title, c.id AS category_id, c.owner_id AS category_owner_id,ua.id AS userid, ua.username AS username, ua.name AS usernameno,v.average AS ratingavg
  FROM `doag_phocagallery` AS a
  LEFT JOIN `doag_languages` AS l 
  ON l.lang_code = a.language
  LEFT JOIN doag_users AS uc 
  ON uc.id=a.checked_out
  LEFT JOIN doag_users AS uua 
  ON uua.id=a.userid
  LEFT JOIN doag_phocagallery_categories AS c 
  ON c.id = a.catid
  LEFT JOIN doag_users AS ua 
  ON ua.id = c.owner_id
  LEFT JOIN doag_phocagallery_img_votes_statistics AS v 
  ON v.imgid = a.id
  WHERE (a.published IN (0, 1))
  GROUP BY a.id
  ORDER BY a.title asc 
  LIMIT 0, 20

Hope for a good advice. I you need more information please let me know.

[christine]

Hallo,

es gibt da u.a. eine: http://teamninjagames.de/plugins/conten ... gallery.js hat eigentlich nichts mit PG zu tun (?)

Deine Seite ist im Wartungsmode. Außerdem, siehe hier: https://sitecheck.sucuri.net/results/teamninjagames.de
Du fährst mit J 3.3.0 Diese ist von April 2014!

Mache bitte zuerst ein Update auf die aktuellste Joomla Version: 3.4.8

Es gab seitdem mehrere updates (schreibe hier nur die letzten auf), davon z.B. J 3.4.5/3.4.6/3.4.7, welche ganz wichtige Sicherheitsupdates waren!

https://www.joomla.org/

Liebe Grüße, Christine

[striker8472]

Hi,

das ist noch die alte Installation mit der phpbb gallery. Die aktuelle ist nicht freigegeben. Ich werde die neue Installation mal temporär freigeben damit du dir einen Eindruck verschaffen kannst.

MFG

[Jan]

Hi, hmm, testing now, without any such results, see images for the first SQL:









There is limit set, so it loads only 20 items per page, the ordering is here which means, the sql query should be load quickly, I have changed the count of categories which do not have any large influence (LEFT JOIN) on the SQL too

In the second query, I get cca 120 ms there, so I cannot say if there is something more on your server, but see the SQL here:
administrator\components\com_phocagallery\models\phocagalleryimgs.php

There the SQL for listing images is build, you can try to comment some parts of the sql row to detect which part can slow down the query (some information in the list is not needed, like rating, etc. etc. so you can modify the sql query and the html output then by your needs)

For example, if I comment:
//$query->select('v.average AS ratingavg');
//$query->join('LEFT', '#__phocagallery_img_votes_statistics AS v ON v.imgid = a.id');

the time of loading reduces, etc.

Jan

[striker8472]

I reuploaded my database and the problem seems to be solved. I'm not sure what the problem was. Sorry I made so much noise. I was afraid about all the time I spend to convert the data from phpbb gallery. Thank you all so much for this great extensions.

[Jan]

Ok