MySQL optimization tips (part 2)

Published on

From my unknown to me reasons I’m a big fan of MySQL database. I like its simplicity, ease of use, and performance. It’s not the greatest, for very long we didn’t have window functions, there are some annoying limitations. It’s still hard to generate reports without dates generation like in PostgreSQL. But it’s getting better and better with every release. In this post, I will share some tips and tricks on how to optimize MySQL 8+ performance.

Find slow queries that need optimization

SELECT schema_name,
       FORMAT_PICO_TIME(total_latency)              AS total_latency,
       exec_count,
       FORMAT_PICO_TIME(total_latency / exec_count) AS latency_per_call,
       QUERY_SAMPLE_TEXT
FROM sys.x$statements_with_runtimes_in_95th_percentile AS t1
         JOIN performance_schema.events_statements_summary_by_digest AS t2 ON t2.DIGEST = t1.digest
WHERE SCHEMA_NAME NOT IN ('mysql', 'performance_schema', 'information_schema', 'sys')
ORDER BY (total_latency / exec_count) DESC
LIMIT 10;

Check InnoDB buffer pool read hit rate

SELECT CONCAT(FORMAT(B.num * 100.0 / A.num, 2), '%') AS Disk_Read_Percentage
FROM (SELECT variable_value AS num
      FROM performance_schema.global_status
      WHERE variable_name = 'Innodb_buffer_pool_read_requests') A,
     (SELECT variable_value AS num
      FROM performance_schema.global_status
      WHERE variable_name = 'Innodb_buffer_pool_reads') B;

This will show you how many reads are served from disk instead of memory. The lower the percentage, the better. A good target is below 1%, very bad is above 5-10%.

In case you have a high percentage of reads from disk, consider increasing the InnoDB buffer pool size to cache more data in memory. You can check the current buffer pool size with:

SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

Output will be in bytes. You can increase it dynamically without restarting the database with:

SET GLOBAL innodb_buffer_pool_size = X;

Recommended value for X is about 70-80% of your total RAM (4GB+) size on a dedicated database server. For smaller instances (like 1GB RAM) you can set it to about 50% of total RAM size.

You can check what is the current size of the buffer pool:

SELECT TABLE_NAME,
       INDEX_NAME,
       COUNT(*)                                                                  AS Pages,
       ROUND(SUM(IF(COMPRESSED_SIZE = 0, 16384, COMPRESSED_SIZE)) / 1024 / 1024) AS 'Total Data (MB)'
FROM information_schema.INNODB_BUFFER_PAGE
WHERE table_name NOT LIKE '`mysql.` %'
GROUP BY TABLE_NAME, INDEX_NAME
ORDER BY 4 DESC, 3 DESC;

Check index selectivity

High selectivity index

SELECT COUNT(DISTINCT email) / COUNT(*) AS selektivity
FROM user;

Low selectivity index

SELECT COUNT(DISTINCT gender) / COUNT(*) AS selektivity
FROM gender;
  • If the selectivity is close to 1, the index is very selective and will be used by the query optimizer.
  • If the selectivity is close to 0, the index is not selective and may not be used by the query optimizer.

Reading explain analyze

Use EXPLAIN ANALYZE to get actual execution statistics of a query, including the number of rows processed and the time taken for each step. This can help identify bottlenecks and areas for optimization.

-> Filter: (`my_table`.email = '[email protected]')  (cost=3794 rows=3511) (actual time=0.109..98.2 rows=2 loops=1)
-> Table scan on my_table  (cost=3794 rows=35107) (actual time=0.102..94.4 rows=37016 loops=1)

Database made a full table scan instead of using an index. It processed 37016 rows to return 2 rows. This is a clear sign that an index on email column would help speed up the query. Cost of the query is 3794 which is quite high for such a simple query. (Higher cost means more resources used). Actual time taken is 98.2 ms which is also quite high for such a simple query.

Quick index creation

By default, creating an index on a large table will lock the table for writes, which can cause downtime for applications that need to write to the table. To avoid this, you can use the following syntax to create an index without locking the table for writes:

ALTER TABLE user
    ADD INDEX idx_email (email),
    ALGORITHM = INPLACE,
    LOCK = NONE;

But there are some limitations:

  • The table must use the InnoDB storage engine.
  • The table must not have any FULLTEXT or SPATIAL indexes.
  • The table must not be a temporary table.

The same trick can be used for adding columns:

ALTER TABLE user
    ADD COLUMN last_login DATETIME NULL,
    ALGORITHM = INPLACE,
    LOCK = NONE;

But there are also limitations:

  • The new column must allow NULL values.
  • The new column must be added at the end of the table.
  • The table must use the InnoDB storage engine.

Hide index before dropping it

Before dropping an index, you can hide it first to see if the query performance is affected. If the performance is not affected, you can safely drop the index. This is useful for identifying unused indexes that can be removed to improve write performance.

ALTER TABLE user
    ALTER INDEX idx_email INVISIBLE;

and to unhide it:

ALTER TABLE user
    ALTER INDEX idx_email VISIBLE;

Conclusion

There is no surprise that most of the optimization tips are related to proper indexing and database configuration. No magic tricks, it’s always the query design, proper indexing, and database configuration that matter the most.

Resources

Most of the tips taken from the following videos:

Both of them are very same/similar regarding the content.