httpwebwitch - 6:13 pm on Sep 1, 2010 (gmt 0)
Thanks @whoisgregg & @1script,
Some reasons why InnoDB seemed like the right solution:
- SELECTs are all very simple single row queries, no joins, no subselects, no full text or "LIKE" queries. There are indexes on all the pertinent columns, and they run extremely fast - when they're not locked up.
- There are an immense % of writes going on, and in my SQL Admin panel I can see that many, sometimes hundreds, of threads are waiting with a status of "LOCKED".
- there are occasionally some COUNT(*) queries, but these are in the minority and have never been a bottleneck.
Here are the results of some initial experiments.
First, I took a dumped backup of the live database and put it in a sandbox, so I could do these things on a database with the same size
ALTER TABLE table_name ENGINE = InnoDB
That one query ran for over 45 minutes, and still wasn't done when I killed the process. My downtime curfew is 30 minutes; any solution that takes the service down for longer than that isn't acceptable.
Then I tried creating a new empty InnoDB table, and dumping the one into the other with INSERT INTO ... SELECT ...
That also ran for about 30 minutes before I killed it. At the time I killed the process, the table was still only 1/3 full
If this was done "hot" on the live db, there would be some pretty serious synchronization problems at the end. With ~50 queries per second over more than 30 minutes, it would be painful to clean up all the dangling messages when the switch is made to the new InnoDB table. Downtime is the only option, and this method exceeded my downtime limitation.
-- so --
I ended up changing the application logic. The old table is there, and the new table too. As users interact with the app, their data is being moved from the old one to the new one. It's a gradual migration, seamless from the user's experience, requiring no downtime at all.
The old MyISAM table gets: SELECT, UPDATE, DELETE.
The new InnoDB table gets: SELECT, UPDATE, DELETE, INSERT.
Already, the size of the old MyISAM table is shrinking, and the InnoDB table is growing. I still see locked threads when there's a DELETE performed on the MyISAM table, but overall the performance has improved.