1 |
root |
1.1 |
=head1 NAME |
2 |
|
|
|
3 |
root |
1.23 |
Coro::Mysql - let other threads run while doing mysql/mariadb requests |
4 |
root |
1.1 |
|
5 |
|
|
=head1 SYNOPSIS |
6 |
|
|
|
7 |
|
|
use Coro::Mysql; |
8 |
|
|
|
9 |
|
|
my $DBH = Coro::Mysql::unblock DBI->connect (...); |
10 |
|
|
|
11 |
|
|
=head1 DESCRIPTION |
12 |
|
|
|
13 |
|
|
(Note that in this manual, "thread" refers to real threads as implemented |
14 |
root |
1.24 |
by the L<Coro> module, not to the built-in windows process emulation which |
15 |
|
|
unfortunately is also called "threads"). |
16 |
root |
1.1 |
|
17 |
root |
1.13 |
This module replaces the I/O handlers for a database connection, with the |
18 |
root |
1.24 |
effect that "patched" database handles no longer block all threads of a |
19 |
|
|
process, but only the thread that does the request. It should work for |
20 |
|
|
both L<DBD::mysql> and L<DBD::MariaDB> connections and a wide range of |
21 |
|
|
mariadb/mysql client libraries. |
22 |
root |
1.1 |
|
23 |
|
|
This can be used to make parallel sql requests using Coro, or to do other |
24 |
root |
1.23 |
stuff while mariadb is rumbling in the background. |
25 |
root |
1.1 |
|
26 |
|
|
=head2 CAVEAT |
27 |
|
|
|
28 |
root |
1.7 |
Note that this module must be linked against exactly the same (shared, |
29 |
root |
1.23 |
possibly not working with all OSes) F<libmariadb>/F<libmysqlclient> |
30 |
root |
1.24 |
library as L<DBD::MariaDB>/L<DBD::mysql>, otherwise it will not work. |
31 |
root |
1.13 |
|
32 |
root |
1.1 |
Also, while this module makes database handles non-blocking, you still |
33 |
|
|
cannot run multiple requests in parallel on the same database handle. If |
34 |
|
|
you want to run multiple queries in parallel, you have to create multiple |
35 |
root |
1.13 |
database connections, one for each thread that runs queries. Not doing |
36 |
|
|
so can corrupt your data - use a Coro::Semaphore to protetc access to a |
37 |
|
|
shared database handle when in doubt. |
38 |
root |
1.1 |
|
39 |
|
|
If you make sure that you never run two or more requests in parallel, you |
40 |
root |
1.4 |
can freely share the database handles between threads, of course. |
41 |
root |
1.1 |
|
42 |
|
|
=head2 SPEED |
43 |
|
|
|
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 |
root |
1.9 |
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 often |
48 |
|
|
decreases the actual time to run many queries considerably. |
49 |
root |
1.1 |
|
50 |
|
|
For very fast queries ("select 0"), this module can add noticable overhead |
51 |
root |
1.9 |
(around 15%, 7% when EV can be used) as it tries to switch to other |
52 |
|
|
coroutines when mysqld doesn't deliver the data immediately, although, |
53 |
|
|
again, when running queries in parallel, they will usually execute faster. |
54 |
root |
1.1 |
|
55 |
root |
1.8 |
For most types of queries, there will be no extra latency, especially on |
56 |
root |
1.1 |
multicore systems where your perl process can do other things while mysqld |
57 |
|
|
does its stuff. |
58 |
|
|
|
59 |
root |
1.4 |
=head2 LIMITATIONS |
60 |
|
|
|
61 |
|
|
This module only supports "standard" mysql connection handles - this |
62 |
|
|
means unix domain or TCP sockets, and excludes SSL/TLS connections, named |
63 |
|
|
pipes (windows) and shared memory (also windows). No support for these |
64 |
|
|
connection types is planned, either. |
65 |
|
|
|
66 |
root |
1.9 |
=head1 CANCELLATION |
67 |
|
|
|
68 |
|
|
Cancelling a thread that is within a mysql query will likely make the |
69 |
|
|
handle unusable. As far as Coro::Mysql is concerned, the handle can be |
70 |
|
|
safely destroyed, but it's not clear how mysql itself will react to a |
71 |
|
|
cancellation. |
72 |
|
|
|
73 |
root |
1.4 |
=head1 FUNCTIONS |
74 |
|
|
|
75 |
root |
1.24 |
Coro::Mysql offers these functions, the only one that oyu usually need is C<unblock>: |
76 |
root |
1.4 |
|
77 |
root |
1.1 |
=over 4 |
78 |
|
|
|
79 |
|
|
=cut |
80 |
|
|
|
81 |
|
|
package Coro::Mysql; |
82 |
|
|
|
83 |
|
|
use strict qw(vars subs); |
84 |
|
|
no warnings; |
85 |
|
|
|
86 |
|
|
use Scalar::Util (); |
87 |
|
|
use Carp qw(croak); |
88 |
|
|
|
89 |
|
|
use Guard; |
90 |
root |
1.9 |
use AnyEvent (); |
91 |
|
|
use Coro (); |
92 |
|
|
use Coro::AnyEvent (); # not necessary with newer Coro versions |
93 |
root |
1.1 |
|
94 |
|
|
# we need this extra indirection, as Coro doesn't support |
95 |
|
|
# calling SLF-like functions via call_sv. |
96 |
|
|
|
97 |
|
|
sub readable { &Coro::Handle::FH::readable } |
98 |
|
|
sub writable { &Coro::Handle::FH::writable } |
99 |
|
|
|
100 |
|
|
BEGIN { |
101 |
root |
1.25 |
our $VERSION = '2.1'; |
102 |
root |
1.1 |
|
103 |
|
|
require XSLoader; |
104 |
|
|
XSLoader::load Coro::Mysql::, $VERSION; |
105 |
|
|
} |
106 |
|
|
|
107 |
|
|
=item $DBH = Coro::Mysql::unblock $DBH |
108 |
|
|
|
109 |
|
|
This function takes a DBI database handles and "patches" it |
110 |
|
|
so it becomes compatible to Coro threads. |
111 |
|
|
|
112 |
|
|
After that, it returns the patched handle - you should always use the |
113 |
|
|
newly returned database handle. |
114 |
|
|
|
115 |
root |
1.4 |
It is safe to call this function on any database handle (or just about any |
116 |
|
|
value), but it will only do anything to L<DBD::mysql> handles, others are |
117 |
|
|
returned unchanged. That means it is harmless when applied to database |
118 |
|
|
handles of other databases. |
119 |
root |
1.3 |
|
120 |
root |
1.10 |
It is also safe to pass C<undef>, so code like this is works as expected: |
121 |
|
|
|
122 |
|
|
my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
123 |
|
|
or die $DBI::errstr; |
124 |
|
|
|
125 |
root |
1.1 |
=cut |
126 |
|
|
|
127 |
|
|
sub unblock { |
128 |
|
|
my ($DBH) = @_; |
129 |
|
|
|
130 |
root |
1.24 |
if ($DBH) { |
131 |
|
|
my $mariadb = $DBH->{Driver}{Name} eq "MariaDB"; |
132 |
|
|
if ($mariadb or $DBH->{Driver}{Name} eq "mysql") { |
133 |
|
|
my $sock = $mariadb ? $DBH->{mariadb_sock} : $DBH->{sock}; |
134 |
|
|
my $sockfd = $mariadb ? $DBH->mariadb_sockfd : $DBH->{sockfd}; |
135 |
|
|
my $cvers = $mariadb ? $DBH->{mariadb_clientversion} : $DBH->{mysql_clientversion}; |
136 |
|
|
|
137 |
|
|
open my $fh, "+>&$sockfd" |
138 |
|
|
or croak "Coro::Mysql unable to dup mariadb/mysql fd"; |
139 |
|
|
|
140 |
|
|
if (AnyEvent::detect ne "AnyEvent::Impl::EV" || !_use_ev) { |
141 |
|
|
require Coro::Handle; |
142 |
|
|
$fh = Coro::Handle::unblock ($fh); |
143 |
|
|
} |
144 |
|
|
|
145 |
|
|
_patch $sock, $sockfd, $cvers, $fh, tied *$$fh; |
146 |
|
|
} |
147 |
|
|
} |
148 |
|
|
|
149 |
|
|
$DBH |
150 |
|
|
} |
151 |
|
|
|
152 |
|
|
=item $bool = Coro::Mysql::is_unblocked $DBH |
153 |
root |
1.3 |
|
154 |
root |
1.24 |
Returns true iff the database handle was successfully patched for |
155 |
|
|
non-blocking operations. |
156 |
|
|
|
157 |
|
|
=cut |
158 |
root |
1.1 |
|
159 |
root |
1.24 |
sub is_unblocked { |
160 |
|
|
my ($DBH) = @_; |
161 |
|
|
|
162 |
|
|
if ($DBH) { |
163 |
|
|
my $mariadb = $DBH->{Driver}{Name} eq "MariaDB"; |
164 |
|
|
if ($mariadb or $DBH->{Driver}{Name} eq "mysql") { |
165 |
|
|
my $sock = $mariadb ? $DBH->{mariadb_sock} : $DBH->{sock}; |
166 |
|
|
return _is_patched $sock |
167 |
root |
1.9 |
} |
168 |
root |
1.3 |
} |
169 |
root |
1.1 |
|
170 |
root |
1.24 |
0 |
171 |
root |
1.1 |
} |
172 |
|
|
|
173 |
root |
1.24 |
=item $bool = Coro::Mysql::have_ev |
174 |
|
|
|
175 |
|
|
Returns true if this Coro::Mysql installation is compiled with special |
176 |
|
|
support for L<EV> or not. |
177 |
|
|
|
178 |
|
|
Even if compiled in, it will only be used if L<EV> is actually the |
179 |
|
|
AnyEvent event backend. |
180 |
|
|
|
181 |
|
|
=cut |
182 |
|
|
|
183 |
root |
1.1 |
1; |
184 |
|
|
|
185 |
|
|
=back |
186 |
|
|
|
187 |
root |
1.4 |
=head1 USAGE EXAMPLE |
188 |
|
|
|
189 |
|
|
This example uses L<PApp::SQL> and L<Coro::on_enter> to implement a |
190 |
|
|
function C<with_db>, that connects to a database, uses C<unblock> on the |
191 |
|
|
resulting handle and then makes sure that C<$PApp::SQL::DBH> is set to the |
192 |
|
|
(per-thread) database handle when the given thread is running (it does not |
193 |
|
|
restore any previous value of $PApp::SQL::DBH, however): |
194 |
|
|
|
195 |
|
|
use Coro; |
196 |
|
|
use Coro::Mysql; |
197 |
|
|
use PApp::SQL; |
198 |
|
|
|
199 |
|
|
sub with_db($$$&) { |
200 |
|
|
my ($database, $user, $pass, $cb) = @_; |
201 |
|
|
|
202 |
root |
1.9 |
my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
203 |
root |
1.4 |
or die $DBI::errstr; |
204 |
|
|
|
205 |
|
|
Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
206 |
|
|
|
207 |
|
|
$cb->(); |
208 |
root |
1.23 |
} |
209 |
root |
1.4 |
|
210 |
|
|
This function makes it possible to easily use L<PApp::SQL> with |
211 |
|
|
L<Coro::Mysql>, without worrying about database handles. |
212 |
|
|
|
213 |
|
|
# now start 10 threads doing stuff |
214 |
|
|
async { |
215 |
|
|
|
216 |
|
|
with_db "DBI:mysql:test", "", "", sub { |
217 |
|
|
sql_exec "update table set col = 5 where id = 7"; |
218 |
|
|
|
219 |
|
|
my $st = sql_exec \my ($id, $name), |
220 |
|
|
"select id, name from table where name like ?", |
221 |
|
|
"a%"; |
222 |
|
|
|
223 |
|
|
while ($st->fetch) { |
224 |
|
|
... |
225 |
|
|
} |
226 |
|
|
|
227 |
|
|
my $id = sql_insertid sql_exec "insert into table values (1,2,3)"; |
228 |
|
|
# etc. |
229 |
|
|
}; |
230 |
|
|
|
231 |
|
|
} for 1..10; |
232 |
|
|
|
233 |
|
|
=head1 SEE ALSO |
234 |
|
|
|
235 |
|
|
L<Coro>, L<PApp::SQL> (a user friendly but efficient wrapper around DBI). |
236 |
|
|
|
237 |
root |
1.13 |
=head1 HISTORY |
238 |
|
|
|
239 |
|
|
This module was initially hacked together within a few hours on a long |
240 |
|
|
flight to Malaysia, and seems to have worked ever since, with minor |
241 |
|
|
adjustments for newer libmysqlclient libraries. |
242 |
|
|
|
243 |
root |
1.23 |
Well, at least until mariadb introduced the new Pluggable Virtual IO API |
244 |
|
|
in mariadb 10.3, which changed and broke everything. On the positive |
245 |
|
|
side, the old system was horrible to use, as many GNU/Linux distributions |
246 |
|
|
forgot to include the required heaqder files and there were frequent small |
247 |
|
|
changes, while the new PVIO system seems to be "official" and hopefully |
248 |
|
|
better supported. |
249 |
|
|
|
250 |
root |
1.1 |
=head1 AUTHOR |
251 |
|
|
|
252 |
|
|
Marc Lehmann <schmorp@schmorp.de> |
253 |
|
|
http://home.schmorp.de/ |
254 |
|
|
|
255 |
|
|
=cut |
256 |
|
|
|