Description:
On a MySQL server that is not serving any traffic, the InnoDB history list length (trx_rseg_history_len) remains excessively high, indicating that the purge process is not effectively reducing the backlog of undo logs. This issue persists despite the server's idle state.
Observed Metrics:
+-----------------------------------+-----------+
| NAME | COUNT |
+-----------------------------------+-----------+
| trx_rseg_history_len | 868936356 |
| purge_del_mark_records | 15014713 |
| purge_upd_exist_or_extern_records | 1750002 |
| purge_invoked | 57622 |
| purge_undo_log_pages | 17286600 |
| purge_dml_delay_usec | 0 |
| purge_stop_count | 0 |
| purge_resume_count | 0 |
| purge_truncate_history_count | 1800 |
| purge_truncate_history_usec | 12238971 |
+-----------------------------------+-----------+
10 rows in set (0.00 sec)
-- After almost ~24 hours later
+-----------------------------------+-----------+
| NAME | COUNT |
+-----------------------------------+-----------+
| trx_rseg_history_len | 827172822 |
| purge_del_mark_records | 42465825 |
| purge_upd_exist_or_extern_records | 10233448 |
| purge_invoked | 197308 |
| purge_undo_log_pages | 59192400 |
| purge_dml_delay_usec | 0 |
| purge_stop_count | 0 |
| purge_resume_count | 0 |
| purge_truncate_history_count | 6166 |
| purge_truncate_history_usec | 44825663 |
+-----------------------------------+-----------+
10 rows in set (0.00 sec)
Significant fragmentation observed, with substantial free extents in undo tablespaces.
+------------+------------+-----------+-----------------+--------------+---------------+-------------+--------------+--------------+-----------------+--------------+--------+
| FILE_ID | FILE_NAME | FILE_TYPE | TABLESPACE_NAME | FREE_EXTENTS | TOTAL_EXTENTS | EXTENT_SIZE | INITIAL_SIZE | MAXIMUM_SIZE | AUTOEXTEND_SIZE | DATA_FREE | STATUS |
+------------+------------+-----------+-----------------+--------------+---------------+-------------+--------------+--------------+-----------------+--------------+--------+
| 4294962707 | ./undo_001 | UNDO LOG | innodb_undo_001 | 14347 | 592864 | 1048576 | 0 | NULL | 16777216 | 11953766400 | NORMAL |
| 4294962325 | ./undo_002 | UNDO LOG | innodb_undo_002 | 230573 | 233936 | 1048576 | 245282897920 | NULL | 16777216 | 240556965888 | NORMAL |
+------------+------------+-----------+-----------------+--------------+---------------+-------------+--------------+--------------+-----------------+--------------+--------+
2 rows in set (0.01 sec)
--No Transactions
mysql> SELECT trx_id, trx_started, trx_state, trx_tables_in_use, trx_rows_locked FROM information_schema.INNODB_TRX;
Empty set (0.00 sec)
How to repeat:
Can't repeat
Suggested fix:
Expected Behaviour:
The InnoDB purge process should efficiently manage the history list length, ensuring it remains within acceptable limits.
Actual Behavior:
The history list length remains elevated, indicating that the purge process is not effectively reducing the backlog of undo logs, even in the absence of active transactions.
