News:

SMF 2.1.4 has been released! Take it for a spin! Read more.

Main Menu

Convert your tables to InnoDB

Started by [Unknown], September 20, 2005, 04:31:53 PM

Previous topic - Next topic

[Unknown]

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]

Joshua Dickerson

Should really use ENGINE instead of TYPE.

Probably would make log_online MEMORY/HEAP too (with some slight modifications)
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

[Unknown]

Quote from: groundup on September 20, 2005, 09:31:51 PM
Should really use ENGINE instead of TYPE.

Probably would make log_online MEMORY/HEAP too (with some slight modifications)

TYPE works on basically every version of MySQL.  ENGINE works, iirc, only on MySQL 4.1 and above.  Since most hosts are still using 4.0, that would be very silly.

And log_online cannot be HEAP/MEMORY because of the text column, except in MySQL 5.0 (which no one runs, I don't care how much you think it's production ready.)

-[Unknown]

Joshua Dickerson

Yeah, it should be ENGINE. Because people should be using the GA release.

You can convert to (var)char. And I am willing to bet some people run 5 (besides me)
Come work with me at Promenade Group



Need help? See the wiki. Want to help SMF? See the wiki!

Did you know you can help develop SMF? See us on Github.

How have you bettered the world today?

Mike Bobbitt

Now that smf_log_search doesn't exist in 1.1, are there other tables we should be converting?

Gargoyle

WOW!!!

I don't have that big of a forum and this made my site turn into a speed freak!!

Very cool !! ;D

xtremecruiser

If we have 4.1 or over we use engine.  If we are using 1.05 and have not updated to the RC is that ok for a later update ? Also can you explain the process a little better for us Microsoft guys ::)
Thanks

Oldiesmann

Good question. You should ask him to give you more details about that...
Michael Eshom
Christian Metal Fans

Bonk

Quote from: That's The Hacker to you on October 05, 2005, 09:36:59 PM
I asked my host about InnoDB and he apparently has it disabled and said "it causes problems"... What's up with that?

Probably the dramatically increased memory use and detailed configuration necessary in my.ini (my.cnf).

Gargoyle

Thats why I love my host... They are very accomidating... But I also pay more than 3 dollars a month... ;D

Ben_S

Quote from: Bonk on October 11, 2005, 12:30:11 PM
Probably the dramatically increased memory use and detailed configuration necessary in my.ini (my.cnf).

The default settings are fine for the vast majority of people and the increased memory usage is a whole 50MB.

Given that InnoDB will vastly help SMF boards, enabling it will probbaly save resources in the end.
Liverpool FC Forum with 14 million+ posts.

Webrunner

Can i change it back when i am not happy with the results?
There is a difference between knowing the path and walking the path.

=========================================
Vrouwen Power! | Sprintweb: No nonsense e-Business consultancy

Ben_S

Liverpool FC Forum with 14 million+ posts.

Elmacik

how?
we the noobs and greedies and  :P :D
Home of Elmacik

Enc0der

lol...  :P
use the same code, and just replace InnoDB with MyISAM..

for example:
ALTER TABLE smf_attachments
TYPE=MyISAM;
ALTER TABLE smf_collapsed_categories
TYPE=MyISAM;
ALTER TABLE smf_log_actions
TYPE=MyISAM;
ALTER TABLE smf_log_boards
TYPE=MyISAM;

etc etc..

Elmacik

i knew it... i asked for the second thingy that [unknown] posted :)

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;
Home of Elmacik

Webrunner

There is a difference between knowing the path and walking the path.

=========================================
Vrouwen Power! | Sprintweb: No nonsense e-Business consultancy

Elmacik

Quote from: Webrunner on October 16, 2005, 06:35:41 AM
Thge same query but Type - MyIsam
i think you didnt read one post above yours
Home of Elmacik

Webrunner

There is a difference between knowing the path and walking the path.

=========================================
Vrouwen Power! | Sprintweb: No nonsense e-Business consultancy

Douglas

Sorry to bump this up, can someone update this for 1.0.5 and 1.1.1 RC*
Doug Hazard
* Full Stack (Web) Developer for The Catholic Diocese of Richmond
(20+ Diocesan sites, 130+ Church sites & 24 School sites)
* Sports Photographer and Media Personality
* CFB Historian
* Tech Admin for one 1M+ post, one 2M+ post and one 10M+ post sites (last two are powered by multiple servers)
* WordPress Developer (Junkie / Guru / Maven / whatever)

Advertisement: