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 | |
… | |
… | |
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 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 the all threads of |
|
|
19 | a process, but only the thread that does the request. |
19 | |
20 | |
20 | This can be used to make parallel sql requests using Coro, or to do other |
21 | This can be used to make parallel sql requests using Coro, or to do other |
21 | stuff while mysql is rumbling in the background. |
22 | stuff while mariadb is rumbling in the background. |
22 | |
23 | |
23 | =head2 CAVEAT |
24 | =head2 CAVEAT |
24 | |
25 | |
25 | Note that this module must be linked against exactly the same (shared, |
26 | Note that this module must be linked against exactly the same (shared, |
26 | possibly not working with all OSes) F<libmysqlclient> library as |
27 | possibly not working with all OSes) F<libmariadb>/F<libmysqlclient> |
27 | DBD::mysql, otherwise it will not work. |
28 | library as L<DBD::mysql>, otherwise it will not work. |
28 | |
29 | |
29 | Also, while this module makes database handles non-blocking, you still |
30 | Also, while this module makes database handles non-blocking, you still |
30 | cannot run multiple requests in parallel on the same database handle. If |
31 | cannot run multiple requests in parallel on the same database handle. If |
31 | you want to run multiple queries in parallel, you have to create multiple |
32 | you want to run multiple queries in parallel, you have to create multiple |
32 | database connections, one for each thread that runs queries. Not doing so |
33 | database connections, one for each thread that runs queries. Not doing |
33 | can corrupt your data - use a Coro::Semaphore when in doubt. |
34 | so can corrupt your data - use a Coro::Semaphore to protetc access to a |
|
|
35 | shared database handle when in doubt. |
34 | |
36 | |
35 | If you make sure that you never run two or more requests in parallel, you |
37 | If you make sure that you never run two or more requests in parallel, you |
36 | can freely share the database handles between threads, of course. |
38 | can freely share the database handles between threads, of course. |
37 | |
39 | |
38 | Also, this module uses a number of "unclean" techniques (patching an |
|
|
39 | internal libmysql structure for one thing) and was hacked within a few |
|
|
40 | hours on a long flight to Malaysia. |
|
|
41 | |
|
|
42 | It does, however, check whether it indeed got the structure layout |
|
|
43 | correct, so you should expect perl exceptions or early crashes as opposed |
|
|
44 | to data corruption when something goes wrong during patching. |
|
|
45 | |
|
|
46 | =head2 SPEED |
40 | =head2 SPEED |
47 | |
41 | |
48 | This module is implemented in XS, and as long as mysqld replies quickly |
42 | This module is implemented in XS, and as long as mysqld replies quickly |
49 | enough, it adds no overhead to the standard libmysql communication |
43 | enough, it adds no overhead to the standard libmysql communication |
50 | routines (which are very badly written, btw.). |
44 | routines (which are very badly written, btw.). In fact, since it has a |
|
|
45 | more efficient buffering and allows requests to run in parallel, it often |
|
|
46 | decreases the actual time to run many queries considerably. |
51 | |
47 | |
52 | For very fast queries ("select 0"), this module can add noticable overhead |
48 | For very fast queries ("select 0"), this module can add noticable overhead |
53 | (around 15%) as it tries to switch to other coroutines when mysqld doesn't |
49 | (around 15%, 7% when EV can be used) as it tries to switch to other |
54 | deliver the data instantly. |
50 | coroutines when mysqld doesn't deliver the data immediately, although, |
|
|
51 | again, when running queries in parallel, they will usually execute faster. |
55 | |
52 | |
56 | For most types of queries, there will be no extra latency, especially on |
53 | 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 mysqld |
54 | multicore systems where your perl process can do other things while mysqld |
58 | does its stuff. |
55 | does its stuff. |
59 | |
56 | |
… | |
… | |
62 | This module only supports "standard" mysql connection handles - this |
59 | This module only supports "standard" mysql connection handles - this |
63 | means unix domain or TCP sockets, and excludes SSL/TLS connections, named |
60 | means unix domain or TCP sockets, and excludes SSL/TLS connections, named |
64 | pipes (windows) and shared memory (also windows). No support for these |
61 | pipes (windows) and shared memory (also windows). No support for these |
65 | connection types is planned, either. |
62 | connection types is planned, either. |
66 | |
63 | |
|
|
64 | =head1 CANCELLATION |
|
|
65 | |
|
|
66 | Cancelling a thread that is within a mysql query will likely make the |
|
|
67 | handle unusable. As far as Coro::Mysql is concerned, the handle can be |
|
|
68 | safely destroyed, but it's not clear how mysql itself will react to a |
|
|
69 | cancellation. |
|
|
70 | |
67 | =head1 FUNCTIONS |
71 | =head1 FUNCTIONS |
68 | |
72 | |
69 | Coro::Mysql offers a single user-accessible function: |
73 | Coro::Mysql offers a single user-accessible function: |
70 | |
74 | |
71 | =over 4 |
75 | =over 4 |
… | |
… | |
79 | |
83 | |
80 | use Scalar::Util (); |
84 | use Scalar::Util (); |
81 | use Carp qw(croak); |
85 | use Carp qw(croak); |
82 | |
86 | |
83 | use Guard; |
87 | use Guard; |
|
|
88 | use AnyEvent (); |
84 | use Coro::Handle (); |
89 | use Coro (); |
|
|
90 | use Coro::AnyEvent (); # not necessary with newer Coro versions |
85 | |
91 | |
86 | # we need this extra indirection, as Coro doesn't support |
92 | # we need this extra indirection, as Coro doesn't support |
87 | # calling SLF-like functions via call_sv. |
93 | # calling SLF-like functions via call_sv. |
88 | |
94 | |
89 | sub readable { &Coro::Handle::FH::readable } |
95 | sub readable { &Coro::Handle::FH::readable } |
90 | sub writable { &Coro::Handle::FH::writable } |
96 | sub writable { &Coro::Handle::FH::writable } |
91 | |
97 | |
92 | BEGIN { |
98 | BEGIN { |
93 | our $VERSION = '1.02'; |
99 | our $VERSION = '2.0'; |
94 | |
100 | |
95 | require XSLoader; |
101 | require XSLoader; |
96 | XSLoader::load Coro::Mysql::, $VERSION; |
102 | XSLoader::load Coro::Mysql::, $VERSION; |
97 | } |
103 | } |
98 | |
104 | |
… | |
… | |
107 | It is safe to call this function on any database handle (or just about any |
113 | It is safe to call this function on any database handle (or just about any |
108 | value), but it will only do anything to L<DBD::mysql> handles, others are |
114 | value), but it will only do anything to L<DBD::mysql> handles, others are |
109 | returned unchanged. That means it is harmless when applied to database |
115 | returned unchanged. That means it is harmless when applied to database |
110 | handles of other databases. |
116 | handles of other databases. |
111 | |
117 | |
|
|
118 | It is also safe to pass C<undef>, so code like this is works as expected: |
|
|
119 | |
|
|
120 | my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
|
|
121 | or die $DBI::errstr; |
|
|
122 | |
112 | =cut |
123 | =cut |
113 | |
124 | |
114 | sub unblock { |
125 | sub unblock { |
115 | my ($DBH) = @_; |
126 | my ($DBH) = @_; |
116 | |
127 | |
117 | if ($DBH->{Driver}{Name} eq "mysql") { |
128 | if ($DBH && $DBH->{Driver}{Name} eq "mysql") { |
118 | my $sock = $DBH->{sock}; |
129 | my $sock = $DBH->{sock}; |
119 | |
130 | |
120 | open my $fh, "+>&" . $DBH->{sockfd} |
131 | open my $fh, "+>&" . $DBH->{sockfd} |
121 | or croak "Coro::Mysql unable to clone mysql fd"; |
132 | or croak "Coro::Mysql unable to clone mysql fd"; |
122 | |
133 | |
|
|
134 | if (AnyEvent::detect ne "AnyEvent::Impl::EV" || !_use_ev) { |
|
|
135 | require Coro::Handle; |
123 | $fh = Coro::Handle::unblock $fh; |
136 | $fh = Coro::Handle::unblock ($fh); |
|
|
137 | } |
124 | |
138 | |
125 | _patch $sock, $DBH->{sockfd}, $fh, tied ${$fh}; |
139 | _patch $sock, $DBH->{sockfd}, $DBH->{mysql_clientversion}, $fh, tied *$$fh; |
126 | } |
140 | } |
127 | |
141 | |
128 | $DBH |
142 | $DBH |
129 | } |
143 | } |
130 | |
144 | |
… | |
… | |
145 | use PApp::SQL; |
159 | use PApp::SQL; |
146 | |
160 | |
147 | sub with_db($$$&) { |
161 | sub with_db($$$&) { |
148 | my ($database, $user, $pass, $cb) = @_; |
162 | my ($database, $user, $pass, $cb) = @_; |
149 | |
163 | |
150 | my $dbh = Coro::Mysql::unblock DBI->connect ($database, $user, $pass) |
164 | my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
151 | or die $DBI::errstr; |
165 | or die $DBI::errstr; |
152 | |
166 | |
153 | Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
167 | Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
154 | |
168 | |
155 | $cb->(); |
169 | $cb->(); |
156 | } |
170 | } |
157 | |
171 | |
158 | This function makes it possible to easily use L<PApp::SQL> with |
172 | This function makes it possible to easily use L<PApp::SQL> with |
159 | L<Coro::Mysql>, without worrying about database handles. |
173 | L<Coro::Mysql>, without worrying about database handles. |
160 | |
174 | |
161 | # now start 10 threads doing stuff |
175 | # now start 10 threads doing stuff |
… | |
… | |
180 | |
194 | |
181 | =head1 SEE ALSO |
195 | =head1 SEE ALSO |
182 | |
196 | |
183 | L<Coro>, L<PApp::SQL> (a user friendly but efficient wrapper around DBI). |
197 | L<Coro>, L<PApp::SQL> (a user friendly but efficient wrapper around DBI). |
184 | |
198 | |
|
|
199 | =head1 HISTORY |
|
|
200 | |
|
|
201 | This module was initially hacked together within a few hours on a long |
|
|
202 | flight to Malaysia, and seems to have worked ever since, with minor |
|
|
203 | adjustments for newer libmysqlclient libraries. |
|
|
204 | |
|
|
205 | Well, at least until mariadb introduced the new Pluggable Virtual IO API |
|
|
206 | in mariadb 10.3, which changed and broke everything. On the positive |
|
|
207 | side, the old system was horrible to use, as many GNU/Linux distributions |
|
|
208 | forgot to include the required heaqder files and there were frequent small |
|
|
209 | changes, while the new PVIO system seems to be "official" and hopefully |
|
|
210 | better supported. |
|
|
211 | |
185 | =head1 AUTHOR |
212 | =head1 AUTHOR |
186 | |
213 | |
187 | Marc Lehmann <schmorp@schmorp.de> |
214 | Marc Lehmann <schmorp@schmorp.de> |
188 | http://home.schmorp.de/ |
215 | http://home.schmorp.de/ |
189 | |
216 | |