0

I'm attempting to setup slave replication of a large MyISAM table. I login to the master server and perform FLUSH TABLES WITH READ LOCK; SHOW MASTER STATUS and that returns right away with the position and file. I then take a LVM snapshot, and then issue UNLOCK TABLES (note: I do this in the same mysql prompt, I do not exit). As far as I know, this is the correct way to do this.

I then rsync the snapshot to my new system, set the permissions and start up mariadb, almost immediately it says, "Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables" and then I look at the processlist for this server and I see that it is doing a 'Repair by sorting' on this table.

Why is this table being repaired, if the tables were originally flushed? Is it possible that the high number of queries and size of the database means that the operating system has not yet flushed the blocks to disk, because it has a lot of dirty buffers, yet still returns me to the command line for the FLUSH TABLES WITH READ LOCK command?

I would like to be able to avoid the repair table, because it takes a day and half to complete, but I don't understand why there is a file descriptor difference in the MYD making mariadb decide it needs to repair. The only way I can think of to be sure there is no difference would be to shutdown completely the master's mariadb process, and then do the lvmsnapshot, however then I cannot get the results of a SHOW MASTER STATUS to setup the replication.

Can someone please tell me how to solve this (also, please don't tell me to move off of MyISAM, believe me I would if I was allowed to).

I decided to try it a second time, and do a myisamchk --update-state --myisam_sort_buffer_size=8G --key_buffer_size=8G --read_buffer_size=1024M --write_buffer_size=1024M /var/lib/mysql/db/store.MYI to see if I could tell why it thinks it needs to repair, and it says this:

Data records: 365221349   Deleted blocks:  266143
myisamchk: warning: 1 client is using or hasn't closed the table properly
- check file-size
- check record delete-chain
myisamchk: warning: Found 34585232 deleted space in delete link chain. Should be 34630096
myisamchk: error: Found 265707 deleted rows in delete link chain. Should be 266143
myisamchk: error: record delete-link-chain corrupted
- check key delete-chain
- check index reference
- check data record references index: 1
- check record links
34600000

and that counter is increasing, I have no idea where it is trying to get to, but I suspect this will take many hours.

11
  • 1
    Out of curiosity, why are you still using MyISAM for this table? InnoDB has crash recovery, and many other advantages. InnoDB has better performance than MyISAM too. Oct 11, 2022 at 16:08
  • 2
    I've used pt-online-schema-change to perform many types of table alterations with nearly no downtime. Alterations in production, during peak hours, any day of week, any time of day. Oct 11, 2022 at 22:04
  • 1
    Another strategy is to create a replica MySQL Server, do the alteration on the replica, and then do a quick switch so the replica becomes the new primary database server. Oct 11, 2022 at 22:06
  • 1
    I definitely recommend using this tool on smaller, less important tables first, until you become confident with using it. I'd recommend the same advice with any other tool that is new to you. Oct 14, 2022 at 14:13
  • 1
    By the way, using ALTER TABLE also creates a copy table, so you need extra space to use it. Regarding the catching up, pt-online-schema-change keeps the original table and the copy table in sync continuously while it's running, by using triggers to repeat every change to the original table. Oct 14, 2022 at 14:14

1 Answer 1

0

The solution to this problem appears to be related to the high number of writes happening, and the speed which I performed the snapshot immediately after doing the FLUSH TABLES WITH READ LOCK. I tried it several more times, and it kept needing repairs.

When I issued the FLUSH TABLES WITH READ LOCK and then opened another terminal, and issued the age old unix magic trick (one I thought was no longer necessary and just a NOOP now days): sync; sync; sync. Then I did the snapshot. This snapshot worked.

Obviously, MyISM is the problem here, and I received many helpful comments about moving to InnoDB, which clearly needs to happen, and I'm hopeful I can convince the right people that it is what needs to be done.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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