Converting to InnoDB

Hardcore

Active member
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!
 
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
 
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?
 
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.
 
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.
 
Top Bottom