MySQL Optimisation and Performance
====================================================================================
MySQL Optimisation
====================================================================================
MySQL Tuner Script
wget -O mysqltuner.pl mysqltuner.pl && perl mysqltuner.pl ------------------------------------------------------------------------------------------------------------------------------------------------
ImplementingCheck VariableMySQL Changesdefault config files
/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"/etc/my.cnf------------------------------------------------------------------------------------------------------------------------------------------------
MySQL Variables
------------------------------------------------------------------------------------------------------------------------------------------------
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');"------------------------------------------------------------------------------------------------------------------------------------------------
Deadlocks
A deadlock is a situation where two or more transactions are unable to proceed because each one is waiting for a lock held by the other transaction. This creates a cycle of dependencies that cannot be resolved without external intervention.
------------------------------------------------------------------------------------------------------------------------------------------------