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
|
The Gearman MySQL user defined functions (UDFs) allow queries in MySQL
to act as a Gearman client. This gives you the ability to run functions
in you query not just on the local machine, but to any machine running
a Gearman worker. This can be useful for a number of reasons, including
processor or memory intensive functions that you wish to run on other
machines, functions that can run in the background, functions that
can run in parallel for aggregate operations, and also to trigger
jobs or other actions from a MySQL query. See the Gearman website
for information at:
http://www.gearmanproject.org/
The MySQL UDFs require the Gearman C server and library package to be
installed. You can find more information about how to do this through
the website above. Once this is installed, you can then compile the
Gearman MySQL UDF package. This can usually be done with the normal:
./configure
make
make install
Once the UDFs have been compiled and installed, you load them into
MySQL using the following queries:
CREATE FUNCTION gman_do RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_high RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_do_background RETURNS STRING
SONAME "libgearman_mysql_udf.so";
CREATE FUNCTION gman_servers_set RETURNS STRING
SONAME "libgearman_mysql_udf.so";
Once loaded, you'll need to add servers for the clients to query
first. This can be done with:
SELECT gman_servers_set("127.0.0.1");
You can also add a different set of servers for each function
type. For example:
SELECT gman_servers_set("192.168.1.1", "resize");
This would direct future queries to use 192.168.1.1 for all Gearman
functions calls to "resize". You can also specify multiple job servers
and port numbers in a single query using the following syntax:
SELECT gman_servers_set("192.168.1.3:7004,192.168.1.4:7004", "index");
Once your servers are setup, you can then run jobs from your queries:
SELECT gman_do("reverse", Host) AS test FROM mysql.user;
SELECT gman_do_high("reverse", Host) AS test FROM mysql.user;
SELECT gman_do_background("reverse", Host) AS test FROM mysql.user;
These examples run a normal job, a high-priority job, or a background
job, respectively. The last job does not return a result, but instead
a job handle that you can later use to query for status. In order to
get the result from a background job, the worker would need to store
the result someplace a client can find it again later, such as back
into a MySQL table, into memcached, or even a file somewhere both
the client and worker can access.
If you would like to get the above examples working, you need to run
'gearmand' to start the job server, and then run 'reverse_worker'
from the examples directory in the C server and library package.
If you ever need to remove the functions from MySQL, you can run:
DROP FUNCTION gman_do;
DROP FUNCTION gman_do_high;
DROP FUNCTION gman_do_background;
DROP FUNCTION gman_servers_set;
Enjoy!
-Eric
eday@oddments.org
http://www.oddments.org/
|