Convert your tables to InnoDB

[Something like that]

If you are running MySQL 5.0.3 or later, you can also convert smf_log_online to type MEMORY/HEAP. First, you need to convert the "url" field to type varchar, and put a length of 512 or so. I chose 1024 for good measure (so what if i waste 512 bytes of RAM per user: even with 1000 logged in users, that's only 512 KB of RAM wasted).

Before MySQL 5.0.3, a varchar was limited to 256 bytes, which isn't long enough for the url field in all cases.

You will get (temporary) forum errors if the length of the value of the url field ever exceeds the length of the url field, so convert url at your own risk.

[knibal]

Hi,

This is my server status:

http://www.hastalamuerte.net/foros/status.php?mysql_info=1

Should I make this changues? I´m usually over 50 simultaneous users

[Ben_S]

If you are running MySQL 5.0.3 or later, you can also convert smf_log_online to type MEMORY/HEAP. First, you need to convert the "url" field to type varchar, and put a length of 512 or so. I chose 1024 for good measure (so what if i waste 512 bytes of RAM per user: even with 1000 logged in users, that's only 512 KB of RAM wasted).

Before MySQL 5.0.3, a varchar was limited to 256 bytes, which isn't long enough for the url field in all cases.

You will get (temporary) forum errors if the length of the value of the url field ever exceeds the length of the url field, so convert url at your own risk.

Good tip, also since you are using varchar, you aren't actually wasting as much ram as you think since it allocates whatever the length is plus 2 bytes to store the actual length (1 if it's less than 256).

Same would probably work for session too, would just have to set the data field to a very long varchar.

[nitins60]

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]

i am newbie n using phpmyadmin/dealing with Mysql. However my forum got 3000+ members and 5000+ posts and 2000+ attachments. Should i convert to innodb to get better performance?
Can any1 make simple web based conversion script (to innodb)? It'll be useful for newbies also.

Some of the factors are exceeding in status.php. Pleas check my status.php and give me suggestions to run it faster, how do i do them?

[Something like that]

Good tip, also since you are using varchar, you aren't actually wasting as much ram as you think since it allocates whatever the length is plus 2 bytes to store the actual length (1 if it's less than 256).

That's generally true, but not for MEMORY tables. Memory tables use a fixed-length row storage format, which is one of the reasons why they don't support text or blob column types.

Same would probably work for session too, would just have to set the data field to a very long varchar.

Yeah, I took at look at the 440 elements currently in my session table, and some entries in the data column were 12k characters long (when they're long, it's full of SQL statements). If I assign the full 64k length for margin, that becomes a large chunk of RAM used.

[Ben_S]

Ah, that kinda puts the dampers on that idea

[moofa]

And a more complte InnoDB Conversion

Code Select Expand


ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
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;
ALTER TABLE smf_ads
TYPE=InnoDB;
ALTER TABLE smf_ads_settings
TYPE=InnoDB;
ALTER TABLE smf_awards
TYPE=InnoDB;
ALTER TABLE smf_awards_members
TYPE=InnoDB;
ALTER TABLE smf_ban_groups
TYPE=InnoDB;
ALTER TABLE smf_ban_items
TYPE=InnoDB;
ALTER TABLE smf_boards
TYPE=InnoDB;
ALTER TABLE smf_board_permissions
TYPE=InnoDB;
ALTER TABLE smf_calendar
TYPE=InnoDB;
ALTER TABLE smf_calendar_holidays
TYPE=InnoDB;
ALTER TABLE smf_categories
TYPE=InnoDB;
ALTER TABLE smf_gallery_cat
TYPE=InnoDB;
ALTER TABLE smf_gallery_comment
TYPE=InnoDB;
ALTER TABLE smf_gallery_pic
TYPE=InnoDB;
ALTER TABLE smf_gallery_report
TYPE=InnoDB;
ALTER TABLE smf_googletagged
TYPE=InnoDB;
ALTER TABLE smf_membergroups
TYPE=InnoDB;
ALTER TABLE smf_messages
TYPE=InnoDB;
ALTER TABLE smf_message_icons
TYPE=InnoDB;
ALTER TABLE smf_moderators
TYPE=InnoDB;
ALTER TABLE smf_ob_googlebot_stats
TYPE=InnoDB;
ALTER TABLE smf_package_servers
TYPE=InnoDB;
ALTER TABLE smf_permissions
TYPE=InnoDB;
ALTER TABLE smf_personal_messages
TYPE=InnoDB;
ALTER TABLE smf_poll_choices
TYPE=InnoDB;
ALTER TABLE smf_subscriptions
TYPE=InnoDB;
ALTER TABLE smf_themes
TYPE=InnoDB;


[Ben_S]

Not all tables benefit from being InnoDB, in fact doing it on messages means you loose some advantages such as the ability to use full text search.

[Something like that]

And a more complte InnoDB Conversion

Code Select Expand


ALTER TABLE smf_attachments
TYPE=InnoDB;
ALTER TABLE smf_collapsed_categories
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;
ALTER TABLE smf_ads
TYPE=InnoDB;
ALTER TABLE smf_ads_settings
TYPE=InnoDB;
ALTER TABLE smf_awards
TYPE=InnoDB;
ALTER TABLE smf_awards_members
TYPE=InnoDB;
ALTER TABLE smf_ban_groups
TYPE=InnoDB;
ALTER TABLE smf_ban_items
TYPE=InnoDB;
ALTER TABLE smf_boards
TYPE=InnoDB;
ALTER TABLE smf_board_permissions
TYPE=InnoDB;
ALTER TABLE smf_calendar
TYPE=InnoDB;
ALTER TABLE smf_calendar_holidays
TYPE=InnoDB;
ALTER TABLE smf_categories
TYPE=InnoDB;
ALTER TABLE smf_gallery_cat
TYPE=InnoDB;
ALTER TABLE smf_gallery_comment
TYPE=InnoDB;
ALTER TABLE smf_gallery_pic
TYPE=InnoDB;
ALTER TABLE smf_gallery_report
TYPE=InnoDB;
ALTER TABLE smf_googletagged
TYPE=InnoDB;
ALTER TABLE smf_membergroups
TYPE=InnoDB;
ALTER TABLE smf_messages
TYPE=InnoDB;
ALTER TABLE smf_message_icons
TYPE=InnoDB;
ALTER TABLE smf_moderators
TYPE=InnoDB;
ALTER TABLE smf_ob_googlebot_stats
TYPE=InnoDB;
ALTER TABLE smf_package_servers
TYPE=InnoDB;
ALTER TABLE smf_permissions
TYPE=InnoDB;
ALTER TABLE smf_personal_messages
TYPE=InnoDB;
ALTER TABLE smf_poll_choices
TYPE=InnoDB;
ALTER TABLE smf_subscriptions
TYPE=InnoDB;
ALTER TABLE smf_themes
TYPE=InnoDB;


You're better off to leave small tables that are infrequently updated as MyISAM. Read performance is very fast for MyISAM. InnoDB is used because it allows concurrent actions while MyISAM doesn't.

[mlsred]

Hi All

I have converted all the tables as recommended in the first post but still have some large tables with lots of records set to MyISAM (see attached). My forum is still preforming intermittently i.e. one thread will appear in less than a second and the next takes 10 seconds.

Should I convert any of these other larger tables?

Cheers
M

[Something like that]

Hi All

I have converted all the tables as recommended in the first post but still have some large tables with lots of records set to MyISAM (see attached). My forum is still preforming intermittently i.e. one thread will appear in less than a second and the next takes 10 seconds.

Should I convert any of these other larger tables?

Cheers
M

I'd convert them all except perhaps phosmf_themes. InnoDB is almost always a better choice (the exception being tables that are rarely changed).

[mlsred]

Hi All

I have converted all the tables as recommended in the first post but still have some large tables with lots of records set to MyISAM (see attached). My forum is still preforming intermittently i.e. one thread will appear in less than a second and the next takes 10 seconds.

Should I convert any of these other larger tables?

Cheers
M

I'd convert them all except perhaps phosmf_themes. InnoDB is almost always a better choice (the exception being tables that are rarely changed).

Thanks for the reply.. I thought, from reading elswhere on the board, that there isn't really a benefit to changing the Messages & PM table and I will loose the ability to use fulltext search on them?

[Something like that]

Hi All

I have converted all the tables as recommended in the first post but still have some large tables with lots of records set to MyISAM (see attached). My forum is still preforming intermittently i.e. one thread will appear in less than a second and the next takes 10 seconds.

Should I convert any of these other larger tables?

Cheers
M

I'd convert them all except perhaps phosmf_themes. InnoDB is almost always a better choice (the exception being tables that are rarely changed).

Thanks for the reply.. I thought, from reading elswhere on the board, that there isn't really a benefit to changing the Messages & PM table and I will loose the ability to use fulltext search on them?

How frequently are posts made? If you only have a few posts per hour, it's no big deal. If there are several posts a minute, it's an issue. Every time someone is making a new post when the messages table is MyISAM, it stops all queries from using the messages table until the update is done: that includes the forum index, the thread listings, and the thread displays (essentially every page outside of PM's and profiles). You do lose the fulltext searching, but if you're suffering from a lagging forum, it's one of the first tables I'd convert.

[青山 素子]

Little secret. There is work being done to get Sphinx support working in SMF (probably as a mod). A few places are using it already for testing.

[Ben_S]

And it works very well .

[mlsred]

How frequently are posts made? If you only have a few posts per hour, it's no big deal. If there are several posts a minute, it's an issue. Every time someone is making a new post when the messages table is MyISAM, it stops all queries from using the messages table until the update is done: that includes the forum index, the thread listings, and the thread displays (essentially every page outside of PM's and profiles). You do lose the fulltext searching, but if you're suffering from a lagging forum, it's one of the first tables I'd convert.

Thanks, converted PM but when I went to convert messages i got this:

Error

SQL query:

ALTER TABLE phosmf_messages TYPE = InnoDB

MySQL said: Documentation
#1214 - The used table type doesn't support FULLTEXT indexes

how do I get around this?

Cheers
M

[Ben_S]

You would need to drop the FULL TEXT index and change to a different search method, although the reality is unless you have numerous posts a second it isn't going to really help all that much.

[Something like that]

You would need to drop the FULL TEXT index and change to a different search method, although the reality is unless you have numerous posts a second it isn't going to really help all that much.

Even if your forum has just a high read load like mine, posting with MyISAM has to wait for all reads to finish, and also causes all those reads to block until the post has been entered. That's with a daily average of 1 hit per second, but during peak times of 5-10+ hits per second, the forum would crawl brutally. MyISAM is very poorly suited for use by a forum, mainly because it has almost no support for parallel read and write tasks.

[Ben_S]

I've not seen any issues whatsoever with my messages table, no evidence of any locking going on, but then most locking would probably be caused by searches and using Sphinx, search is no issue.

[Something like that]

I've not seen any issues whatsoever with my messages table, no evidence of any locking going on, but then most locking would probably be caused by searches and using Sphinx, search is no issue.

Sphinx looks like a lot of fun. It'll be neat when it's implemented and tested