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.

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

Check for deadlocks:

SHOW ENGINE INNODB STATUS;
SHOW STATUS LIKE 'Table_locks_%';
  • Table_locks_immediate: The number of times a table lock was acquired immediately.
  • Table_locks_waited: The number of times a table lock couldn't be acquired immediately and had to wait.

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

Deadlock logging

This will cause MySQL to log all deadlock errors to the error log, which can then be examined for more details.

SET GLOBAL innodb_print_all_deadlocks = 1;

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

MySQL Monitoring software such as NewRelic can provide further information on deadlocks and general MySQL performance issues.

Resolving Deadlocks

Once identified, you can resolve deadlocks by:

  • Rewriting Queries: Optimize queries to reduce lock contention.
  • Using Consistent Locking Order: Ensure all transactions acquire locks in a consistent order.
  • Adding Indexes: Improve query performance and reduce the duration of locks.
  • Reducing Transaction Size: Break large transactions into smaller ones to reduce lock time.
  • Implementing Retries: Add retry logic in your application to handle deadlocks gracefully.

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

Back to top