… | |
… | |
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 the all |
|
|
16 | threads of a process, but only the thread that does the request. |
16 | |
17 | |
17 | This can be used to make parallel sql requests using Coro, or to do |
18 | This can be used to make parallel sql requests using Coro, or to do |
18 | other stuff while mysql is rumbling in the background. |
19 | other stuff while mysql is rumbling in the background. |
19 | |
20 | |
20 | CAVEAT |
21 | CAVEAT |
21 | Note that this module must be linked against exactly the same |
22 | Note that this module must be linked against exactly the same (shared, |
|
|
23 | possibly not working with all OSes) libmysqlclient library as |
22 | libmysqlclient library as DBD::mysql, otherwise it will not work. |
24 | DBD::mysql, otherwise it will not work. |
|
|
25 | |
|
|
26 | Also, this module requires a header file that apparently isn't installed |
|
|
27 | everywhere (violite.h), and therefore comes with it's own copy, which |
|
|
28 | might or might not be compatible to the violite.h of your library - when |
|
|
29 | in doubt, make sure all the libmysqlclient header files are installed |
|
|
30 | and delete the violite.h header that comes with this module. |
|
|
31 | |
|
|
32 | On the good side, this module does a multitude of checks to ensure that |
|
|
33 | the libray versions match on the binary level, so on incompatibilities |
|
|
34 | you should expect an exception when trying to unblock a handle, rather |
|
|
35 | than data corruption. |
23 | |
36 | |
24 | Also, while this module makes database handles non-blocking, you still |
37 | Also, while this module makes database handles non-blocking, you still |
25 | cannot run multiple requests in parallel on the same database handle. If |
38 | 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 |
39 | you want to run multiple queries in parallel, you have to create |
27 | multiple database connections, one for each thread that runs queries. |
40 | multiple database connections, one for each thread that runs queries. |
|
|
41 | Not doing so can corrupt your data - use a Coro::Semaphore to protetc |
|
|
42 | access to a shared database handle when in doubt. |
28 | |
43 | |
29 | If you make sure that you never run two or more requests in parallel, |
44 | 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. |
45 | 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 | |
46 | |
40 | SPEED |
47 | SPEED |
41 | This module is implemented in XS, and as long as mysqld replies quickly |
48 | This module is implemented in XS, and as long as mysqld replies quickly |
42 | enough, it adds no overhead to the standard libmysql communication |
49 | enough, it adds no overhead to the standard libmysql communication |
43 | routines (which are very badly written). |
50 | routines (which are very badly written, btw.). In fact, since it has a |
|
|
51 | more efficient buffering and allows requests to run in parallel, it |
|
|
52 | often decreases the actual time to run many queries considerably. |
44 | |
53 | |
45 | For very fast queries ("select 0"), this module can add noticable |
54 | For very fast queries ("select 0"), this module can add noticable |
46 | overhead (around 15%) as it tries to switch to other coroutines when |
55 | overhead (around 15%, 7% when EV can be used) as it tries to switch to |
47 | mysqld doesn't deliver the data instantly. |
56 | other coroutines when mysqld doesn't deliver the data immediately, |
|
|
57 | although, again, when running queries in parallel, they will usually |
|
|
58 | execute faster. |
48 | |
59 | |
49 | For most types of queries, there will be no overhead, especially on |
60 | 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 |
61 | multicore systems where your perl process can do other things while |
51 | mysqld does its stuff. |
62 | mysqld does its stuff. |
|
|
63 | |
|
|
64 | LIMITATIONS |
|
|
65 | This module only supports "standard" mysql connection handles - this |
|
|
66 | means unix domain or TCP sockets, and excludes SSL/TLS connections, |
|
|
67 | named pipes (windows) and shared memory (also windows). No support for |
|
|
68 | these connection types is planned, either. |
|
|
69 | |
|
|
70 | CANCELLATION |
|
|
71 | Cancelling a thread that is within a mysql query will likely make the |
|
|
72 | handle unusable. As far as Coro::Mysql is concerned, the handle can be |
|
|
73 | safely destroyed, but it's not clear how mysql itself will react to a |
|
|
74 | cancellation. |
|
|
75 | |
|
|
76 | FUNCTIONS |
|
|
77 | Coro::Mysql offers a single user-accessible function: |
52 | |
78 | |
53 | $DBH = Coro::Mysql::unblock $DBH |
79 | $DBH = Coro::Mysql::unblock $DBH |
54 | This function takes a DBI database handles and "patches" it so it |
80 | This function takes a DBI database handles and "patches" it so it |
55 | becomes compatible to Coro threads. |
81 | becomes compatible to Coro threads. |
56 | |
82 | |
57 | After that, it returns the patched handle - you should always use |
83 | After that, it returns the patched handle - you should always use |
58 | the newly returned database handle. |
84 | the newly returned database handle. |
59 | |
85 | |
|
|
86 | It is safe to call this function on any database handle (or just |
|
|
87 | about any value), but it will only do anything to DBD::mysql |
|
|
88 | handles, others are returned unchanged. That means it is harmless |
|
|
89 | when applied to database handles of other databases. |
|
|
90 | |
|
|
91 | It is also safe to pass "undef", so code like this is works as |
|
|
92 | expected: |
|
|
93 | |
|
|
94 | my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
|
|
95 | or die $DBI::errstr; |
|
|
96 | |
|
|
97 | USAGE EXAMPLE |
|
|
98 | This example uses PApp::SQL and Coro::on_enter to implement a function |
|
|
99 | "with_db", that connects to a database, uses "unblock" on the resulting |
|
|
100 | handle and then makes sure that $PApp::SQL::DBH is set to the |
|
|
101 | (per-thread) database handle when the given thread is running (it does |
|
|
102 | not restore any previous value of $PApp::SQL::DBH, however): |
|
|
103 | |
|
|
104 | use Coro; |
|
|
105 | use Coro::Mysql; |
|
|
106 | use PApp::SQL; |
|
|
107 | |
|
|
108 | sub with_db($$$&) { |
|
|
109 | my ($database, $user, $pass, $cb) = @_; |
|
|
110 | |
|
|
111 | my $dbh = DBI->connect ($database, $user, $pass)->Coro::Mysql::unblock |
|
|
112 | or die $DBI::errstr; |
|
|
113 | |
|
|
114 | Coro::on_enter { $PApp::SQL::DBH = $dbh }; |
|
|
115 | |
|
|
116 | $cb->(); |
|
|
117 | } |
|
|
118 | |
|
|
119 | This function makes it possible to easily use PApp::SQL with |
|
|
120 | Coro::Mysql, without worrying about database handles. |
|
|
121 | |
|
|
122 | # now start 10 threads doing stuff |
|
|
123 | async { |
|
|
124 | |
|
|
125 | with_db "DBI:mysql:test", "", "", sub { |
|
|
126 | sql_exec "update table set col = 5 where id = 7"; |
|
|
127 | |
|
|
128 | my $st = sql_exec \my ($id, $name), |
|
|
129 | "select id, name from table where name like ?", |
|
|
130 | "a%"; |
|
|
131 | |
|
|
132 | while ($st->fetch) { |
|
|
133 | ... |
|
|
134 | } |
|
|
135 | |
|
|
136 | my $id = sql_insertid sql_exec "insert into table values (1,2,3)"; |
|
|
137 | # etc. |
|
|
138 | }; |
|
|
139 | |
|
|
140 | } for 1..10; |
|
|
141 | |
|
|
142 | SEE ALSO |
|
|
143 | Coro, PApp::SQL (a user friendly but efficient wrapper around DBI). |
|
|
144 | |
|
|
145 | HISTORY |
|
|
146 | This module was initially hacked together within a few hours on a long |
|
|
147 | flight to Malaysia, and seems to have worked ever since, with minor |
|
|
148 | adjustments for newer libmysqlclient libraries. |
|
|
149 | |
60 | AUTHOR |
150 | AUTHOR |
61 | Marc Lehmann <schmorp@schmorp.de> |
151 | Marc Lehmann <schmorp@schmorp.de> |
62 | http://home.schmorp.de/ |
152 | http://home.schmorp.de/ |
63 | |
153 | |