0

Will CHECK TABLE on large InnoDb table causing issue with blocking of online transactions? Assuming the table size is 5GB.

nikkida
  • 1

1 Answers1

1

According to the MySQL BOL, CHECK TABLE blocks other threads on the table.

When running CHECK TABLE on large InnoDB tables, other threads may be blocked during CHECK TABLE execution. To avoid timeouts, the semaphore wait threshold (600 seconds) is extended by 2 hours (7200 seconds) for CHECK TABLE operations. If InnoDB detects semaphore waits of 240 seconds or more, it starts printing InnoDB monitor output to the error log. If a lock request extends beyond the semaphore wait threshold, InnoDB aborts the process. To avoid the possibility of a semaphore wait timeout entirely, run CHECK TABLE QUICK instead of CHECK TABLE.

Thanks!

Rajesh Ranjan
  • 1,941
  • 3
  • 16
  • 34