The other day, some of our users reported that saving data had become extremely slow. In some cases, forms were taking up to 15 seconds to submit.
Not good. Not good at all.
MyISAM locks the table.
While looking at MySQL Workbench, I could see that a number of UPDATE and INSERT queries were queuing up. Furthermore, I could see that these queries were all destined for a large MyISAM table that we had.
The table in question contained a large amount of data, most of which was indiscriminately dumped into a TEXT column that had a FULLTEXT index on it.
This data was basically a copy of everything that users were submitting.
Originally, the system imported this data into the table at certain times during the day.
However, for some reason or another, the developer before me had decided to scrap this design and update the table in real-time instead.
Read locks.
The problem wasn’t apparent until the SELECT queries on the table became more complex and the data grew in size over time.
SELECT statements were taking longer to complete, and these SELECT statements were acquiring a READ LOCK on the table.
In case you didn’t already know, MyISAM locking works like so:
- SELECT operations place a READ LOCK on the entire table.
- INSERT, UPDATE and DELETE queries place a WRITE LOCK on the entire table.
This is opposed to InnoDB, which only locks the row.
Multiple READ LOCKS can exist on a MyISAM table at the same time. This means that several SELECT queries can run at the same time.
Write locks.
However, a WRITE LOCK can only be placed on a MyISAM table if there are no active READ LOCKS or WRITE LOCKS.
This means that an INSERT, UPDATE, ALTER TABLE or DELETE query will have to wait and queue if a READ LOCK or another WRITE LOCK is already in place.
Essentially, this means that a WRITE LOCK can only exist by itself.
In our case, this led to the following situation:
- User A was running a lengthy SELECT query on the table.
- User B and User C were trying to run UPDATE queries on the table.
Because the SELECT query acquired a READ LOCK on the table, the queries from User B and User C had to queue up and wait.
As a result, one user was able to grind the system to a halt.
The fix.
Although I did not have the time to do a complete design overhaul, I was able to solve the slowness issue by creating a new InnoDB table.
All INSERT and UPDATE queries were directed towards this new InnoDB table, which was basically a replica of the large MyISAM table.
Because it was using the InnoDB engine, INSERT and UPDATE queries completed almost immediately.
Then, at midnight, outside of normal office hours, I imported all of the new data over.
This was feasible in our case because we were only using the table to store historic data.
Conclusion.
Having frequent reads and writes on the same MyISAM table can be a very bad idea.
If your table requires a lot of INSERT and UPDATE queries, then you might need to rethink your design.