Justin Swanhart (swanhart) wrote,
Justin Swanhart
swanhart

Now available in swanhart-tools: NATIVE asynchronous query execution for any MySQL client!

There is often a need to run queries in the background in MySQL. This generally is accomplished using a message queue (like gearman), or by using extensions to a client (PHP has such extensions) or by using MariaDB's native async query interface (C client only I think).

While such solutions work well, they don't work for say a GO client, or for the mysql command line client itself.

I present "async"; part of the Swanhart Toolkit (http://github.com/greenlion/swanhart-tools). Async is a stored procedure and event based solution for asynchronous queries.

It consists of:

  • A queue table to hold the SQL to run, the state of execution, error messages, etc

  • A settings table that controls the number of parallel threads to use for executing queries

  • A stored routine worker that picks up SQL queries and executes them

  • An EVENT that runs the worker threads (which exits if too many are running)

  • SQL stored routines for dropping SQL into the queue (async.queue), checking the status of the query(async.check), and getting the result of a query (async.wait).

It works with MySQL 5.6 or greater (you can make it work with 5.5 if you get rid of GET DIAGNOSTICS with the tradeoff that you won't have good error messages for your broken queries.

Just run the setup.sql script, enable the event scheduler, then:
call async.queue('select sleep(50)');  -- returns the query number (also in @query_number)
call async.check(@query_number)\G
call async.wait(@query_number);

-- eight workers run by default (update the settings table to change that)
mysql> show processlist;
+------+-----------------+-----------+-------+---------+------+-----------------------------+------------------+
| Id   | User            | Host      | db    | Command | Time | State                       | Info             |
+------+-----------------+-----------+-------+---------+------+-----------------------------+------------------+
|  143 | root            | localhost | async | Query   |    0 | User sleep                  | DO SLEEP(v_wait) |
|  146 | event_scheduler | localhost | NULL  | Daemon  |    0 | Waiting for next activation | NULL             |
|  147 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  148 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  149 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  150 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  151 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
|  152 | root            | localhost | async | Connect |    0 | User sleep                  | DO SLEEP(v_wait) |
| 1525 | root            | localhost | async | Query   |    0 | starting                    | show processlist |
+------+-----------------+-----------+-------+---------+------+-----------------------------+------------------+
9 rows in set (0.00 sec)

-- queue a background query
mysql> call async.queue('select sleep(25)');
+--------------+
| QUERY_NUMBER |
+--------------+
|            1 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- check the status
mysql> call async.check(@query_number)\G
*************************** 1. row ***************************
        q_id: 1
    sql_text: select sleep(25)
  created_on: 2016-02-24 14:09:30
  started_on: 2016-02-24 14:09:30
completed_on: NULL
      parent: NULL
   completed: 0
       state: RUNNING
       errno: NULL
      errmsg: NULL
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- wait for a query to finish (or just get the result of a finished query)
mysql> call async.wait(@query_number);
+-----------+
| sleep(25) |
+-----------+
|         0 |
+-----------+
1 row in set (10.18 sec)

Query OK, 0 rows affected (10.18 sec)

-- run a query that generates an error
mysql> call async.queue('select sleep(25) from bad_table');
+--------------+
| QUERY_NUMBER |
+--------------+
|            2 |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- get the error information for the bad query
mysql> call async.check(@query_number)\G
*************************** 1. row ***************************
        q_id: 2
    sql_text: select sleep(25) from bad_table
  created_on: 2016-02-24 14:10:04
  started_on: 2016-02-24 14:10:04
completed_on: 2016-02-24 14:10:04
      parent: NULL
   completed: 1
       state: ERROR
       errno: 42S02
      errmsg: Table 'async.bad_table' doesn't exist
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)
Tags: asynchronous query, background, mysql, parallel query
Subscribe
  • Post a new comment

    Error

    default userpic

    Your reply will be screened

    Your IP address will be recorded 

    When you submit the form an invisible reCAPTCHA check will be performed.
    You must follow the Privacy Policy and Google Terms of use.
  • 0 comments