Mysql – I have a DB that is locking up from numerous updates to a table: Should I convert it from MyISAM to InnoDB

innodblockingMySQLperformance

FYI: I don't believe this is a duplicate question, I've looked and most are general questions with vague answers. See more specifics below:

Currently a database I am in charge of locks up every once in awhile to a state that becomes unresponsive, and I need to manually restart the MySQL server to make it become responsive again (Yuck). I believe the culprit is an UPDATE query that is being run, which is being called every 1 minute for each active user in the system.

The query is something like this:

UPDATE logging_table SET log_time = log_time + 60 WHERE id = X;

The table is not small (approx 0.5 million records) and is both updated and selected from quite often; it is a "Main operations" type table that is related to quite a few other tables (No FK's, just relations in the PHP framework we are using). The monitoring app I use shows the above query consuming about 40% of the overall database time in our app, followed by a SELECT query that gets information from that table.

Currently the table is MyISAM as it worked fine for a lower number of users, but as the active users participate during peak times of the day, the database locks up.

Does this sound like a table I should convert over to InnoDB (The rest of the tables were setup as MyISAM by default)? I should note that it is not feasible at this point to split this table so that frequent updates can happen on another table.

Thanks for any suggestions.

Best Answer

In almost every use case, InnoDB is preferred over MyISAM. So, yes.

To make sure the indexes, etc are converted correctly, see if anything in MySQL to InnoDB checklist needs to be addressed. Note that key_buffer_size should be decreased and innodb_buffer_pool_size increased.

In MyISAM, an UPDATE blocks all other operations on the table. In InnoDB, it blocks only the one row (assuming the WHERE clause restricts the scope to a single row).

It is ok to have a mixture of MyISAM tables and InnoDB tables.

Related Question