• This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn more.

InnoDB or MyISAM - what is better for xenForo ???

shenmuee

Active member
#1
i was wonder as a continuity to this post. if someone have some spare time, please share with me (and others) your feelings about using InnoDB and MyISAM storage engines in MySQL?

Which one is faster or serves better for xenForo, in your opinion?

My xenForo phpmyadmin issed;

 

Brogan

XenForo moderator
Staff member
#2
Here are a few posts about them:
http://xenforo.com/community/threads/mysql-myisam-or-innodb.388/page-3#post-144437
http://xenforo.com/community/threads/mybb-faster-then-xenforo.12119/page-2#post-168697
http://xenforo.com/community/threads/database-size-question.12804/#post-168352
http://xenforo.com/community/threads/why-does-it-take-long.10021/#post-137034

And this post by Kier:
We use MyISAM, InnoDB and Memory table types, depending upon the requirements of each table.
Each table type has its own benefits. For example, MyISAM is held back by table-level locking on writes whereas InnoDB only has to lock the particular row that is being written. However, if you can avoid locking, MyISAM is hugelyfaster for data writes than InnoDB. So it's not as simple as saying that one particular table type is better than another.
 

Mike

XenForo developer
Staff member
#4
I should note that the vast majority of the default tables should be InnoDB - we actually specifically create them as that. I can only guess your server settings changed after installation.
 

Brogan

XenForo moderator
Staff member
#7
Evet, senin tabloların yanlış. Bu dogru.
Yes, your tables are incorrect. They should be:

MyISAM
xf_import_log
xf_search_index
xf_session
xf_session_admin

MEMORY
xf_attachment_view
xf_session_activity
xf_thread_view

The rest are InnoDB.
 

Brogan

XenForo moderator
Staff member
#9
You can change the type by selecting the table, clicking on the Operations tab, then choosing the Storage engine.

You should investigate why they changed though as that may occur again in the future.
 

Jaxel

Well-known member
#10
Basically when it comes down to it... MyISAM is better for WRITING, InnoDB is better for READING.

For tables that get written to often, such as session tables, it would be better for MyISAM. But tables are are just storages of information to be parsed later (like 99% of the tables out there), InnoDB is probably better.

That being said, MyISAM also supports full-text searching, while InnoDB does not.
 

Kier

XenForo Developer
Staff member
#11
Basically when it comes down to it... MyISAM is better for WRITING, InnoDB is better for READING.
That's true, but misses some important points, the most important of which is support for table-level or row-level locking.

While MyISAM is faster for writing, this comes at the expense of locking the entire table while write operations are happening. This is never a problem during development, when there are rarely going to be more than a couple of people concurrently connecting to the database, but in full-scale production when there are potentially thousands, it manifests itself as a serious lack of scalability.

InnoDB, while slower, is hugely more scaleable and reliable because of its support for transactions (the ability to roll-back changes in a multi-step operation) and row-level locking, so that during write operations only the affected rows are locked rather than the entire table, which allows multiple operations to be executed simultaneously on the same table.

On balance, InnoDB is a superior solution, and should be the default choice of table type unless there are specific reasons to require another type, such as wanting to use MySQL Full Text searching (requires MyISAM) or extremely fast non-permanent data throughput (could use a HEAP / MEMORY table).
 

shenmuee

Active member
#12
You can change the type by selecting the table, clicking on the Operations tab, then choosing the Storage engine.

You should investigate why they changed though as that may occur again in the future.
Ayrıca bu sql komutu işe yaradı >.<

alter table xf_admin engine=innodb;
 
#15
I know this is an ancient thread, but wanted to add for anyone who stumbles across via Google.

If you don't know what version of MySQL you're running, just ignore--this isn't for you.

MySQL 5.6 and MariaDB 10 support fulltext indexes on InnoDB tables, so if you're running one of those, you can also convert xf_search_index to InnoDB. The one thing you'll need to change is in the Admin Control Panel > Performance, you'll need to uncheck use delayed insert. I know it's implied it's a good thing to check, but it's not supported on InnoDB tables. It's basically a hack to get MyISAM to do what InnoDB does natively, and if you try to leave it on for InnoDB tables, your users won't be able to post to the forum anymore (found out the hard way).

I switched the search index table to InnoDB on a forum that has >2M posts, and was shocked by how much faster the search index rebuild went compared to when it was MyISAM. I did a little researching, and basically on large tables InnoDB has a few characteristics that make for much faster inserts. It's also probably affected by me having a small MyISAM cache but a large InnoDB buffer pool. I'm also caching all my sessions to Memcached, so I went ahead and just switched all the tables to InnoDB. That lets MySQL handle figuring out what should be cached without me having to pick and choose whether to push more RAM toward MyISAM or InnoDB.
 
Last edited: