PP 5.21: Performance Issues

dontom · October 24th, 2005, 06:06 PM

after upgrading to 5.21 i realized the new version behaves slower than the version we used before:

Index.php http://fotos.mtb-news.de/fotos/
not from cache:
Page generated in 11.36390 seconds with 26 queries

from cache:
Page generated in 2.17906 seconds with 9 queries

a random picture: http://fotos.mtb-news.de/fotos/showp...46/limit/views
Page generated in 2.29257 seconds with 36 queries

Currently it is 01:00 am and I am nearly the only person on our photo-server - it is a dual xeon standalone machine.
can additional indices or limits in the sql-commands be used to speed up things?

Anything else I could do?

We already use eAccelerator, binary data (=photos) is delivered by lighttpd
Thanks
Thomas

Chuck S · October 24th, 2005, 07:24 PM

Hello

How is this slower?? The new versions is loads faster than any previous version and yours stats here show it. The cache was introduced in 5.2

Index.php http://fotos.mtb-news.de/fotos/
not from cache:
Page generated in 11.36390 seconds with 26 queries

from cache:
Page generated in 2.17906 seconds with 9 queries

Chuck S · October 24th, 2005, 07:26 PM

Here is again with 150000+ images and like 30 categories 55,000 user etc I think this is darn good

Page generated in 1.66513 seconds with 7 queries

Michael P · October 24th, 2005, 07:41 PM

I'd be curious to run with $showsqldebug = 2 to see what the timing of each query is. We could possibly look at indexes to see if there is a way to improve the slower queries; but the cache should really take care of alot.

Keep in mind, if you integrate with a forum and use their headers (read: vB), there is the overhead of their queries/setup which is not computed in ours.

dontom · October 24th, 2005, 11:40 PM

Hi Michael,
I already had switched on the debugging: attached the output of index.php.
edit: I am not able to attach it - a file with the zipped queries of index.php is 416 kb big - where can I email it to?

Chuck: no it is not good - it should be far less then one second, especially if it a part of it already is cached. I just checked vbulletin - the index there currently loads in less then a second and our forums also have 50K users, > 2000K posts etc. Asides from this I measured the performance around midnight - without any traffic involved...

queries2.txt - the queries for http://fotos.mtb-news.de/fotos/showp...71/limit/views

queries3.txt
http://fotos.mtb-news.de/fotos/showgallery.php/cat/503

queries4.txt
http://fotos.mtb-news.de/fotos/comshow.php
(contains the same query twice! see 4 and 5)

Perhaps some limits would do good here to avoid transferring huge amounts of data from db to webserver.

We already went through the same thing after the last upgrade - please see the third post on this page and Michaels post on the same page:
http://www.photopost.com/members/for...ighlight=debug

after these discussions and updated files we went down under 1 sec for the index with pp 502 without any altered file

Thomas

Thanks for your help

dontom · October 25th, 2005, 12:03 AM

Quote:

Originally Posted by Chuck S

Hello

How is this slower?? The new versions is loads faster than any previous version and yours stats here show it. The cache was introduced in 5.2

Index.php http://fotos.mtb-news.de/fotos/
not from cache:
Page generated in 11.36390 seconds with 26 queries

from cache:
Page generated in 2.17906 seconds with 9 queries

It was below one second before, using 5.02
Thomas

Michael P · October 25th, 2005, 07:41 AM

If you look at queries3.txt, you'll notice that except for the first query, none of the others take more than .0X seconds (most in the .00X range).

queries2.txt has one longer query (#10), but again, I question weather the entire page generation time is because of the queries. We can look at the index for these tables.

There are two queries, but they aren't the same - one is to get a total number the other is to get the actual number of comments. However, we can reduce the amount of data being collected by changing line 38 in comshow.php to:

Code:

Content visible to verified customers only.

we can also prevent that second query from happening on subsequent page views by adding to the typecast on line 18:

Code:

Content visible to verified customers only.

Do you have page compression turned on? What if you turn it off?

I see the stats for your index page:

Page generated in 3.86875 seconds with 8 queries

Can you tell me the timing for hose 8 queries (not alot of queries); looking for some indication of where the added delay is coming from.

dontom · October 25th, 2005, 07:48 AM

index.php:

Page generated in 3.60033 seconds with 9 queries
1: SELECT * FROM cache WHERE id=1 (0.94689 sec)
2: SELECT u.userid,u.username,u.email,u.password,u.usergroupid,u.timezoneoffset,s.sessionhash,u.salt,u.membergroupids FROM user u LEFT JOIN session s ON s.userid=u.userid WHERE u.userid=1 (0.58092 sec)
3: SELECT groupid,cpaccess,modaccess,diskspace,uploadsize,uplimit,uploads,comments,editpho,editposts,useralbums,reqmod,canassign FROM usergroups WHERE groupid=6 LIMIT 1 (0.00378 sec)
4: SELECT id FROM photos WHERE approved=0 (0.00304 sec)
5: REPLACE INTO iponline (userid,ipaddr,date,what,item) VALUES ('Thomas','213.183.87.178','1130244351','index','') (0.00110 sec)
6: DELETE FROM iponline WHERE date < 1130243751 (0.00045 sec)
7: SELECT cat,laston FROM laston WHERE userid='1' (0.27459 sec)
8: SELECT userid,date FROM iponline ORDER BY date DESC (0.00252 sec)

We use standard gzip encoding on php level, not especially in pp.

The above queries sum up to 1.81 secs - so we have 1.8 secs left used for moving data from / to mysql/apache and processing the php itself - can you send me a version of index.php to trace throug the single parts of the script?

dontom · October 25th, 2005, 07:50 AM

load also has gone up (see image attached), although monday has more visitors on our site (the down-peak yesterday was me performing the upgrade)

Thomas

Michael P · October 25th, 2005, 08:16 AM

You should see mine on my EF site - with 1,200 users online my loads are in the 10-14 range. I don't think the loads are anything to be concerned about if you have an active site.

The only query that's on the slow side is the one querying the cache table; which I assume is larger on your site.

You currently have 900 users on your site - that going to put a load on your server by itself (I know, because I struggle with more than 900 users on my vB site and performance) - the load isn't being cause by PP, but rather vB. Even your vB pages are loading on the slower side.

I'm not sure what to suggest at this point; given the activity on your server and the amount of data, I still think the cache is saving you time.

Michael P · October 25th, 2005, 09:24 AM

Keep in mind, you also have a fullboat of options turned on; that's going to effect performance when you turn on everything. You have the calendar on, the recent comments, top rated, etc.

The more options you have enabled, the more work being done. If you turned off some of the bells and whistles, then you'd reduce the load.

I would suggest turning off the calendar and clearing your cache to remove that from the data being collected/transfered since the calendar can use a bit of resources on larger sites.

Disable the Calendar PAL and then run this query:

UPDATE cache SET timestamp=0,calcache=NULL WHERE id=1

Michael P · October 25th, 2005, 01:46 PM

Something else to try is turning off all the PALs and then enabling one at a time to see where the hits are and then deciding if you want those PALs enabled.

And, finally, turn off the online tracking for new photos/posts. In Index Options, "Display new comments/photos indicator on index?" set to NO. And "Display number of viewers on categories?" to NO will save a query.

Those options can significantly improve performance on larger setups.

Michael P · October 26th, 2005, 03:13 PM

I've made a slight modification to the pp-inc.php in the build. If you download the build, upload the pp-inc.php and set your cache timeout to 0; let me know if that improves your performance. (see this thread for more info.)

(In Global Options, "Minutes to cache index page data for featured photos, statistics and PALs?" - set this to 0).

dontom · October 27th, 2005, 12:11 AM

I uploaded pp-inc and set cache timeout to zero.

Index page:
Page generated in 9.30507 seconds with 19 queries
1: SELECT u.userid,u.username,u.email,u.password,u.usergroupid,u.timezoneoffset,s.sessionhash,u.salt,u.membergroupids FROM user u LEFT JOIN session s ON s.userid=u.userid WHERE u.userid=1 (0.00341 sec)
2: SELECT groupid,cpaccess,modaccess,diskspace,uploadsize,uplimit,uploads,comments,editpho,editposts,useralbums,reqmod,canassign FROM usergroups WHERE groupid=6 LIMIT 1 (0.00115 sec)
3: SELECT id,ugnoupload,ugnoanno,ugnoview,ugnopost,ugnovote,password,watermark,cattype,ismember,catname,parent,children,thumbs,canzip,moderators,header,footer,headtags FROM categories (0.03113 sec)
4: SELECT id FROM photos WHERE approved=0 (0.00036 sec)
5: REPLACE INTO iponline (userid,ipaddr,date,what,item) VALUES ('Thomas','193.158.37.118','1130389820','index','') (0.00094 sec)
6: DELETE FROM iponline WHERE date < 1130389220 (0.00028 sec)
7: 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 categories c LEFT JOIN photos p ON c.lastphoto=p.id LEFT JOIN photos p2 ON c.thumbnail=p2.id LEFT JOIN comments com ON com.id=c.lastpost ORDER BY c.catorder ASC (0.04890 sec)
8: SELECT cat,laston FROM laston WHERE userid='1' (0.20121 sec)
9: SELECT data FROM datastore WHERE title = 'userstats' (0.05886 sec)
10: SELECT SUM(views) as totalviews, SUM(filesize) as diskuse, count(*) as totalphotos FROM photos (0.63711 sec)
11: SELECT count(*) FROM comments (0.00026 sec)
12: SELECT user,userid,COUNT(*) AS pcount FROM photos GROUP BY user ORDER BY pcount DESC LIMIT 5 (1.23238 sec)
13: SELECT c.username,c.userid,c.cat,c.photo,c.comment,p.bigimage,p.cat,p.storecat,p.userid,p.width,p.height FROM comments c LEFT JOIN photos p ON p.id=c.photo WHERE comment != '' ORDER BY c.date DESC LIMIT 10 (0.44943 sec)
14: SELECT cat,id,title,rating FROM photos ORDER BY rating DESC,views DESC LIMIT 5 (1.16050 sec)
15: SELECT userid,date FROM iponline ORDER BY date DESC (0.00187 sec)
16: SELECT id,user,userid,cat,storecat,title,bigimage,height,width FROM photos WHERE approved=1 AND storecat=0 ORDER BY date DESC LIMIT 13 (1.46648 sec)
17: SELECT id,user,userid,cat,storecat,title,bigimage,height,width FROM photos WHERE approved=1 AND storecat=0 ORDER BY views DESC LIMIT 13 (1.25564 sec)
18: SELECT id,user,userid,cat,storecat,title,bigimage,height,width, id*0+RAND() as rand_col FROM photos WHERE approved=1 ORDER BY rand_col DESC LIMIT 13 (1.78520 sec)

It is 7am here with not too many users online..

last comment column and calendar was already switched off yesterday, but load went over 70.00.

I hope this improves load today, we'll see it later

Michael P · October 27th, 2005, 07:14 AM

You have a PM.

On my pages, with over 1,000 users on my vBulletin site I get stats of:

Page generated in 0.09152 seconds with 11 queries

But you have a larger gallery with 150k images and 53k comments, so some of the bells and whistles may need to be turned off. I can also look at your system to see how we can improve some of the queries.

Some of the options, like "Under Category PAL" and "Highest Rated" images can be turned off to save query time (those are the longer queries - #12, #13, #14). You can turn on the Last Comment column, that has no effect on performance.

I can also look at modifing the cache code to cache the photo blocks only to get rid of those queries.

dontom · March 6th, 2006, 12:28 AM

somehow this all didn't really help - we decided to rewrite showphoto.php with an emphasis on the performance side. we enabled the new version yesterday around 18:00 - see the image attached for the result.

Page generation time also went down from 1.xx or 2.xx seconds to 0.02 - 0.05 seconds
Example: http://fotos.mtb-news.de/fotos/showp...71/limit/views

Stats:
Pictures 161.000 Comments: 195.000

Chuck S · March 6th, 2006, 07:23 AM

Your page takes forever to load from what I see because of your footer or whatever hack it is that displays all those member profiles. Just my .02 cents. I am on 6mb cable and the page took about 3 seconds to load.

Also I don't know if you ever saw this but since this is a very old thread Michael posted this some time back.

http://www.photopost.com/forum/showt...38#post1113838

dontom · March 7th, 2006, 12:07 AM

The example had lots of comments - what you are refering to are the comments incl. the avatars of their posters.
here you only have some comments - loading of these images shouldnt matter: http://fotos.mtb-news.de/fotos/showp...o/226510/cat/2

I am not talking about page loading times but page generation times - before I was somewhere between 1 and 2 seconds or even higher with loads of traffic - now this happens in 0.02 secs.
The old suggestions didnt help.

I am just happy that we tried it, because we were already thinking of getting an additional server - something we don't need to do now.

Thomas

Chuck S · March 7th, 2006, 08:53 AM

I was just pointing out Michael posted a thread which is newer that this thread on performance enhancements.

mtha · April 19th, 2006, 10:04 AM

Quote:

Originally Posted by dontom

somehow this all didn't really help - we decided to rewrite showphoto.php with an emphasis on the performance side. we enabled the new version yesterday around 18:00 - see the image attached for the result.

Page generation time also went down from 1.xx or 2.xx seconds to 0.02 - 0.05 seconds
Example: http://fotos.mtb-news.de/fotos/showp...71/limit/views

Stats:
Pictures 161.000 Comments: 195.000

sounds interesting. what did you do to the file? will you share, or release a mod. for others to benefit from it?