0

I am loading a csv file into a mysql database table that uses MyISAM as storage engine. From the process list I can see that this is accomplished in two phases: 1) reading the csv file and 2) repairing the indexes by sorting. FYI, I have kept the indexing to enabled as it was by defult.

Although I am setting myisam_repair_threads to 2 I think mysql is not taking this into count as show processlist shows only one entry in the process list. Also, it takes a little bit of time interval before starting repairing, why?

Please correct my understanding if I am wrong! Why mysql is repairing the tables with only one thread although I have set it to 2? Is there anything I am getting wrong about repairing strategy? TIA!

Improve this question
4
  • Why REPAIR? Indexes are usually built on the fly. Repairing is only needed after a crash. – Rick James Oct 14 '19 at 23:47
  • @RickJames - if you keep motoring the process list (using show processlist) it will show you the query progress in these two phases that i have mentioned above – mghh Oct 15 '19 at 4:50
  • Reading the csv -- in app code? using LOAD DATA? loading as ENGINE=CSV? – Rick James Oct 15 '19 at 18:28
  • from mysql cli, and yes, using LOAD DATA – mghh Oct 17 '19 at 6:03
0

Read carefully:

MySQL 8.0 Reference Manual / ... / Server System Variables

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.

If your table have 2 indices - then 2 threads will (must) be used.

PS. I'm not sure that you will see this in processlist... this has to be checked.

Improve this answer
5
  • and what about the repairing starting after a bit of pause? any idea? – mghh Oct 9 '19 at 4:33
  • @mghh - How many indexes does the table have? Multiple threads are used only if you multiple indexes. – Rick James Oct 14 '19 at 23:48
  • And that may apply only to "Repair by sorting" (versus using the key_buffer). – Rick James Oct 14 '19 at 23:50
  • @RickJames - the table has 11 indexes, one of which is a composite one – mghh Oct 15 '19 at 4:47
  • @Akina is correct about repairing thread count, but i can't find out the reason behind the repairing being initiated after a bit of interval. – mghh Oct 23 '19 at 4:27

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.