MySQL has support for two major database storage engines: MyISAM and InnoDB. MyISAM has, at least in past releases, been the default, and is the default for SMF installations. And, this is for good reason: MyISAM reads tables much faster than InnoDB.
However, InnoDB has some major benefits over MyISAM. First, it's important to have a basic understanding of how a query works. Let's say I run a query like this:
SELECT ID_MSG
FROM smf_messages
WHERE subject = 'Convert your tables to InnoDB'
LIMIT 1;
While MySQL is looking for the specific topic (which can take some time, since the subject column has no index) no one can write to the table. That means, no one can post to the table. For the messages table, this isn't a huge problem - and it has to be MyISAM for a FULLTEXT index anyway.
However, it matters more for tables like topics, log_topics, and friends. For example, every time you view a topic, this happens:
UPDATE smf_topics
SET numViews = numViews + 1
WHERE ID_TOPIC = ###
LIMIT 1;
If the table is locked, it will have to wait for... whatever is happening to finish. The same goes for multiple people viewing multiple topics at once. They have to wait in line for the topics table to become available.
This is not true for InnoDB. If two people view the same topic, yes... they will have to wait for the numViews update. But if they view different topics, it's immediate. This is a huge difference, and can make more difference than you'd expect, depending on what's going on on your server. But, remember... there is a penalty. MyISAM may lock badly, but it still reads faster. For tables that don't change much, like categories, there's usually no reason to use InnoDB.
For example, if you look at status.php, and see:
95 (87 sleeping, 2 running, 6 locked)
That means that the running queries are locking other queries, making them wait. This usually means you should be using InnoDB for some table which you aren't, but doesn't always mean that. With SMF, when your forum reaches a point of activity where it's benefical, you can run the following queries to change your tables to InnoDB:
ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
TYPE=InnoDB;
ALTER TABLE smf_log_actions
TYPE=InnoDB;
ALTER TABLE smf_log_boards
TYPE=InnoDB;
ALTER TABLE smf_log_errors
TYPE=InnoDB;
ALTER TABLE smf_log_karma
TYPE=InnoDB;
ALTER TABLE smf_log_mark_read
TYPE=InnoDB;
ALTER TABLE smf_log_online
TYPE=InnoDB;
ALTER TABLE smf_log_search
TYPE=InnoDB;
ALTER TABLE smf_log_topics
TYPE=InnoDB;
ALTER TABLE smf_members
TYPE=InnoDB;
ALTER TABLE smf_pm_recipients
TYPE=InnoDB;
ALTER TABLE smf_sessions
TYPE=InnoDB;
ALTER TABLE smf_settings
TYPE=InnoDB;
ALTER TABLE smf_topics
TYPE=InnoDB;
Additionally, you'll probably want log_floodcontrol 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)
) TYPE=HEAP;
What is phpMyAdmin? Before running these queries, it's a good idea to put your forum in maintenance mode. They will take, for large forums, quite some time (10 minutes, in cases.)
What is repair_settings.php?-[Unknown]
