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.<\/p>\n\n\n\n
MyISAM is notorious for table crashes and always need a table repair as a quick remedy.<\/p>\n\n\n\n
\nTable 'myisam_table' is marked as crashed and should be repaired.\n<\/pre>\n<\/blockquote>\n\n\n\nAfter seeing above error we quickly jump on to one of the following<\/p>\n\n\n\n
\nrepair table myisam_table\n myisamchk -r -q myisam_table\n<\/pre>\n<\/blockquote>\n\n\n\nOfcourse there are more options but that’s not something we’re talking here.<\/p>\n\n\n\n
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.”<\/i><\/p>\n\n\n\n\n\n\n\n“woohoo… let’s repair in parallel threads”<\/p>\n\n\n\n
and people are quickly tempted to use it ignoring an important line right below it: “Multi-threaded repair is still beta-quality code”.<\/b><\/i><\/p>\n\n\n\n
You might see “Repair with N threads” in processlist output but is it actually helping?<\/p>\n\n\n\n
\nmysql> show processlist;\n+-----------+------------------+-------------------+---------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+\n| Id | User | Host | db | Command | Time | State | Info |\n+-----------+------------------+-------------------+---------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+\n...\n| 224316896 | root | localhost | my_db | Query | 85 | Repair with 2 threads | repair table XXX |\n...\n<\/pre>\n<\/blockquote>\n\n\n\nThis 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<\/strong> is our hint of the change in myisam_repair_threads value.<\/p>\n\n\n\n
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.<\/p>\n\n\n\n
\nmysql> show table status like 'XXX'\\G\n*************************** 1. row ***************************\n Name: XXX\n Engine: MyISAM\n Version: 10\n Row_format: Dynamic\n Rows: 1872\n Avg_row_length: 13147\n Data_length: 32025064\nMax_data_length: 281474976710655\n Index_length: 145408\n Data_free: 7413408\n Auto_increment: 28173\n...\n<\/pre>\n<\/blockquote>\n\n\n\nFinally the repair was killed, but it remained in Killed state for more than the anticipated time:<\/p>\n\n\n\n
\nmysql> show processlist;\n+-----------+------------------+-------------------+---------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+\n| Id | User | Host | db | Command | Time | State | Info |\n+-----------+------------------+-------------------+---------+---------+----------+----------------------------------+------------------------------------------------------------------------------------------------------+\n...\n| 224316896 | root | localhost | my_db | Killed | 4009 | Repair with 2 threads | repair table XXX |\n...\n<\/pre>\n<\/blockquote>\n\n\n\n(That’s too much)<\/p>\n\n\n\n
Note that in this case, the only thing odd I found is “myisam_repair_threads”:<\/p>\n\n\n\n
\nmysql> show global variables like '%repair%';\n+-----------------------+-------+\n| Variable_name | Value |\n+-----------------------+-------+\n| myisam_repair_threads | 32 |\n+-----------------------+-------+\n1 row in set (0.00 sec)\n<\/pre>\n<\/blockquote>\n\n\n\nBecause the server needed a quick resolution, it was concluded to reboot MySQL (as it was slave and feasible).<\/p>\n\n\n\n
Changed my.cnf to set myisam_repair_threads to default (1) and rebooted MySQL.<\/p>\n\n\n\n
Later the repair table command then finished in 6 sec. (Much better with single thread, isn’t it?)<\/p>\n\n\n\n
\nmysql> repair table XXX;\n+---------------------------+--------+----------+----------+\n| Table | Op | Msg_type | Msg_text |\n+---------------------------+--------+----------+----------+\n| my_db.XXX | repair | status | OK |\n+---------------------------+--------+----------+----------+\n1 row in set (6.58 sec)\n<\/pre>\n<\/blockquote>\n\n\n\n“Luckily” it was slave that faced the issue!<\/p>\n\n\n\n
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.<\/p>\n\n\n\n