Twenty-four things you can do to make SMF go faster (Updated June 16th, 2010)

Started by Vekseid, February 16, 2009, 06:29:50 AM

Previous topic - Next topic

Vekseid

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.

1: 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 : )

2: 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.

Note (May 26th, 2011): If you insist on using the reverse hostname lookup, you can alleviate the lag penalty imposed by reducing the amount of time for timeout. For example, I have

options timeout:1

in my /etc/resolv.conf file. This causes the timeout to be 1 second, rather than 5 as per default. A three-second delay in loading your page is much better than 15!

If you run your own server or VPS, you can add this yourself. Your host should be able to make this adjustment, if not.
[/color]

3: 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.

4: 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.

Note (May 26th, 2011): Participation icon queries are one of my 'culprit queries' and I've been forced to disable them for now. To be specific, you can turn this off in Admin -> Forums -> Posts and Topics -> Topic Settings

5: 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.

6: 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.

7: 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.

8: 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.

9: 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.

10: 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.

11: 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.

12: Consider using database-driven sessions and offloading all attachments to Mediawiki. There is an awesome MediaWiki extension here 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.

Addition #1 (March 5th): 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).

Addition #2 (May 3rd): 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.

Addition #3 (July 1st): 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.

Addition #4 (November 1st): 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.

Addition #5 (November 1st): 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.

1: 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 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 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, 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 : )
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Something like that

#1
Excellent post!

Quote from: Vekseid on February 16, 2009, 06:29:50 AM
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.

Three things.

1. Might as well tell them the change needed to smf_boards:

ALTER TABLE `smf_boards` ADD INDEX ( `ID_CAT` ), DROP PRIMARY KEY, ADD PRIMARY KEY ( `boardOrder` , `ID_BOARD` ), ENGINE=InnoDB;


2. Important! Make sure innodb_buffer_pool_size is set to a reasonable amount in my.cnf or performance will suffer horribly. A reasonable rule of thumb would be a eighth (using MyISAM) to a quarter (InnoDB only) of the available RAM in a mixed web/PHP/MySQL server. The default 8M is absolutely too low.

innodb_buffer_pool_size = 256M


3. If you're only running SMF on the machine, consider setting innodb_flush_log_at_trx_commit=0 in my.cnf. This dramatically speeds up queries that update the database, making InnoDB as fast as MyISAM.

Vekseid

Ah right, thanks. Again though, this is mostly about tuning things from SMF's end, rather than the server, that's a much larger and more generic topic, really.
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

matasanos

as result of tip 8

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.


i have a bad-result.

see here

http://www.simplemachines.org/community/index.php?topic=212330.msg1936124#msg1936124


someone knows what has happened?

Vekseid

#4
Added Addition #1: Pruning zero-post members.

Edit: Also, you have a lot of boards - I don't speak your language so I can't make suggestions, but basically the size of those two tables is going to be governed by members * boards. Merging boards and pruning zero post members will take care of that nicely.
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

matasanos

Quote from: Vekseid on February 16, 2009, 04:39:48 PM
Added Addition #1: Pruning zero-post members.

Edit: Also, you have a lot of boards - I don't speak your language so I can't make suggestions, but basically the size of those two tables is going to be governed by members * boards. Merging boards and pruning zero post members will take care of that nicely.
thanks vekseid!!
big words!

Stüldt Håjt


vbgamer45

Community Suite for SMF - Take your forum to the next level built for SMF, Gallery,Store,Classifieds,Downloads,more!

SMFHacks.com -  Paid Modifications for SMF

Mods:
EzPortal - Portal System for SMF
SMF Gallery Pro
SMF Store SMF Classifieds Ad Seller Pro

metallica48423

Justin O'Leary
Ex-Project Manager
Ex-Lead Support Specialist

QuoteMicrosoft wants us to "Imagine life without walls"...
I say, "If there are no walls, who needs Windows?"


Useful Links:
Online Manual!
How to Help us Help you
Search
Settings Repair Tool



Something like that

#11
smf_log_online can also be converted to type MEMORY in MySQL 5.0.3 and later (needed to use a long varchar for the url field -- some sites will have long urls). This can speed things up quite a bit if you have a lot of users online.


ALTER TABLE `smf_log_online` CHANGE `url` `url` VARCHAR( 1024 ) NOT NULL, ENGINE = MEMORY;


I've been running like that for over a year without issue.

edit: fixed wrong table type in code.

Vekseid

I think you mean MEMORY there?

Online is the current URL I believe, could probably get away with 255.
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Something like that

Quote from: Vekseid on February 17, 2009, 03:18:04 AM
I think you mean MEMORY there?

Fixed. Thanks!

Quote
Online is the current URL I believe, could probably get away with 255.

No. It tracks the area the action and the user agent to guard against session id hijacking via cookie theft. For instance, here's some examples:

a:1:{s:10:"USER_AGENT";s:48:"Opera/9.62 (X11; Linux i686; U; en) Presto/2.1.1";}
a:2:{s:5:"board";i:11;s:10:"USER_AGENT";s:90:"Mozilla/5.0 (Windows; U; Windows NT 6.0; en-US; rv:1.9.0.6) Gecko/2009011913 Firefox/3.0.6";}
a:1:{s:10:"USER_AGENT";s:87:"Mozilla/5.0 (Windows; U; Windows NT 5.1; fi; rv:1.9.0.6) Gecko/2009011913 Firefox/3.0.6";}
a:3:{s:6:"action";s:7:"profile";s:1:"u";s:4:"3698";s:10:"USER_AGENT";s:127:"Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_5_6; en-us) AppleWebKit/525.27.1 (KHTML, like Gecko) Version/3.2.1 Safari/525.27.1";}


255 is way too short. 512 might be cutting it a bit close. The maximum length in my table is currently 322:

mysql> SELECT MAX( Length( url ) )  FROM `smf_log_online`;
+----------------------+
| MAX( Length( url ) ) |
+----------------------+
|                  322 |
+----------------------+
1 row in set (0.00 sec)


So you can only do the conversion to MEMORY with a varchar 512 or so, and you can only do varchars longer than 255 in MySQL 5.0.3 or later.

Vekseid

...did not know it was a serialized string -_-

Should probably instead roll that into a new item that goes over converting tinytext and some text columns to varchar in general.
Adult Role Playing Forums - - Over five million posts - - Elliquiy's LAMP configuration (maybe NSFW)

Blog about Forums and Servers - - Twenty things to make Simple Machines Forum go faster

Private/Instant Message requests for free support will be ignored.

Something like that

Quote from: Vekseid on February 17, 2009, 03:58:31 AM
Should probably instead roll that into a new item that goes over converting tinytext and some text columns to varchar in general.

I suppose it belongs in both places. It's most useful here, but you need to do the conversion first, too.

sheryltoo

I would like to try moving my attachments to a new folder but I don't really understand what you're telling me to do.
Would you be able to explain in "dummy" language?
Sheryl

Something like that

Quote from: sheryltoo on February 17, 2009, 08:17:16 AM
I would like to try moving my attachments to a new folder but I don't really understand what you're telling me to do.
Would you be able to explain in "dummy" language?
Sheryl

To quote from Ben_S:

Quote
You can do this through Admin > Attachments and Avatars > Avatar Settings > Upload avatars to > Specific directory.

Naturally create the directory and make it writable.

To move existing avatars to the new directory, access index.php?action=manageattachments;sa=moveAvatars

Is that enough?

Ben_S

Quote from: Vekseid on February 16, 2009, 06:29:50 AM
4: 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.

In my experience the less you have per page, the less the resource usage, in fact in the past when I was having a lot of traffic, I used to reduce mine from 40 to 20 in order to cope with the influx.

Yes, you will have more page views to contend with with lower figures, but you will also be manipulating less data with each page view.
Liverpool FC Forum with 14 million+ posts.

sheryltoo

QuoteTo move existing avatars to the new directory, access index.php?action=manageattachments;sa=moveAvatars


This is what I don't understand.
Sheryl

Advertisement: