1 | =head1 NAME |
1 | =head1 NAME |
2 | |
2 | |
3 | Coro::Mysql - let other threads run while doing mysql requests |
3 | Coro::Mysql - let other threads run while doing mysql/mariadb requests |
4 | |
4 | |
5 | =head1 SYNOPSIS |
5 | =head1 SYNOPSIS |
6 | |
6 | |
7 | use Coro::Mysql; |
7 | use Coro::Mysql; |
8 | |
8 | |
9 | my $DBH = Coro::Mysql::unblock DBI->connect (...); |
9 | my $DBH = Coro::Mysql::unblock DBI->connect (...); |
10 | |
10 | |
11 | =head1 DESCRIPTION |
11 | =head1 DESCRIPTION |
12 | |
12 | |
13 | (Note that in this manual, "thread" refers to real threads as implemented |
13 | (Note that in this manual, "thread" refers to real threads as implemented |
14 | by the Coro module, not to the built-in windows process emulation which |
14 | by the L<Coro> module, not to the built-in windows process emulation which |
15 | unfortunately is also called "threads") |
15 | unfortunately is also called "threads"). |
16 | |
16 | |
17 | This module "patches" DBD::mysql database handles so that they do not |
17 | This module replaces the I/O handlers for a database connection, with the |
18 | block the whole process, but only the thread that they are used in. |
18 | 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. |
19 | |
22 | |
20 | This can be used to make parallel sql requests using Coro, or to do other |
23 | This can be used to make parallel sql requests using Coro, or to do other |
21 | stuff while mysql is rumbling in the background. |
24 | stuff while mariadb is rumbling in the background. |
22 | |
25 | |
23 | =head2 CAVEAT |
26 | =head2 CAVEAT |
24 | |
27 | |
25 | Note that this module must be linked against exactly the same |
28 | Note that this module must be linked against exactly the same (shared, |
26 | F<libmysqlclient> library as DBD::mysql, otherwise it will not work. |
29 | possibly not working with all OSes) F<libmariadb>/F<libmysqlclient> |
|
|
30 | library as L<DBD::MariaDB>/L<DBD::mysql>, otherwise it will not work. |
27 | |
31 | |
28 | Also, while this module makes database handles non-blocking, you still |
32 | Also, while this module makes database handles non-blocking, you still |
29 | cannot run multiple requests in parallel on the same database handle. If |
33 | cannot run multiple requests in parallel on the same database handle. If |
30 | you want to run multiple queries in parallel, you have to create multiple |
34 | you want to run multiple queries in parallel, you have to create multiple |
31 | database connections, one for each thread that runs queries. |
35 | 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. |
32 | |
38 | |
33 | If you make sure that you never run two or more requests in parallel, you |
39 | If you make sure that you never run two or more requests in parallel, you |
34 | cna freely share the database handles between threads, of course. |
40 | can freely share the database handles between threads, of course. |
35 | |
|
|
36 | Also, this module uses a number of "unclean" techniques (patching an |
|
|
37 | internal libmysql structure for one thing) and was hacked within a few |
|
|
38 | hours on a long flight to Malaysia. |
|
|
39 | |
|
|
40 | It does, however, check whether it indeed got the structure layout |
|
|
41 | correct, so you should expect perl exceptions or early crashes as opposed |
|
|
42 | to data corruption when something goes wrong. |
|
|
43 | |
41 | |
44 | =head2 SPEED |
42 | =head2 SPEED |
45 | |
43 | |
46 | This module is implemented in XS, and as long as mysqld replies quickly |
44 | This module is implemented in XS, and as long as mysqld replies quickly |
47 | enough, it adds no overhead to the standard libmysql communication |
45 | enough, it adds no overhead to the standard libmysql communication |
48 | routines (which are very badly written). |
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 often |
|
|
48 | decreases the actual time to run many queries considerably. |
49 | |
49 | |
50 | For very fast queries ("select 0"), this module can add noticable overhead |
50 | For very fast queries ("select 0"), this module can add noticable overhead |
51 | (around 15%) as it tries to switch to other coroutines when mysqld doesn't |
51 | (around 15%, 7% when EV can be used) as it tries to switch to other |
52 | deliver the data instantly. |
52 | coroutines when mysqld doesn't deliver the data immediately, although, |
|
|
53 | again, when running queries in parallel, they will usually execute faster. |
53 | |
54 | |
54 | For most types of queries, there will be no overhead, especially on |
55 | For most types of queries, there will be no extra latency, especially on |
55 | multicore systems where your perl process can do other things while mysqld |
56 | multicore systems where your perl process can do other things while mysqld |
56 | does its stuff. |
57 | does its stuff. |
57 | |
58 | |
|
|
59 | =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 | =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 | =head1 FUNCTIONS |
|
|
74 | |
|
|
75 | Coro::Mysql offers these functions, the only one that oyu usually need is C<unblock>: |
|
|
76 | |
58 | =over 4 |
77 | =over 4 |
59 | |
78 | |
60 | =cut |
79 | =cut |
61 | |
80 | |
62 | package Coro::Mysql; |
81 | package Coro::Mysql; |
… | |
… | |
66 | |
85 | |
67 | use Scalar::Util (); |
86 | use Scalar::Util (); |
68 | use Carp qw(croak); |
87 | use Carp qw(croak); |
69 | |
88 | |
70 | use Guard; |
89 | use Guard; |
|
|
90 | use AnyEvent (); |
71 | use Coro::Handle (); |
91 | use Coro (); |
|
|
92 | use Coro::AnyEvent (); # not necessary with newer Coro versions |
72 | |
93 | |
73 | # we need this extra indirection, as Coro doesn't support |
94 | # we need this extra indirection, as Coro doesn't support |
74 | # calling SLF-like functions via call_sv. |
95 | # calling SLF-like functions via call_sv. |
75 | |
96 | |
76 | sub readable { &Coro::Handle::FH::readable } |
97 | sub readable { &Coro::Handle::FH::readable } |
77 | sub writable { &Coro::Handle::FH::writable } |
98 | sub writable { &Coro::Handle::FH::writable } |
78 | |
99 | |
79 | BEGIN { |
100 | BEGIN { |
80 | our $VERSION = '0.1'; |
101 | our $VERSION = '2.0'; |
81 | |
102 | |
82 | require XSLoader; |
103 | require XSLoader; |
83 | XSLoader::load Coro::Mysql::, $VERSION; |
104 | XSLoader::load Coro::Mysql::, $VERSION; |
84 | } |
105 | } |
85 | |
106 | |
… | |
… | |
89 | so it becomes compatible to Coro threads. |
110 | so it becomes compatible to Coro threads. |
90 | |
111 | |
91 | After that, it returns the patched handle - you should always use the |
112 | After that, it returns the patched handle - you should always use the |
92 | newly returned database handle. |
113 | newly returned database handle. |
93 | |
114 | |
|
|
115 | 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 | |
|
|
120 | 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 | |
94 | =cut |
125 | =cut |
95 | |
126 | |
96 | sub unblock { |
127 | sub unblock { |
97 | my ($DBH) = @_; |
128 | my ($DBH) = @_; |
98 | my $sock = $DBH->{sock}; |
|
|
99 | |
129 | |
|
|
130 | 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 | |
100 | open my $fh, "+>&" . $DBH->{sockfd} |
137 | open my $fh, "+>&$sockfd" |
101 | or croak "Coro::Mysql unable to clone mysql fd"; |
138 | or croak "Coro::Mysql unable to dup mariadb/mysql fd"; |
102 | |
139 | |
|
|
140 | if (AnyEvent::detect ne "AnyEvent::Impl::EV" || !_use_ev) { |
|
|
141 | require Coro::Handle; |
103 | $fh = Coro::Handle::unblock $fh; |
142 | $fh = Coro::Handle::unblock ($fh); |
|
|
143 | } |
104 | |
144 | |
105 | _patch $sock, $DBH->{sockfd}, tied ${$fh}; |
145 | _patch $sock, $sockfd, $cvers, $fh, tied *$$fh; |
106 | $DBH->{private_Coro_Mysql} = guard { |
146 | } |
107 | _unpatch $sock; |
|
|
108 | undef $fh; |
|
|
109 | }; |
147 | } |
110 | |
148 | |
111 | $DBH |
149 | $DBH |
112 | } |
150 | } |
113 | |
151 | |
|
|
152 | =item $bool = Coro::Mysql::is_unblocked $DBH |
|
|
153 | |
|
|
154 | Returns true iff the database handle was successfully patched for |
|
|
155 | non-blocking operations. |
|
|
156 | |
|
|
157 | =cut |
|
|
158 | |
|
|
159 | 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 | } |
|
|
168 | } |
|
|
169 | |
|
|
170 | 0 |
|
|
171 | } |
|
|
172 | |
|
|
173 | =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 | |
114 | 1; |
183 | 1; |
115 | |
184 | |
116 | =back |
185 | =back |
|
|
186 | |
|
|
187 | =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 | my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
|
|
203 | or die $DBI::errstr; |
|
|
204 | |
|
|
205 | Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
|
|
206 | |
|
|
207 | $cb->(); |
|
|
208 | } |
|
|
209 | |
|
|
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 | =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 | 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. |
117 | |
249 | |
118 | =head1 AUTHOR |
250 | =head1 AUTHOR |
119 | |
251 | |
120 | Marc Lehmann <schmorp@schmorp.de> |
252 | Marc Lehmann <schmorp@schmorp.de> |
121 | http://home.schmorp.de/ |
253 | http://home.schmorp.de/ |