SMF optimization suggestion

[ashish101]

Hi guys

I have a fairly large SMF 2.x forum, around 20k posts in almost 8k topics, with 100k+ registered users, on an average 100users (including bots) are online at a time. We are on a dedicated server running Apache 2, PHP 5 and MySQL 5. I had already done most of the 24 things to do to optimize performance, but recently started seeing some server performance issues on the server, so had to revert the MyISAM to InnoDB engine change due to a recent outage. I don't know about the forum speed for users but server load got reduced a bit after changing MySQL engine from InnoDB to MyISAM for the tables. I also recently enabled MySQL caching by customizing /etc/my.cnf based on some suggestions on the internet.

Some things which are in bold from status.php:-
Thread cache usage:(thread_cache_size)    0.3986 (should be >= 0.7 and <= 0.9)
Temporary table disk usage:(tmp_table_size)    0.7576 (should be <= 0.5)
Query cache miss rate:(query_cache_limit)    0.5803 (should be <= 0.5)
Query cache prune rate:(query_cache_size)    0.1211 (should be <= 0.05)

So here are my questions:-
1. For a forum of this size and activity, is InnoDB a better solution? (http://www.simplemachines.org/community/index.php?topic=293441.0)
2. Does MySQL caching help with InnoDB?
3. One other issue is size of google_tagged packages' table size, it's 200k+ rows. How can I optimize it?
4. Is there anything else we can do to optimize SMF and the packages?

Thanks in advance, really appreciate your time on this.
Cheers

[Ensiferous]

So here are my questions:-[/b]
1. For a forum of this size and activity, is InnoDB a better solution? (http://www.simplemachines.org/community/index.php?topic=293441.0)
2. Does MySQL caching help with InnoDB?
3. One other issue is size of google_tagged packages' table size, it's 200k+ rows. How can I optimize it?
4. Is there anything else we can do to optimize SMF and the packages?

1. InnoDB is always better than MyISAM provided you configure the amount of RAM available to it properly.
2. MySQL caching helps with all types of queries, however, I'm not sure it's worth overly much if you use the SMF caching feature.
3. Uninstall the mod that does this or complain to the mod author.
4. Use Nginx. Search the forum for more information on using it with SMF.

[ashish101]

Thanks Ensiferous.

1. Is there a chance that InnoDB can cause server load issues?
2. Ok cool.
3. The mod is quite useful, it's just that the forum gets so much traffic from Google that it's becoming quite heavy, will check with the mod author.
4. Great you suggested Nginx, I actually did lot of research on the same. I will ask my server guys if they can provide some help on installation and setup, not much idea about it.

[Ensiferous]

1. Is there a chance that InnoDB can cause server load issues?

Yes. If you do not configure it properly it can seriously damage the performance of your forum. Older MySQL versions are configured for MyISAM usage by default and you absolutely need to study MySQL configuration and specifically learn how to configure the resources available to InnoDB.

[ashish101]

How to find out if InooDB is the culprit? Any tips for optimizing the server for InnoDB?

I have already set the InnoDB buffer size to 256M.

I am quite new to InnoDB. Please help.

Thanks

[ashish101]

We are still having issues with MySQL. Can you please suggest config values for /etc/my.cnf

[Ben_S]

As you haven't provided any details about your server, noone can provide any real suggestions.

[ashish101]

Apologies for not providing enough information. There's some serious issues with our server/config because mysql is failing all the time. I can't even open status.php to provide the exact info.
In short our server config: P4 2.8GHZ with 2GB RAM

Mainly 1 site running, stats: SMF 2.x forum, around 20k posts in almost 8k topics, with 100k+ registered users, on an average 100users (including bots) are online at a time

We are on a dedicated server running Apache 2, PHP 5.3.6 and MySQL 5. I, trying to recompile with PHP5.2.17 to see if that solves the current issue.

[Forum Labs]

I think 256M for your innodb buffer size is not enough. I normally assign half or more of my available ram to innodb.

You should also try nginx as Ensiferous already suggested as it uses less resources than apache.

[Ensiferous]

with 100k+ registered users

SMF is optimized for many posts/topics and not so much for many members. This was an issue I ran into as well and in the end I simply modified index.php to disable the memberlist page as it could cause very long running processes.

[Forum Labs]

with 100k+ registered users

SMF is optimized for many posts/topics and not so much for many members. This was an issue I ran into as well and in the end I simply modified index.php to disable the memberlist page as it could cause very long running processes.

When you say memberlist, is that the online list or the member search?

[Something like that]

I think 256M for your innodb buffer size is not enough. I normally assign half or more of my available ram to innodb.

You should also try nginx as Ensiferous already suggested as it uses less resources than apache.

Considering he's running Apache, allocating 1G of his 2G RAM wouldn't be recommended. 512M is probably better. Given his user count, 256M is the absolute minimum though.

[Ensiferous]

When you say memberlist, is that the online list or the member search?

I mean this one: http://www.simplemachines.org/community/index.php?action=mlist;sort=website_url;start=256380

[ashish101]

For now I have went back to MyISAM, looks like my.cnf was creating the big troubles. Hopefully, the server will be fine in next few hours and then I can try the suggestions above.

I will try nginx, is there a good guide you know which explains nginx installation on apache, configuration etc. And as far as I know .htaccess (pretty urls mod) will break with nginx. I have seen some threads about it, is there any other issue I should be aware of?

[ashish101]

Hi guys

Things are pretty stable now, so have started performance optimization steps again. As first step have changed default my.cnf values to below:-
set-variable = max_connections=500
safe-show-database
query_cache_size=64M
join_buffer_size=16M
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size=8
table_cache=256

Link to my status.php: http://www.sharetermpapers.com/status.php

I am totally novice regarding mysql optimization. Please help.

Thanks

[Forum Labs]

I just joined your forum. I just want to let you know that your forum loads so slow. I tried to post and it took almost 1 minute to load

You can also try this script http://mysqltuner.pl/mysqltuner.pl

[ashish101]

Thanks Forum Labs for the feedback. Yeah, the server is getting overloaded and hence slower, that's why trying to optimize it.

I am using mysqltuner script already to try to optimize mysql settings but can't do much due to RAM/CPU limitations.

[ashish101]

I have just changed the tables to InnoDB based on the recommendations here: http://www.simplemachines.org/community/index.php?topic=293441.0

And have set the innodb_buffer_pool_size=1G , hope this improves server load and performance.

[Vekseid]

Hi guys

Things are pretty stable now, so have started performance optimization steps again. As first step have changed default my.cnf values to below:-
set-variable = max_connections=500
safe-show-database
query_cache_size=64M
join_buffer_size=16M
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size=8
table_cache=256

Link to my status.php: http://www.sharetermpapers.com/status.php

I am totally novice regarding mysql optimization. Please help.

Thanks

thread_cache_size should always equal max_connections.

For your server I would set both to 64

join_buffer_size should never exceed 256k. The only per-query buffer that should exceed 256k is read_rnd_buffer_size, and then more than 2m is unnecessary.

table_cache should never be less than 512 for any SMF forum. I would recommend 2048 or 4096 (they don't take up a lot of ram).

In MySQL 5.1 and later, this variable has been replaced by two better variables - the table_definition_cache and the table_open_cache. You should set these instead
table_definition_cache=512
(a bit more than the total number of tables your database holds plus the maximum amount of temporary tables - total tables + 32 + some leeway for mods and such)
table_open_cache=2048
(MySQL needs to open copies of each table it loads for every active connection. The theoretical maximum would be definition_cache * max_connections, but the reality is far less than this, as, obviously, not every table needs to be opened on each query).

[ashish101]

Thanks Vekseid heaps for the help. It's by chance that I was just reading your blog only (for the first time in my life) about MySQL etc. What a co-incidence!

About join_buffer_size , I increased the size of this just because mysqltuner.pl script suggested it.

I have just updated my.cnf as per your recommendations:-
[mysqld]
set-variable = max_connections=64
safe-show-database
query_cache_size=64M
join_buffer_size=256k
tmp_table_size=64M
max_heap_table_size=64M
thread_cache_size=64
innodb_buffer_pool_size=1G
table_definition_cache=512
table_open_cache=2048
read_rnd_buffer_size=2M
concurrent_insert=2
low_priority_updates=1

Cheers and thanks again for your post.