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
  • 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
  • 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
0

Identify all corrupted tables using myisamchk

# myisamchk /var/lib/mysql/bugs/*.MYI >> /tmp/myisamchk_log.txt

myisamchk: error: Wrong bytesec: 0-0-0 at linkstart: 18361936
MyISAM-table 'attach_data.MYI' is corrupted
Fix it using switch "-r" or "-o"
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'groups.MYI' is usable but should be fixed
myisamchk: warning: 1 client is using or hasn't closed the table properly
MyISAM-table 'profiles.MYI' is usable but should be fixed

When you redirect the output of myisamchk to a temporary file, it will display only the corrupted table names on the screen. The /tmp/myisamchk_log.txt file will contain information about all the tables including the good ones, as shown below:

Checking MyISAM file: user_group_map.MYI
Data records:     182   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1

Repair the corrupted table using myisamchk

Execute the myisamchk as shown below, with -r option to repair the corrupted tables identified in the above step.

# myisamchk -r profiles.MYI

- recovering (with sort) MyISAM-table 'profiles.MYI'
Data records: 80
- Fixing index 1
- Fixing index 2

You can also read reference 1 and reference 2

Improve this answer

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.