Performance enhancements part 2

Intermediate Improvements
These steps are going to require some support from your host, or for you to actually be the host, as in running off of a VPS or dedicated hosting solution. The total improvement in speed can be extremely dramatic, however - you can easily cut your forum's response time in half. These will be possible with any and every vps/dedicated host, of course, and pretty much any shared host you ought to be doing business with.

Converting Tables to InnoDB and MEMORY
Download and run status.php from the checklist post here. Click on "Show more information" and look for the have_innodb: setting. Does it say YES? Good. If it says no, and you are on a shared host, and after all of the above you are still having issues, it's time to get a VPS or at least a better host. Make sure innodb_buffer_pool_size is sufficient 8M is only going to work for a tiny forum. 64M will do you fine up to low-end Big Boards levels, but if you have a lot of members and topics you may find that you need more, though you are clearly pushing "Needs a VPS" territory at that point.

First, clear out your error logs from smf's error page, and make a backup of your database.

You may also want to wipe out the read message indicators if you do not want your forum to be unresponsive for too long when you do some of this:

TRUNCATE smf_log_topics; TRUNCATE smf_log_boards; TRUNCATE smf_log_mark_read;

'''This removes all message read indicators. It will annoy your users.''' You'll want to weigh it against the amount of time converting these effectively freezes your forum. If you do not do this these three are going to take their sweet sweet time.

ALTER TABLE smf_attachments ENGINE=InnoDB; ALTER TABLE smf_collapsed_categories ENGINE=InnoDB; ALTER TABLE smf_log_actions ENGINE=InnoDB; ALTER TABLE smf_log_boards ENGINE=InnoDB; ALTER TABLE smf_log_errors ENGINE=InnoDB; ALTER TABLE smf_log_karma ENGINE=InnoDB; ALTER TABLE smf_log_mark_read ENGINE=InnoDB; ALTER TABLE smf_log_online ENGINE=InnoDB; ALTER TABLE smf_log_topics ENGINE=InnoDB; ALTER TABLE smf_members ENGINE=InnoDB; ALTER TABLE smf_sessions ENGINE=InnoDB; ALTER TABLE smf_settings ENGINE=InnoDB; ALTER TABLE smf_topics ENGINE=InnoDB; ALTER TABLE smf_log_activity ENGINE=InnoDB;

'''The following command will only work for SMF 1.1. SMF 2.0 commands are next.'''

You will also want flood control to be memory/heap:

DROP TABLE smf_log_floodcontrol; CREATE TABLE smf_log_floodcontrol ( ip char(16) NOT NULL,  logTime int(10) unsigned NOT NULL default '0',  PRIMARY KEY (ip),  KEY logTime (logTime) ) ENGINE=MEMORY;

For SMF 2.0 the create statements for this is a bit different:

CREATE TABLE `smf_log_floodcontrol` ( `ip` char(16) NOT NULL default '',  `log_time` int(10) unsigned NOT NULL default '0',  `log_type` varchar(8) NOT NULL default 'post',  PRIMARY KEY  (`ip`,`log_type`) ) ENGINE=MEMORY;

Finally, on a forum with a lot of personal messages this is going to take awhile. So warn people, etc. etc.

SMF 2.0 has a few additional tables that you may wish to convert to InnoDB:

Unless you have installed mods, this should be a fairly complete list as of SMF 1.1 and 2.0 (I think >_>). Your mileage will vary regarding the amount of benefit this will provide - I switched my own forum after ~1M posts and response roughly doubled.

Some suggest converting -all- tables except floodcontrol to InnoDB. For most tables this is perfectly reasonable, but for smf_personal_messages and smf_messages, as well as similar "Post and forget" tables like a Shoutbox, this can actually be counterproductive to performance, particularly with a good-sized key_buffer and concurrent_insert = 2 in your my.cnf config. A warning, however, is that smf_boards currently depends on MyISAM's ordering in order to display boards properly - if you convert it to InnoDB you will want to make adjustments to the key and/or SMF accordingly:

SMF 1.1: SMF 2.0 RC 1:

2: Run PHP 5. If your host does not at least give you the option, get a new host.

3: If possible, keep SMF in its own database. If you do something like I do and have SMF, Drupal, MediaWiki, and other sites all on the same physical server, it's a good idea to give each such application and even every -instance- of each such application its own database. The primary purpose of this is not quite speed, but rather organization and faster backups.

4: Backup scripts make the world go round. You will need shell access for this, and I am making the possibly erroneous assumption that your server uses bash. There are several steps involved here:

A: Disable SMF's table optimization. This is in Server Settings -> Feature Configuration, set Optimize tables every how many days to 0 to disable it.

B: Make two subdirectories from your home directory. Call one 'backups' and the other 'scripts', without the quotes. chmod o-rx the both of them.

C: We're going to want to let your members know that your forum is getting backed up, because once you get into the million post range this is going to bring it down for awhile. Make a file in the scripts directory called Settings.lock.php - obviously, you're going to want to customize this appropriately.

SMF 2.0 Note They decided to move theme loading, etc. before Settings.php. So rather than this, you just have to swap out index.php : / Annoying.

D: Let's make a script in your scripts directory. Call it backup.smf and be sure to chmod 700 it. You are certainly going to have to edit the directories, database names, and password accordingly. Use whereis mysqlcheck/mysqldump/nice/bzip2 to make sure the directories calling them are accurate. You may also wish to remove --auto-repair from the mysqlcheck line, though fortunately, as you will note, you have already made a backup immediately beforehand in case something goes wrong. : )

Line by line, this script: 1: Calls the interpreter 2: Makes a backup of our Settings.php file 3: Copies the locked Settings.php file over our forum Settings.php, shutting down the forum 4: Calls nice (this is optional, I use it because it's not the only aspect of the site) to make a backup via mysqldump. If you are not using a custom search index (Sphinx or search is just disabled) then you can omit the ignored table. The `date %a` in the filename returns the three-letter weekday. Omit if you just want one backup. `date +%b-%d` would let you keep a year's worth of backups (god why...) etc. The weekly log rotation seems to be a good compromise between space and having more than one backup to try and recover from IMO. 5: Optimizes our database and checks it for errors, again using nice. 6: I'm paranoid, even though I run my server on my own, do not want any random chance of someone breaking into an account and reading it. 7: Since we've got our database backed up, we can freely restore our original Settings.php before we compress the file. 8: nice is less optional here. Now that we're up and running again, compress the ginormous backup we just made. Do not pipe mysqldump to bzip2. You will end up taking your forum down for far longer than it needs to be unless you have a tiny forum.

E: run crontab -e and add this line: 35 4  *   *   *     /home/userdir/scripts/backup.smf

Change the directory and time as needed. This runs the backup at 4:35 AM my time, each morning, which is when my forums have the least amount of activity.

F: I use a WinSCP script to synchronize these with my machine at 5 AM each morning. You can set something up similar for your own machine or operating system.

Additional Item #1 (March 7th): '''Get rid of Text tables. - note, this is included in SMF 2.0 RC2'''

Frequently, when doing sorts, joins and other operations, MySQL needs to create a temporary table. MySQL usually tries to create these as MEMORY tables, however, if the resulting table would have any TEXT or BLOB columns, it is incapable of doing this, instead creating it as a MyISAM table on-disk. This can murder performance if you do not have /tmp mounted in memory or if you have a tiny key buffer.

Converting TINYTEXT columns to VARCHAR(255) is fairly safe.

The following is for SMF 2.0 (pre-RC2) ALTER TABLE `smf_admin_info_files` CHANGE `filename` `filename` VARCHAR(255) NOT NULL; ALTER TABLE `smf_admin_info_files` CHANGE `path` `path` VARCHAR(255) NOT NULL; ALTER TABLE `smf_admin_info_files` CHANGE `parameters` `parameters` VARCHAR(255) NOT NULL; ALTER TABLE `smf_admin_info_files` CHANGE `filetype` `filetype` VARCHAR(255) NOT NULL; ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL; ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL; ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL; ALTER TABLE `smf_ban_items` CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL; ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_custom_fields` CHANGE `field_desc` `field_desc` VARCHAR(255) NOT NULL; ALTER TABLE `smf_custom_fields` CHANGE `field_options` `field_options` VARCHAR(255) NOT NULL; ALTER TABLE `smf_custom_fields` CHANGE `mask` `mask` VARCHAR(255) NOT NULL; ALTER TABLE `smf_custom_fields` CHANGE `default_value` `default_value` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_comments` CHANGE `recipient_name` `recipient_name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_errors` CHANGE `file` `file` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_member_notices` CHANGE `subject` `subject` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_packages` CHANGE `filename` `filename` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_packages` CHANGE `package_id` `package_id` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_packages` CHANGE `name` `name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_packages` CHANGE `version` `version` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_packages` CHANGE `member_installed` `member_installed` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_packages` CHANGE `member_removed` `member_removed` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_packages` CHANGE `themes_installed` `themes_installed` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_reported` CHANGE `membername` `membername` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_reported` CHANGE `subject` `subject` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_reported_comments` CHANGE `membername` `membername` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_reported_comments` CHANGE `comment` `comment` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_spider_hits` CHANGE `url` `url` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_subscribed` CHANGE `vendor_ref` `vendor_ref` VARCHAR(255) NOT NULL; ALTER TABLE `smf_mail_queue` CHANGE `recipient` `recipient` VARCHAR(255) NOT NULL; ALTER TABLE `smf_mail_queue` CHANGE `subject` `subject` VARCHAR(255) NOT NULL; ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL; ALTER TABLE `smf_openid_assoc` CHANGE `handle` `handle` VARCHAR(255) NOT NULL; ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_package_servers` CHANGE `url` `url` VARCHAR(255) NOT NULL; ALTER TABLE `smf_permission_profiles` CHANGE `profile_name` `profile_name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL; ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL; ALTER TABLE `smf_polls` CHANGE `poster_name` `poster_name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL; ALTER TABLE `smf_spiders` CHANGE `spider_name` `spider_name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_spiders` CHANGE `user_agent` `user_agent` VARCHAR(255) NOT NULL; ALTER TABLE `smf_spiders` CHANGE `ip_info` `ip_info` VARCHAR(255) NOT NULL; ALTER TABLE `smf_subscriptions` CHANGE `description` `description` VARCHAR(255) NOT NULL; ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL; ALTER TABLE `smf_personal_messages` CHANGE `from_name` `from_name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_personal_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `real_name` `real_name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `personal_text` `personal_text` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `website_title` `website_title` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `website_url` `website_url` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `location` `location` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `icq` `icq` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `msn` `msn` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `member_ip` `member_ip` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `secret_question` `secret_question` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `additional_groups` `additional_groups` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `member_ip2` `member_ip2` VARCHAR(255) NOT NULL;

There are also a few of these in the messages table. While the above may not be very disruptive, the below will prevent posting on a large forum. Each operation took nearly a minute for my forums:

ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL; ALTER TABLE `smf_messages` CHANGE `poster_name` `poster_name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_messages` CHANGE `poster_email` `poster_email` VARCHAR(255) NOT NULL; ALTER TABLE `smf_messages` CHANGE `poster_ip` `poster_ip` VARCHAR(255) NOT NULL; ALTER TABLE `smf_messages` CHANGE `modified_name` `modified_name` VARCHAR(255) NOT NULL;

Thanks to Keeper for converting this mess to SMF 1.1:

ALTER TABLE `smf_attachments` CHANGE `filename` `filename` VARCHAR(255) NOT NULL; ALTER TABLE `smf_ban_groups` CHANGE `reason` `reason` VARCHAR(255) NOT NULL; ALTER TABLE `smf_ban_items` CHANGE `hostname` `hostname` VARCHAR(255) NOT NULL, CHANGE `email_address` `email_address` VARCHAR(255) NOT NULL; ALTER TABLE `smf_boards` CHANGE `name` `name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_categories` CHANGE `name` `name` VARCHAR(255) NOT NULL; ALTER TABLE `smf_log_banned` CHANGE `email` `email` VARCHAR(255) NOT NULL; ALTER TABLE `smf_membergroups` CHANGE `stars` `stars` VARCHAR(255) NOT NULL; ALTER TABLE `smf_package_servers` CHANGE `name` `name` VARCHAR(255) NOT NULL, CHANGE `url` `url` VARCHAR(255) NOT NULL; ALTER TABLE `smf_polls` CHANGE `question` `question` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL; ALTER TABLE `smf_poll_choices` CHANGE `label` `label` VARCHAR(255) NOT NULL; ALTER TABLE `smf_settings` CHANGE `variable` `variable` VARCHAR(255) NOT NULL; ALTER TABLE `smf_themes` CHANGE `variable` `variable` VARCHAR(255) NOT NULL; ALTER TABLE `smf_members` CHANGE `lngfile` `lngfile` VARCHAR(255) NOT NULL, CHANGE `realName` `realName` VARCHAR(255) NOT NULL, CHANGE `emailAddress` `emailAddress` VARCHAR(255) NOT NULL, CHANGE `personalText` `personalText` VARCHAR(255) NOT NULL, CHANGE `websiteTitle` `websiteTitle` VARCHAR(255) NOT NULL, CHANGE `websiteUrl` `websiteUrl` VARCHAR(255) NOT NULL, CHANGE `location` `location` VARCHAR(255) NOT NULL, CHANGE `icq` `icq` VARCHAR(255) NOT NULL, CHANGE `msn` `msn` VARCHAR(255) NOT NULL, CHANGE `avatar` `avatar` VARCHAR(255) NOT NULL, CHANGE `usertitle` `usertitle` VARCHAR(255) NOT NULL, CHANGE `memberIP` `memberIP` VARCHAR(255) NOT NULL, CHANGE `secretQuestion` `secretQuestion` VARCHAR(255) NOT NULL, CHANGE `additionalGroups` `additionalGroups` VARCHAR(255) NOT NULL, CHANGE `memberIP2` `memberIP2` VARCHAR(255) NOT NULL; ALTER TABLE `smf_messages` CHANGE `subject` `subject` VARCHAR(255) NOT NULL, CHANGE `posterName` `posterName` VARCHAR(255) NOT NULL, CHANGE `posterEmail` `posterEmail` VARCHAR(255) NOT NULL, CHANGE `posterIP` `posterIP` VARCHAR(255) NOT NULL, CHANGE `modifiedName` `modifiedName` VARCHAR(255) NOT NULL, CHANGE `fromName` `fromName` VARCHAR(255) NOT NULL, CHANGE `subject` `subject` VARCHAR(255) NOT NULL;

You may want to file down your board descriptions so they can fit inside a : SELECT LENGTH(description), id_board FROM smf_boards WHERE LENGTH(description) > 250;

Trim the offenders and then apply. ALTER TABLE `smf_boards` CHANGE `description` `description` VARCHAR(255) NOT NULL;

If you do not care to do that, see the following.

Some TEXT columns can also be set to VARCHAR safely. This is, however, slightly riskier and there are some logistics involved, so if you're squeamish feel free to skip this step.

ALTER TABLE `smf_ban_groups` CHANGE `notes` `notes` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_log_actions` CHANGE `extra` `extra` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_log_comments` CHANGE `body` `body` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_log_errors` CHANGE `url` `url` VARCHAR(8000) NOT NULL; ALTER TABLE `smf_log_errors` CHANGE `message` `message` VARCHAR(8000) NOT NULL; ALTER TABLE `smf_log_group_requests` CHANGE `reason` `reason` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_log_member_notices` CHANGE `body` `body` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_log_online` CHANGE `url` `url` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_log_reported` CHANGE `body` `body` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_log_subscribed` CHANGE `pending_details` `pending_details` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_membergroups` CHANGE `description` `description` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_sessions` CHANGE `data` `data` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_settings` CHANGE `value` `value` VARCHAR(16000) NOT NULL; ALTER TABLE `smf_themes` CHANGE `value` `value` VARCHAR(16000) NOT NULL;

May 26th, 2010 Note - I recently made an attempt at converting my main forum's personal messages, shoutbox messages, and messages tables to InnoDB. This was a mistake. I've kept the messages table converted - it reduces locking incidents - but *nix in general does a pretty good job of caching disk reads for MyISAM tables. If all that happens is insert and delete, do not fret over it.

Addition #2 (May 3rd): Control your Expires Header - This is often done in server configurations but can also be done through .htaccess. Note that your provider needs to have mod expires on in order for this to function. This can drastically cut your forum's bandwidth needs.

ExpiresActive On
 * 1) Turn it on

ExpiresDefault A3600
 * 1) Set a default expiry time. One hour is fine, but a day or longer may be appropriate.

ExpiresByType application/x-httpd-php A0 ExpiresByType application/x-python-code A0 ExpiresByType text/html A0
 * 1) Turn expiry off for dynamic content (or potentially dynamic content).

 ExpiresActive Off 

'''Addition #3 (July 1st): If you have an opcode cache enabled, disable template eval If you install phpBB and SMF side by side, and install an opcode cache, you will notice that SMF goes from winning the horserace by about 20% to losing by about 25%. The culprit here is eval - in order to assist coders in modifying their templates, SMF first evals the template to make sure everything is okay.

If you're at the point where you are using a VPS or dedicated server to host your site, however, you should be keen enough to be using a test template before pushing it live. All the eval is doing once you install APC or XCache is slowing you down.

Just execute the following code:

REPLACE INTO smf_settings (variable, value) VALUES ('disableTemplateEval', '1');

And you're back up to speed : )

Original post: http://www.simplemachines.org/community/index.php?topic=293441.0