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.
