Question How to improve performance?

Queries that don't use index -- not all of those are slow. Joomla and Kunena have both a lot of non-indexed queries that seek only few hundred items -- at most.

But some slow queries are really issue in Kunena 1.x as it's using a lot of joins and doesn't have own tables for topics that are containing cached information. Even then, I was running Kunena 1.6 (now 2.0) in 220k post site with 17k users having over 2000 registered users logging in every day. Kunena 1.6 scales up to 200-300k posts with 2 cores and 4M memory -- Kunena 2.0 can handle >2M posts and hundreds of thousands users (but serving concurrent users needs a few processors and a lot of memory).

Individual category permissions are real issue in Joomla 1.5 as it's not designed to handle that many user groups. But in J1.7 it shouldn't be issue either (I haven't tried it, though).

If you are finding out that MySQL is the issue, you should optimize your MySQL server for Kunena (I do have my own settings somewhere...), but it means using more memory instead of CPU time. You should also use MySQL 5.1+ over 4.2/5.0 as it's much better on choosing right indexes on joins.

Again, fantastic information, Matias. Thank you so much. I'll dig around to see if I can find where you posted the settings for the server here. It also sounds like 2.0 adds some improvements, so hopefully that will mitigate some issues when it is released as well.

Time to figure out how we can afford more memory