Today I have perhaps the most important one that I have seen so far, it came after debugging this database query:
explain extended SELECT feed.*, f.friend_id AS is_friend, apps.app_title, u.user_id, u.profile_page_id, u.server_id AS user_server_id, u.user_name, u.full_name, u.gender, u.user_image, u.is_invisible, u.user_group_id, u.language_id FROM phpfox_feed AS feed JOIN phpfox_user AS u ON(u.user_id = feed.user_id) LEFT JOIN phpfox_friend AS f ON(f.user_id = feed.user_id AND f.friend_user_id = 1) LEFT JOIN phpfox_app AS apps ON(apps.app_id = feed.app_id) WHERE feed.time_stamp > '0' AND feed.feed_reference = 0 GROUP BY feed.feed_id ORDER BY feed.time_update DESC LIMIT 10Why is it such a naughty query? well the feed table is the main table here, but the only two conditions to filter it are the time_stamp and the feed_reference. Most of the time feed_reference will be 0 so this isn't a great filter, and time_stamp > 0 is trivial, in fact ignored by MySQL. So, many times this will run through the entire feed table, which is very likely to host hundreds of thousands of records.
Luckily we have a setting in the AdminCP that can help with this, "Feed Limit (Days)", this tiny little setting will help you greatly to improve performance. What it does is to limit the feeds to a number of days in the past. One way to find a good value for this is to do this check, add a photo to the feed or something that you can easily identify, come tomorrow and look for that feed, if you cannot find it in the first page (before the ajax load ) then you think that 1 day is enough, but for safety check the next page of feeds, if you cannot find it in the 3d page then my advise is to set this to 3 so it looks a maximum of 3 days in the past.
Hope it helps
Dear Purefan,You made a great discovery. However, i am afraid that the said setting is on applicable on the "dashboard feeds" and not on "Profile Feeds". If it can act similarly on Profile feeds, then off course that can save the hassle of looking up entire feed table when user visits the profile page.Thanks.
ReplyDeleteThank you for your comment. The feed works differently in profiles and the query is different, it uses different indexes and different conditions. So far I have not found a query that scans the entire feed table in profiles, I will post more tips as time permits but I can quickly tell you that Timeline is a little costly for the database and having it disabled is a performance gain.
DeleteThanks for your reply. I believe that once you try to optimize timeline queries you can make it very fast and better optimized. For example, on load of timeline the query should be like this:- (just a suggestion)
ReplyDeleteOn loading of profile only the last lets say 20 activity feeds or say 30 feeds be loaded by default (by user ID and using LIMIT clause) .
Similarly the yearly timeline should work in a similar way, if a user click "2011" and then "September" then the last 30 feeds of September 2011 activity of that user should be displayed and for more retrieval "view more" should be used.
Its just a idea...what do you think?
Thanks alot!
It looks like you missed a part in your post (after the "be like this"), but we do these things already, the problem is that to get the "last X" feeds mysql needs to scan a lot records in the database before it returns only some records. And scanning tables is what takes resources.
DeleteI didnt have enough time to try a new approach for timeline years, but I think I may be able to squeeze some performance gain by not getting a COUNT but only the very first feed in a given condition, still need to test this more though.
Thanks for your comment!
Purefan, As you're looking for ways to optimize scalability for phpFox, have you checked out Varnish Cache? https://www.varnish-cache.org/
ReplyDeleteI have seen lots of great feedback of that, reducing CPU and RAM with over 75%!
I would love to see a tutorial to have that setup with phpFox.
The thing is that varnish is independent of the code running, you can have it in front of an ASP script, a NodeJS app or a Perl script and it will work just the same, its great Ive heard, but not something that you can integrate Into a script.
DeleteThe way these accelerators work is that you put them in front of your web server (apache for example) and they intercept traffic before they reach your code, for common things the accelerator will not send the request to the web server, so it speeds things up like this.
It helps yeah, but you cannot put it inside Perl, Asp, Php,...
So if you want varnish, you dont need a tutorial for Phpfox, you need a tutorial for your server, if you run ubuntu here's one: https://www.varnish-cache.org/installation/ubuntu
There are others in their official site.
Hope it helps