To Nha Notes | March 26, 2025, 2:52 p.m.
Yes, based on MySQL performance tuning best practices, the following parameters can help mitigate performance impacts when deleting data by dropping a table partition:
Purpose: Controls the number of background threads responsible for purging undo logs.
Impact: Dropping large partitions generates undo logs, which can slow down performance. Increasing innodb_purge_threads (e.g., SET GLOBAL innodb_purge_threads = 4;) helps speed up the cleanup process.
Purpose: Defines the I/O capacity of the storage system.
Impact: Dropping a partition may create a spike in I/O load. Adjusting this parameter ensures that background tasks (such as purge and flushing) do not overwhelm the disk.
Purpose: Helps speed up lookups for frequently accessed indexes.
Impact: If enabled, it can reduce performance overhead when dropping partitions by optimizing index operations.
Purpose: Defines the size of InnoDB redo logs.
Impact: If partitions contain large amounts of data, increasing the log file size can improve write performance and reduce bottlenecks.
Purpose: Controls the frequency of flushing logs to disk.
Impact: Setting it to 2 (instead of the default 1) can reduce disk I/O overhead when deleting or dropping partitions.
Would you like specific configuration examples for these settings in your MySQL environment?
When you run:
SHOW ENGINE INNODB STATUS\G;
and see an output like:
History list length 3672
it indicates that there are 3,672 unpurged undo log entries waiting to be cleared by InnoDB’s purge process. This metric is a key indicator of how efficiently the background purge operations are keeping up with your database’s DML activity.
A moderately high history list length, such as 3672, can suggest that purge operations may be lagging, potentially leading to increased disk I/O and CPU usage. This is a signal to monitor your system performance and consider tuning parameters like innodb_purge_threads if you consistently observe similar values—especially under heavy write/delete workloads—to ensure that undo logs are purged promptly and system performance remains optimal.
https://releem.com/docs/mysql-performance-tuning/innodb_purge_threads