FAQFAQ   SearchSearch   MemberlistMemberlist   UsergroupsUsergroups  Who is OnlineWho is Online   Join! (free) Join! (free)  
 ProfileProfile   Log in to check your private messagesLog in to check your private messages   Log inLog in 
  • Welcome
  • Guest

Faster forums part three (slow queries)

 
Post new topic   Reply to topic    myfreeforum.org Forum Index -> Forum Blogs
View previous topic :: View next topic  
Author Message
myff admin
Site Admin
Site Admin
FLAG

Virtual Cash: 92890

Joined: 22 May 2005
Posts: 32203



Add Karma

rated by 281 members
Add Comment
Show Comments


online/offline
PostPosted: Sun Aug 09, 2009 8:03 pm    Post subject: Faster forums part three (slow queries)  Reply with quote

Another review on slow queries on the forums shows up a very frequent culprit.

This is the bit that shows who is online. There are two notable issues there, one is that phpbb2 lacks an index on session_time and the other is that this is a query that is being made perhaps many times a minute whilst we don't really need to see the information changing more than once a minute at most.

The lack of an index whilst suspect is not necessarily wrong, as it is the sort of query where mysql may decide to ignore the index anyway. But we can and should cache the results and not repeat the query incessantly.
_________________

Family Friendly Shareware | | Web Design/Services | Free Forums
Back to top
View user's profile Send private message Visit poster's website
myff admin
Site Admin
Site Admin
FLAG

Virtual Cash: 92890

Joined: 22 May 2005
Posts: 32203



Add Karma

rated by 281 members
Add Comment
Show Comments


online/offline
PostPosted: Mon Aug 31, 2009 9:59 am    Post subject: Reply with quote

I am also introducing an index for topic_last_post_id in the topics table.

There are a lot of queries that use it, and some evidence that some queries can go a little rogue and scan whole tables when this index is not present.

Some of these issues seem to occur with databases where the tables have not been optimized, which should not be an issue here since active forums to get an optimize once in a while.

I did already have the index available on the downsizer forum, and just sabotaged it by removing the index   There was no discernible effect.

However on balance it is responsible for a lot of unindexed queries, and a lot of people do seem to report benefits.
_________________

Family Friendly Shareware | | Web Design/Services | Free Forums
Back to top
View user's profile Send private message Visit poster's website
myff admin
Site Admin
Site Admin
FLAG

Virtual Cash: 92890

Joined: 22 May 2005
Posts: 32203



Add Karma

rated by 281 members
Add Comment
Show Comments


online/offline
PostPosted: Wed Sep 02, 2009 10:14 am    Post subject: Reply with quote

It is a little hard to quantify, but since this change the database snapshots that log slow queries are not showing queries with last_post_id any more.

The snapshot system is not the same as the comprehensive slow query log system in mysql, but it is good enough to be showing the slowest points of database operation, so I think we can call this a success
_________________

Family Friendly Shareware | | Web Design/Services | Free Forums
Back to top
View user's profile Send private message Visit poster's website
myff admin
Site Admin
Site Admin
FLAG

Virtual Cash: 92890

Joined: 22 May 2005
Posts: 32203



Add Karma

rated by 281 members
Add Comment
Show Comments


online/offline
PostPosted: Fri Sep 04, 2009 9:35 am    Post subject: Reply with quote

Hit on 3 more database routines this morning, two potentially nasty ones that I very glad to see sorted
_________________

Family Friendly Shareware | | Web Design/Services | Free Forums
Back to top
View user's profile Send private message Visit poster's website
myff admin
Site Admin
Site Admin
FLAG

Virtual Cash: 92890

Joined: 22 May 2005
Posts: 32203



Add Karma

rated by 281 members
Add Comment
Show Comments


online/offline
PostPosted: Sat Sep 05, 2009 4:57 pm    Post subject: Reply with quote

This is a small tweak that will occasional mean that posts since last visit will show a few more posts than it should.

The time it uses changes from:

$newpoststime = $userdata['user_lastvisit'];

to

$newpoststime = (intval($userdata['user_lastvisit']/3600)*3600);

This means the time is rounded to a granular hour. Which means in turn if you click on "View posts since last visit" on a forum with a lot of members, then there is far more likelyhood of the same query hitting the database and hence being cached for a quick result.
_________________

Family Friendly Shareware | | Web Design/Services | Free Forums
Back to top
View user's profile Send private message Visit poster's website
myff admin
Site Admin
Site Admin
FLAG

Virtual Cash: 92890

Joined: 22 May 2005
Posts: 32203



Add Karma

rated by 281 members
Add Comment
Show Comments


online/offline
PostPosted: Sat Sep 05, 2009 6:38 pm    Post subject: Reply with quote

I hate to do it, as it is tedious and the rewards are very small, but any query like:
Code:

'SELECT *   FROM ' . THEMES_TABLE . '
      WHERE themes_id = ' . (int) $style;

is in efficient. It has to query the database to find the field names first.

the query should be:


Code:

'SELECT themes_id,template_name,style_name,head_stylesheet,body_background,body_bgcolor,body_text,body_link, body_vlink,body_alink,body_hlink,tr_color1,tr_color2,tr_color3,tr_class1,tr_class2,tr_class3, th_color1,th_color2,th_color3,th_class1,th_class2,th_class3,td_color1,td_color2,td_color3, td_class1,td_class2,td_class3,fontface1,fontface2,fontface3,fontsize1,fontsize2,fontsize3, fontcolor1,fontcolor2,fontcolor3,span_class1,span_class2,span_class3,img_size_poll,img_size_privmsg
      FROM ' . THEMES_TABLE . '
WHERE themes_id = ' . (int) $style;


The difference is probably about 5/10000 of a second, almost infinitesimal but still measurable time that can add up.

I once worked with someone who was adamant that nothing could be done to speed up his code, this for a company where the major problem was the code speed. First off a turned things on there head in terms of how the code was used. I can't tell but I'd guess that probably more than doubled the speed, then I set about all the little things, it took a long time but I think I squeezed 50% more out, slice by little slice. I'm certain that last squeeze made the difference between success and failure.
_________________

Family Friendly Shareware | | Web Design/Services | Free Forums
Back to top
View user's profile Send private message Visit poster's website
myff admin
Site Admin
Site Admin
FLAG

Virtual Cash: 92890

Joined: 22 May 2005
Posts: 32203



Add Karma

rated by 281 members
Add Comment
Show Comments


online/offline
PostPosted: Sun Sep 06, 2009 6:07 pm    Post subject: Reply with quote

I checked the query cache efficiency just before doing the latest posts change. It was 76.6% e.g. already quite an extraordinary proportion of eligible database queries don't have to be done again, the results can be picked directly from the cache.

A day later and we are running at 77.5% It would be rash to assert that the change is a certain cause of this increase, but all the same it is good.
_________________

Family Friendly Shareware | | Web Design/Services | Free Forums
Back to top
View user's profile Send private message Visit poster's website
myff admin
Site Admin
Site Admin
FLAG

Virtual Cash: 92890

Joined: 22 May 2005
Posts: 32203



Add Karma

rated by 281 members
Add Comment
Show Comments


online/offline
PostPosted: Wed Sep 09, 2009 2:50 am    Post subject: Reply with quote

three days later and we have 80%


_________________

Family Friendly Shareware | | Web Design/Services | Free Forums
Back to top
View user's profile Send private message Visit poster's website
Display posts from previous:   
Post new topic   Reply to topic    myfreeforum.org Forum Index -> Forum Blogs All times are GMT
Page 1 of 1
 
 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Card File  Gallery  Forum Archive
Powered by phpBB © 2001, 2005 phpBB Group
Create your own free forum | Buy a domain to use with your forum