2

I'm having problems with a large MySQL InnoDB table and I was wondering if anyone here had advice for improving stabililty.

The issue that I'm having is that CHECK TABLE ... FAST will lock the table, preventing any reading, and for a table this large, that means its offline for an hour.

The default Debian install will run CHECK TABLE on everything whenever MySQL starts, and this is causing downtime. For now, I have disabled this functionality, but it makes me a little nervous to me mucking with these kind of system settings.

I have another cronjob that was bulk loading data into the table that was using ALTER TABLE ... DISABLE KEYS and ALTER TABLE ... ENABLE KEYS, and it's possible that this is related, but I haven't been able to confirm that yet.

Has anyone seen situations where CHECK TABLE prevents access to large InnoDB tables?

Sam Minnée
  • 143
  • 1
  • 5

1 Answers1

1

I wrote in ServerFault awhile back to show that you cannot DISABLE KEYS and ENABLE KEYS on an InnoDB table.

CHECK TABLE should always table lock any table MyISAM or InnoDB.

You could create a script called /var/lib/mysql/my_init.sql with the following

CHECK TABLE < whatever table 1 >;
CHECK TABLE < whatever table 2 >;
...

This file should contain whever number of tables you want to CHECK TABLE on.

Now add this line to /etc/my.cnf

init_file=/var/lib/mysql/my_init.sql

Then restart mysql and the custom check table script will run.

BTW I hope you are using innodb_file_per_table. If you are not, please read this URL

Community
  • 1
RolandoMySQLDBA
  • 16,364
  • 3
  • 47
  • 80
  • Yeah, the fact that `CHECK TABLE` locks is my problem! If it locks, why is Debian configured by default to call it whenever MySQL starts? The whole house of cards seems very shaky to me. – Sam Minnée Mar 21 '11 at 22:17