Skip to main content

MySQL Optimisation, Performance, and Logging

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

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

=isainnodb_buffer_pool_size storageSpecifies areathe insize of the memory wherebuffer MySQLused willby keepInnoDB informationto aboutcache data and indexes. Setting this appropriately can significantly improve read/write performance for InnoDB tables. query_cache_size Controls the structuresize of yourthe tablesquery likecache, columns,which indexesstores etc.results Thisof variableSELECT is usedqueries to speed up subsequent identical queries. Effective use depends on query patterns and workload. key_buffer_size Sets the processsize of queryingthe buffer used for index blocks for MyISAM tables. Adjusting this can enhance performance for MyISAM-based applications. max_connections Limits the databasenumber becauseof itsimultaneous savesclient connections allowed to the MySQL fromserver. havingProperly tosetting repeatedlythis readprevents theseresource structuresexhaustion fromand disk.maintains server stability.

tmp_table_size =

max_heap_table_size = 

innodb_buffer_pool_size

=Define thisthe ismaximum responsiblesize forof cachingtemporary datatables and index pagescreated in memory toor on disk. Optimizing these can reduce disk I/O and 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