Automated Transaction and Session Killers with MariaDB Xpand
This page is part of MariaDB's Documentation.
The parent of this page is: Query Tuning Recommendations for MariaDB Xpand
Topics on this page:
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 |
---|---|---|
| Kill the oldest transaction when available disk space goes below this percentage and available undo space goes below trxshoot_ | 5 |
| Kill the oldest transaction when available undo space goes below this percentage and available disk space goes below trxshoot_ | 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 |
---|---|---|
| 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 |
| 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 |
| 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 | 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 |
---|---|---|
| The number of seconds that Xpand will wait for a client to read data. | 60 |
| The number of seconds the Xpand waits for activity on a connection before closing it. | 28800 |