Performance enhancements: Difference between revisions

This page is entirely too long and should be broken up in to a category or subpages or a mixture of both

The following are twenty-four improvements that you should be able to make to SMF regardless of your host, though only the first set will be possible anywhere. If the second set is not possible (outside of say, using Postgres over MySQL, in which case you probably don't need the second set), then I seriously suggest finding a new host. Hosts should provide you with shell access, access to crontab, php5 and InnoDB. An inability to do these things securely does not speak well of confidence in them.

Not all of these ideas are mine, but I did tweak some of them. I give credit to other places in the form of a link - I wanted everything in one place for people to easily reference, as opposed to being forced to check several lists - some of which are quite old. I believe this covers the overwhelming majority of what you would want to do without significantly altering the schema (TinyText needs to die a slow, horrible death), SMF's code (as does the @ operator), or your server configuration. A lot can be done in these areas, but the first two are probably best dealt with by pressuring the SMF team directly, while the last deserves a far more comprehensive discussion.

If anyone has additional comments or optimizations, feel free to mention them. If appropriate I will edit them in here.

Basic Improvements

Just about anyone can make use of these optimizations, regardless of how much control you have over your server. The first few options here can have an extremely dramatic effect on your server's performance, I've sorted them roughly in order of potential impact.

Move your uploaded avatars directory

In the administration panel, go to Attachments and Avatars, and then go to Avatar Settings.

For Upload avatars to... select: Specific directory... For Upload directory: put in the raw, publicly accessible server directory you want them added to ie /var/www/yoursite/docs/forums/avs

So assuming /docs was your webroot, you would put /forums/avs for your upload URL. Don't do what I did and make it a subdirectory of your main avatars directory, SMF still handles it like an attachment directory for some reason so if someone uploads a .png avatar, someone else decides to use it and the first person replaces it with a .jpg avatar, the first will be deleted.

The benefit of this borders on the ridiculous. Every single avatar load is another php call, which loads a session, the user's settings and permissions, the board and its theme. In addition, two additional calls to the database are made to load the avatar itself, and to increment the view counter. So not only are you reducing the processor load on your server, but you are also reducing your database load. If five different avatars are displayed on a page, that's five additional php calls and dozens of database queries getting made.

After you do this access index.php?action=manageattachments;sa=moveAvatars to use the hidden feature to move your avatars over.

SMF 2.0 RC3 Update (June 16th)

the url is apparently now index.php?action=admin;area=manageattachments;sa=moveAvatars - thank you Spuds : )

Disable hostname lookups

This is located in Features and Options -> Layout and Options or Server Settings -> General in RC1 and on. If you leave hostname lookups on, every single page access carries with it a reverse DNS query. If your host's RDNS is having issues or goes down, you are going to hear about the lag from your members.

Enable (or disable) gzip compression

This is located in Server Settings -> Feature Configuration. This has the greatest benefit when combined with #1, particularly for dialup users.

Note: Your webserver configuration may render this irrelevant. If your webserver compresses the type of data you use to serve your webpages, you should specifically disable this, as the webserver can better handle it.

Tune topics, members, and posts per page

In Posts and Topics -> Topic Settings. Setting this low can have a detrimental affect on your forum's raw activity, however, at least it did on mine. If people click more, they make more requests, after all, and get more frustrated. I use fifty topics per page and twenty-five posts per page, which seems to be close to ideal. Disabling participation icons will also reduce load a slight bit, but some may find this annoying.

If you are going to enable search, and are not or cannot use Sphinx, use a Large Custom Index

SMF's custom index works by taking a hash of every available word, and then building an index of which words point to which messages. 'Small' means this is a 16 bit hash, 'Moderate' is 24 and 'Large' is a 32 bit hash. 'Large' is thus only two bytes larger per record than small is, plus an additional number of records based on the average number of unique words per post you have - which is not going to be a significant increase. The speed improvement you get for the additional ~40% space usage is well worth it.

Don't use post moderation on an active forum

Where active is over a thousand posts a day or so. Apparently the query is rather slow, but it is not difficult to have a self policing forum.

Disable the recent posts feature on the board index

Let people click a link if they want to see more than the last post they have access to, compiling that list is expensive especially if you have a lot of groups set up. Also I find it rather ugly, myself. This can be found under your Theme's settings, Number of recent posts to display on board index: - set to zero.

Run the mark read for inactive users script periodically

The log_topics, log_boards, and log_mark_read tables are not the best designs, and for some unlucky folks end up dominating their database. Pruning them can help.

Do you really need that many boards?

I tend to see this rather often and the answer is almost always no. Too many boards actually reduces your forum's activity, for one, but in the case of optimization here, each and every board takes up resources on the relatively common index load, as well as space for the log_boards and log_mark_read tables, with an amount that grows for each new member you gain.

Do you really need the calendar?

When enabled, SMF looks for an event on each and every thread load - there are ways to make it more efficient but in general it's not an often-used feature.

====Ben has a post about disabling features according to server load here in the GUI for SMF 2.0 RC2==== Don't enable this on a shared server or VPS unless you know what you are doing or at least have some guidance, but this can be pretty dramatic. My members use 'show unread posts' or 'show unread replies' roughly thirty thousand times per day. During high loads, even if they're caused by someone else, those sorts of requests are not helping.

Consider using database-driven sessions and offloading all attachments to Mediawiki

There is an awesome MediaWiki extension <a href="http://www.mediawiki.org/wiki/Extension:SMF_Auth_Integration" class="bbc_link new_win" target="_blank">here</a> though some minor tweaks are needed for 1.13 - see the talk page. Combine with hotlinking protection and you can have a slightly more efficient file handling system. It is likely more difficult for your members to use, however.

Prune zero-post members

If they really want in, they can post next time. They just take up more and more space in your database, which will be especially notable if you follow tip #8. A simple way to do this is to make one postcount group have zero posts, another at one post, go to members -> search for members, sort by user ID (Ascending), check and start purging until you get to the last month or whatever is comfortable. You can go to Features & Options -> Layout and Options, set members per page to 100 to make it faster (just don't go overboard and start deleting recent or genuinely active members).

Perform only necessary tasks, and spread them out (SMF 2.0 only)

I just had this murder my forum, twice. While these do not necessarily slow down php, they can have a pretty dramatic effect on your forum if there are DNS issues, or simply the volume becomes so great that your server stops to send out thousands of emails.

The tasks can be found directly under Scheduled Tasks, in 'maintenance'. Be sure that approval notifications are disabled unless you really want them, and that optimize database is disabled if you are doing it with your own script (see mine below). The default execution time is 6 PM which, for a large forum, is really not the best. If you have a smaller (but still reasonably active) forum, you may want to spread them out by an hour or so - remember it's not enough for php to fire off emails to your mailserver, your mailserver also handles the emails, so don't have them running in immediate succession.

Cap the length of your threads

This is one of those things that ought to be a setting - even vBulletin is known for choking on large threads, look up rpg.net's motivational poster history. Large threads mean there is a large result set for further instructions in the query to prune from, and if a thread is too disproportionally large, even browsing the thread will result in using table scans instead of the index - a very bad situation indeed.

30k is what I use for Elliquiy, and Ben uses 25k for Red and White Kop. Keep in mind, though, that we are both running finely tuned dedicated servers with two hard disks, two cores, and 4gb of RAM. You may want to cap thread length closer to 1k posts.

This also has the effect of making individual threads less overwhelming.

Don't go crazy on on-line time

In Features and Options -> General, the online time threshold defaults to 15 minutes, as in vBulletin. Some people like to set this rather high to give the illusion of an extremely active forum, but people are already wising to this. In addition, you get diminishing returns, and the larger it is, the more time your forum will spend processing this extremely active table. It is also quite annoying - I had to modify the Users Online Today mod, simply because looking through hundreds of names is annoying.

If you really want, disable tracking daily page views

in the same section as #4 there. My tepid enthusiasm is shared by others with highly tuned, CPU intensive forums - you do not need to do this to have a solid-performing forum.

It is an additional query on each page load, however, and if it becomes an issue and like most of us, you want to keep it, be sure to convert the underlying table (log_actions) to InnoDB as described below.

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 don't want your forum to be unresponsive for too long when you do some of this:

DELETE FROM smf_log_topics;
DELETE FROM smf_log_boards;
DELETE FROM 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 don't 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.

ALTER TABLE smf_pm_recipients ENGINE=InnoDB; SMF 2.0 has a few additional tables that you may wish to convert to InnoDB:

ALTER TABLE smf_pm_rules ENGINE=InnoDB; ALTER TABLE smf_log_spider_stats ENGINE=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: ALTER TABLE `smf_boards` ADD INDEX (`ID_CAT`), ADD UNIQUE KEY (`ID_BOARD`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `boardOrder`, `ID_BOARD` ), ENGINE=InnoDB; SMF 2.0 RC 1: ALTER TABLE `smf_boards` ADD UNIQUE KEY (`id_board`), DROP PRIMARY KEY, ADD PRIMARY KEY ( `board_order`, `id_board` ), ENGINE=InnoDB; 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.

$maintenance = 2; $mtitle = 'Backup in progress'; $mmessage = 'We are in the midst of the nightly optimization and backup script. This currently takes a few minutes. Thank you for your patience. : )';

$mbname = 'Your Forum Name'; $language = 'english'; $boardurl = 'http://yourdomain.com/forums'; $webmaster_email = '[email protected]'; $cookiename = 'CookieName';

$boarddir = '/var/www/forums/docs/forums'; $sourcedir = '/var/www/forums/docs/forums/Sources'; 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. : )

#!/bin/bash cp /var/www/webdir/docs/forums/Settings.php /home/userdir/scripts/Settings.orig.php cp /home/userdir/scripts/Settings.lock.php /var/www/webdir/docs/forums/Settings.php /usr/bin/nice /usr/bin/mysqldump -q -u dbusername -p"dbpassword" --ignore-table=smf_forums.smf_log_search_words smf_forums > /home/userdir/backups/smf_forums.`date +%a`.sql /usr/bin/nice /usr/bin/mysqlcheck -ao -–auto-repair -u dbusername -p"dbpassword" --databases smf_forums /bin/chmod o-r /home/userdir/backups/smf_forums.`date +%a`.sql cp /home/userdir/scripts/Settings.orig.php /var/www/webdir/docs/forums/Settings.php /usr/bin/nice /bin/bzip2 -f9 /home/userdir/backups/smf_forums.`date +%a`.sql 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, don't 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 don't 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 <a href="http://www.simplemachines.org/community/index.php?topic=336278.msg2256722#msg2256722" class="bbc_link new_win" target="_blank">Keeper for converting this mess to SMF 1.1</a>:

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 VARCHAR(255):

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 don't 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, don't 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.

Turn it on

ExpiresActive On

Set a default expiry time. One hour is fine, but a day or longer may be appropriate.

ExpiresDefault A3600

Turn expiry off for dynamic content (or potentially dynamic content).

ExpiresByType application/x-httpd-php A0 ExpiresByType application/x-python-code A0 ExpiresByType text/html A0

<FilesMatch "\.(php|py|pyc|pyo)$"> ExpiresActive Off </FilesMatch>

Addition #3 (July 1st): If you have an opcode cache enabled, <a href="http://www.simplemachines.org/community/index.php?topic=50227.0" class="bbc_link new_win" target="_blank">disable template eval</a> 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 More tips: http://www.simplemachines.org/community/index.php?topic=279639.0

Revision as of 01:07, 4 November 2010 (view source) Groundup (talk \| contribs) No edit summary ← Older edit		Revision as of 01:08, 4 November 2010 (view source) Groundup (talk \| contribs) mNo edit summary Newer edit →
Line 1:		Line 1:
	{{notice\|'''This page is entirely too long and should be broken up in to a category or subpages or a mixture of both'''}}}		{{notice\|'''This page is entirely too long and should be broken up in to a category or subpages or a mixture of both'''}}

	The following are twenty-four improvements that you should be able to make to SMF regardless of your host, though only the first set will be possible anywhere. If the second set is not possible (outside of say, using Postgres over MySQL, in which case you probably don't need the second set), then I seriously suggest finding a new host. Hosts should provide you with shell access, access to crontab, php5 and InnoDB. An inability to do these things securely does not speak well of confidence in them.		The following are twenty-four improvements that you should be able to make to SMF regardless of your host, though only the first set will be possible anywhere. If the second set is not possible (outside of say, using Postgres over MySQL, in which case you probably don't need the second set), then I seriously suggest finding a new host. Hosts should provide you with shell access, access to crontab, php5 and InnoDB. An inability to do these things securely does not speak well of confidence in them.

Navigation

Search

Tools