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.

Bill Karwin
  • 16,963
  • 3
  • 31
  • 45
basedrum1
  • 21
  • 5

1 Answers1

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.

basedrum1
  • 21
  • 5