converting 8GIB database to InnoDB

[xml]

when I imported my vBulletin database to xenforo the InnoDB was disabled in my server and i didnt know i should check if it is disabled or enabled because it is not mentioned in xenforo manual so all my database tables are MyISAM, how I wish that xenforo-requirements-test or xenforo install script includes InnoDB enabled check

1. I want to know if converting for my case to InnoDB is a must OR highly recomended???

if it is a must then this mean i dont have time and i have to convert right now or the database will be corrupted because xenforo is programmed for InnoDB .

if it is highly recomended then it mean xenforo can work with MyISAM with no serious issues and no fear of database corruption and i have plenty of time to convert to InnoDB any time i want

I really want the answer to be highly recomended so I can take all my time to think and plan to make a very carefull checklist for the conversion from MyISAM to InooDB


2. I have 2 tables that really concerns me because of thier size is very large which might lead to conversion failuer :

xf_post ( 3,726,212 rows and 2.1 GIB size )
xf_search_index ( 3,833,158 rows and 4.9 GIB size )
Edit: xf_search_index should be MyISAM so it wont need to be conveted

The total dabase size is 8 GIB and 20 millions rows

Do you think phpMyAdmin can handle the conversion? what other things i should consider?

Thanks in advance for any reply

 

[DBA]

2. I have 2 tables that really concerns me because of thier size is very large which might lead to conversion failuer :

xf_post ( 3,726,212 rows and 2.1 GIB size )
xf_search_index ( 3,833,158 rows and 4.9 GIB size )

<slightly off topic>

I'd recommend implementing the XF Enhanced Search as it'll allow you to drop the xf_search_index table, reducing your database size by over half.

http://xenforo.com/community/threads/the-xf_search_index-table-can-be-truncated-if-using-xfes.26182/

 

[xml]

Thank you DBA

XF Enhanced Search will be in my list now and my concerns now are lowered by 50%

waiting kindly to hear from xenforo experts the comments on the rest of my questions

 

[Paul B]

Have a read of this thread, in particular the comments from Kier and Mike: http://xenforo.com/community/threads/mysql-myisam-or-innodb.388/

Some more threads here: http://xenforo.com/community/search/1820747/?q=innodb+myisam&t=post&o=date&c[user][0]=2&c[user][1]=3

 

[Jake Bunce]

Kier recommends making the switch:

http://xenforo.com/community/threads/changing-tables-from-myisam-to-innodb.14476/#post-189581

XenForo relies heavily on functionality provided by InnoDB over MyISAM so you should definitely make the change, however you should note that not all XenForo tables use InnoDB, so be careful when running your queries.

http://xenforo.com/community/threads/innodb-or-myisam-what-is-better-for-xenforo.13575/#post-178294

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

I have never tried changing the storage engine on such a large database. I prefer to run large queries using the mysql prompt in the shell. That way the connection won't possibly timeout before the query returns. But even if you use phpmyadmin and the connection times out, the query should still finish you just won't be notified.

Make a backup first.

 

[xml]

Hello Borgan and Jake

from what I read, I understand if I choose to keep my xenforo board database on MyISAM I ONLY just have to live with table locking as it was for me the past 10 years with my vBulletin board 2.x and 3.x? well, my vBulletin database been all those years on MyISAM and table locking did not cause me any real trouble

actually I am fairly satisfied currently with the performance and the speed of my Xenforo boared on MyISAM, and from what I read from google search, InnoDB is a very resource intensive and certinly the board will be slower in case i converted to InnoDB unless I moved to a high performance server with at least 16 rams of memory according to my current database size.

Any comments from mysql experts will be very apreciated

 

[Paul B]

Any comments from mysql experts will be very apreciated

I'm fairly sure Jake, Kier and Mike are more than qualified to comment.

 

[Slavik]

Hello Borgan and Jake

from what I read, I understand if I choose to keep my xenforo board database on MyISAM I ONLY just have to live with table locking as it was for me the past 10 years with my vBulletin board 2.x and 3.x? well, vBulletin database been all those years on MyISAM and table locking did not cause me any real trouble

actually I am fairly satisfied currently with the performance and the speed of my Xenforo boared on MyISAM, and from what I read from google search, InnoDB is a very resource intensive and certinly the board will be slower unless I moved to a high performance server with at least 16 rams of memory according to my current database size.

Any comments from mysql experts will be very apreciated

Few things.

Remember that over HALF of your database is the search tables. This should ideally be dropped in favor of Elasticsearch. Those search tables however are not run on InnoDB, they are left as MyISAM anyway.

InnoDB provides numerous benefits over MyISAM, so you should make the switch. Just remember, not every table in XenForo uses InnoDB, off the top of my head you need to leave the import log, the search tables, the session tables as MyISAM.

 

[Wildcat Media]

In my case it depended more on traffic, not the number of database rows. Several years ago, MyISAM was the cause of our forum grinding to a halt: simultaneous postings were locking up vB's post table for several minutes at a time. It did take awhile to convert to InnoDB back then, but it immediately cleared up the issue.

We now routinely have 800+ members online on our vB 3.7 forum during peak hours and most days, never have a problem. Back then, I'd say we had 450-500 members online during peak hours, and the forum was all but inaccessible before I switched away from MyISAM.

To be honest, I even had the table locking issue on a very low volume phpBB2 forum. I converted a handful of tables to InnoDB and the occasional (but annoying) delays were removed permanently.

In general, tables that are primarily read-only (IOW, updated only a couple of times per day) would probably live nicely on MyISAM. But if the table is in constant use, being written to, InnoDB will prevent problems with locking.

 

[xml]

In my case it depended more on traffic, not the number of database rows. Several years ago, MyISAM was the cause of our forum grinding to a halt: simultaneous postings were locking up vB's post table for several minutes at a time. It did take awhile to convert to InnoDB back then, but it immediately cleared up the issue.

We now routinely have 800+ members online on our vB 3.7 forum during peak hours and most days, never have a problem. Back then, I'd say we had 450-500 members online during peak hours, and the forum was all but inaccessible before I switched away from MyISAM.

To be honest, I even had the table locking issue on a very low volume phpBB2 forum. I converted a handful of tables to InnoDB and the occasional (but annoying) delays were removed permanently.

In general, tables that are primarily read-only (IOW, updated only a couple of times per day) would probably live nicely on MyISAM. But if the table is in constant use, being written to, InnoDB will prevent problems with locking.

Hello Rudy

my board have around 250 - 300 online members with around 1000 new post every day and i did not notice niether the board members any slow in the performance or speed of the board. the only locking happen is just when i do backups. i can confirm from what i saw on my board that xenforo can work efficiently on MyISAM if the server and mysql are well optimised.

I have no idea how well my board will perform on InnoDB But from what i read in google searches InnoDB is a lot more resource intensive and slower than MyISAM and its for comercial websites more than personal websites.