FUDforum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » FUDforum Development » Bug Reports » Caching content for anonymous users
Show: Today's Messages :: Unread Messages :: Show Polls :: Message Navigator
| Subscribe to topic | Bookmark topic 
Switch to threaded view of this topic Create a new topic Submit Reply
Caching content for anonymous users [message #165641] Fri, 29 July 2011 06:46 Go to next message
eclipsewebmaster is currently offline  eclipsewebmaster   Canada
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
I'm having problems with the "Unanswered messages" link, and other similar ones, when a user is not logged in.

The query used to generate some pages causes a tmp table on disk. In Eclipse's case, that tmp table is 1021M in size, and it is growing every month. The query takes about 4s to run. We have a 16G ramdisk, but there are some occasions where multiple users hitting similar links can cause our ramdisk to fill up.

Since some of the joined tables in those queries are constantly changing, MySQL's query cache cannot be used, so the query must be run constantly. Below is a sample of the query.

So my request is to either a) cache the results of this query in a table that gets updated every x minutes, or change the structure of the query so that it can benefit from the mysql query cache.

For the time being, I've had to disable those "Unanswered Messages" pages at Eclipse.



SELECT
m.*, COALESCE(m.flag_cc, u.flag_cc) AS disp_flag_cc, COALESCE(m.flag_country, u.flag_country) AS disp_flag_country,t.thread_opt, t.root_msg_id, t.last_post_id, t.forum_id,f.message_threshold, f.name, u.id AS user_id, u.alias AS login, u.avatar_loc, u.email, u.posted_msg_count, u.join_date,u.location,u.sig, u.custom_status, u.icq, u.jabber, u.affero, u.aim, u.msnm, u.yahoo, u.skype, u.google, u.twitter, u.last_visit AS time_sec, u.users_opt,l.name AS level_name, l.level_opt, l.img AS level_img,p.max_votes, p.expiry_date, p.creation_date, p.name AS poll_name, p.total_votes,pot.id AS cant_vote,r.last_view, mm.id AS md,m2.subject AS thr_subject,g1.group_cache_opt
FROM fud_msg m
INNER JOIN fud_thread t ON m.thread_id=t.id
INNER JOIN fud_msg m2 ON m2.id=t.root_msg_id
INNER JOIN fud_forum f ON t.forum_id=f.id
INNER JOIN fud_cat c ON f.cat_id=c.id INNER JOIN fud_group_cache g1 ON g1.user_id=0 AND g1.resource_id=f.id
LEFT JOIN fud_read r ON r.thread_id=t.id AND r.user_id=0
LEFT JOIN fud_users u ON m.poster_id=u.id
LEFT JOIN fud_level l ON u.level_id=l.id LEFT JOIN fud_poll p ON m.poll_id=p.id LEFT JOIN fud_poll_opt_track pot ON pot.poll_id=p.id AND pot.user_id=0
LEFT JOIN fud_mod mm ON mm.forum_id=f.id AND mm.user_id=0
WHERE m.apr=1
AND t.replies=0
AND (mm.id IS NOT NULL OR ((g1.group_cache_opt) & 2) > 0)
ORDER BY
f.last_post_id DESC , t.last_post_date DESC , m.post_stamp DESC LIMIT 40 OFFSET 0;
Re: Caching content for anonymous users [message #165792 is a reply to message #165641] Thu, 11 August 2011 10:24 Go to previous messageGo to next message
eclipsewebmaster is currently offline  eclipsewebmaster   
Messages: 46
Registered: November 2009
Location: Ottawa, Ontario, Canada
Karma: 0
Member
add to buddy list
ignore all messages by this user
I can see some potential solutions:

a) break the query down into smaller queries to not confuse the MySQL query optimizer

b) find a way to stop writing frequently to tables that need to be read (ie, post counts and view counts are preventing some queries from being cached)

c) cache the "Unanswered" content for anonymous users, since search engines can bang on them quite hard

In the short term, a) is perhaps the easiest to implement. I'll take a look at it when I get a chance, but for the time being, I may need to forbid the Unanswered content at the Apache level, since this query is a huge DoS waiting to happen.
Re: Caching content for anonymous users [message #165804 is a reply to message #165792] Fri, 12 August 2011 08:31 Go to previous messageGo to next message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
The "File System Cache" plugin in FUDforum 3.0.3 may also help (anon users will be served cached output, bypassing the template and associated SQL).

We should also mark these links with rel="nofollow".
Re: Caching content for anonymous users [message #166255 is a reply to message #165804] Mon, 17 October 2011 06:03 Go to previous message
naudefj is currently offline  naudefj   South Africa
Messages: 3624
Registered: December 2004
Karma: 17
Senior Member
Administrator
Core Developer
remove from buddy list
ignore all messages by this user
Also, see http://sourceforge.net/apps/trac/fudforum/changeset/5395
Quick Reply
Formatting Tools:   
  Switch to threaded view of this topic Create a new topic
Previous Topic: cannot register
Next Topic: PDF creation error (FUDforum 3.0.3)
Goto Forum:
  

-=] Back to Top [=-
[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Thu Oct 19 09:00:14 EDT 2017

Total time taken to generate the page: 0.00506 seconds