Database table type
Installation and Configuration
Hi Dave -
Funny that you bring this up - this is something I've been actually working on on and off over the last couple months - basically migrating MyISAM tables to InnoDB. Our goal is to be 100% InnoDB, but it's not as simple as just switching table types, so don't do that.
The reason Jamroom uses MyISAM for some tables, is that for those tables we need to be able to get a very fast TOTAL row count from the table, which is something MyISAM supports by reading directly from the table meta data (since it keeps track of total row counts in it's meta data). This is something that InnoDB does not do (nor is really possible to do so in a table type that supports transactions), so we have to design an alternate "counter" mechanism that keeps track of counts in the table.
So a few things:
1) We're still using MyISAM tables - I'm not sure when that will change so we are 100% InnoDB. For now you need to stay on MyISAM for the tables that are MyISAM. Since I know this is affecting you, I will make this a bit higher priority than it has been (i.e. I've just been doing testing when I am working on something else related to the table). Our goal is to be able to run JR on Galera Cluster or MySQL NDB, which requires all tables to be InnoDB.
2) It's not always true that MyISAM uses more RAM - in fact it can be the opposite, since InnoDB stores a copy of it's Primary Key in all secondary indexes, which means it can use a larger amount of RAM than a similar MyISAM table.
3) While it's true that you can guarantee a snapshot of a InnoDB table, you can actually do the same with MyISAM by locking the table before your dump. However in practice I don't think you'll have any issues by not locking. mysqldump will always give you a "current" snapshot of the table - i.e. you're not going to get any partial entries, however any queries "in flight" may not be dumped if they have not committed. This is a problem in theory - in practice I've never seen an issue with this, and have managed hundreds and hundreds of MySQL servers. I'd recommend having your provider check out Percona's xTraBackup:
https://www.percona.com/software/mysql-database/percona-xtrabackup
It does "hot" dumps of InnoDB tables which nice.
So for now you'll need to stay with MyISAM, so hopefully that does not cause you too much issues. If it does and you need to consider alternate hosting let me know
Hope this helps!
