Kill idle transaction¶

Database servers face a constant challenge: managing resources efficiently while maintaining system stability. The kill idle transactions timeout option is a strategic tool to address this challenge. When you set a time limit, any transaction that stays inactive beyond this limit is automatically stopped. This action prevents forgotten or stuck transactions from slowing down your database by blocking critical cleanup processes.

The option has the following benefits:

Automatically terminates long-running, inactive database connections
Prevents resource hogging by abandoned or forgotten transactions
Frees up database connection pools
Reduces unnecessary memory consumption
Protects against potential connection leaks
Prevents unnecessary server load from dormant sessions

You must also be consider the following:

May interrupt legitimate long-running queries
Requires careful configuration to avoid disrupting critical processes
Can cause unexpected application behavior if timeout is too aggressive
Might create additional overhead in monitoring and logging
Requires precise tuning for different application requirements

We recommend that you start with conservative timeout settings and review the logs frequently to track terminated transactions.

This feature works with all types of database storage that support transactions.

Determine the idle transaction threshold¶

When setting up a database, you must decide how long to let inactive transactions sit before ending them. This decision affects the database’s performance.

Items to consider	Description
How your database is used	Look at how long transactions usually take and how often they happen. If most transactions finish quickly, you should end idle ones sooner.
How many things happen at once	Count how many transactions your system handles simultaneously. If it’s a lot, you might need to end idle transactions faster to free up space for new ones.
How it affects speed	Watch how idle transactions change your database’s speed. If they slow things down a lot, ending them sooner can help keep everything running smoothly.
What your business needs	Consider what’s important for your work. Some important transactions might need more time, so you shouldn’t end them too quickly.

InnoDB purge¶

The InnoDB purge process in MySQL removes outdated row versions (undo logs) from the system. When a transaction modifies data, InnoDB keeps old row versions for rollback and to support transactions running with multi-version concurrency control (MVCC). Once these versions are no longer needed, the purge process deletes them to free up space and improve performance.

Blocking the InnoDB purge can lead to increased disk space usage and potential performance degradation. This feature helps prevent issues such by:

Limiting idle transactions: It kills any idle transaction after a specified threshold, ensuring transactions don’t remain idle for too long.
Preventing mistakes: Users can’t accidentally block the InnoDB purge by leaving transactions idle.
Improving performance: Keeping the purge process running smoothly helps maintain optimal database performance.

System variables¶