Doesn’t mean others will have a similar experience(in terms of performance and scalability) with us. Obviously. YMMV.
Alternative take, sadly from personal experience: At one job, I got asked to help out a customer who just couldn't get the performance they needed out of their database. They wanted me to help migrate them to a larger server, which I did. Not long afterwards they came back asking for help tuning their database, because it was still slow as molasses. Turns out it was a site trying to be like a yellow pages. Their central bottleneck came from a single field in a single table that detailed the businesses. That field was "categories", a nice FULL TEXT field. Every category had a four letter short code. They wanted to have businesses exist in multiple categories, and so what they'd do was have an alphabetically sorted list of short codes, semicolon separated, e.g. "DOCT;DENT;MEDI" (for a business that offered Doctor and Dentist services). When you went to look at the medical table, the query would do something roughly of the form "SELECT * FROM businesses WHERE categories LIKE '%MEDI;%';". This would have been about 2009. There was no way to index off that field, every query would have to do a full table scan of that column to find out every relevant business. It worked, and when just a small handful of people were using the site at the same time, everything was A-OK as the server was really powerful and brute force was viable. Add any more users and the thing would fall to its knees. It wasn't that MySQL was the problem, it wasn't, it was that they were using it wrong. Switching engines wouldn't have fixed the fundamental flaw in the schema. I even showed them how they could solve all of their problems with a relatively simple schema change, but they wouldn't change things. They did spend a lot of time ranting about how it was MySQL's problem and "How can Google manage it but MySQL can't", when it really, absolutely and truly wasn't MySQL's fault. From the company perspective, everything was mostly great. They were paying for database servers far bigger than they needed, and paying for my time to read their rants, and have my advice ignored. You can lead a horse to the water, but you can't make it drink, I guess?
Just because you can use a tool one way, doesn't mean it's the right way to use it.
From the perspective of your average jack-of-all-trades sysadmin, who has had to dabble in DBA work from time to time, here's what seemed really strange to me:
InnoDB -> MyISAM. That was a really, really strange choice. When you mention that at the outset as a change made, that's the kind of choice that rings alarm bells in my head and makes me think "They really don't know what they're doing". MyISAM is an older technology than InnoDB and has a large number of drawbacks to it. A few key ones:
* MyISAM writes lock up the entire table, vs InnoDB row level locking. The whole table becomes read only until the write is finished. You can only have one write or update happening at a time, forcing you in to effective serialisation for all writes. That introduces a nasty scaling limitation.
* MyISAM doesn't support transactions. It doesn't barf when it sees transaction related instructions, it just ignores them.
* MyISAM's crash recovery is next to negligible. InnoDB has transaction logs around and the like that help it to recover from a crash gracefully without data loss, along with a host of better approaches to data storage on disk.
* Development on it virtually ceased a long time ago. Lots of effort around query optimisations for modern architecture, multi-processing etc. etc. have gone in to InnoDB etc. and not in to MyISAM.
The only advantage MyISAM had over InnoDB for a while was lack of FULL TEXT column support in InnoDB, but that was added in version 5.6, which went RC about 5 years ago or so.
I can't imagine a single person who knows anything about MySQL considering that change.
You also indicated that the engines start out fine, but performance dropped over time, and that you were deleting data older than a certain length of time. That makes me think a few things:
1) Your tables were getting badly fragmented. By constantly deleting data older than a certain age, you were forcing reads and writes to be all over the place. The impact is worse if you're not using partitioned tables. Which leads into..
2) Not using MySQL native table partitioning. This introduces some major advantages with queries, allowing more parallelisation of various actions underneath it. It also has the advantage of limiting the scope of any operation, particularly index updates (as I understand it, index updates on a partitioned table only end up modifying the index for a single partition, rather than having to modify an index for the entire table).
2. We didn't need transactions - if the producer would fail while it was executing the REPLACE statements, we 'd start it over and it wouldn't be a problem (idempotency)
3. We didn't care for crash recovery either -- if aything would go wrong, we 'd rebuild those tables (we only cared for 2 weeks or so worth of rows, rebuilding them wouldn't take long).
I think you ignore that, despite MyISAM's deficiencies, it's really fast if you don't care for the aforementioned properties/warranties provided by more modern engines. And it was -- for our dataset, it was almost twice as fast as InnoDB.
We have been running mySQL in production since release 3.x; we moved to it from mSQL. It may not mean much, but we know it mySQL well, at least some of our folks do.
As I said in another reply, we didn't use native table partitioning because we didn't get the expected benefits in a different use-case/dataset, but we certainly should have considered it.
Thank you for the suggestions though :)