Question Kunena 1.5.11 Query optimization to improve performance of large forum

Joomla Version: 1.5.18 Stable
Kunena Version: 1.5.11
Mysql version: 5.0.77-log
PHP Version: 5.2.14

We are running site with about 10k registered users. At peak time, we see anywhere between 150-200 active users, i.e. users whos session has not expired. The fb_messages table has about 2.2 million records in it.

We are seing performance problem with few queries. The one that takes most server time comes from default_ex/showcat.php. The query is below:

As you might note it looks at almost 800k rows and returns just 20 (due to pagination). The problem with this query is that it uses file based temp table and filesort. Attached is the explain plan from JetProfiler:



We had to point mysql’s tmpdir to /dev/shm (memory-based file system), due to file system not being able to keep up with query demands at peak load.

After that change, the query performance increased, its response decreased to about 1s from about 6s. However, this query occupies about 3x more database time compared to the next busiest query.
I am looking to understand what business function does this query do and any recommendations to improve its performance. I looked at newest code in showcat.php from kunena 1.6 and did not see any changes to this particular query.

Some ideas that could or could not be implemented I this query is to add more limiting range in the where clause to not scan all entries, but look at, say highest 50% of m.thread or m.id. Not sure if it will help the business case for this query.

As really quick fix (althought small one) this should help you to get about 30% performance improvement on that query:

To make the query really fast, we are making some changes into our database model for Kunena 1.7.

For kunena 1.6 it works?

Optimization was missing before RC3 (which will be out shortly).

In Kunena.com optimization seems to result ~10% better performance inside categories and index page.

This is what needs to be changed for Kunena 1.6:

I have added combined parentid+catid index as it was suggested, the query performance degraded by 49%. I looked at the explain plan, and found that instead of new parentid_catid index, mysql chose catid only. I then dropped parentid and catid indexes, the mysql chose parentid_catid index and performance improved by 24%. Thanks for reql quick tuning recommendation.

Here is the code that was run:
explain plan for new index:
Does anyone know is there a best way to break the query into two? I really don't like that most of the time is spent dealing with file-based temp table. The query profile after index change shows that the slowest step is still remains copying to temp table, which dropped down from about 4.4s to 3.8s on average of 10 tests.

Breaking up the query is no solve. You can try it yourself. Even if you eliminate the join (incorrect results), the index lookup is still there.

You should definitely consider Kunena 1.6 for your site. The overall performance improvements have been significant and the query volume has been cut down dramatically. It will lower the load on your mysql instance significantly.

For 1.7 we are working on a new thread table design that takes care of these issues in a different way.

Having said that, the query in question on our server runs in less than 0.07 seconds. Server setup definitely plays a role if you have a large forum with millions of posts.