Skip to main content

MySQL Optimisation and Performance

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

MySQL Optimisation

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

MySQL Tuner Script

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

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

Check MySQL default config files
/usr/sbin/mysqld --verbose --help | grep -A 1 "Default options"

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

MySQL Variables

table_definition_cache = is a storage area in memory where MySQL will keep information about the structure of your tables like columns, indexes etc. This variable is used to speed up the process of querying the database because it saves MySQL from having to repeatedly read these structures from disk. 

innodb_buffer_pool_size = this is responsible for caching data and index pages in memory to improve query performance. The size we set this to will control how much of your data is held and accessed in memory instead of from the disk which is much slower when it comes to complex queries. 

innodb_buffer_pool_instances = this variable allows innodb to distribute the buffer pool across multiple instances, each having it's own data and indexes. Each instance runs independently and can be accessed in parallel which brings potential to improve concurrency and performance. 

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

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.

 

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

Back to top