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

innodb_buffer_pool_size Specifies the size of the memory buffer used by InnoDB to cache data and indexes. Setting this appropriately can significantly improve read/write performance for InnoDB tables.
query_cache_size Controls the size of the query cache, which stores results of SELECT queries to speed up subsequent identical queries. Effective use depends on query patterns and workload.
key_buffer_size Sets the size of the buffer used for index blocks for MyISAM tables. Adjusting this can enhance performance for MyISAM-based applications.
max_connections Limits the number of simultaneous client connections allowed to the MySQL server. Properly setting this prevents resource exhaustion and maintains server stability.

tmp_table_size =

max_heap_table_size = 

 Define the maximum size of temporary tables created in memory or on disk. Optimizing these can reduce disk I/O and improve query performance.

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

MyISAM v InnoDB

InnoDB has row-level locking. MyISAM only has full table-level locking. 

    InnoDB:
      Default storage engine in MySQL. Supports ACID-compliant transactions. Provides row-level locking and foreign key constraints. Uses a clustered index for primary key storage. MyISAM:
        Older storage engine, primarily used before InnoDB became the default. Provides table-level locking. Faster for read-heavy operations. Does not support transactions or foreign keys.

         

        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