1 | NAME |
1 | NAME |
2 | Coro::Mysql - let other threads run while doing mysql requests |
2 | Coro::Mysql - let other threads run while doing mysql/mariadb requests |
3 | |
3 | |
4 | SYNOPSIS |
4 | SYNOPSIS |
5 | use Coro::Mysql; |
5 | use Coro::Mysql; |
6 | |
6 | |
7 | my $DBH = Coro::Mysql::unblock DBI->connect (...); |
7 | my $DBH = Coro::Mysql::unblock DBI->connect (...); |
8 | |
8 | |
9 | DESCRIPTION |
9 | DESCRIPTION |
10 | (Note that in this manual, "thread" refers to real threads as |
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 |
11 | implemented by the Coro module, not to the built-in windows process |
12 | emulation which unfortunately is also called "threads") |
12 | emulation which unfortunately is also called "threads"). |
13 | |
13 | |
14 | This module "patches" DBD::mysql database handles so that they do not |
14 | This module replaces the I/O handlers for a database connection, with |
15 | block the whole process, but only the thread that they are used in. |
15 | 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. |
16 | |
19 | |
17 | This can be used to make parallel sql requests using Coro, or to do |
20 | This can be used to make parallel sql requests using Coro, or to do |
18 | other stuff while mysql is rumbling in the background. |
21 | other stuff while mariadb is rumbling in the background. |
19 | |
22 | |
20 | CAVEAT |
23 | CAVEAT |
21 | Note that this module must be linked against exactly the same |
24 | Note that this module must be linked against exactly the same (shared, |
22 | libmysqlclient library as DBD::mysql, otherwise it will not work. |
25 | possibly not working with all OSes) libmariadb/libmysqlclient library as |
|
|
26 | DBD::MariaDB/DBD::mysql, otherwise it will not work. |
23 | |
27 | |
24 | Also, while this module makes database handles non-blocking, you still |
28 | Also, while this module makes database handles non-blocking, you still |
25 | cannot run multiple requests in parallel on the same database handle. If |
29 | cannot run multiple requests in parallel on the same database handle. If |
26 | you want to run multiple queries in parallel, you have to create |
30 | you want to run multiple queries in parallel, you have to create |
27 | multiple database connections, one for each thread that runs queries. |
31 | multiple database connections, one for each thread that runs queries. |
|
|
32 | Not doing so can corrupt your data - use a Coro::Semaphore to protetc |
|
|
33 | access to a shared database handle when in doubt. |
28 | |
34 | |
29 | If you make sure that you never run two or more requests in parallel, |
35 | If you make sure that you never run two or more requests in parallel, |
30 | you cna freely share the database handles between threads, of course. |
36 | you can freely share the database handles between threads, of course. |
31 | |
|
|
32 | Also, this module uses a number of "unclean" techniques (patching an |
|
|
33 | internal libmysql structure for one thing) and was hacked within a few |
|
|
34 | hours on a long flight to Malaysia. |
|
|
35 | |
|
|
36 | It does, however, check whether it indeed got the structure layout |
|
|
37 | correct, so you should expect perl exceptions or early crashes as |
|
|
38 | opposed to data corruption when something goes wrong. |
|
|
39 | |
37 | |
40 | SPEED |
38 | SPEED |
41 | This module is implemented in XS, and as long as mysqld replies quickly |
39 | This module is implemented in XS, and as long as mysqld replies quickly |
42 | enough, it adds no overhead to the standard libmysql communication |
40 | enough, it adds no overhead to the standard libmysql communication |
43 | routines (which are very badly written). |
41 | 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 | |
44 | |
45 | For very fast queries ("select 0"), this module can add noticable |
45 | For very fast queries ("select 0"), this module can add noticable |
46 | overhead (around 15%) as it tries to switch to other coroutines when |
46 | overhead (around 15%, 7% when EV can be used) as it tries to switch to |
47 | mysqld doesn't deliver the data instantly. |
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. |
48 | |
50 | |
49 | For most types of queries, there will be no overhead, especially on |
51 | For most types of queries, there will be no extra latency, especially on |
50 | multicore systems where your perl process can do other things while |
52 | multicore systems where your perl process can do other things while |
51 | mysqld does its stuff. |
53 | mysqld does its stuff. |
|
|
54 | |
|
|
55 | 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 | 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 | FUNCTIONS |
|
|
68 | Coro::Mysql offers these functions, the only one that oyu usually need |
|
|
69 | is "unblock": |
52 | |
70 | |
53 | $DBH = Coro::Mysql::unblock $DBH |
71 | $DBH = Coro::Mysql::unblock $DBH |
54 | This function takes a DBI database handles and "patches" it so it |
72 | This function takes a DBI database handles and "patches" it so it |
55 | becomes compatible to Coro threads. |
73 | becomes compatible to Coro threads. |
56 | |
74 | |
57 | After that, it returns the patched handle - you should always use |
75 | After that, it returns the patched handle - you should always use |
58 | the newly returned database handle. |
76 | the newly returned database handle. |
59 | |
77 | |
|
|
78 | 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 | 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 | $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 | 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 | my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
|
|
115 | 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 | 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 | 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 | |
60 | AUTHOR |
160 | AUTHOR |
61 | Marc Lehmann <schmorp@schmorp.de> |
161 | Marc Lehmann <schmorp@schmorp.de> |
62 | http://home.schmorp.de/ |
162 | http://home.schmorp.de/ |
63 | |
163 | |