-
Notifications
You must be signed in to change notification settings - Fork 3
/
README
206 lines (143 loc) · 7.54 KB
/
README
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
MySQL Query Job Queue
---------------------
(C) 2012, 2013 Adrian M. Partl, eScience Group AIP Potsdam
This MySQL daemon plugin provides a job queue to which SQL queries can
be submitted. These will then be executed according to priority and
number of already running queries.
The main use of such a job queue is to manage queries that take long
times to run. This is especially needed for queries requiring full
table scans on large tables. It is possible to limit the concurrent
execution of such large queries and provide all the I/O throughput
to only a handfull of queries.
Further the job queue provides the possibility to define maximum
execution times after which a job will be killed by the daemon.
It is possible to define user groups and queues with different execution
priorities. Each query needs to be submitted to a queue and the jobs
priority is calculated according to the user group and queue. However
the queue itself does not provide any user management, this facillity
needs to be provided else where.
The main design goals have been to connect the queue to our Big Data
Webframework DAIQUIRI. If the queue might seem usefull to you in a
different context, just contact the authors. It should not be too hard
to make the queue fit for different use cases.
And please note that this plugin "injects" into the MySQL server code.
Any bug this plugin has, can bring down the whole server. YOU HAVE BEEN
WARNED, so don't blame us if you use this in a productive environment
where harm can arrise...
To install
----------
1) Download the mysql source corresponding to your installed
version. Building mysql from scratch is recommended.
Note: ONLY MYSQL VERSION 5.5 AND ABOVE ARE CURRENTLY
SUPPORTED
2) edit CMakeList.txt accordingly to point to the directory
where mysql is installed.
3) edit CMakeList.txt accordingly to point to the directory
of the mysql sources
4) edit CMakeList.txt accordingly, if you are using MariaDB
5) edit CMakeList.txt accordingly, if you want the queue to behave as follows:
Default behaviour: Whenever a job is killed/timed out in the queue, the queue
waits until the job has been successfully killed and starts
the next job afterwards.
In certain cases (especially with buggy storage engines or functions
that don't handle MySQL KILL properly), this will lock up the queue if
a MySQL thread cannot be killed. To prevent this, uncomment the approperiate
option in CMakeLists.txt
Alternative behaviour: Whenever a job is killed/timed out in the queue, the queue starts
the next job as soon as the kill request has been issued to the MySQL thread.
The queue does not wait on the job to properly exit.
4) mkdir build
cd build
5) cmake ..
6) make
7) make install
8) log into mysql server as root and execute the commands in
install_qqueue.sql
This will setup the neccessary tables, install the plugin
and create all the UDFs needed to administer the queue.
9) Create user groups using "select qqueue_addUsrGrp(name, priority)
10) Create queue using "select qqueue_addQueue(name, priority, timeout)
11) adjust global valiables
qqueue_numQueriesParallel and
qqueue_intervalSec
to your liking...
show variables like '%qqueue%';
12) DONE
GENERAL WARNING!
----------------
Up to now, jobs are always executed as ROOT users! No security
context change is performed.
--------------------------------------------------------------
| IT IS THEREFORE YOUR RESPONSIBILITY TO MAKE SURE, |
| ALL JOBS ARE KOSCHER!!!! |
| |
| DONT BLAME US, YOU HAVE BEEN WARNED! |
--------------------------------------------------------------
Usage UDF
---------
Please always administer the queue through the provided UDFs.
Direct manipulation of the system tables is possible, however
no consistency checks are then performed, no timestamps are
set and priorities are not calculated...
User Groups table:
The user table holds information about various user groups that
can be defined. Basically this alters the overall priority of a
job which is the product of the user group priority and the queue
priority. Higher number means higher priority...
mysql.qqueue_usrGrps
qqueue_addUsrGrp(string usrGrp_name, int usrGrp_priority)
qqueue_updateUsrGrp(int usrGrp_id, string usrGrp_name, int usrGrp_priority)
qqueue_flushUsrGrps()
(to delete, use SQL on system table and flush the groups)
Queues table:
The queues table allow the definition of various queues (like in
PBS/Torque). Queues have a priority and a timeout. If a query in
a given queue exceeds its timelimit, the query queue daemon will
kill the query! Time is given in seconds!
mysql.qqueue_queues
qqueue_addQueue(string queue_name, int queue_priority, int queue_timeout)
qqueue_updateQueue(int queue_id, string queue_name, int queue_priority,
int queue_timeout)
qqueue_flushQueues()
(to delete, use SQL on system table and flush the groups)
Pending Job table:
Table containing the submitted jobs that are still pending or running.
The userId is not used by the queue and can be used for reference in
a user management level. The paqu_flag (parallel query flag) informs
the query daemon, that the query should be run as is and no
CREATE TABLE foo SELECT ....
is added to the provided query on the last SELECT statement. If paqu_flag
is 0, then the CREATE TABLE statement is added to the last SELECT
statement in the query. Multi queries are supported and are run under one
MySQL connection/thread. Temporary tables and variables should be conserved.
delJob will delete the job if it is still pending and will kill the job
if it is already running.
mysql.qqueue_jobs
qqueue_addJob(int jobId, int userId, string usrGrpName, string queueName,
string query, string result_db, string result_table,
string comment, int paqu_flag, (optional) string actualQuery)
qqueue_delJob(int jobId)
Comment on certain options in qqueue_addJob:
- jobId: You can set the jobId to a value of your choice (it is your responsibility
that the jobId is unique), or set this to "NULL" for generation by the queue
- paqu_flag: If you set the paqu_flag (mainly intendet for use with the paqu parallel
query facility), the string given in query is not parsed and no "CREATE
TABLE" statement is added. The query holds the original query. It is up to
the user to provide a correctly "CREATE TABLE" escaped query in actualQuery.
History Job table:
After any job execution terminates due to whatever reason, the job is
moved to the qqueue_history table.
mysql.qqueue_history
Usage Stored Procedures
-----------------------
If you have fully run "install_qqueue.sql", two stored procedures have been
installed in the "mysql" database:
qqueue_clean_history():
By running "CALL qqueue_clean_history();" in the mysql database, you can set
all jobs in the history as DELETED, that have no corresponding result table
anymore. This should be run as root, that mysql has full access to the complete
information_schema.tables table.
qqueue_wipe_history():
By running "CALL qqueue_wipe_history();" in the mysql database, all jobs are deleted
that have no corresponding result table anymore. This should be run as root, that
mysql has full access to the complete information_schema.tables table.