In this post we’re going to look at how changing MySQL variable mysql_repair_threads for betterment of a repair table command became root cause of an instance reboot. Also how repair with n threads message tracks down to this.
MyISAM is notorious for table crashes and always need a table repair as a quick remedy.
Table 'myisam_table' is marked as crashed and should be repaired.
After seeing above error we quickly jump on to one of the following
repair table myisam_table myisamchk -r -q myisam_table
Ofcourse there are more options but that’s not something we’re talking here.
Once this become regular and/or table is large, we look forward to speedup the operation. Then stands out one variable that comes as a ray of hope: mysql_repair_threads
As MySQL documentation says “for value greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process.”
“woohoo… let’s repair in parallel threads”
and people are quickly tempted to use it ignoring an important line right below it: “Multi-threaded repair is still beta-quality code”.
You might see “Repair with N threads” in processlist output but is it actually helping?
mysql> show processlist; +-----------+------------------+-------------------+---------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----------+------------------+-------------------+---------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+ ... | 224316896 | root | localhost | my_db | Query | 85 | Repair with 2 threads | repair table XXX | ...
This is what happened recently when someone issued repair table on one of the crashed table and the query wouldn’t finish even after an hour! As you can note processlist message of Repair with 2 threads is our hint of the change in myisam_repair_threads value.
Repair operation went on for more than estimated time while the table in question was not even 50M and system was well-fed with CPU & RAM.
mysql> show table status like 'XXX'\G
*************************** 1. row ***************************
Name: XXX
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 1872
Avg_row_length: 13147
Data_length: 32025064
Max_data_length: 281474976710655
Index_length: 145408
Data_free: 7413408
Auto_increment: 28173
...
Finally the repair was killed, but it remained in Killed state for more than the anticipated time:
mysql> show processlist; +-----------+------------------+-------------------+---------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +-----------+------------------+-------------------+---------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+ ... | 224316896 | root | localhost | my_db | Killed | 4009 | Repair with 2 threads | repair table XXX | ...
(That’s too much)
Note that in this case, the only thing odd I found is “myisam_repair_threads”:
mysql> show global variables like '%repair%'; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | myisam_repair_threads | 32 | +-----------------------+-------+ 1 row in set (0.00 sec)
Because the server needed a quick resolution, it was concluded to reboot MySQL (as it was slave and feasible).
Changed my.cnf to set myisam_repair_threads to default (1) and rebooted MySQL.
Later the repair table command then finished in 6 sec. (Much better with single thread, isn’t it?)
mysql> repair table XXX; +---------------------------+--------+----------+----------+ | Table | Op | Msg_type | Msg_text | +---------------------------+--------+----------+----------+ | my_db.XXX | repair | status | OK | +---------------------------+--------+----------+----------+ 1 row in set (6.58 sec)
“Luckily” it was slave that faced the issue!
Now, myisam_repair_threads is The Only beta quality variable listed on MySQL documentation. The variable myisam_repair_threads is in beta quality since ages and the latest MySQL 5.7 is no good for that too.
There is already a bug report conveying the risk. I looked at MariaDB & Percona. Neither mariaDB mention “beta” around this feature (not sure if they’ve a fix!!) nor I see any mention of this in Percona MySQL documentation. I’d rather remove it than putting it in beta for so long and risking production instances!
Anyways, time for conclusion?
Do not increase myisam_repair_threads from it’s sensible default 1. Do not use it at all. Just forget it!