I could be wrong, but I imagine they're using
MEMORY because it has better performance characteristics under lighter workloads where locking isn't as much of an issue, and the data is ephemeral anyways.
It probably made a lot more sense back when XF was first designed as disk IO was precious, and it was a simple way for a pure in memory store. Now days you can just throw NVMe SSDs at the problem and forget about it.
Joining between InnoDB and Memory/MyIASM tables is painful once you get contention.
Do you just convert everything in the db to InnoDB for this? I figured they were using MyISAM and MEMORY for specific reasons. I'd love to just convert everything over and set up Galera.
Yes, I converted everything to
. Even the
table, as full-text search is actually supported with very latest version of MySQL/MariaDB even if I don't use it.
You need to change all MEMORY tables to INNODB and also give them a unique id column to use Galera. This is what we have done. MEMORY tables don't replicate in HA environment.
With XF2, every table has a primary key (multi-column primary keys is OK). Just a matter of hitting all the tables which use non-Innodb tables.
This query finds all non-innodb tables and generates SQL statements to convert them;
select concat('alter table `',table_schema,'`.`',`table_name`,'` engine=InnoDB;')
where `engine` <> 'innodb' AND `table_schema` = '<mydatabase>';
Note; you need to set a <mydatabase>, as there are a pile of MySQL internal tables which you can't change the table type for without hosing your install!