Request for help with Server Config

[mytreo]

Hi folks, I need a little help with our MySQL config. Our forum is fine most of the time but under heavy load it can go REALLY slow and seems to bring the whole site to it's knees

I've followed the checklist for performance problems and the only thing I haven't done is converted some tables to INNODB but this is because I have had major problems using INNODB in the past because I wasn't sure what parameters to use in the my.cnf. I'm also still using php and mysql v4 - but we have other software that is dependant on this and can't use v5, so we're stuck with 4 for the timebeing.

So, ignoring the INNODB conversion could you please provide some pointers for speeding up the forum, or making it more efficient.

Forum: http://mytreo.net/forum/
Status (some things look bad here?): http://mytreo.net/forum/status.php
phpinfo: http://mytreo.net/forum/status.php?php

The forum is running on a dedicated server: 2 x 2.6Ghz P4, 2 GB RAM, only one site on the server although it does also have Sendmail, Spamassasin, and Clam-AV running. MySQL 4.1.x and PHP 4.3.11

Posts: 223,799

Not sure the average number of users at peak time - maybe 300-400 over 15 minutes?

Mods installed: online users today, Buddy List, G&S_Sticky, Integrated Chat, Topic Solved

Any help you could provide, especially regarding the problems flagged up in status.php, would be much appreciated. Hope I have provided enough info.

Many thanks
Chris

[TheMaTrIx]

Ignoring InnoDB is whats wrong.

If you want I'll help you with the config  for a 2GB Ram server with InnoDB.

With a highly active forum, the advantages of row vs table locking (eg. InnoDB vs MyISAM) become overly aparant.

[mytreo]

Okay thanks, well where do I start with converting to INNODB?

[mytreo]

I should say, I see this post http://www.simplemachines.org/community/index.php?topic=50217.0 and I can follow that, but what settings would you suggest for my.cnf? Currently everything INNODB related is commented out in my.cnf:

# Uncomment the following if you are using InnoDB tables
#innodb_data_home_dir = /var/lib/mysql/
#innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend
#innodb_log_group_home_dir = /var/lib/mysql/
#innodb_log_arch_dir = /var/lib/mysql/
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
#innodb_buffer_pool_size = 384M
#innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
#innodb_log_file_size = 100M
#innodb_log_buffer_size = 8M
#innodb_flush_log_at_trx_commit = 1
#innodb_lock_wait_timeout = 50

Also, do I need to put the forum in maintenance mode before converting the tables to INNODB?

[TheMaTrIx]

Its always best to go in maintenance mode while doing any kind of direct kind of database actions.

For the settings, the defaults look good.
Considering your on a 2GB Ram server, you should be able to safely double up on the buffer_pool_size and log_file_size parameters.

The directories I can't really comment on, since they differ for every linux distro and the admins preferences.

Also, if no other sites are present on that server and no other databases, make sure to tone down the MyISAM memory usage settings to minimal value's, so that memory is liberated for use by other apps.

[MH-MINI]

Also, It is a good idea to backup before doing any changes.

Thanks,
Mini

[mytreo]

Thanks for your help guys. I still haven't got round to changing the tables (waiting to upgrade to 1.1 first) but I found that spamassassin was hogging our system resources and fixing that seems to have solved most of our problems.

[Overseer]

wow big forum!

youi're running an old old version of SMF.. 1.0.3 ?!

this has a security hole.. can u not upgrade because of mod's? (which is why i never install them...)

[Oldiesmann]

I definitely recommend upgrading to 1.0.6 if you're going to wait for 1.1 Final.

Also, while you're waiting on 1.1, I would recommend installing eAccelerator on your server if it isn't already installed. This will allow you to take advantage of the built-in caching features in 1.1, which should improve performance.

[MH-MINI]

eAccelerator has caused me sooo many problems, especially on PHP4.4.2.

So I do *not* reccommend you install it unless you like apache failing and refusing to start.

Thanks,
Mini