Is there a way to have mariadb check tables in the background and not lock the tables after a crash? We have a number of fairly large databases and suffered a hardware failure that resulted in most of them checking tables on a restart which unfortunately blocked access to the databases for hours.

I get that a check is a good thing, however of all the databases, only one ended up needing to be fixed.

Ta. Peter.

  • There is no background check. /etc/mysql/debian-start runs mysqlcheck with --all-databases --fast --silent. Use InnoDB which is fault tolerant. Ensure to give it a decent innodb_buffer_pool_size, defaults are usually too small. – danblack Feb 18 '19 at 22:06
  • What sort of buffer_pool_size would you recommend (or is that a "how long is the piece of string" question)? – Peter Nunn Feb 26 '19 at 4:52
  • Look at the SHOW GLOBAL STATUS LIKE 'innodb_buffer_pool% and see how much is used and how much churn there is. At 100% used with a small amount of churn is ideal. – danblack Feb 26 '19 at 5:10
  • innodb_buffer_pool_size should be set to 70% of available RAM when using only InnoDB. – Rick James Mar 4 '19 at 0:04

Avoid the problem by switching your table from MyISAM to InnoDB.

  • They already are InnoDB. – Peter Nunn Feb 26 '19 at 4:51

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.