Page MenuHomePhabricator

[toolsdb] Automatically terminate long transactions
Open, MediumPublic

Description

In the ToolsDB config file, we have max_statement_time = 3600 that will kill any query taking longer than 1 hour to complete.

It is still possible to have transactions that last longer than that, if they run multiple queries inside the same transaction and each query takes less than 1 hour. In extreme cases, where a transaction is started and kept open for multiple days, this can cause major issues, as in T409716: [toolsdb] ibdata1 growing on primary.

From a quick search, I don't think there's an easy way to prevent this on the MariaDB side. We could set idle_transaction_timeout to terminate all transactions that are idle for more than a certain amount of time (e.g. 5 minutes), but this would not entirely eliminate the risk. It would still be possible for a tool to start a transaction, then run a query every minute, and it would never reach the 5 minute "idle" threshold.

Another approach could be to write a script that periodically checks for active transactions, and kills them if they've been active for more than a certain amount of time (e.g. 1 hour, to match the current value for max_statement_time).

Event Timeline

Restricted Application added a subscriber: Aklapper. · View Herald Transcript
fnegri triaged this task as Medium priority.Nov 11 2025, 5:56 PM
Cw95yt5 subscribed.
This comment was removed by Aklapper.
JJMC89 removed a subscriber: Cw95yt5.
fnegri changed the task status from Open to In Progress.Nov 21 2025, 6:12 PM

I tried setting SET GLOBAL idle_transaction_timeout=300; in the primary, to see if it helps with T409716: [toolsdb] ibdata1 growing on primary, but I'm not too confident it will be enough.

I think we need to start working on the script to automatically kill transactions based on their active time.

FYI in production there are already mechanism to automatically kill queries, so we might just be able to reuse them.

@Volans I checked if wmf-pt-kill (used in clouddbs) did support killing sessions based on transaction time, but IIUC it only considers query time or idle time. I will ask Data-Persistence if there are other scripts in production that we can reuse.

Otherwise we can write a new script that finds long transactions based on innodb_trx.trx_started, e.g.:

SELECT trx_started, trx_mysql_thread_id FROM `information_schema`.`innodb_trx` ORDER BY `trx_started`;
fnegri changed the task status from In Progress to Open.Nov 26 2025, 10:40 AM
fnegri moved this task from Inbox to FY2025/2026-Q1-Q2 on the cloud-services-team board.

Change #1298835 had a related patch set uploaded (by FNegri; author: FNegri):

[operations/puppet@production] toolsdb: automatically terminate idle transactions

https://gerrit.wikimedia.org/r/1298835

I did set SET GLOBAL idle_transaction_timeout=60; last Friday while debugging T428139: [toolsdb] Transaction History Length growing too much. It did not completely resolve the issue, because as noted in the description of this task, tools can still keep a long transaction open if they don't leave it "idle" for more than 60 seconds, and I saw this happen. However, I think it's better than nothing and I don't see any reason not to set this value permanently. I created the patch above (https://gerrit.wikimedia.org/r/1298835) to add it to the ToolsDB conf.

For reference, a useful query to find long transactions and their respective users:

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)

Change #1298835 merged by FNegri:

[operations/puppet@production] toolsdb: automatically terminate idle transactions

https://gerrit.wikimedia.org/r/1298835