MySQL, repair a non-broken table

MySQL

Whenever a table crashes, MySQL returns as error: Table xxx crashed. Please repair the table.

Now I was wondering, what will happen if I repair a table that is healthy? Will it be ignored? Can I damage the table?

Reason: In the past days different tables have crashed and I'm considering issuing a repair on all tables as preventive measure.

Best Answer

Prevention: Use ENGINE=InnoDB instead of MyISAM. Innodb is self-repairing.

Damage: No. REPAIR TABLE is safe. However, the standard practice is first do CHECK TABLE. Then, if that says to, run REPAIR TABLE.

SUGGESTIONS

If the Server is dedicated to doing only MySQL, please change the following in /etc/my.cnf:

[mysqld]
innodb_open_files=1000
innodb_flush_method=O_DIRECT
innodb_buffer_pool_size=768M
innodb_log_file_size=192M

If the server is at least dual-core, add these

innodb_buffer_pool_instances=2
innodb_read_io_threads=16
innodb_write_io_threads=16
innodb_io_capacity=2000

Next, login to mysql run SET GLOBAL innodb_fast_shutdown = 0;

Next, run the following in the OS

cd /var/lib/mysql
service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile1 ib_logfile1.bak
service mysql start

Give it a Try !!!

UPDATE 2012-12-31 08:30 EDT

From your last comment

It stopped climbing around 1 gb. I removed unused databases and it just seems mysql 5.5 stores a lot of data in memory as this didn't happen in 5.0. Did mysql change a lot?

Yes, MySQL has changed a lot. In fact, there are many cases where upgrading from MySQL 5.0 to MySQL 5.5 and resulted in performance degradation. InnoDB 5.5 is now equipped to do hyperthreading and multicore engagement.

Percona actually tested this out awhile ago.

Please read me past posts on this subject

Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5
Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap)
Oct 05, 2011 : Query runs a long time in some newer MySQL versions
Sep 20, 2011 : Multi cores and MySQL Performance
Jun 19, 2011 : How do I properly perform a MySQL bake-off?

I also wrote about this in ServerFault and StackOverflow

Feb 22, 2012 : https://serverfault.com/questions/362388/mysql-5-1-vs-mysql-5-5-5-1-twice-as-fast/362706#362706
Feb 08, 2012 : https://stackoverflow.com/questions/9202825/mysql-5-5-perfomance/9202860#9202860

MySQL: Can’t find record in tablename

You can try to use the REPAIR TABLE myTable USE_FRM

"Probably" your .MYI file is missing or have corrupted headers

This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file

However before proceed check the documentation of your MySQL 5.1 version and make a backup copy of your .MYI,.frm, and .MYD file in order to avoid loss of data

Best Answer

Related Solutions

Mysql slowly uses memory until it starts to use swap

SUGGESTIONS

UPDATE 2012-12-31 08:30 EDT

MySQL: Can’t find record in tablename

Related Question