Advertisement:

Author Topic: Convert your tables to InnoDB  (Read 213972 times)

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Convert your tables to InnoDB
« on: September 20, 2005, 04:31:53 PM »
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]

Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,775
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #1 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)
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?

Offline [Unknown]

  • SMF Friend
  • SMF Master
  • *
  • Posts: 36,102
  • Gender: Male
Re: Convert your tables to InnoDB
« Reply #2 on: September 20, 2005, 10:19:13 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]

Offline Joshua Dickerson

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 12,775
  • Gender: Male
    • joshuaadickerson on GitHub
    • joshuaadickerson on LinkedIn
Re: Convert your tables to InnoDB
« Reply #3 on: September 20, 2005, 11:03:57 PM »
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)
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?

Offline Mike Bobbitt

  • Full Member
  • ***
  • Posts: 597
    • Army.ca
Re: Convert your tables to InnoDB
« Reply #4 on: September 25, 2005, 10:58:47 AM »
Now that smf_log_search doesn't exist in 1.1, are there other tables we should be converting?

Offline Gargoyle

  • Sophist Member
  • *****
  • Posts: 1,148
  • Gender: Male
    • South Side Street Cars
Re: Convert your tables to InnoDB
« Reply #5 on: October 02, 2005, 08:05:00 PM »
WOW!!!

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

Very cool !! ;D

Offline xtremecruiser

  • Jr. Member
  • **
  • Posts: 375
    • GolfDiscussions.com
Re: Convert your tables to InnoDB
« Reply #6 on: October 03, 2005, 06:39:15 PM »
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

Offline Oldiesmann

  • Developer
  • SMF Super Hero
  • *
  • Posts: 24,814
  • Gender: Male
  • Ask me about the function DB :)
    • oldiesmann on Facebook
    • Oldiesmann on GitHub
    • http://www.linkedin.com/in/michaeleshom on LinkedIn
    • @oldiesmann on Twitter
    • Archie Comics Fan Forum
Re: Convert your tables to InnoDB
« Reply #7 on: October 05, 2005, 11:53:37 PM »
Good question. You should ask him to give you more details about that...
Michael Eshom
Webmaster / SMF Lead Developer
oldiesmann@simplemachines.org

Bonk

  • Guest
Re: Convert your tables to InnoDB
« Reply #8 on: October 11, 2005, 12:30:11 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).

Offline Gargoyle

  • Sophist Member
  • *****
  • Posts: 1,148
  • Gender: Male
    • South Side Street Cars
Re: Convert your tables to InnoDB
« Reply #9 on: October 11, 2005, 06:46:46 PM »
Thats why I love my host... They are very accomidating... But I also pay more than 3 dollars a month... ;D

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,702
  • xxx
Re: Convert your tables to InnoDB
« Reply #10 on: October 11, 2005, 08:36:57 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.

Offline Webrunner

  • Full Member
  • ***
  • Posts: 525
  • Gender: Male
    • Vrouwenpower
Re: Convert your tables to InnoDB
« Reply #11 on: October 14, 2005, 07:48:07 PM »
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

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,702
  • xxx
Re: Convert your tables to InnoDB
« Reply #12 on: October 14, 2005, 07:51:26 PM »
yes
Liverpool FC Forum with 14 million+ posts.

Offline Elmacik

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 8,276
  • Gender: Male
  • = Human Draft =
    • IT Store
Re: Convert your tables to InnoDB
« Reply #13 on: October 16, 2005, 03:27:15 AM »
how?
we the noobs and greedies and  :P :D
Home of Elmacik

Offline Enc0der

  • Jr. Member
  • **
  • Posts: 358
  • Gender: Male
  • I'm a llama!
Re: Convert your tables to InnoDB
« Reply #14 on: October 16, 2005, 04:06:51 AM »
lol...  :P
use the same code, and just replace InnoDB with MyISAM..

for example:
Code: [Select]
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..

Offline Elmacik

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 8,276
  • Gender: Male
  • = Human Draft =
    • IT Store
Re: Convert your tables to InnoDB
« Reply #15 on: October 16, 2005, 04:08:56 AM »
i knew it... i asked for the second thingy that [unknown] posted :)
Code: [Select]
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

Offline Webrunner

  • Full Member
  • ***
  • Posts: 525
  • Gender: Male
    • Vrouwenpower
Re: Convert your tables to InnoDB
« Reply #16 on: October 16, 2005, 06:35:41 AM »
Thge same query but Type - MyIsam
There is a difference between knowing the path and walking the path.

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

Offline Elmacik

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 8,276
  • Gender: Male
  • = Human Draft =
    • IT Store
Re: Convert your tables to InnoDB
« Reply #17 on: October 16, 2005, 06:36:48 AM »
Thge same query but Type - MyIsam
i think you didnt read one post above yours
Home of Elmacik

Offline Webrunner

  • Full Member
  • ***
  • Posts: 525
  • Gender: Male
    • Vrouwenpower
Re: Convert your tables to InnoDB
« Reply #18 on: October 16, 2005, 06:53:56 AM »
Ooops.. sorry
 :o
There is a difference between knowing the path and walking the path.

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

Offline Douglas

  • SMF Friend
  • SMF Hero
  • *
  • Posts: 3,082
  • Gender: Male
  • Non sibi sed patriae
    • @BearlyDoug on Twitter
    • TwoCentsRadio.net
Re: Convert your tables to InnoDB
« Reply #19 on: December 14, 2005, 01:05:35 AM »
Sorry to bump this up, can someone update this for 1.0.5 and 1.1.1 RC*
Doug Hazard
* Web Developer / Operations Analyst for Richweb.com
* Sports Media Personality covering Collegiate Football and Basketball
* CFB Historian (GridironHistory.com)
* Tech Admin for one 1M+ post, one 2M+ post and one 8M+ post sites (last two are powered by 10+ servers)
* SMF Fanatic