MySQL Punisher

Watch and kill active mysql queries that exeeded a specefic timeout.

Needs

If you have a limited mysql connection pool and you are not sure if you may have unexpected queries that may take a really long time and allocate the pool for this time. So use the mysql-punisher to watch the exceeded execution time for the active query processes, killed them, log them and let your app work as expected for all end users.

Installation

To use the mysql-punisher from you command line interface, you have to

npm install mysql-punisher -g

Usage

mysql-punisher -h <hostname> -u <username> -p <password>

or

mpun -h <hostname> -u <username> [-p <password>]

Using PM2 (Recommended)

PM2 is a process manager for Node.js applications.

npm install -g pm2
pm2 start mysql-punisher -- -h <hostname> -u <username> -p <password>
pm2 startup
pm2 save

Logs

All killed process will be logged into the stdout in the following format:

KILL {id} {time}s {query}

Example

KILL 8978 31s SELECT SLEEP(100)

Requirements

The user that used to establish the connection usign the option -u should have the privilege to execute SHOW FULL PROCESSLIST for the desired processes.

Testing

To check if everything is working fine and the mysql-punisher kills the queries, you can make a dump long query by:

SELECT SLEEP(100);

Options

• --help output usage information
• -h, --host <host> host name (default: 'localhost')
• -u, --user <user> user name (default: 'root')
• -p, --password <password> password (default: '')
• -t, --timeout <seconds> timeout in seconds (default: 30)
• -i, --interval <millisecond> timer's interval in millisecond (default: 1000)
• --watch-database <database> watch a specefic database processes (default: 'all')
• --watch-host <host> watch a specefic host's processes (default: 'all')
• --watch-user <user> watch a specefic user's processes (default: 'all')

License

This project is under the MIT license.