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

Converting to InnoDB

Hardcore

Active member
#1
Hey there -

Installed XenForo using MyISAM; had skip-innodb set and our default engine set. Looking to convert those tables that are not setup as MyISAM or MEMORY to InnoDB; as XenForo would prefer. (re: http://xenforo.com/community/threads/mysql-myisam-or-innodb.388/page-3#post-258835)

I have no problem converting the tables (re: http://xenforo.com/community/threads/some-server-log-errors.26959/#post-323255), but I'm not overly familiar with the InnoDB engine or settings so I'm looking for some basic must have settings / tips.
  • MySQL is on a dedicated 16 core server w/ 8GB.
  • Some tables are MyISAM, some are InnoDB; as per XenForo and previous custom tables.
Any help would be appreciated.

Thanks!
 

Slavik

XenForo moderator
Staff member
#3
The main setting you want to look at is innodb_buffer_pool_size

Set it to whatever your database size is +20%

So if your db is 1gb, set the buffer pool to 1.2gb
 

Hardcore

Active member
#4
This is really the best primer: http://www.mysqlperformanceblog.com/2007/11/01/innodb-performance-optimization-basics/

innodb_buffer_pool_size and innodb_flush_log_at_trx_commit (and possibly innodb_flush_method) are some of the most important. You probably don't need to set the innodb_buffer_pool_size that large - it should be set based on the size of your data.
Thanks Mike. Am I also right in assuming that most the current my.cnf setting can still remain so long as I'm still using MySIAM tables too?
 

Mike

XenForo developer
Staff member
#5
Thanks Mike. Am I also right in assuming that most the current my.cnf setting can still remain so long as I'm still using MySIAM tables too?
Yeah, generally speaking. Though as you use less MyISAM, you may be able to turn some values down.
 

Hardcore

Active member
#6
Success.

Though during my test I discovered a typo in this recommended script: http://xenforo.com/community/threads/some-server-log-errors.26959/#post-323255

Code:
'xf_session_adminxf_attachment_view'
Should be:

Code:
'xf_session_admin','xf_attachment_view'
Had to move all /var/lib/mysql/ib* files to a temp directory too. They were lingering from the sever's defaults before I tweaked it for MyISAM and wasn't allowing InnoDB to work.

Thanks again all. I think I'm going to like working with InnoDB.