2

Why would MySQL be using Repair with 1 thread to rebuild the indexes on a table (1 primary key, 2 billion rows) when I have myisam_repair_threads set to 4?

2

According to the MySQL Documentation on myisam_repair_threads

If this value is greater than 1, MyISAM table indexes are created in parallel (each index in its own thread) during the Repair by sorting process. The default value is 1.

Note Multi-threaded repair is still beta-quality code.

As it says, it is in beta. Allowing for setting the value is evidently not implemented.

UPDATE 2013-05-13 22:24 EDT

Given the table you mentioned in the comment, here is what you could do to see what just what MySQL does to handles the situation. Let's say the table is mydb.mytable and uses the MyISAM storage engine. This is what mechanically has to happen:

use mydb
CREATE TABLE mytable_new LIKE mytable;
ALTER TABLE mytable_new DISABLE KEYS;
INSERT INTO mytable_new SELECT * FROM mytable;
ALTER TABLE mytable_new ENABLE KEYS;
ALTER TABLE mytable_new RENAME mytable_old, mytable_new RENAME mytable;
DROP TABLE mytable_old;

This is as granular as you can get as far as the repair threads go. Each unique index and the PRIMARY KEY is loaded during the INSERT INTO mytable_new SELECT * FROM mytable phase. All the remaining nonunique indexes are built during the ALTER TABLE mytable_new ENABLE KEYS; phase.

Since Oracle is concentrating on the development of InnoDB, I double if they will get around to implementing myisam_repair_threads as a number beyond 1. Why? Think about. Index pages are written sequentially with myisam_repair_threads set to 1. If myisam_repair_threads was greater 1, each set of index pages per index would be written in an interleaved fashion. This would make the index fragmented. Of course, the solution would be to write each index's set of sequential pages in eight(8) different places. They would eventually have to be written contiguously into one common index space (in the case of the MyISAM table, that would be the .MYI file). Until Oracle comes up with a way to create nonunique indexes in parallel, you will have to settle for one thread.

3
  • Also, the question only mentions one index on the table ("1 primary key") so that would just be one thread in any case. May 13 '13 at 16:01
  • OK, though I have another table with 2 billion rows and 8 indexes, and that one uses only 1 thread as well.
    – Alasdair
    May 14 '13 at 2:57
  • It does seem that the reason was because that table only had 1 index. When I did it on a table with 8 indexes then there were 8 threads (possibly a coincidence).
    – Alasdair
    May 15 '13 at 16:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.