Convert your tables to InnoDB

[AleXit]

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... 

[青山 素子]

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.

[AleXit]

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?

[IchBin™]

Why not just restore to a backup before you made the changes? You did back up right?

[AleXit]

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?

[青山 素子]

You can try converting back to MyISAM and doing what I suggested previously.

[AleXit]

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 ?

[Ben_S]

Pretty much any not in the suggested list for InnoDB.

[AleXit]

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?

[Ben_S]

The only changes are additional log_search, they can probably be InnoDB.

[Skuzzy]

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.

[AleXit]

Ok, I reverted the wrong-converted tables to MyISAM, following the list in first post.

Thank you for you help guys

[Sverre]

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.

[Ben_S]

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.

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.

[mark7144]

Entirely upto you, you should do the search ones at the very least though.

Why?

[evgenydeep]

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

[evgenydeep]

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;

[mark7144]

Noticed any improvements?

[evgenydeep]

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.

[mark7144]

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.