Jump to content

Portal:Toolforge/Admin/Runbooks/ToolsDBHistoryLengthGrowing

From Wikitech

This happens when the InnoDB Transaction History Length in a ToolsDB host grows beyond 50 million items (arbitrary threshold based on current usage levels).

The procedures in this runbook require admin permissions to complete.

Error / Incident

This is a Warning displayed in https://alerts.wikimedia.org/?q=team%3Dwmcs.

If InnoDB transactions remain open for an extended time (hours or days), MariaDB has to keep a record of all changes in the undo log in order to provide a consistent view of the data at the moment the transaction was started.

For a more detailed explanation, see https://dev.mysql.com/doc/refman/8.4/en/innodb-purge-configuration.html.

If left unchecked, it will eventually cause an increase in disk space usage, which will lead to a Portal:Toolforge/Admin/Runbooks/ToolsDBAlmostFull alert.

Side effect: when the History Length is big, shutting down MariaDB will take much longer.

Debugging

Finding the transactions that are causing the History Length to grow

MariaDB [(none)]> SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_sec, trx_state, trx_mysql_thread_id, user, command, state FROM information_schema.innodb_trx JOIN information_schema.processlist p ON trx_mysql_thread_id = p.id HAVING age_sec > 60 ORDER BY trx_started ASC;
+--------------+---------------------+---------+-----------+---------------------+--------+---------+--------------+
| trx_id       | trx_started         | age_sec | trx_state | trx_mysql_thread_id | user   | command | state        |
+--------------+---------------------+---------+-----------+---------------------+--------+---------+--------------+
|            0 | 2026-06-15 09:36:38 |    1536 | RUNNING   |           675665986 | s53685 | Query   | Sending data |
|            0 | 2026-06-15 09:36:53 |    1521 | RUNNING   |           675667320 | s53685 | Query   | Sending data |
|            0 | 2026-06-15 09:37:15 |    1499 | RUNNING   |           675668876 | s53685 | Query   | Sending data |
| 168336424468 | 2026-06-15 09:39:54 |    1340 | RUNNING   |           675681281 | s51138 | Sleep   |              |
|            0 | 2026-06-15 10:00:12 |     122 | RUNNING   |           675771558 | s51412 | Query   | Sending data |
|            0 | 2026-06-15 10:00:28 |     106 | RUNNING   |           675772925 | s55175 | Sleep   |              |
|            0 | 2026-06-15 10:01:06 |      68 | RUNNING   |           675775167 | s53781 | Sleep   |              |
+--------------+---------------------+---------+-----------+---------------------+--------+---------+--------------+
7 rows in set (0.005 sec)

ToolsDB debugging dashboard

The ToolsDB debugging dashboard in Grafana contains a chart with the Transacation History Length over time.

Common issues

Tools using autocommit=False

If a tool connects to ToolsDB using autocommit=False (the default in pymysql), then issues a query (even a simple SELECT) without closing the connection, MariaDB will open a transaction and keep it open for the duration of the connection. In https://gerrit.wikimedia.org/r/c/operations/puppet/+/1298835 we set idle_transaction_timeout = 60 that will auto-terminate transactions that remain idle for more than 1 minute, but if a tool issues a SELECT every minute, the transaction will never be closed.

This happened in phab:T409716.

How to fix it: identify the tool causing this using the query in the "Debugging" section above, then either stop it or get in touch with the maintainer and ask them to add autocommit=True or avoid keeping long-lived connections.

Tools making long queries

If a tool runs a long query against ToolsDB (we currently allow up to 1 hour for query duration), a corresponding transaction will remaing open for the same time. If before that query ends, the tool issues another long query, it can create a chain of queries that will effectively prevent MariaDB from successfully purging the undo log.

This happened in phab:T428139.

How to fix it: identify the tool causing this using the query in the "Debugging" section above, then stop it or get in touch with the maintainer asking them to optimize their queries.

Support contacts

The main discussion channel for this alert is the #wikimedia-cloud-admin in IRC.

If the situation is not clear or you need additional help, you can also contact the Data Persistence team (#wikimedia-data-persistence on IRC).

Old incidents

Add any incident tasks here!

Retrieved from "https://wikitech.wikimedia.org/w/index.php?title=Portal:Toolforge/Admin/Runbooks/ToolsDBHistoryLengthGrowing&oldid=2431407"