- Posts: 3
- Thank you received: 0
Kunena 6.3.0 released
The Kunena team has announce the arrival of Kunena 6.3.0 [K 6.3.0] in stable which is now available for download as a native Joomla extension for J! 4.4.x/5.0.x/5.1.x. This version addresses most of the issues that were discovered in K 6.2 and issues discovered during the last development stages of K 6.3
Question Kunena 1.5.11 Query optimization to improve performance of large forum
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.
Please Log in or Create an account to join the conversation.
To make the query really fast, we are making some changes into our database model for Kunena 1.7.
Please Log in or Create an account to join the conversation.
Please Log in or Create an account to join the conversation.
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:
Please Log in or Create an account to join the conversation.
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.
Please Log in or Create an account to join the conversation.
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.
We love stars on the Joomla Extension Directory .
Please Log in or Create an account to join the conversation.
By breaking up the query I was thinking for ways to mysql to not use filesort. It is my understanding that the group by clause is forcing mysql to not use in-memory temp tables, but write them to disk, then sort the entries there.
If I have 2 queries and join them in php I was hoping that neither one would be processed with filesort.
Please Log in or Create an account to join the conversation.
1) it doesn't get corrupted on power failures (lost all data in MyISAM table -- a few times)
2) you can keep large parts of the tables in memory
3) it's generally faster, except on SELECT COUNT(*)
It does have some limitations, but there's no need to use InnoDB in all your tables.
Please Log in or Create an account to join the conversation.
What I would suggest to start is to check you MySQL server setup. See here: www.day32.com/MySQL/
Make sure you have enough memory for the buffers in the RIGHT places. Also enable query caching in MySQL.
To improve that particual query the easiest thing would be to profile your data and see how much is really every going to be needed. If you have 2M records in that table, how many new get added every day/week/month.
How many pages of threads do you really need in showcat. Will people every go to page #200+ to look up threads or is it more likely that they will use search for that.
If so a simple timelimit on that page might be all it takes to make it run very fast. I would experiment with 1 year, 6 month and 3 month time limits. For now this could be hard coded in there if that works for you.
For Kunena 1.7 (already in development) the new thread structures will eliminate that type of query alltogether.
Hope this helps!
We love stars on the Joomla Extension Directory .
Please Log in or Create an account to join the conversation.
Hope this helps!
We love stars on the Joomla Extension Directory .
Please Log in or Create an account to join the conversation.