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,TABLE_SCHEMA TABLE_NAME,as DbName ,TABLE_NAME as TableName ,ENGINE as Engine FROM information_schema.TABLES WHERE ENGINE = ENGINE='MyISAM' AND TABLE_SCHEMA NOT IN('mysql','information_schema','performance_schema'); "

ListGenerate commands to swap table engine infrom textMyISAM file:to InnoDB

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

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

Changing Table Engine

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