Advertisement:

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

Offline AleXit

  • Newbie
  • *
  • Posts: 9
  • Gender: Male
    • Studenti.Fi.it
Re: Convert your tables to InnoDB
« Reply #140 on: April 17, 2008, 04:51:13 PM »
you shouldnt convert all.. didnt you read the post?
Yes I read them but I would like to have better performances ...
No one report corruption table issue, at most slowness...

So, what it could be gone wrong? Tables seem ok, not corrupted and well optimized, but I cannot change order of board and categories !

Please help me...  :(

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #141 on: April 17, 2008, 04:52:12 PM »
I converted all my tables to InnoDB... everything goes ok except one bug:
The order of my board are completely reverted, and I can NOT change it anymore.

You shouldn't convert all tables to InnoDB, problems like this will surface.

The quickest fix is to go into the admin and change the order of one board and one category. Switch them to a different position, save, then switch them back. The reason is that the board re-order changes the order the rows are stored in the database (to speed up display, I believe). When you convert, they go to numerical order, not display order.


you shouldnt convert all.. didnt you read the post?
Yes I read them but I would like to have better performances ...
No one report corruption table issue, at most slowness...

InnoDB isn't always the faster table type. If it was, it would be used by default. InnoDB is good for tables with lots of inserts happening along with selects. The board table doesn't change often enough to make a switch worthwhile for most forums.

Note that switching certain tables to InnoDB also disables the ability to use a fulltext search index. You need to use a custom index instead.
« Last Edit: April 17, 2008, 04:54:57 PM by Motoko-chan »
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline AleXit

  • Newbie
  • *
  • Posts: 9
  • Gender: Male
    • Studenti.Fi.it
Re: Convert your tables to InnoDB
« Reply #142 on: April 17, 2008, 05:02:49 PM »
Ok.. I know, I made a big mistake :(

I have already tried to change board ordering as you suggests, but nothing happen! In the admin board order looks ok, but in the index board not.
I created also a new test forum and set it at the top of a category: in the index of the board it went to the bottom!

Re-covert some tables to MyISAM, could help?

Offline IchBin™

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,115
  • Gender: Male
  • I don't speak German.
Re: Convert your tables to InnoDB
« Reply #143 on: April 17, 2008, 05:34:04 PM »
Why not just restore to a backup before you made the changes? You did back up right?
IchBin™        TinyPortal
Coding Guidelines       

Offline AleXit

  • Newbie
  • *
  • Posts: 9
  • Gender: Male
    • Studenti.Fi.it
Re: Convert your tables to InnoDB
« Reply #144 on: April 17, 2008, 05:45:59 PM »
Why not just restore to a backup before you made the changes? You did back up right?
Ehm..  :-[
Yes I have a backup but it is not made exactly before the conversion...

I could restore only the boards table... it's "smf_boards", right?

There is not a way to reorder it correctly?

Offline 青山 素子

  • Server Team
  • SMF Super Hero
  • *
  • Posts: 17,074
  • 戦場ヶ原、蕩れ!
    • srvrguy on GitHub
    • @motokochan on Twitter
    • Nekomusume Moe
Re: Convert your tables to InnoDB
« Reply #145 on: April 17, 2008, 06:17:15 PM »
You can try converting back to MyISAM and doing what I suggested previously.
Motoko-chan
Director, Simple Machines

Note: Unless otherwise stated, my posts are not representative of any official position or opinion of Simple Machines.


Offline AleXit

  • Newbie
  • *
  • Posts: 9
  • Gender: Male
    • Studenti.Fi.it
Re: Convert your tables to InnoDB
« Reply #146 on: April 17, 2008, 06:30:06 PM »
You can try converting back to MyISAM and doing what I suggested previously.
I solved before reading this post: i converted back "smf_boards" to MyISAM, and order it by "boardOrder" 
This has solved the issue...:)

Now.. which table you suggest it is best to reconvert to MyISAM ?
« Last Edit: April 17, 2008, 06:41:35 PM by AleXit »

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #147 on: April 17, 2008, 06:47:51 PM »
Pretty much any not in the suggested list for InnoDB.
Liverpool FC Forum with 14 million+ posts.

Offline AleXit

  • Newbie
  • *
  • Posts: 9
  • Gender: Male
    • Studenti.Fi.it
Re: Convert your tables to InnoDB
« Reply #148 on: April 17, 2008, 06:55:18 PM »
Pretty much any not in the suggested list for InnoDB.
Yes but I think the list in first post of this thread is not updated for smf 1.1.4 (some tables are changed...)
Anyone can confirm that?

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #149 on: April 17, 2008, 06:57:48 PM »
The only changes are additional log_search, they can probably be InnoDB.
Liverpool FC Forum with 14 million+ posts.

Offline Skuzzy

  • Jr. Member
  • **
  • Posts: 279
Re: Convert your tables to InnoDB
« Reply #150 on: April 18, 2008, 08:31:10 AM »
I should think anything not using FULLTEXT should be able to convert to InnoDB.  It is just a trade-off.  MyISAM is faster than InnoDB, but at the expense of low granularity locking which can kill your forum performance on tables that have a high number of concurrent UPDATES and SELECTS.

For instance, it really makes no sense to make the 'membergroups' table InnoDB as it is mostly read and not updated.  In this case, it is faster to leave it MyISAM.

Of course, InnoDB is usually more resilient than MyISAM, so take that into consideration as well.

By the way, if you have a large forum, you probably will want to make sure you turn on the use of separate files for the tables in InnoDB, as the default single file can get really large, really fast and can slow down accesses.

Offline AleXit

  • Newbie
  • *
  • Posts: 9
  • Gender: Male
    • Studenti.Fi.it
Re: Convert your tables to InnoDB
« Reply #151 on: April 18, 2008, 09:00:39 AM »
Ok, I reverted the wrong-converted tables to MyISAM, following the list in first post.

Thank you for you help guys ;)

Offline Sverre

  • Sr. Member
  • ****
  • Posts: 748
Re: Convert your tables to InnoDB
« Reply #152 on: April 25, 2008, 07:03:28 AM »
Should really use ENGINE instead of TYPE.

If you are on a MySQL version which supports it, should all tables, whether you've converted them to InnoDB or not, be changed to use ENGINE?

It all depends on your board access patterns and your traffic. Heavy posting needs different optimization than heavy viewing.

In general, INNODB works best for tables that have a lot of insertions, MyISAM is faster for tables that get a lot of SELECTs. Also, once you get heavier traffic, you need to increase the buffers for INNODB or you'll lose the advantage it gives.

I think I speak for a lot of SMF admins out there, or at least I hope I'm not the only one, when I say that a more detailed guide to help us identify the need to convert tables to InnoDB would be greatly appreciated.

Offline Ben_S

  • SMF Friend
  • SMF Super Hero
  • *
  • Posts: 11,725
  • xxx
Re: Convert your tables to InnoDB
« Reply #153 on: April 28, 2008, 06:01:49 AM »
If you are on a MySQL version which supports it, should all tables, whether you've converted them to InnoDB or not, be changed to use ENGINE?

Engine & type are the same thing, just the usage of type is depreciated and removed from MySQL 6. Once the tables have been converted it makes no difference what command you used.

Quote
I think I speak for a lot of SMF admins out there, or at least I hope I'm not the only one, when I say that a more detailed guide to help us identify the need to convert tables to InnoDB would be greatly appreciated.

I'd only convert the tables recommended in the first post in this topic and the log_search_* tables that are new to 1.1.

Converting _messages I definitely would not recommend, regardless of whether you use FULLTEXT search or not, in my experience the additional overhead of InnoDB on this table does not justify the benefits it brings.
Liverpool FC Forum with 14 million+ posts.

Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #154 on: July 09, 2008, 07:37:10 AM »
Entirely upto you, you should do the search ones at the very least though.
Why?
« Last Edit: July 09, 2008, 07:47:19 AM by mark7144 »

Offline evgenydeep

  • Semi-Newbie
  • *
  • Posts: 99
Re: Convert your tables to InnoDB
« Reply #155 on: September 05, 2008, 02:59:23 PM »
Hello "community" ;-), can dear ALL advise, which tables is better to make InooDB?
My forum description is here: http://www.simplemachines.org/community/index.php?topic=259843.0

Offline evgenydeep

  • Semi-Newbie
  • *
  • Posts: 99
Re: Convert your tables to InnoDB
« Reply #156 on: September 06, 2008, 02:40:27 AM »
After readind this topic from first to last message, i decided to convert following tables:

ALTER TABLE smf_attachments TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_collapsed_categories TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_actions TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_boards TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_errors TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_karma TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_mark_read TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_online TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_messages TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_results TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_subjects TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_search_topics TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_log_topics TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_members TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_pm_recipients TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_sessions TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_settings TYPE=InnoDB;
ALTER TABLE forumgr_forum.smf_topics TYPE=InnoDB;

Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #157 on: September 06, 2008, 06:55:39 PM »
Noticed any improvements?

Offline evgenydeep

  • Semi-Newbie
  • *
  • Posts: 99
Re: Convert your tables to InnoDB
« Reply #158 on: September 07, 2008, 06:10:58 AM »
Honestly saying, not too much. If there are any improvements... may be they are hided.
But looking after requests I noticed that some previously "stacked" situations began solving much faster.

My decision: converting to innoDB is one of step to make forum better and for big forums this is MUST HAVE.

Offline mark7144

  • Sophist Member
  • *****
  • Posts: 1,174
Re: Convert your tables to InnoDB
« Reply #159 on: September 09, 2008, 07:05:54 PM »
and for big forums this is MUST HAVE.
What is considered BIG?

My forum has been around since July 2005 and has 361,587 posts.