Optimisation

====================================================================================

MySQL Optimisation

====================================================================================

MySQL Tuner Script

wget -O mysqltuner.pl mysqltuner.pl && perl mysqltuner.pl 

------------------------------------------------------------------------------------------------------------------------------------------------

Implementing Variable Changes

/etc/my.cnf

------------------------------------------------------------------------------------------------------------------------------------------------

MyISAM v InnoDB

InnoDB has row-level locking. MyISAM only has full table-level locking. 

Check table engine: 

mysql -e "SELECT TABLE_SCHEMA as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');"

Generate commands to swap table engine from MyISAM to InnoDB

mysql -e "SELECT CONCAT('ALTER TABLE \`', TABLE_SCHEMA,'\`.\`',TABLE_NAME, '\` ENGINE = InnoDB;') FROM information_schema.TABLES WHERE ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema');"

------------------------------------------------------------------------------------------------------------------------------------------------

Changing Table Engine

------------------------------------------------------------------------------------------------------------------------------------------------