Daiquiri and the query queue
This page explains how to setup the qqueue MySQL job queue, how to configure it and how it interfaces with Daiquiri.
The compilation part is extensively explained in the README file of the query queue source code at https://github.com/adrpar/mysql_query_queue. Please have a look there. It also explains the basic functionality. Here we will go somewhat into details on how things work.
The basic philosophy of the job queue is simple. Every job that is submitted to the queue will obtain a priority factor. The priority is calculated from the queue the job is submitted to and the user group the user belongs to. Basically those two variables are multiplied to obtain the priority. Higher priority goes first from the list.
This might be important to you, if you have people that are administrators and need to have jobs executed faster than guests to your application. You might have other members of your collaboration that want to be treated VIP style. This can be obtained by specifying the priority modifier for the user groups. When using daiquiri, the user groups need to correspond to the ones specified in your application.
The different queues might allow for a distinction between shorter jobs, which have a smaller timeout and higher priority, than longer lasting jobs. This basically results in a behavior where first jobs in the small queue (if you have such a queue defined) are treated first, and only if there is nothing more to do, run the long jobs. This is what you can achieve with the queues.
The query queue runs as a daemon plugin in MySQL server and can be configured using three different global variables. These can be set at server run time through the MySQL prompt (only server instance wide, after restart the settings will be lost), or you specify them in the my.cnf (or other config file).
qqueue_intervalsecdefault 5 sec – this variable defines after how many seconds the daemon will fire up and look for new jobs to execute. This variable can be used to reduce the load the queue itself poses.
qqueue_numqueriesparalleldefault 2 – number of queries that are run in parallel. Depending on your setup, this can be higher. We theorize that the best performance on one query can be achieved, if the IO can be saturated. This might be possible with 1 or 2 parallel queries. But this needs scientific evaluation…
qqueue_recoverydefault ON – if the server goes down and the query queue is resumed on restart, this flag will define whether the whole queue should be discareded (if recovery is set to FALSE) or if the queue should try to restart any query that was in the queue. Some might fail, others might go again. Depends on the query…
Tables in the mysql database
QQueue adds the following tables in the mysql database:
qqueue_historySaving the history of any query that has been processed with qqueue. This is where all the queries go, after they have been executed or failed.
qqueue_jobsThe active job queue. Any entry in this table will be handled at the desired time, depending on priority. Everything in this table is considered as active or pending!
qqueue_queuesTable holding information about the query queues.
qqueue_usrGrpsTable holding information about the user groups.
One word of caution, just in case you claim you have not been warned: Never alter these tables directly through SQL. You can do this, but beware the consequences! Use the UDFs instead, since they will check whether you ask the qqueue something sensible or if you are bound for troubles. You have been warned! Select is ok, of course.
QQueue and Daiquiri
The default job queue used by Daiquiri is called simple and set in
config.query.queue.type = 'simple'. The simple job queue (which in fact is not really a queue at all, since it blocks until the job has finished), does not take any other options.
If you want to use the query queue, set
config.query.queue.type to 'qqueue'. This requires that qqueue is correctly setup in your MySQL instance and that Daiquiri has access to the qqueue tables in MySQL. The first step in setting up qqueue, is to mark the MySQL instance that runs the queue. This is most likely the instance where you science tables are. In the init.php file, turn on the
qqueue option in the according database connection configuration (usually
database.user.qqueue). If this option is set, the required Daiquiri MySQL setup is produced for you if you run init.php -u.
Further you need to create for each user group in Daiquiri (i.e. all the Daiquiri roles) an entry in the qqueue user group table using the qqueue_addUsrGrp MySQL UDF. This needs to be done in the MySQL client:
mysql> SELECT qqueue_addUsrGrp(groupName, priority); -- e.g. mysql> SELECT qqueue_addUsrGrp('guest', 1); mysql> SELECT qqueue_addUsrGrp('user', 10); mysql> SELECT qqueue_addUsrGrp('admin', 20);
After you added the groups, you need to use
qqueue_flushUsrGrps() to push the changes to the qqueue:
mysql> SELECT qqueue_flushUsrGrps();
Then you can add the different queues you might want to have. You need to add at least one! (Timeout is given in seconds)
mysql> SELECT qqueue_addQueue(queueName, priority, timeout); -- e.g. mysql> SELECT qqueue_addQueue('short', 20, 30); mysql> SELECT qqueue_addQueue('long', 10, 300);
Flush the queues:
mysql> SELECT qqueue_flushQueues();
This is the setup on the MySQL side. On the Daiquiri side you can then alter the following options if you are not happy with the default behavior:
config.query.queue.qqueue.defaultUsrGrpdefault 'user', this is the default user group that a query is submitted to if none is specified (or something goes wrong).
config.query.queue.qqueue.defaultQueuedefault 'short', this is the default queue that is used if none is specified. This especially applies to the guest user.
So an example init.php setting would look like (at the right position of course):
'queue' => array( 'type' => 'qqueue', 'qqueue' => array( 'defaultUsrGrp' => 'user', 'defaultQueue' => 'short' ) ),
That's it. Should be about everything you need.