Very Poor Performance

[mihirp]

Hi,

Currently I have around 13,000 images and around 300 categories. The site seems pretty slow.

Under a category which has around 200 sub-cats, it takes around 9 seconds to load.

The following is the query:

Page generated in 9.83779 seconds with 15 queries
1: SELECT varname,setting FROM pp_settings (0.00106 sec)
2: SELECT u.userid,u.username,u.email,u.password,u.usergroupid,u.timezoneoffset,s.sessionhash,u.salt,s.lastactivity,u.membergroupids FROM user u LEFT JOIN session s ON s.userid=u.userid WHERE u.userid=1 (0.00065 sec)
3: UPDATE session SET lastactivity = '1173089027' , location = '/gallery/showgallery.php/cat/506' WHERE userid='1' (0.00015 sec)
4: SELECT groupid,cpaccess,modaccess,diskspace,uploadsize,uplimit,uploads,comments,editpho,editposts,useralbums,reqmod,canassign FROM pp_usergroups WHERE groupid=6 LIMIT 1 (0.00054 sec)
5: SELECT id,ugnoupload,ugnoanno,ugnoview,ugnopost,ugnovote,password,watermark,cattype,ismember,catname,parent,children,thumbs,canzip,moderators,header,footer,headtags,subalbum FROM pp_categories (0.00333 sec)
6: SELECT c.id,c.catname,c.thumbs,c.cattype,c.parent,c.theme,c.thumbtype, c.sortorder,c.frames,c.intro,c.introtitle,c.introcopy,c.ratingtype,c.takevotes,c.ismember,n.userid FROM pp_categories c LEFT JOIN pp_notify n ON (n.userid='1' AND n.photo=c.id AND n.type=1) WHERE c.id=506 (0.00152 sec)
7: REPLACE INTO pp_laston VALUES('506', '1', '1173089027') (0.00024 sec)
8: SELECT * FROM pp_sort WHERE sortid=2 (0.00016 sec)
9: SELECT c.id,c.parent,c.catname,c.description,c.photos,c.private, p.userid, p.user, p.bigimage, p.cat, p.storecat, p.approved, p.width, p.height, p2.bigimage, p2.cat, p2.storecat, p2.userid, p2.user FROM pp_categories c LEFT JOIN pp_photos p ON p.id=c.lastphoto LEFT JOIN pp_photos p2 ON p2.id=c.thumbnail WHERE ( (c.children=506 OR c.subalbum=506) AND c.cattype='a' AND c.photos > 0 ) (0.00125 sec)
10: SELECT c.id, c.description, c.catorder, c.photos, c.posts, c.catdepth, p.id, p.bigimage, p.cat, p.approved, p.user, p.userid, p.date, p.width, p.height, p2.bigimage, p2.cat, p2.storecat, p2.userid, p2.width, p2.height, com.username, com.id, com.date, com.photo FROM pp_categories c LEFT JOIN pp_photos p ON c.lastphoto=p.id LEFT JOIN pp_photos p2 ON c.thumbnail=p2.id LEFT JOIN pp_comments com ON com.id=c.lastpost ORDER BY c.catorder ASC (0.00672 sec)
11: SELECT cat,laston FROM pp_laston WHERE userid='1' (0.00041 sec)
12: SELECT item, count(*) FROM pp_iponline GROUP BY item (0.00018 sec)
13: SELECT id,user,userid,cat,storecat,date,title,description,keywords,bigimage,width,height,filesize,views,medwidth,medheight,medsize,approved,rating,allowprint,numcom,lastposter FROM pp_photos WHERE approved < 2 AND cat=506 ORDER BY disporder,date DESC (0.00743 sec)
14: REPLACE INTO pp_iponline (userid,ipaddr,date,what,item) VALUES ('xxx','xxx','1173089028','showgallery','506') (0.00034 sec)
15: SELECT userid,date FROM pp_iponline WHERE item=506 ORDER BY date DESC (0.00020 sec)

How can I speed it up?

I dread to think what may happen if the website gets around 100,000+ photos with around 5000+ categories.

The server is pretty powerful : Dual processor + 1gb ram + its a dedicated machine only for this website and I have not yet opened it for general public. Meaning there are no viewers other than myself.

[Sybaris]

So why would those querys make it load in 9 seconds when the sum total of them all is less than a second?

[Chuck S]

Exactly Sybaris is right here why is your site slow because of Photopost

Your query timing is real fast so your site is chocking on something else. it would help for you to post urls here where you think things are slow. You can do things like turn off the category cache to see if your site speeds up in global options and also turn off spider urls also

[mihirp]

Hi Sybaris and Chuck,

I tried it now again using a diff pc at my home which has a faster connection and now it looks fine. Could it be that the internet connection was slow and that was causing the loading time to show as 9 seconds. However, as I understand "page generation" time is server specific parsing time, so when all the queries were sumed up to less than a second, why was the total Page generated time showing as 9 seconds. Is it a bug or is the Page generated time computed as page loading time on the client side as opposed to individual query time which are server side.?

[Chuck S]

It is the loading time it takes it to load or process over your connection if it is fine on another computer connection it must be specific to that computer