1 |
NAME |
2 |
Coro::Mysql - let other threads run while doing mysql requests |
3 |
|
4 |
SYNOPSIS |
5 |
use Coro::Mysql; |
6 |
|
7 |
my $DBH = Coro::Mysql::unblock DBI->connect (...); |
8 |
|
9 |
DESCRIPTION |
10 |
(Note that in this manual, "thread" refers to real threads as |
11 |
implemented by the Coro module, not to the built-in windows process |
12 |
emulation which unfortunately is also called "threads") |
13 |
|
14 |
This module "patches" DBD::mysql database handles so that they do not |
15 |
block the whole process, but only the thread that they are used in. |
16 |
|
17 |
This can be used to make parallel sql requests using Coro, or to do |
18 |
other stuff while mysql is rumbling in the background. |
19 |
|
20 |
CAVEAT |
21 |
Note that this module must be linked against exactly the same (shared, |
22 |
possibly not working with all OSes) libmysqlclient library as |
23 |
DBD::mysql, otherwise it will not work. |
24 |
|
25 |
Also, while this module makes database handles non-blocking, you still |
26 |
cannot run multiple requests in parallel on the same database handle. If |
27 |
you want to run multiple queries in parallel, you have to create |
28 |
multiple database connections, one for each thread that runs queries. |
29 |
Not doing so can corrupt your data - use a Coro::Semaphore when in |
30 |
doubt. |
31 |
|
32 |
If you make sure that you never run two or more requests in parallel, |
33 |
you can freely share the database handles between threads, of course. |
34 |
|
35 |
Also, this module uses a number of "unclean" techniques (patching an |
36 |
internal libmysql structure for one thing) and was initially hacked |
37 |
within a few hours on a long flight to Malaysia. |
38 |
|
39 |
It does, however, check whether it indeed got the structure layout |
40 |
correct, so you should expect perl exceptions or early crashes as |
41 |
opposed to data corruption when something goes wrong during patching. |
42 |
|
43 |
SPEED |
44 |
This module is implemented in XS, and as long as mysqld replies quickly |
45 |
enough, it adds no overhead to the standard libmysql communication |
46 |
routines (which are very badly written, btw.). In fact, since it has a |
47 |
more efficient buffering and allows requests to run in parallel, it |
48 |
often decreases the actual time to run many queries considerably. |
49 |
|
50 |
For very fast queries ("select 0"), this module can add noticable |
51 |
overhead (around 15%, 7% when EV can be used) as it tries to switch to |
52 |
other coroutines when mysqld doesn't deliver the data immediately, |
53 |
although, again, when running queries in parallel, they will usually |
54 |
execute faster. |
55 |
|
56 |
For most types of queries, there will be no extra latency, especially on |
57 |
multicore systems where your perl process can do other things while |
58 |
mysqld does its stuff. |
59 |
|
60 |
LIMITATIONS |
61 |
This module only supports "standard" mysql connection handles - this |
62 |
means unix domain or TCP sockets, and excludes SSL/TLS connections, |
63 |
named pipes (windows) and shared memory (also windows). No support for |
64 |
these connection types is planned, either. |
65 |
|
66 |
CANCELLATION |
67 |
Cancelling a thread that is within a mysql query will likely make the |
68 |
handle unusable. As far as Coro::Mysql is concerned, the handle can be |
69 |
safely destroyed, but it's not clear how mysql itself will react to a |
70 |
cancellation. |
71 |
|
72 |
FUNCTIONS |
73 |
Coro::Mysql offers a single user-accessible function: |
74 |
|
75 |
$DBH = Coro::Mysql::unblock $DBH |
76 |
This function takes a DBI database handles and "patches" it so it |
77 |
becomes compatible to Coro threads. |
78 |
|
79 |
After that, it returns the patched handle - you should always use |
80 |
the newly returned database handle. |
81 |
|
82 |
It is safe to call this function on any database handle (or just |
83 |
about any value), but it will only do anything to DBD::mysql |
84 |
handles, others are returned unchanged. That means it is harmless |
85 |
when applied to database handles of other databases. |
86 |
|
87 |
It is also safe to pass "undef", so code like this is works as |
88 |
expected: |
89 |
|
90 |
my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
91 |
or die $DBI::errstr; |
92 |
|
93 |
USAGE EXAMPLE |
94 |
This example uses PApp::SQL and Coro::on_enter to implement a function |
95 |
"with_db", that connects to a database, uses "unblock" on the resulting |
96 |
handle and then makes sure that $PApp::SQL::DBH is set to the |
97 |
(per-thread) database handle when the given thread is running (it does |
98 |
not restore any previous value of $PApp::SQL::DBH, however): |
99 |
|
100 |
use Coro; |
101 |
use Coro::Mysql; |
102 |
use PApp::SQL; |
103 |
|
104 |
sub with_db($$$&) { |
105 |
my ($database, $user, $pass, $cb) = @_; |
106 |
|
107 |
my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
108 |
or die $DBI::errstr; |
109 |
|
110 |
Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
111 |
|
112 |
$cb->(); |
113 |
} |
114 |
|
115 |
This function makes it possible to easily use PApp::SQL with |
116 |
Coro::Mysql, without worrying about database handles. |
117 |
|
118 |
# now start 10 threads doing stuff |
119 |
async { |
120 |
|
121 |
with_db "DBI:mysql:test", "", "", sub { |
122 |
sql_exec "update table set col = 5 where id = 7"; |
123 |
|
124 |
my $st = sql_exec \my ($id, $name), |
125 |
"select id, name from table where name like ?", |
126 |
"a%"; |
127 |
|
128 |
while ($st->fetch) { |
129 |
... |
130 |
} |
131 |
|
132 |
my $id = sql_insertid sql_exec "insert into table values (1,2,3)"; |
133 |
# etc. |
134 |
}; |
135 |
|
136 |
} for 1..10; |
137 |
|
138 |
SEE ALSO |
139 |
Coro, PApp::SQL (a user friendly but efficient wrapper around DBI). |
140 |
|
141 |
AUTHOR |
142 |
Marc Lehmann <schmorp@schmorp.de> |
143 |
http://home.schmorp.de/ |
144 |
|