Running simultaneous queries with Python, for killing them

This topic is about, simple approach to run multiple sql statements for testing XtraBackup behaviour – if it is going to kill them due to blocked FTWRL(FLUSH TABLES WITH READ LOCK).

According to the DOC -> Backup Locks

Percona Server has implemented this feature to be a lightweight alternative to FLUSH TABLES WITH READ LOCK for both physical and logical backups. Three new statements are now available: LOCK TABLES FOR BACKUP, LOCK BINLOG FOR BACKUP and UNLOCK BINLOG.

So by default, if you are using PS it is going to use LOCK TABLES FOR BACKUP prior copying non-InnoDB tables.

Well, how to force to use FTWRL instead of Backup Locks?
Congratulations – we have --no-backup-locks option, if you specify it, the FTWRL will be used.

Okay, again reading the DOC -> #killing-the-blocking-queries

So here are the combination of options to kill blocking queries:

--no-backup-locks
--ftwrl-wait-timeout=0 
--ftwrl-wait-query-type=all 
--ftwrl-wait-threshold=1 
--kill-long-queries-timeout=1 
--kill-wait-query-type=all 
--kill-long-query-type=all 

Good to go, but the missing thing is, to create blocking queries.

* First we need some MyISAM tables. I used sysbench to create some tables and then altered them:

        # Altering some of the table engines from innodb to myisam
        for i in range(20, 25):
            sql_alter_engine = "alter table sysbench_test_db.sbtest{} engine=myisam".format(i)
            RunBenchmark.run_sql_statement(basedir=self.basedir, sql_statement=sql_alter_engine)

* As I am using the MySQL client directly, the best is to have some bash script for running SQL redirected to /dev/null(because I don’t need any result from SQL result, I will run benchmark query):

#!/usr/bin/env bash
# Executed as run_sql_queries.sh BASEDIR_PATH 50 SOCKET
BASEDIR=$1
MYSQL_SOCK=$2
SQL=$3
MYSQL_USER=root

${BASEDIR}/bin/mysql --user=${MYSQL_USER} --socket=${MYSQL_SOCK} -e "${SQL}" > /dev/null 2>&1

* Now calling this bash script from Python:
Passing the basedir of PS, socket connection and actual SQL to be run, it is going to be passed to bash script:

    @staticmethod
    def parallel_sleep_queries(basedir, sql, sock):
        dir_path = os.path.dirname(os.path.realpath(__file__))
        bash_command = "{}/run_sql_queries.sh {} {} '{}'".format(dir_path, basedir, sock, sql)
        try:
            process = Popen(
                split(bash_command),
                stdin=None,
                stdout=None,
                stderr=None)
        except Exception as e:
            print(e)

* Using Python’s concurrent.futures here for true concurrent run(prior taking backup):

            with concurrent.futures.ProcessPoolExecutor(max_workers=50) as pool:
                for _ in range(10):
                    for i in range(20, 25):
                        pool.submit(
                            self.parallel_sleep_queries(basedir=self.basedir,
                                                        sock="{}/socket.sock".format(self.basedir),
                                                        sql="select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest{}".format(
                                                            i)))
            self.all_backup() # Taking backup with options provided above

So the thing is quite simple here, I have created a bash script to call mysql cli, then called it from Python method with Popen and the called this method concurrently.
The result is quite handy. Actual backup command(it is generated from config file):

/home/shahriyar.rzaev/XB_TEST/server_dir/target/percona-xtrabackup-2.4.x-debug/bin/xtrabackup --defaults-file= --user=root \
--password=''  --target-dir=/home/shahriyar.rzaev/XB_TEST/backup_dir/ps_5_7_x_2_4/cycle1/full/2017-12-09_06-59-04 --backup \
--socket=/home/shahriyar.rzaev/XB_TEST/server_dir/PS061217-percona-server-5.7.20-18-linux-x86_64-debug/socket.sock \
--compress=quicklz --compress-chunk-size=65536 --compress-threads=4 --encrypt=AES256 \ 
--encrypt-key=VVTBwgM4UhwkTTV98fhuj+D1zyWoA89K --encrypt-threads=4 --encrypt-chunk-size=65536 --slave-info \
--no-version-check --core-file --parallel=10 --throttle=40 --check-privileges --ftwrl-wait-timeout=0 \
--ftwrl-wait-query-type=all --ftwrl-wait-threshold=1 --kill-long-queries-timeout=1 --kill-wait-query-type=all \
--kill-long-query-type=all --no-backup-locks \
--keyring-file-data=/home/shahriyar.rzaev/XB_TEST/server_dir/PS061217-percona-server-5.7.20-18-linux-x86_64-debug/mysql-keyring/keyring 
--stream="xbstream" > \
/home/shahriyar.rzaev/XB_TEST/backup_dir/ps_5_7_x_2_4/cycle1/full/2017-12-09_06-59-04/full_backup.stream

The result of running and killing blocking queries:

171209 06:59:15 Executing FLUSH TABLES WITH READ LOCK...
171209 06:59:15 Kill query timeout 1 seconds.
171209 06:59:16 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: /home/shahriyar.rzaev/XB_TEST/server_dir/PS061217-percona-server-5.7.20-18-linux-x86_64-debug/socket.sock
171209 06:59:16 Killing query 308 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 309 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 310 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 311 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 312 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 313 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 314 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 315 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 316 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 317 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 318 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 319 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 320 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 321 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 322 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 323 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 324 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 325 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 326 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 327 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 328 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 329 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 330 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 331 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 332 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 333 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 334 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 335 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 336 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 337 (duration 13 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 338 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 339 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 340 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 341 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 342 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 343 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 344 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 345 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 346 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 347 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 348 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 349 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 350 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 351 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 352 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Killing query 353 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest20
171209 06:59:16 Killing query 354 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest21
171209 06:59:16 Killing query 355 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest22
171209 06:59:16 Killing query 357 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest23
171209 06:59:16 Killing query 358 (duration 12 sec): select benchmark(9999999, md5(c)) from sysbench_test_db.sbtest24
171209 06:59:16 Kill query thread stopped
171209 06:59:16 Starting to backup non-InnoDB tables and files

The final thing to note here is, how to provide this bash files from setup.py?
You can use something like this in setup.py:

    packages=['general_conf', 'backup_prepare', 'partial_recovery', 'master_backup_script', 'prepare_env_test_mode'],
    package_data={
        'prepare_env_test_mode': ['*.sh']
    },

For full code please see:
prepare_env_test_mode

Thanks 🙂

Author: Shahriyar Rzayev

Azerbaijan MySQL User Group leader.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s