1 |
root |
1.2 |
NAME |
2 |
root |
1.6 |
Coro::Mysql - let other threads run while doing mysql/mariadb requests |
3 |
root |
1.2 |
|
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 |
root |
1.6 |
emulation which unfortunately is also called "threads"). |
13 |
root |
1.2 |
|
14 |
root |
1.5 |
This module replaces the I/O handlers for a database connection, with |
15 |
root |
1.6 |
the effect that "patched" database handles no longer block all threads |
16 |
|
|
of a process, but only the thread that does the request. It should work |
17 |
|
|
for both DBD::mysql and DBD::MariaDB connections and a wide range of |
18 |
|
|
mariadb/mysql client libraries. |
19 |
root |
1.2 |
|
20 |
|
|
This can be used to make parallel sql requests using Coro, or to do |
21 |
root |
1.6 |
other stuff while mariadb is rumbling in the background. |
22 |
root |
1.2 |
|
23 |
|
|
CAVEAT |
24 |
root |
1.4 |
Note that this module must be linked against exactly the same (shared, |
25 |
root |
1.6 |
possibly not working with all OSes) libmariadb/libmysqlclient library as |
26 |
|
|
DBD::MariaDB/DBD::mysql, otherwise it will not work. |
27 |
root |
1.5 |
|
28 |
root |
1.2 |
Also, while this module makes database handles non-blocking, you still |
29 |
|
|
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 |
31 |
|
|
multiple database connections, one for each thread that runs queries. |
32 |
root |
1.5 |
Not doing so can corrupt your data - use a Coro::Semaphore to protetc |
33 |
|
|
access to a shared database handle when in doubt. |
34 |
root |
1.2 |
|
35 |
|
|
If you make sure that you never run two or more requests in parallel, |
36 |
root |
1.3 |
you can freely share the database handles between threads, of course. |
37 |
root |
1.2 |
|
38 |
|
|
SPEED |
39 |
|
|
This module is implemented in XS, and as long as mysqld replies quickly |
40 |
|
|
enough, it adds no overhead to the standard libmysql communication |
41 |
root |
1.4 |
routines (which are very badly written, btw.). In fact, since it has a |
42 |
|
|
more efficient buffering and allows requests to run in parallel, it |
43 |
|
|
often decreases the actual time to run many queries considerably. |
44 |
root |
1.2 |
|
45 |
|
|
For very fast queries ("select 0"), this module can add noticable |
46 |
root |
1.4 |
overhead (around 15%, 7% when EV can be used) as it tries to switch to |
47 |
|
|
other coroutines when mysqld doesn't deliver the data immediately, |
48 |
|
|
although, again, when running queries in parallel, they will usually |
49 |
|
|
execute faster. |
50 |
root |
1.2 |
|
51 |
root |
1.4 |
For most types of queries, there will be no extra latency, especially on |
52 |
root |
1.2 |
multicore systems where your perl process can do other things while |
53 |
|
|
mysqld does its stuff. |
54 |
|
|
|
55 |
root |
1.3 |
LIMITATIONS |
56 |
|
|
This module only supports "standard" mysql connection handles - this |
57 |
|
|
means unix domain or TCP sockets, and excludes SSL/TLS connections, |
58 |
|
|
named pipes (windows) and shared memory (also windows). No support for |
59 |
|
|
these connection types is planned, either. |
60 |
|
|
|
61 |
root |
1.4 |
CANCELLATION |
62 |
|
|
Cancelling a thread that is within a mysql query will likely make the |
63 |
|
|
handle unusable. As far as Coro::Mysql is concerned, the handle can be |
64 |
|
|
safely destroyed, but it's not clear how mysql itself will react to a |
65 |
|
|
cancellation. |
66 |
|
|
|
67 |
root |
1.3 |
FUNCTIONS |
68 |
root |
1.6 |
Coro::Mysql offers these functions, the only one that oyu usually need |
69 |
|
|
is "unblock": |
70 |
root |
1.3 |
|
71 |
root |
1.2 |
$DBH = Coro::Mysql::unblock $DBH |
72 |
|
|
This function takes a DBI database handles and "patches" it so it |
73 |
|
|
becomes compatible to Coro threads. |
74 |
|
|
|
75 |
|
|
After that, it returns the patched handle - you should always use |
76 |
|
|
the newly returned database handle. |
77 |
|
|
|
78 |
root |
1.3 |
It is safe to call this function on any database handle (or just |
79 |
|
|
about any value), but it will only do anything to DBD::mysql |
80 |
|
|
handles, others are returned unchanged. That means it is harmless |
81 |
|
|
when applied to database handles of other databases. |
82 |
|
|
|
83 |
root |
1.4 |
It is also safe to pass "undef", so code like this is works as |
84 |
|
|
expected: |
85 |
|
|
|
86 |
|
|
my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
87 |
|
|
or die $DBI::errstr; |
88 |
|
|
|
89 |
root |
1.6 |
$bool = Coro::Mysql::is_unblocked $DBH |
90 |
|
|
Returns true iff the database handle was successfully patched for |
91 |
|
|
non-blocking operations. |
92 |
|
|
|
93 |
|
|
$bool = Coro::Mysql::have_ev |
94 |
|
|
Returns true if this Coro::Mysql installation is compiled with |
95 |
|
|
special support for EV or not. |
96 |
|
|
|
97 |
|
|
Even if compiled in, it will only be used if EV is actually the |
98 |
|
|
AnyEvent event backend. |
99 |
|
|
|
100 |
root |
1.3 |
USAGE EXAMPLE |
101 |
|
|
This example uses PApp::SQL and Coro::on_enter to implement a function |
102 |
|
|
"with_db", that connects to a database, uses "unblock" on the resulting |
103 |
|
|
handle and then makes sure that $PApp::SQL::DBH is set to the |
104 |
|
|
(per-thread) database handle when the given thread is running (it does |
105 |
|
|
not restore any previous value of $PApp::SQL::DBH, however): |
106 |
|
|
|
107 |
|
|
use Coro; |
108 |
|
|
use Coro::Mysql; |
109 |
|
|
use PApp::SQL; |
110 |
|
|
|
111 |
|
|
sub with_db($$$&) { |
112 |
|
|
my ($database, $user, $pass, $cb) = @_; |
113 |
|
|
|
114 |
root |
1.4 |
my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
115 |
root |
1.3 |
or die $DBI::errstr; |
116 |
|
|
|
117 |
|
|
Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
118 |
|
|
|
119 |
|
|
$cb->(); |
120 |
|
|
} |
121 |
|
|
|
122 |
|
|
This function makes it possible to easily use PApp::SQL with |
123 |
|
|
Coro::Mysql, without worrying about database handles. |
124 |
|
|
|
125 |
|
|
# now start 10 threads doing stuff |
126 |
|
|
async { |
127 |
|
|
|
128 |
|
|
with_db "DBI:mysql:test", "", "", sub { |
129 |
|
|
sql_exec "update table set col = 5 where id = 7"; |
130 |
|
|
|
131 |
|
|
my $st = sql_exec \my ($id, $name), |
132 |
|
|
"select id, name from table where name like ?", |
133 |
|
|
"a%"; |
134 |
|
|
|
135 |
|
|
while ($st->fetch) { |
136 |
|
|
... |
137 |
|
|
} |
138 |
|
|
|
139 |
|
|
my $id = sql_insertid sql_exec "insert into table values (1,2,3)"; |
140 |
|
|
# etc. |
141 |
|
|
}; |
142 |
|
|
|
143 |
|
|
} for 1..10; |
144 |
|
|
|
145 |
|
|
SEE ALSO |
146 |
|
|
Coro, PApp::SQL (a user friendly but efficient wrapper around DBI). |
147 |
|
|
|
148 |
root |
1.5 |
HISTORY |
149 |
|
|
This module was initially hacked together within a few hours on a long |
150 |
|
|
flight to Malaysia, and seems to have worked ever since, with minor |
151 |
|
|
adjustments for newer libmysqlclient libraries. |
152 |
|
|
|
153 |
root |
1.6 |
Well, at least until mariadb introduced the new Pluggable Virtual IO API |
154 |
|
|
in mariadb 10.3, which changed and broke everything. On the positive |
155 |
|
|
side, the old system was horrible to use, as many GNU/Linux |
156 |
|
|
distributions forgot to include the required heaqder files and there |
157 |
|
|
were frequent small changes, while the new PVIO system seems to be |
158 |
|
|
"official" and hopefully better supported. |
159 |
|
|
|
160 |
root |
1.2 |
AUTHOR |
161 |
|
|
Marc Lehmann <schmorp@schmorp.de> |
162 |
|
|
http://home.schmorp.de/ |
163 |
|
|
|