Table of contents
MySQL Performance Tuning
$ mysqladmin debug - dumps some information about locks in use, used memory and query usage to the MySQL log file. This may help solve some problems. This command also provides some useful information even if you haven’t compiled MySQL for debugging!
MySQL> SHOW ENGINE INNODB STATUS\G;
unix# mysqladmin processlist status
Sometimes detected by unable to open foo.myi file errors in logs
myisamchk path to something.MYI - check and repair the table file
As described in manual myisamchk must be never used when mysqld is also running.
Note that even myisamchk message contains reference to this issue:
warning: 1 clients is using or hasn’t closed the table properly
A “client using” it in this case usually means mysqld itself which is running.
Please confirm if you have ran myisamchk after shutting down mysqld. If you had both running at the same time this is not a bug, but expected behaviour.
On the contrast mysqlcheck can be used with running mysqld as it sends CHECK or REPAIR statements to mysqld.
mysqlcheck -v -uusername -p -A dbname tablename
A corrupt table can also be seen by trying to do a describe on the table. If it fails: mysql> repair table <tablename>; can be tried Further reading at dev.mysql.comslave status \G; stop slave start slave ; show slave status \G; mysql> show master logs; - checks what the log files are called for the replication. +
mysql> ALTER DATABASE problem_db CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 1 row affected (0.05 sec) mysql> ALTER TABLE problem_db.problem_table CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci; Query OK, 177 rows affected (0.68 sec) Records: 177 Duplicates: 0 Warnings: 0