There are tools like pt-kill from the percona tool kit that may
print/kill the long running transactions at MariaDB, MySQL or at
Percona data instances, but a lot of backup scripts are just some
simple bash lines.
So checking for long running transactions before the backup to be
executed seems to be a step that is missed a lot.
Here is one line that might be just added in every bash script
before the backup to be executed
Variant 1. Just log all the processlist entries and calculate
which ones were running longer than TIMELIMIT:
$ export TIMELIMIT=70 && echo "$(date) : check for long runnig queries start:" >> /tmp/processlist.list.to.kill && mysql -BN -e 'show processlist;' | tee -a /tmp/processlist.list.to.kill | awk -vlongtime=${TIMELIMIT} '($6>longtime){print "kill "$1";"}' | tee -a /tmp/processlist.list.to.kill
Variant 2: Log all the processlist, calculate the calculate which processes …
[Read more]