1DBD::Multi(3)         User Contributed Perl Documentation        DBD::Multi(3)
2
3
4

NAME

6       DBD::Multi - Manage Multiple Data Sources with Failover and Load
7       Balancing
8

SYNOPSIS

10         use DBI;
11
12         my $other_dbh = DBI->connect(...);
13
14         my $dbh = DBI->connect( 'dbi:Multi:', undef, undef, {
15             dsns => [ # in priority order
16                 10 => [ 'dbi:SQLite:read_one.db', '', '' ],
17                 10 => [ 'dbi:SQLite:read_two.db', '', '' ],
18                 20 => [ 'dbi:SQLite:master.db',   '', '' ],
19                 30 => $other_dbh,
20                 40 => sub {  DBI->connect },
21             ],
22             # optional
23             failed_max    => 1,     # short credibility
24             failed_expire => 60*60, # long memory
25             timeout       => 10,    # time out connection attempts after 10 seconds.
26         });
27
28         $dbh->prepare(...);  # Works like any other DBI handle.
29
30
31         $dbh->multi_do_all(  # Loops through every single DB handle.
32           sub {
33               my $dbh = shift;
34               ...
35           } );
36

DESCRIPTION

38       This software manages multiple database connections for failovers and
39       also simple load balancing.  It acts as a proxy between your code and
40       your database connections, transparently choosing a connection for each
41       query, based on your preferences and present availability of the DB
42       server.
43
44       This module is intended for read-only operations (where some other
45       application is being used to handle replication).
46
47       This software does not prevent write operations from being executed.
48       This is left up to the user. See "SUGGESTED USES" below for ideas.
49
50       The interface is nearly the same as other DBI drivers except that it
51       allows you to specify multiple connections for a single handle.
52

CONFIGURING CONNECTIONS

54   Configuring DSNs
55       Specify an attribute to the "connect()" constructor, "dsns". This is a
56       list of DSNs to configure. The configuration is given in pairs. First
57       comes the priority of the DSN. Second is the DSN.
58
59       The priorities specify which connections should be used first (lowest
60       to highest).  As long as the lowest priority connection is responding,
61       the higher priority connections will never be used.  If multiple
62       connections have the same priority, then one connection will be chosen
63       randomly for each operation.  Note that the random DB is chosen when
64       the statement is prepared.   Therefore executing multiple queries on
65       the same prepared statement handle will always run on the same
66       connection.
67
68       The second parameter can a DBI object, a code ref which returns a DBI
69       object, or a list of parameters to pass to the DBI "connect()"
70       instructor.   If a set of parameters or a code ref is given, then
71       DBD::Multi will be able to attempt re-connect in the event that the
72       connection is lost.   If a DBI object is used, the DBD::Multi will give
73       up permanently once that connection is lost.
74
75       These connections are lazy loaded, meaning they aren't made until they
76       are actually used.
77
78   Configuring Failures
79       By default, after a data source fails three times, it will not be tried
80       again for 5 minutes.  After that period, the data source will be tried
81       again for future requests until it reaches its three failure limit (the
82       cycle repeats forever).
83
84       To change the maximum number of failures allowed before a data source
85       is deemed failed, set the "failed_max" parameter. To change the amount
86       of time we remember a data source as being failed, set the
87       "failed_expire" parameter in seconds.
88
89   Timing out connections.
90       By default, if you attempt to connect to an IP that isn't answering,
91       DBI will hang for a very long period of time.   This behavior is not
92       desirable in a multi database setup.   Instead, it is better to give up
93       on slow connections and move on to other databases quickly.
94
95       DBD::Multi will give up on connection attempts after 5 seconds and then
96       try another connection.   You may set the "timeout" parameter to change
97       the timeout time, or set it to 0 to disable the timeout feature
98       completely.
99

EXTRA METHODS

101   multi_do_all
102       Loops through every database handle, executing an arbitrary coderef
103       passing the current database handle as the first parameter and the
104       original connection parameters as the second parameter.
105
106       If a database is unreachable, multi_do_all will skip over it.
107
108           use Data::Dumper;
109           my $expected_value = ...;
110           $dbh->multi_do_all(
111               sub {
112                   my $dbh = shift;
113                   my $source = shift;
114                   my($value) = $dbh->selectrow_array("SELECT ...");
115                   unless ( $value eq $expected_value ) {
116                       die "Unexpected value, $value found. (Expected $expected_value).  Data Source:\n", Dumper( $source );
117                   }
118               } );
119

SUGGESTED USES

121       Here are some ideas on how to use this module effectively and safely.
122
123       It is important to remember that "DBD::Multi" is not intended for read-
124       write operations.  One suggestion to prevent accidental write
125       operations is to make sure that the user you are connecting to the
126       databases with has privileges sufficiently restricted to prevent
127       updates.
128
129       Read-write operations should happen through a separate database handle
130       that will somehow trigger replication to all of your databases.  For
131       example, your read-write handle might be connected to the master server
132       that replicates itself to all of the subordinate servers.
133
134       Read-only database calls within your application would be updated to
135       explicitly use the read-only (DBD::Multi) handle. It is not necessary
136       to find every single call that can be load balanced, since they can
137       safely be sent through the read/write handle as well.
138

TODO

140       There really isn't much of a TODO list for this module at this time.
141       Feel free to submit a bug report to github
142       <https://github.com/dwright/DBD-Multi/issues> if you think there is a
143       feature missing.
144
145       Although there is some code intended for read/write operations, this
146       should be considered not supported and not actively developed at this
147       time.  The actual read/write code remains un-documented because in the
148       event that I ever do decide to work on supporting read/write
149       operations, the API is not guaranteed to stay the same.  The focus of
150       this module is presently limited to read-only operations.
151

TESTING

153       DBD::Multi has it's own suite of regression tests.   But, suppose you
154       want to verify that you can slip DBD::Multi into whatever application
155       you already have written without breaking anything.
156
157       Thanks to a feature of DBI, you can regression test DBD::Multi using
158       any existing tests that already use DBI without having to update any of
159       your code.  Simply set the environment variable DBI_AUTOPROXY to
160       'dbi:Multi:' and then run your tests.  DBD::Multi should act as a
161       silent pipe between your application and whatever database driver you
162       were previously using.  This will help you verify that you aren't
163       currently using some feature of the DBI that breaks DBD::Multi (If you
164       are, please do me a favor and submit a bug report so I can fix it).
165

SEE ALSO

167       There are other modules that have similar, but different objectives.
168       Depending on your specific needs these may be more or less suitable for
169       your task:
170
171       CGI::Application::Plugin::DBH
172           A plugin for the CGI::Application framework which makes it easy to
173           support two database handles, and also supports lazy-loading.
174
175       DBD::Multiplex
176           The original inspiration for this module.  It doesn't support as
177           many connection configurations options at this module.   It does
178           try to support write options in a single master, mutliple slave
179           configuration.   It does this by parsing your SQL and trying to
180           decide if you were doing a read or write operation.
181
182       DBIx::HA
183           Written after this module.  Built for high availability rather than
184           load balancing.   It purposely ignores some DBI features in favor
185           of producing the fastest results for the most common operations.
186           It doesn't utilize the standard DBI->connect() API, which means it
187           will not work as a drop-in auto proxy.
188
189       DBI, perl - You should probably already know about these before using
190       this module.
191

AUTHOR

193       Initially written by Casey West and Dan Wright for pair Networks, Inc.
194       (www.pair.com)
195
196       Maintained by Dan Wright.  <DWRIGHT@CPAN.ORG>.
197
198
199
200perl v5.34.0                      2022-01-21                     DBD::Multi(3)
Impressum