This is the second article of the series dedicated to MySQL trending.
As I wrote before, understanding your workload and seeing the evolution of it over time can help anticipating problems and work on solutions before the breakdown.
This article covers MySQL History List Length also known as HLL.
MySQL History List is related to InnoDB Undo Logs. InnoDB is a multi-version storage engine (MVCC). It keeps information about old versions of changed rows to support transactional features such as concurrency and rollback. This information is stored in undo tablespaces in a data structure called a rollback segment.
This means that you can start a transaction and continue to see a consistent snapshot even if the data changed by other transaction. This behavior is related to the isolation level. By default in MySQL, the transaction isolation is REPEATABLE-READ:
SQL> show global variables like '%isola%'; +-----------------------+-----------------+ | Variable_name | Value | +-----------------------+-----------------+ | transaction_isolation | REPEATABLE-READ | +-----------------------+-----------------+
To provide such isolation, InnoDB needs to keep old versions of rows that are modified until there is still a transaction open.
All those changes are kept in a linked list pointing to the previous version of the same row which itself points to a previous version of the same row, etc… This means that each time a row is updated, within a new transaction, the old version is copied over to the respective rollback-segment with pointer to it.
Each rows have then a 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment. If the row was updated, the undo log record contains the information necessary to rebuild the content of the row before it was updated.
START TRANSACTION; and not yet committed or rolled-back.
On the illustration above, a second transaction (tx 100) insert a record. By default (REPEATABLE_READ), the second row is not visible in trx 99 (TRX_ID for that row is greater than 99).
Now when data is updated, the changes are also kept in the undo update:
And this keep increasing until the undo segments are not purged:
This is a high level illustration on how it works in InnoDB.
The History List Length quantifies the amount of changes (the amount of records containing preview changes).
If a record contains a large amount of versions, retrieving the value might take longer in the oldest transactions.
In the MySQL Manual, we can read: Undo logs in the rollback segment are divided into insert and update undo logs. Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits. Update undo logs are used also in consistent reads, but they can be discarded only after there is no transaction present for which InnoDB has assigned a snapshot that in a consistent read could require the information in the update undo log to build an earlier version of a database row.
Reading those lines, we could understand that if we have a long transaction (even inactive), that has accessed some rows that are not used by any other transaction, that won’t impact the history list… this is not the case!
The metrics are available in the INFORMATION_SCHEMA.INNODB_METRICS table when enabled or in the output of SHOW ENGINE INNODB STATUS\G:
MySQL> select * from INFORMATION_SCHEMA.INNODB_METRICS
where name='trx_rseg_history_len'\G
*************************** 1. row ***************************
NAME: trx_rseg_history_len
SUBSYSTEM: transaction
COUNT: 8319
MAX_COUNT: 92153
MIN_COUNT: 7
AVG_COUNT: NULL
COUNT_RESET: 8319
MAX_COUNT_RESET: 92153
MIN_COUNT_RESET: 7
AVG_COUNT_RESET: NULL
TIME_ENABLED: 2022-05-25 10:23:17
TIME_DISABLED: NULL
TIME_ELAPSED: 135495
TIME_RESET: NULL
STATUS: enabled
TYPE: value
COMMENT: Length of the TRX_RSEG_HISTORY list
MySQL> show engine innodb status\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 2022-05-27 00:01:46 139760858244672 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 43 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 4146 srv_active, 0 srv_shutdown, 76427 srv_idle srv_master_thread log flush and writes: 0 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5954 OS WAIT ARRAY INFO: signal count 60629 RW-shared spins 0, rounds 0, OS waits 0 RW-excl spins 0, rounds 0, OS waits 0 RW-sx spins 0, rounds 0, OS waits 0 Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx ------------ TRANSACTIONS ------------ Trx id counter 903438 Purge done for trx's n:o < 883049 undo n:o < 0 state: running but idle History list length 9746
Trending Graph
Let’s have a look at this graph:
We can see that the History List Lengt (trx_rseg_history_len) is increasing linearly… but the workload is not:
When HLL increases significantly over a period of time, it means that InnoDB is keeping a large amount of old row versions instead of purging them because one or more long-running transaction has not committed or was abandoned without being rolled back.
In MySQL starting a transaction and then performing a simple SELECT starts all this MVCC mechanism.
Daniel Nichter in his book, Efficient MySQL Performance, explains that a normal value for innodb.trx_rseg_history_len is less than 1,000. If it goes over 100,000, this can become problematic and an alert should be sent.
I recommend you to read the chapter MVCC and the Undo Logs, page 276 of Daniel’s book.
Size matters !
Something that is important to know and that is not exposed in MySQL, is that HLL represents an amount of changes, not the size of those changes. So, even less that 1,000 can be problematic if those are full of huge blobs for example.
Let’s have a look again to the History List Length for the next 10 minutes:
We can see that as soon as we stopped a transaction that was kept open (sleeping) all get solved almost immediately !
The workload is sysbench OLTP insert (not using the employees database) and we created a long transaction using the employees database. This long transaction statement was:
MySQL> start transaction; MySQL> select * from employees.titles limit 10; +--------+-----------------+------------+------------+ | emp_no | title | from_date | to_date | +--------+-----------------+------------+------------+ | 10001 | Senior Engineer | 1986-06-26 | 9999-01-01 | | 10002 | Staff | 1996-08-03 | 9999-01-01 | | 10003 | Senior Engineer | 1995-12-03 | 9999-01-01 | | 10004 | Engineer | 1986-12-01 | 1995-12-01 | | 10004 | Senior Engineer | 1995-12-01 | 9999-01-01 | | 10005 | Senior Staff | 1996-09-12 | 9999-01-01 | | 10005 | Staff | 1989-09-12 | 1996-09-12 | | 10006 | Senior Engineer | 1990-08-05 | 9999-01-01 | | 10007 | Senior Staff | 1996-02-11 | 9999-01-01 | | 10007 | Staff | 1989-02-10 | 1996-02-11 | +--------+-----------------+------------+------------+ 10 rows in set (0.0002 sec) MySQL> We did nothing for 10 minutes MySQL> rollback;
The graph bellow represent the same transaction idle for 4mins in the middle of 10 minutes sysbench OLT Read/Write:
What does a large HLL really mean ?
The reason why History List Length increases is that the InnoDB Purge activity is lagging !
The purge thread is responsible for emptying and truncating undo tablespaces (see the manual).
What could be responsible of such lag into the purge process ?
- the write activity is too high and the purge is unable to process as fast
- a long running transaction is blocking the purge and the purge won’t progress until the transaction is finished
We will see later, how we can deal with this, but first, let’s have a look at the performance.
Performance
Even if HLL doesn’t impact directly performance, it might become problematic when a lot of versions of the rows need to be traversed.
Let’s see this behavior with the example above. If we perform the following SELECT when we start the long transaction that we will let open (abandoned), pay attention to the size of HLL and the execution time:
MySQL> SELECT id, k, (
SELECT count FROM information_schema.innodb_metrics
WHERE name='trx_rseg_history_len') HLL
FROM sbtest.sbtest1 WHERE c LIKE '36%' LIMIT 10;
+-----+-------+-----+
| id | k | HLL |
+-----+-------+-----+
| 10 | 34610 | 98 |
| 288 | 561 | 98 |
| 333 | 54800 | 98 |
| 357 | 96142 | 98 |
| 396 | 82983 | 98 |
| 496 | 65614 | 98 |
| 653 | 38837 | 98 |
| 684 | 61922 | 98 |
| 759 | 8758 | 98 |
| 869 | 50641 | 98 |
+-----+-------+-----+
10 rows in set (0.0006 sec)
If we try again later in the same transaction (we didn’t rolled-back or committed it), the same query we can notice something different:
MySQL> SELECT id, k, (
SELECT count FROM information_schema.innodb_metrics
WHERE name='trx_rseg_history_len') HLL
FROM sbtest.sbtest1 WHERE c LIKE '36%' LIMIT 10;
+-----+-------+--------+
| id | k | HLL |
+-----+-------+--------+
| 10 | 34610 | 391836 |
| 288 | 561 | 391836 |
| 333 | 54800 | 391836 |
| 357 | 96142 | 391836 |
| 396 | 82983 | 391836 |
| 496 | 65614 | 391836 |
| 653 | 38837 | 391836 |
| 684 | 61922 | 391836 |
| 759 | 8758 | 391836 |
| 869 | 50641 | 391836 |
+-----+-------+--------+
10 rows in set (1.9848 sec)
The query is now much slower when the History List Length is large.
As explained in this excellent post of Jeremy Cole, in write-heavy databases, having a large History List Length may require reverting the version of a large amount of rows to very old versions. This will slow down the transaction itself and in the worst case may mean that very long-running queries in a write-heavy database can never actually complete; the longer they run the more expensive their reads get.
Having large HLL means that the undo logs also increase. With MySQL 8.0, you have more control on Undo Log tablespaces (see the manual), but you still need to monitor your diskspace !
Solutions
If the HLL is growing, the first step is to identify which reasons of the two listed above the system is experiencing.
Purge is not able to follow heavy writes
If the purge threads are not able to keep up with the write workload, it is necessary to throttle the write activity.
In MySQL 8.0, a maximum purge lag can be configured for InnoDB: innodb_max_purge_lag.
When the purge lag exceeds the innodb_max_purge_lag threshold, a delay is imposed on INSERT, UPDATE and DELETE operations to allow time for purge operations to catch up.
In some exceptionally rare situations that delay became way too high, this is why you have also the possibility to cap it using innodb_max_purge_lag_delay.
Another tunable setting related to InnoDB’s Purge, is innodb_purge_threads that represents the number of background threads dedicated to the Purge operation.
There is no ideal number to recommend, as usual, it depends 😉
The manual explains this point very well:
If the innodb_max_purge_lag setting is exceeded, purge work is automatically redistributed among available purge threads. Too many active purge threads in this scenario can cause contention with user threads, so manage the innodb_purge_threads setting accordingly.
If DML action is concentrated on few tables, keep the innodb_purge_threads setting low so that the threads do not contend with each other for access to the busy tables. If DML operations are spread across many tables, consider a higher innodb_purge_threads setting. The maximum number of purge threads is 32.
The innodb_purge_threads setting is the maximum number of purge threads permitted. The purge system automatically adjusts the number of purge threads that are used.
Long Running Transactions
As pointed earlier, a long running transaction, even a sleeping/stalled one, will block the purge and regardless of the write workload, even if it’s very low, the HLL will continue to grow for the entire life of that transaction.
The only way to fix this is by stopping those long transactions (commit, rollback, kill).
To find such long running transactions, this Performance_Schema query can be used:
MySQL> SELECT ROUND(trx.timer_wait/1000000000000,3) AS trx_runtime_sec,
format_pico_time(trx.timer_wait) AS trx_runtime,
processlist_id, trx.thread_id AS thread_id,
trx.event_id AS trx_event_id, trx.isolation_level,
trx.autocommit, stm.current_schema AS db,
stm.sql_text AS query,
stm.rows_examined AS rows_examined,
stm.rows_affected AS rows_affected,
stm.rows_sent AS rows_sent,
IF(stm.end_event_id IS NULL, 'running', 'done') AS exec_state,
ROUND(stm.timer_wait/1000000000000,3) AS exec_time
FROM performance_schema.events_transactions_current trx
JOIN performance_schema.events_statements_current stm USING (thread_id)
JOIN threads USING (thread_id)
WHERE trx.state = 'ACTIVE' AND trx.timer_wait > 1000000000000 * 1\G
*************************** 1. row ***************************
trx_runtime_sec: 1040.443
trx_runtime: 17.34 min
processlist_id: 107
thread_id: 147
trx_event_id: 73
isolation_level: REPEATABLE READ
autocommit: NO
db: sbtest
query: select * from employees.titles limit 10
rows_examined: 10
rows_affected: 0
rows_sent: 10
exec_state: done
exec_time: 0.000
1 row in set (0.0004 sec)
If the state and the query doesn’t change between multiple runs, the query can be considered as stalled or abandoned. A DBA should take action and kill it.
The Isolation level also impact this, I recommend to use READ-COMMITTED instead of the default REPEATABLE-READ as it will help reducing the HLL.
Indeed, with READ-COMMITTED, a new read-view is spawned for each SQL statement, and only kept active for it’s duration, as opposed to REPEATABLE-READ in which the read-view’s lifetime is tied to the whole transaction. This means that in REPEATABLE-READ as show earlier with the example, if you start a transaction and perform one SELECT and go for a coffee, you are still blocking the undo log purge, but with READ-COMMITTED as soon as the query finishes, the undo log purge is no longer blocked.
Is READ-COMMITTED always better ?
DimitriK points that there are also some caveats with READ-COMMITTED as he explains in this post. This is something you need to explore and maybe change the isolation level only for the session of those long transactions an eventually use READ-UNCOMMITTED if you can afford dirty reads.
Reclaiming Undo Log’s disk space
With MySQL 8.0 we have two methods to truncate undo tablespaces to reclaim the diskspace, which can be used individually or in combination to manage undo tablespace size.
The first method is automated by enabling innodb_undo_log_truncate which is now enabled by default.
The second is manual, with a SQL statement the DBA has the possibility to mark a undo log tablespace as innactive. All transactions using rollback segments in that specific tablespace, are permitted to finish. After transactions are completed, the purge system frees the rollback segments in the undo tablespace, then it is truncated to its initial size, and the undo tablespace state changes from inactive to empty.
Two undo logs are always required, so when you set a undo log tablespace to inactive, prior, you must have a least 3 active ones (including the one to set inactive).
The manual SQL syntax is:
MySQL> ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
It’s possible to list the undo log tablespaces and their state by running the following query:
MySQL> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE row_format='undo' ;
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
+-----------------+--------+
There are also some status variables related to the Undo Tablespaces:
MySQL> SELECT * FROM global_status WHERE variable_name
LIKE 'Innodb_undo_tablespaces%';
+----------------------------------+----------------+
| VARIABLE_NAME | VARIABLE_VALUE |
+----------------------------------+----------------+
| Innodb_undo_tablespaces_total | 2 |
| Innodb_undo_tablespaces_implicit | 2 |
| Innodb_undo_tablespaces_explicit | 0 |
| Innodb_undo_tablespaces_active | 2 |
+----------------------------------+----------------+
The above output is the default of MySQL 8.0. In case we would like to set inactive innodb_undo_001, this is the error we will get:
ERROR 3655 (HY000): Cannot set innodb_undo_001 inactive since there would be less than 2 undo tablespaces left active
So we need to create another one first using the following syntax:
MySQL> CREATE UNDO TABLESPACE my_undo_003 ADD DATAFILE 'my_undo_003.ibu'; Query OK, 0 rows affected (0.47 sec)
On the filesystem, we can see the new added tablespace:
[root@imac ~]# ls /var/lib/mysql/*undo* -lh -rw-r----- 1 mysql mysql 16M May 31 20:13 /var/lib/mysql/my_undo_003.ibu -rw-r----- 1 mysql mysql 32M May 31 20:12 /var/lib/mysql/undo_001 -rw-r----- 1 mysql mysql 32M May 31 20:13 /var/lib/mysql/undo_002
Now we can set it to inactive:
mysql> ALTER UNDO TABLESPACE innodb_undo_001 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE row_format='undo' ;
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | empty |
| innodb_undo_002 | active |
| my_undo_003 | active |
+-----------------+--------+
3 rows in set (0.00 sec)
When empty, we can set it back to active, and if we want, we can also delete the extra one like this:
MySQL> ALTER UNDO TABLESPACE my_undo_003 SET INACTIVE;
Query OK, 0 rows affected (0.00 sec)
MySQL> DROP UNDO TABLESPACE my_undo_003;
Query OK, 0 rows affected (0.01 sec)
MySQL> SELECT NAME, STATE FROM INFORMATION_SCHEMA.INNODB_TABLESPACES
WHERE row_format='undo' ;
+-----------------+--------+
| NAME | STATE |
+-----------------+--------+
| innodb_undo_001 | active |
| innodb_undo_002 | active |
+-----------------+--------+
2 rows in set (0.00 sec)
Conclusion
You understand now why it’s important to monitor InnoDB History List Length and in case it increases too much, identify if the purge is just not able to floow the write workload or if some long transactions are just totally blocking the InnoDB Purge.
The other method is manual, performed using SQL statements.
Thank you to Kuba and DimK for reviewing the article.
Hello Frederic,
Thank you – this is a very timely and thorough article.
Datadog and Mr Nichter’s book notified me recently of the importance of HLL on the servers I monitor.
Datadog identifies HLL in Aurora as aws.rds.rollback_segment_history_list_length
Cloudwatch identifies HLL as RollbackSegmentHistoryListLength
Innotop shows HLL as ‘History’ and the ‘Undo’ level of transactions (although Innotop is not always available to me).
My simple counter program now includes active transactions and HLL: https://github.com/Tinram/MySQL/tree/master/mysqlmon
Maybe HLL stats would be a worthwhile addition to Innotopgo? (currently see only ‘Isolation Level’ and ‘Trx length’ in the stats).
Just a couple of points that might be worth considering:
Jesper W. K. recommends reducing innodb_lock_wait_timeout to reduce contention/throughput delays from timing-out transactions.
Group Commit/increasing binlog_group_commit_sync_delay may have some potential by allowing more transactions to be committed together.
Cheers,
Martin
[…] [MySQL MVCC] A graph a day, keeps the doctor away ! – MySQL History List Length […]
Great article, helped me a lot!
I was struggling with the exact same problem on one database – the purging couldn’t keep up with the write-intensive load and the HLL lag just kept increasing.
Tried to increase the vCPUs and memory on the machnine, also doubled the number of innodb_purge_threads from 8 to 16 – no result whatsoever. Also tried increasing the value for binlog_group_commit_sync_delay – no success here either.
Only after setting “innodb_max_purge_lag” and “innodb_max_purge_lag_delay” to reasonable values the lag started decreasing.
I don’t quite get how throttling the DML operations affects the HLL lag, but it does work.
Something of importance though, setting these two variables to non-zero values raised the CPU usage of the machine with roughly 12% during lag catch-up and this is on a VM with 48 vCPUs.