424676 - Improve performance of forums

Status: VERIFIED DUPLICATE of bug 425135

(support.mozilla.org :: Forum, task)

Description

[Jason Barnabe (np)]

Forum threads take anywhere for 5 to 25 seconds to load. This is especially annoying for contributors because they open a lot of threads.

Will the site-wide performance improvements help this? Can we profile the code? Should we be archiving old threads?

My goal here is a consistent 5 seconds or less to load a thread's page.

Comment 1

[Jason Barnabe (np)]

Attachment: perf improvements for commentslib

Some code optimizations for forum thread listings and forum thread pages.

-Prevents 4 libraries for inbound e-mail from loading when we're not going to use them anyway
-Prevents a call to process_inbound_mail when there's no mail server configured, which saves us from running an additional select * from `tiki_forums` where `forumId`=?
-Prevents get_last_forum_posts from running select * from `tiki_comments` where `objectType` = ? and `object`=? order by ? if the number of records is 0.

I don't think this will help performance all that much, but it's a start, and there are no functional changes.

Comment 2

[Jason Barnabe (np)]

Attachment: stop loading data for prev/next thread buttons

Bug 410721 removed the previous/next thread feature from the UI. This patch comments out the code that was loading the data. This saves us one get_forum_topics call when loading a thread, which would've have performed 2 queries to the comments table and gotten 3 rows.

Comment 3

[Jason Barnabe (np)]

Attachment: use forum_info to optimize add_comments_extras

Make add_comments_extras take an optional forum_info parameter. Multiply each of these wins by the number of posts loaded:

-If the forum doesn't show post count or user level, don't load them (no win for us, because we use post count)
-If the forum doesn't show e-mails, don't load them (this should win us one select from the users table, but this wasn't getting called anyway on my machine)
-Remove the is_reported load because it's not used anywhere (win one select from tiki_forums_reported)
-If the forum doesn't show online status, don't load it (win one select from tiki_sessions, but this looks like it's cached anyway)

Comment 4

[Jason Barnabe (np)]

Attachment: if threshold isn't set, don't use it

Tiki has a feature like Slashdot where you can set it so you only see comments above a certain level. We don't use this feature, and we don't even let people rate posts.

This patch makes it so that if the threshold is 0, the "# comments below your threshold" is set to 0 instead of calculated (saves one select from tiki_comments). It also make it so if the threshold is 0, threshold isn't used when grabbing the list of comments to display.

Comment 5

[Jason Barnabe (np)]

Attachment: object_has_permission optimization for one group case

According to bug 425135, object_has_permission has the slowest, callingiest query. This will optimize it so that it performs an easier query when there's only one group (use = instead of IN). 

On my piddly permissions table of 67 rows, this sped the query up by 10%.

Comment 6

[Jason Barnabe (np)]

Attachment: use calculated column to put stickies on top

When loading threads, the current code does two sets of queries: one to load the sticky threads, one to load the normal ones.

This patch instead makes it do one set of queries, using a calculated column (type = "sticky") to sort the results.

This patch doubles the speed of this query on my machine.

Comment 7

[Nelson Ko [:nkoth]]

Comment on attachment 311930 [details] [diff] [review]
object_has_permission optimization for one group case

for the performance5.patch, should use array($groups[0].....) instead of array_merge

Comment 8

[Nelson Ko [:nkoth]]

all above committed to SVN

Comment 9

[Jason Barnabe (np)]

Attachment: cache userlink results in memory

Calls to modifier.userlink.php are cached to disk. In the most result profile results for a list of 200 threads, it's called 400 times (once for thread starter, and one for last poster). Calls to is_file and filemtime (to check the cache file's creation date) take up 10% of the processing.

This patch adds a memory cache for modifier.userlink.php. The results will be stored in a global variable the first time they are read so we don't have to check the disk subsequent times on the same request.

Comment 10

[Jason Barnabe (np)]

Attachment: cache locale date strings

While we're going to stop loading translated strings for English, we're still doing it for other locales obviously. Included in translations is strings for formatting dates, like month names. In a thread list of 200, there are 3600 calls to tra. This patch will cache the translations in memory for the duration of the request. Worst case scenario, this is an extra 39 (12 months * 2 + 7 days * 2 + "of") strings in memory.

Comment 11

[Jason Barnabe (np)]

Attachment: reduce queries in get_forum_topics

Comments->get_forum_topics takes 46% of the time in tiki-view_forum.php. It first loads the list of threads using one query. Then, for all threads with a reply, it loads info about the last post in the thread (to display the last poster). It uses one query per thread for this.

This patch combines all the last-post-loading queries into one larger query. I expect this will help things, but don't have enough data in my DB to be able measure it.

Comment 12

[Laura Thomson :laura]

tracking bug, moving out to 0.7

Comment 13

[Laura Thomson :laura]

This is now a dupe of https://bugzilla.mozilla.org/show_bug.cgi?id=425135, it's the same query.

Comment 14

[Stephen Donner [:stephend] Not actively reading bugmail]

Verified dup