Any ideas where to start finding the problem and resolve it?
[Table needs repair too often]
Hey guys, I manage a VPS for a client where the DB performance tanks every couple of weeks. A repair fixes the issue but results in 20-30 minutes of downtime due to a locked MyISAM table. The table is small but very active with inserts and deletes. It also has a good amount of indexes.
Any ideas where to start finding the problem and resolve it?
Replies
I'm using MySQL 5.7 GA. I considered trying that. Since this has been a lingering problem, I want to fully understand the issue, then take corrective measures that I know will resolve the issue.
Good plan. 🙂
Why not convert the tables to innodb? Will prevent the repair from blocking all the table and instead block only by row 1 at a time
Good idea! At least that's what I thought when I had it. :) That table gets so many reads that InnoDB can't keep up. We tried it. We'd have to scale if we convert to InnoDB, which is certainly an option but I want to exhaust other options first.
How big is the table? How often is it written to and deleted?
File size is 123M. 330k rows. How would you go about measuring write and delete metrics?
Larry Kagan I wasn’t wondering about actual metrics, was just looking for “a lot” or “not very often.”
What I wanted to understand is if this table is of the type where records are created, used, then deleted on a very frequent basis, or if it is mostly read from and occasionally updated or a new recorded added?
The reason I asked about file size is to understand if you could move into memory, either MEMORY tables or Redis or similar.
What is the table used for, and why do you need so many indexes? Is the table being corrupted or the indexes or both, or can you tell?
Also, where is is hosted? Do you have full control of the server or is it managed?
I see. I tried (apparently poorly) to get the point across that it's very active with inserts, deletes, updates. In other words 'a lot'.
I didn't build the application so I don't know the need for all the indexes.
The table is being used for inventory which changes often. Memory-based storage isn't an option for inventory.
I haven't found if the table or the index get corrupt yet.
It's a DO droplet that I have full control of.
Larry Kagan Gotcha. That clarifies the use case.
I wonder if the app deletes records it could instead mark for reuse?
Which MySQL are you using?
This is really outside my expertise, but I wonder if switching to Percona could help?
https://www.percona.com
Do you know which queries are causing these issues? As another said convert to InnoDB if possible. Also install MySQLTuner to see if it picks anything up. Can you share the my.cnf?
No idea which queries are causing the issues. I don’t believe any specific queries are to blame. The slow query log will show some queries that it didn’t show the day before. I replied to the InnoDB comment with a reason that’s a last resort. I did test it again yesterday with InnoDB with a > 3x slow down in select queries. I found out about MySQLTuner yesterday. Wasn’t sure if the project was still active. I’ll try it out. I’ll get you the config later in the day (I’m still in bed). Thanks for trying to help.
Larry I'd start with trying to find out which queries are causing the issues by logging all queries temporarily, can also use the SAVEQUERIES variable in wp-config.php or New Relic. I'd say at this point you need data!
Mike Andreasen Thanks again. I’ve done that. The queries that indicate a slow down are inconsistent. I’ve already optimized the slowest queries. The problem is deeper than queries. I’m thinking at this point there’s an issue with the indexes. Unfortunately, I think I have to wait for another slow down to see if the optimizer is skipping the indexes, then figure out why. It seems that the high turnover in data along with the relatively large number of indexes is causing the optimizer to slip indexes. InnoDB would likely resolve this, but again, that’s a last resort. Like you said, we need more data and for that, I have to wait for another slow down.
MySQL config is here (expires in a week): https://pastebin.com/7FeJ2yZ5
I ran MySQLTuner and adjusted the config to match the suggested settings.
