Post #1815987

2026-04-30 13:06 UTC

I’m shortly going to start on a project to migrate a legacy platform from MyISAM to InnoDB — just over 100,000 tables across 4,000 databases, all on a single server. Does anyone have any advice on things to watch out for? (I’m aware of the basics like keeping primary keys small in InnoDB. At this stage we’re not yet looking to take advantage of InnoDB features such as transactions and foreign keys, but we do need to maintain performance.)

Replies (7)

@greem@cyberplace.social 2026-04-30 13:15

@katemorley@hachyderm.io Have you got the storage space to run a separate server instance on a different path/socket/port on that machine? It might make the conversion a little easier - you could do a dump and import inline without necessarily needing the dump files on disk.

Open ##2319472
@pwaring@social.xk7.net 2026-04-30 13:15

@katemorley@hachyderm.io I've never had any problems moving from MyISAM to InnoDB, and I've done a lot over the years. Most of the arguments for sticking with MyISAM (e.g. full text indexes) are no longer relevant. Given the numbers involved though you might want to experiment with the various InnoDB config options, as they can make/break a setup. I have found this resource to be useful in the past, though normally I've just done ALTER TABLE tbl SET Engine=InnoDB: https://mysql.rjweb.org/doc.php/myisam2innodb

Open ##2319473
@Ollivdb@nrw.social 2026-04-30 13:35

@katemorley@hachyderm.io @isotopp@infosec.exchange You were the expert in MySQL, right?

Open ##2319474
@samb@techhub.social 2026-04-30 14:01

@katemorley@hachyderm.io Probably that all the performance knobs are different with InnoDB, so any performance tuning that may have been done over the years for MyISAM won't apply to the migrated databases (it's all about the innodb buffer pools now). Might be worth staging and testing with some representative workloads if a performance regression will be an issue.

Open ##2319475
@FritzAdalis@infosec.exchange 2026-04-30 14:24

@katemorley@hachyderm.io @hrbrmstr@mastodon.social Do you have good test plans for the applications and a way to measure and quantify performance? If not you're kind of going in blind. An application mapping tool can help to figure out which apps are actually using which dbs and from where. It might help with the cutover vs. piecemeal decision. (Doing it piecemeal will also create the project that never ends unless you have strong exec support.) If you do a cutover make sure you have a trivial failback plan and well-defined rules for pass/fail. Don't be afraid to fail if you can easily fail back and try again. Hope I'm not mansplaining, these are just things I've had to learn the hard way over the years. Not even MySQL-specific.

Open ##2319476
@gairdeachas@mastodon.social 2026-04-30 14:32

@katemorley@hachyderm.io on disk, InnoDB tables can be much larger than MyISAM tables - sometimes up to 2x depending on the table definition. When I converted DProofreaders to InnoDB we left our ~80k project tables as MyISAM due to disk constraints and converted everything else. InnoDB table compression didn't help in our case but it's something to consider if disk space is a factor.

Open ##2319477
@coldclimate@hachyderm.io 2026-05-01 17:24

@katemorley@hachyderm.io subscribes for replies because this awesome

Open ##2319478