Automated Transaction and Session Killers with MariaDB Xpand

Transaction and Session Killers

MariaDB Xpand provides support for automated transactions and session killers.

The goal of the automated transaction and session killers is to protect Xpand service resources against unintentionally long-running operations that may degrade database performance or availability. Each of these are configurable and can be completely disabled if desired.

The automated transaction killers will cancel transactions that exceed the specified thresholds. Each transaction killer uses global variables to specify the threshold conditions that should be exceeded before transactions are killed.

Note

Since these checks are only run periodically, the result is that transactions may exceed the threshold for some time before being killed.

Example: If the global variable is set to kill a transaction after 20 seconds of being idle and is run every 30 seconds, the transaction may not be killed until the 49 second mark.

Capacity Transaction and Session Killers

The capacity killer (trxshoot) cancels transactions when the amount of storage space falls below the specified thresholds. In order to configure these Xpand variable, connect to the Xpand service socket.

The global variables to control this are as follows:

System Variable

Description

Default

trxshoot_disk_min_pct

Kill the oldest transaction when available disk space goes below this percentage and available undo space goes below trxshoot_undo_min_pct.

5

trxshoot_undo_min_pct

Kill the oldest transaction when available undo space goes below this percentage and available disk space goes below trxshoot_disk_min_pct.

2

If both of the thresholds are met, trxshoot is activated, the transaction with the oldest xid will be killed, and an error will be returned to the requesting application.

Time-based Session Killer

The process to kill long-running transactions by killing their associated session is intended to prevent unintentional ill effects on a cluster due to long running sessions. In order to configure these Xpand variable, connect to the Xpand service socket.

These global variables control when Xpand kills idle sessions with transactions:

System Variable

Description

Default

idle_trx_timeout_s

Maximum allowed age for idle transactions. Specify 0 for no timeout. This killer will be activated if the time between transactions within a session exceeds the threshold. This occurs mainly due to sessions that have been left open unintentionally.

120

trx_timeout_s

Maximum allowed age for transactions. Specify 0 for no timeout. The session killer will be activated if the total transaction age exceeds this threshold, whether the transaction is actively doing work or not. The age of active transactions can be found by querying system.sessions.

0

write_trx_timeout_s

Maximum allowed age for write transactions. Specify 0 for no timeout. The session killer will be activated if the total time for a write transaction exceeds this threshold. When enabling this session killer, the value for write_trx_timeout_s should be set to a lower value than trx_timeout_s.

0

Please note that when session_idle_kill is invoked, it will kill the entire session containing the individual transaction.

Network Timeout Killer

The network timeout killer detects and closes idle sessions to unresponsive clients. In order to configure these Xpand variable, connect to the Xpand service socket.

Thee global variables control when Xpand closes sessions due to network timeouts:

System Variable

Description

Default

net_write_timeout

The number of seconds that Xpand will wait for a client to read data.

60

wait_timeout

The number of seconds the Xpand waits for activity on a connection before closing it.

28800