Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 2 down vote accepted

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.

share|improve this answer
    
Also, the question only mentions one index on the table ("1 primary key") so that would just be one thread in any case. –  Michael - sqlbot 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

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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