Recommended load balancing solutions (especially with PostgreSQL)?

Hello,

Today I've been researching database load balancing solutions to use
with an Apache/mod_perl application and a PostgreSQL database. Right now
I'm using Slony-I for replication, and it seems to work well enough.

The following is the conclusions I've come to from my own research.
Feedback from actual experience is appreciated.

- pgpool
pgpool is one solution to load balancing PostgreSQL connections.
However, I found a number of things not to like about it (again, mostly
from research):
-- says it adds 7 to 15% overhead.
-- Dumb about "SELECT nextval". They all need a space before them.
An accident here could cause havoc, when "SELECT nextval" gets
sent to a read-only db copy.
-- Has a bad failover policy: If the master becomes unavailable,

it starts sending both reads and writes to the read-only slave:
http://pgfoundry.org/pipermail/pgpool-general/2006-May/00037 8.html


- DBD::Multiplex
There's a fairly recent thread on why not to use this module:
http://www.mail-archive.com/dbi-dev@perl.org/msg04444.html

I found a couple more issues that looked like bugs to me:

Some SELECTs will be falsely classifed as "modifying" statements
http://rt.cpan.org/Ticket/Display.html?id=24220

no support for SELECTs that modify
http://rt.cpan.org/Ticket/Display.html?id=24219

#####

What's beginning to appeal to me know is to solve part load balancing in
the application by using two database handles: a read/write one, and a
read-only one. From there, it's much easier problem to solve: The R/w
handle always talks to the master and the Read-only handle can be load
balanced without being so "smart". Some further details about going
this route:

- I would leave the default handle as "read-write". That means if I
accidently miss converting a handle to "read only" somewhere, it will
still work, it just won't be load-balanced.

- CGI::Application::Plugin::DBH will make adding the second handle
easy. I'll still use $self->dbh() to access the default handle, and
$self->dbh('ro') for the new Read-only handle.

- DBD::Multi looks like it could handle this kind of simple load
balancing. As a bonus, it has some fail-over logic in it, so if the
slave was unvailable for a bit, that would be handled transparently.

Does that seem sound? What's worked for you?

Mark

Re: Recommended load balancing solutions (especially with PostgreSQL)?

On Thu, Jan 04, 2007 at 02:54:37PM -0500, Mark Stosberg wrote:
>
> Hello,
>
> Today I've been researching database load balancing solutions to use
> with an Apache/mod_perl application and a PostgreSQL database. Right now
> I'm using Slony-I for replication, and it seems to work well enough.

> #####
>
> What's beginning to appeal to me know is to solve part load balancing in
> the application by using two database handles: a read/write one, and a
> read-only one. From there, it's much easier problem to solve: The R/w
> handle always talks to the master and the Read-only handle can be load
> balanced without being so "smart".

A wise approach. I believe DBIx::Class is taking that approach.

> Some further details about going this route:
>
> - I would leave the default handle as "read-write". That means if I
> accidently miss converting a handle to "read only" somewhere, it will
> still work, it just won't be load-balanced.
>
> - CGI::Application::Plugin::DBH will make adding the second handle
> easy. I'll still use $self->dbh() to access the default handle, and
> $self->dbh('ro') for the new Read-only handle.
>
> - DBD::Multi looks like it could handle this kind of simple load
> balancing. As a bonus, it has some fail-over logic in it, so if the
> slave was unvailable for a bit, that would be handled transparently.
>
> Does that seem sound? What's worked for you?

Also take a look at DBIx::HA.

Tim.

Re: Recommended load balancing solutions (Thanks)

Tim Bunce wrote:
> On Thu, Jan 04, 2007 at 02:54:37PM -0500, Mark Stosberg wrote:
>> Hello,
>>
>> Today I've been researching database load balancing solutions to use
>> with an Apache/mod_perl application and a PostgreSQL database. Right now
>> I'm using Slony-I for replication, and it seems to work well enough.
>
>> #####
>>
>> What's beginning to appeal to me know is to solve part load balancing in
>> the application by using two database handles: a read/write one, and a
>> read-only one. From there, it's much easier problem to solve: The R/w
>> handle always talks to the master and the Read-only handle can be load
>> balanced without being so "smart".
>
> A wise approach. I believe DBIx::Class is taking that approach.

You deserve some credit. I think I got the idea from you, through an
previous post I found during my research. Thanks!

Mark

Re: Recommended load balancing solutions (especially with PostgreSQL)?

Henri Asseily wrote:
>
> I don't know if I uploaded the latest version of DBIx::HA to CPAN, I'll
> do it asap.

The docs for DBIx::HA say

"It is very simple to load-balance across read-only database servers.
Simply randomize or reorder the 'db_stack' entry in your database
configuration on a per-process basis."

I know it could be dead simple, but could an example of what this looks
like be provided?

How well has this worked in practice? So, if an Apache/mod_perl
application has 24 children, about 12 would end up talking to each DB as
a primary?

Also, DBD::Multi /does/ have this feature-- automatically randomly
selecting between to dbs with the same priority. Perhaps this feature
could be merged in.

I do like the "time out" feature that DBIx::HA has.

Mark

Re: Recommended load balancing solutions?

On Jan 8, 2007, at 10:10 PM, Mark Stosberg wrote:

> Henri Asseily wrote:
>>
>> I don't know if I uploaded the latest version of DBIx::HA to CPAN,
>> I'll
>> do it asap.
>
> The docs for DBIx::HA say
>
> "It is very simple to load-balance across read-only database servers.
> Simply randomize or reorder the 'db_stack' entry in your database
> configuration on a per-process basis."
>
> I know it could be dead simple, but could an example of what this
> looks
> like be provided?

Take the example of the pod:

$DATABASE::conf{'test'} = {
max_retries => 2,
db_stack => [
[ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1',
$attrib ],
[ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2',
$attrib ],
[ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3',
$attrib ],
], ...

Assume his 'test' handle is a read-only handle.
The stack definition shows that you have 3 read-only servers: prod1,
prod2 and prod3.
The particular process that uses this stack will start with prod1,
and if it fails max_retries times, it will switch to the next one in
the stack, prod2, and so on.
If you want to have your application to be load-balanced across all
the read-only databases, you can do it in many ways, but ultimately
it boils down to having your children processes hitting different
databases, and having stacks that are as unique as possible (so that
if a db server fails, all its dependents don't fail over to the same
secondary).

Here's a complex example:
In the case above, say you have 20 children for those 3 databases.
The different stack permutations are:

1: prod1,prod2,prod3
2: prod1,prod3,prod2
3: prod2,prod1,prod3
4: prod2,prod3,prod1
5: prod3,prod1,prod2
6: prod3,prod2,prod1

Either dynamically or statically create these 6 permutations and put
them in @all_db_stacks:
Then when one of your 20 children starts up, it will take a stack.
Either have each take the next stack down, or randomly take a stack.
One example under Apache if you don't want to have a shared global is
to grab the child's process id and get its modulus by the size of
@all_db_stack:
$stack_number = $$ % scalar(@all_db_stacks)

And so in the example config above:
db_stack => $all_db_stacks[$stack_number]

Assuming that Apache recycles its children pretty uniformly, you
should have a uniform distribution across the database servers. If
hypothetically the child process are 1 to 20, then stacks 1 and 2
will be allocated to 4 children each, while stacks 3,4,5 and 6 will
be allocated to 3 children each for a total of 2x4+4x3=20 children.
So when all the db servers are functioning properly, prod1 should
have 8 clients, prod2 and 3 should have 6 each. If prod1 dies, its
children get split evenly between prod2 and prod3.

>
> How well has this worked in practice? So, if an Apache/mod_perl
> application has 24 children, about 12 would end up talking to each
> DB as
> a primary?

In the case where you have 24 children and 2 database servers, sure.
You'll have 12 children hitting one db, and 12 hitting the other. And
if one dies, all 24 will hit the remaining live one. There's no other
way.

>
> Also, DBD::Multi /does/ have this feature-- automatically randomly
> selecting between to dbs with the same priority. Perhaps this feature
> could be merged in.

DBIx::HA focuses on high availability. This means not only load
balancing, but failover, especially in a mod_perl environment where
the children are always connected. You can't just randomly select a
db for each call for a number of reasons, the biggest one being that
you'd need a live connection to all the dbs. In the case of 3
database servers and 100 children, each db server would need to have
100 connects active. Now that may not be a problem for you, but when
you have 100 web servers with 50 children each, few database servers
are happy with 5,000 concurrent open connections.

In such large setups, you also want good control over which servers/
children connect to which possible databases. In the case when you
have 100 web servers, you'd probably want each server to have its own
database failover stack, with all its children following the same
pattern and using the same resources. This helps immensely the
operations people who want to know machine and resource dependencies.

I found that using a simple low-tech stack system allowed for many
possibilities, even though it's not very glamorous.

>
> I do like the "time out" feature that DBIx::HA has.

That's another critical issue for high availability. There are too
many ways a database server can be unavailable, and the standard
timeouts of the database drivers are completely unreliable because
they're 'safe'. DBIx::HA uses unsafe timeouts, so technically the
Perl interpreter could be in a bad state after a timeout, but
practically I've never seen this happen and being able to time out a
query or a connect is an absolute necessity.


As to how well DBIx::HA has worked in practice, we're using it
heavily at Shopzilla. One of the systems that's using it has hundreds
of modperl servers hitting a couple dozen database servers. Database
queries run in the tens of millions a day, etc...

That system is actually so large that we are considering going to the
next step beyond DBIx::HA because the system is so big that even with
DBIx::HA and the web server children connecting only to the first
database server in the stack, there are too many connections on a
single database server. At some point, when you optimize the database
calls you end up being able to throw many more clients at a db,
ultimately overloading its capacity to manage thousands of constantly
open connections. So now we need to pool the connections, but that's
another story for another time.

H

Re: Recommended load balancing solutions?

Henri Asseily wrote:
> Take the example of the pod:
>
> $DATABASE::conf{'test'} = {
> max_retries => 2,
> db_stack => [
> [ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1', $attrib ],
> [ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2', $attrib ],
> [ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3', $attrib ],
> ], ...
>
> Assume his 'test' handle is a read-only handle.
> The stack definition shows that you have 3 read-only servers: prod1,
> prod2 and prod3.
> The particular process that uses this stack will start with prod1, and
> if it fails max_retries times, it will switch to the next one in the
> stack, prod2, and so on.
> If you want to have your application to be load-balanced across all the
> read-only databases, you can do it in many ways, but ultimately it boils
> down to having your children processes hitting different databases, and
> having stacks that are as unique as possible (so that if a db server
> fails, all its dependents don't fail over to the same secondary).
>
> Here's a complex example:
> In the case above, say you have 20 children for those 3 databases.
> The different stack permutations are:
>
> 1: prod1,prod2,prod3
> 2: prod1,prod3,prod2
> 3: prod2,prod1,prod3
> 4: prod2,prod3,prod1
> 5: prod3,prod1,prod2
> 6: prod3,prod2,prod1
>
> Either dynamically or statically create these 6 permutations and put
> them in @all_db_stacks:
> Then when one of your 20 children starts up, it will take a stack.
> Either have each take the next stack down, or randomly take a stack. One
> example under Apache if you don't want to have a shared global is to
> grab the child's process id and get its modulus by the size of
> @all_db_stack:
> $stack_number = $$ % scalar(@all_db_stacks)
>
> And so in the example config above:
> db_stack => $all_db_stacks[$stack_number]

Couldn't an option be added as an alternative to db_stack that indicates
a desire for random ordering, thereby eliminating the need to precreate
all possible permutations? I'm imaging something like this (modified
version of your example):

$DATABASE::conf{'test'} = {
max_retries => 2,
db_pool => [
[ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1', $attrib ],
[ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2', $attrib ],
[ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3', $attrib ],
], ...

So, db_stack maintains ordering, while db_pool tells DBIx::HA to
randomize the list of servers.

-ofer

load-balancing with DBIx::HA (was: Re: Recommended load balancingsolutions?)

Ofer Nave wrote:
>
> Couldn't an option be added as an alternative to db_stack that indicates
> a desire for random ordering, thereby eliminating the need to precreate
> all possible permutations? I'm imaging something like this (modified
> version of your example):
>
> $DATABASE::conf{'test'} = {
> max_retries => 2,
> db_pool => [
> [ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1', $attrib ],
> [ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2', $attrib ],
> [ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3', $attrib ],
> ], ...
>
> So, db_stack maintains ordering, while db_pool tells DBIx::HA to
> randomize the list of servers.

I like the idea of having this feature built-in, but I think the name
could clearer, perhaps call it "load balance":

$DATABASE::conf{'test'} = {
load_balance => 1,
db_stack => [
[ 'dbi:Sybase:server=prod1;database=test','user1','pass1',$att rib ],
[ 'dbi:Sybase:server=prod2;database=test','user2','pass2',$att rib ],
], ...


Mark

Re: load-balancing with DBIx::HA (was: Re: Recommended load balancing solutions?)

On Jan 11, 2007, at 3:18 PM, Mark Stosberg wrote:

> Ofer Nave wrote:
>>
>> Couldn't an option be added as an alternative to db_stack that
>> indicates
>> a desire for random ordering, thereby eliminating the need to
>> precreate
>> all possible permutations? I'm imaging something like this (modified
>> version of your example):
>>
>> $DATABASE::conf{'test'} = {
>> max_retries => 2,
>> db_pool => [
>> [ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1',
>> $attrib ],
>> [ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2',
>> $attrib ],
>> [ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3',
>> $attrib ],
>> ], ...
>>
>> So, db_stack maintains ordering, while db_pool tells DBIx::HA to
>> randomize the list of servers.
>
> I like the idea of having this feature built-in, but I think the name
> could clearer, perhaps call it "load balance":
>
> $DATABASE::conf{'test'} = {
> load_balance => 1,
> db_stack => [
> [ 'dbi:Sybase:server=prod1;database=test','user1','pass1',
> $attrib ],
> [ 'dbi:Sybase:server=prod2;database=test','user2','pass2',
> $attrib ],
> ], ...

The problem is that there are too many ways to load balance, which
gets even more confusing when you use presistent connections such as
those in mod_perl. Unless you have a large number of client processes
compared the the servers, when you use persistent connections you're
rarely going to be happily load balancing in a way that is equitable.
Certain algorithms are better for certain situations such as a lower
ratio of clients to servers, for example.
And since the developer knows her systems better than I do, let her
write a proper balancing routine for her needs. The trivial ones are
unnecessary for me to include since they're trivial, and the more
advanced ones are too specific to a situation for the general public
(i.e. probably the 3 people using this module) to care about...

As an interesting exercise, I'd be curious to see if you and Ofer can
agree on a load balancing routine to use. :-)

H

Re: Recommended load balancing solutions?

--MZf7D3rAEoQgPanC
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable

On 2007-01-10 15:06:56 -0800, Ofer Nave wrote:
> Henri Asseily wrote:
> >Take the example of the pod:
> >$DATABASE::conf{'test'} =3D {
> > max_retries =3D> 2,
> > db_stack =3D> [
> > [ 'dbi:Sybase:server=3Dprod1;database=3Dtest', 'user1', 'pass1', $attr=
ib ],
> > [ 'dbi:Sybase:server=3Dprod2;database=3Dtest', 'user2', 'pass2', $attr=
ib ],
> > [ 'dbi:Sybase:server=3Dprod3;database=3Dtest', 'user3', 'pass3', $attr=
ib ],
> > ], ...
[...]
> >Here's a complex example:
> >In the case above, say you have 20 children for those 3 databases.
> >The different stack permutations are:
> >1: prod1,prod2,prod3
> >2: prod1,prod3,prod2
> >3: prod2,prod1,prod3
> >4: prod2,prod3,prod1
> >5: prod3,prod1,prod2
> >6: prod3,prod2,prod1
> >Either dynamically or statically create these 6 permutations and put the=
m in=20
> >@all_db_stacks:
[...]
>=20
> Couldn't an option be added as an alternative to db_stack that indicates =
a=20
> desire for random ordering, thereby eliminating the need to precreate all=
=20
> possible permutations? I'm imaging something like this (modified version=
of=20
> your example):
>=20
> $DATABASE::conf{'test'} =3D {
> max_retries =3D> 2,
> db_pool =3D> [
> [ 'dbi:Sybase:server=3Dprod1;database=3Dtest', 'user1', 'pass1', $attr=
ib ],
> [ 'dbi:Sybase:server=3Dprod2;database=3Dtest', 'user2', 'pass2', $attr=
ib ],
> [ 'dbi:Sybase:server=3Dprod3;database=3Dtest', 'user3', 'pass3', $attr=
ib ],
> ], ...

How about just using shuffle from the standard module List::Util?

use List::Util qw(shuffle);

$DATABASE::conf{'test'} =3D {
max_retries =3D> 2,
db_stack =3D> [
shuffle (
[ 'dbi:Sybase:server=3Dprod1;database=3Dtest', 'user1', 'pass1', $att=
rib ],
[ 'dbi:Sybase:server=3Dprod2;database=3Dtest', 'user2', 'pass2', $att=
rib ],
[ 'dbi:Sybase:server=3Dprod3;database=3Dtest', 'user3', 'pass3', $att=
rib ],
)
], ...

hp

--=20
_ | Peter J. Holzer | If I wanted to be "academically correct",
|_|_) | Sysadmin WSR | I'd be programming in Java.
| | | hjp@wsr.ac.at | I don't, and I'm not.
__/ | http://www.hjp.at/ | -- Jesse Erlbaum on dbi-users

--MZf7D3rAEoQgPanC
Content-Type: application/pgp-signature
Content-Disposition: inline

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iQDQAwUBRaZf7VLjemazOuKpAQJv/wXTBa7Q2YbY5fqCuQvvf4Ag4xaq+yIe DJUR
mLgNm8uf+unK8KRmelaaYaR7HOTCuhS7bHcNcTMZqHvCcyRPhZ20mleYYjUX 3xLW
JjsiL0s5CB/XPqd475qUeRKeAwgvBEG+A9w/8/JRkWzTtNIIR6JilCu9Put4 U8HR
nl5MMoXUdGM0whqgyetqDEkoCuzrum0T97Xf9Dss8kGxpiAAKk7lVLcL/tqL 1n9C
h4OU7ES7eqxqmu4g/SVZFQ5ZOw==
=dgC8
-----END PGP SIGNATURE-----

--MZf7D3rAEoQgPanC--

Re: load-balancing with DBIx::HA

Henri Asseily wrote:
>>
>> $DATABASE::conf{'test'} = {
>> load_balance => 1,
>> db_stack => [
>> [ 'dbi:Sybase:server=prod1;database=test','user1','pass1',$att rib ],
>> [ 'dbi:Sybase:server=prod2;database=test','user2','pass2',$att rib ],
>> ], ...
>
> The problem is that there are too many ways to load balance, which gets
> even more confusing when you use presistent connections such as those in
> mod_perl. Unless you have a large number of client processes compared
> the the servers, when you use persistent connections you're rarely going
> to be happily load balancing in a way that is equitable. Certain
> algorithms are better for certain situations such as a lower ratio of
> clients to servers, for example.
> And since the developer knows her systems better than I do, let her
> write a proper balancing routine for her needs. The trivial ones are
> unnecessary for me to include since they're trivial, and the more
> advanced ones are too specific to a situation for the general public
> (i.e. probably the 3 people using this module) to care about...

I see your point. I would refine my suggestion to be the following then:

load_balance => 1

This would just do something "obvious" or "easy". I think this still
would be no so obvious to those just getting into the field.

As a second option, support this, which is also easy:

load_balance => \&code_ref,

I suspect it's clearer to you than me what the arguments and return
values of such a routine would be, but it solves the problem in a useful
way. First, you don't have to choose, write, or maintain a specific
implementation. Second, because there is a hook here in the API for this
with document input and return values, other people can publish and
share plugins.

Eventually, you could just recommend "oh, you need the load balancing
plugin that Ofer wrote", or "the simple one that Mark wrote, etc". :)

I have used callback plugin systems like this as part of
Data::FormValidator and CGI::Application, and they have been helpful for
the reasons cited above.

Mark

Re: load-balancing with DBIx::HA

On Jan 11, 2007, at 4:16 PM, Mark Stosberg wrote:

> Henri Asseily wrote:
>>>
>>> $DATABASE::conf{'test'} = {
>>> load_balance => 1,
>>> db_stack => [
>>> [ 'dbi:Sybase:server=prod1;database=test','user1','pass1',
>>> $attrib ],
>>> [ 'dbi:Sybase:server=prod2;database=test','user2','pass2',
>>> $attrib ],
>>> ], ...
>>
>> The problem is that there are too many ways to load balance, which
>> gets
>> even more confusing when you use presistent connections such as
>> those in
>> mod_perl. Unless you have a large number of client processes compared
>> the the servers, when you use persistent connections you're rarely
>> going
>> to be happily load balancing in a way that is equitable. Certain
>> algorithms are better for certain situations such as a lower ratio of
>> clients to servers, for example.
>> And since the developer knows her systems better than I do, let her
>> write a proper balancing routine for her needs. The trivial ones are
>> unnecessary for me to include since they're trivial, and the more
>> advanced ones are too specific to a situation for the general public
>> (i.e. probably the 3 people using this module) to care about...
>
> I see your point. I would refine my suggestion to be the following
> then:
>
> load_balance => 1
>
> This would just do something "obvious" or "easy". I think this still
> would be no so obvious to those just getting into the field.
>
> As a second option, support this, which is also easy:
>
> load_balance => \&code_ref,
>
> I suspect it's clearer to you than me what the arguments and return
> values of such a routine would be, but it solves the problem in a
> useful
> way. First, you don't have to choose, write, or maintain a specific
> implementation. Second, because there is a hook here in the API for
> this
> with document input and return values, other people can publish and
> share plugins.
>
> Eventually, you could just recommend "oh, you need the load balancing
> plugin that Ofer wrote", or "the simple one that Mark wrote, etc". :)
>
> I have used callback plugin systems like this as part of
> Data::FormValidator and CGI::Application, and they have been
> helpful for
> the reasons cited above.

(I actually have set up a similar callback in DBIx::HA when a
failover happens)

Well, Peter Holzer gave you the obvious solution:

use List::Util qw(shuffle);

$DATABASE::conf{'test'} = {
max_retries => 2,
db_stack => [
shuffle (
[ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1',
$attrib ],
[ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2',
$attrib ],
[ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3',
$attrib ],
)
], ...


And for a callback, what's the point? You're writing the above, so
you can write whatever code you want. DBIx::HA doesn't specify what
the db_stack is about, nor what the load balancing should be. All it
wants is a stack with a certain format.

Here's a more generalized example of Peter's:

sub load_balance {
use List::Util qw(shuffle);
return shuffle(shift);
}


$DATABASE::conf{'test'} = {
max_retries => 2,
db_stack => [
load_balance (
[ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1',
$attrib ],
[ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2',
$attrib ],
[ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3',
$attrib ],
)
], ...


Again, all this stuff is trivial and I don't see the point of
including it in the module. But if you do, submit a patch and I'll
add it in.
In the meantime, for the next version I'll put Peter's example in the
POD.
Thanks.

H

Re: Recommended load balancing solutions?

Peter J. Holzer wrote:
> On 2007-01-10 15:06:56 -0800, Ofer Nave wrote:
>> Henri Asseily wrote:
>>> Take the example of the pod:
>>> $DATABASE::conf{'test'} = {
>>> max_retries => 2,
>>> db_stack => [
>>> [ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1', $attrib ],
>>> [ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2', $attrib ],
>>> [ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3', $attrib ],
>>> ], ...
> [...]
>>> Here's a complex example:
>>> In the case above, say you have 20 children for those 3 databases.
>>> The different stack permutations are:
>>> 1: prod1,prod2,prod3
>>> 2: prod1,prod3,prod2
>>> 3: prod2,prod1,prod3
>>> 4: prod2,prod3,prod1
>>> 5: prod3,prod1,prod2
>>> 6: prod3,prod2,prod1
>>> Either dynamically or statically create these 6 permutations and put them in
>>> @all_db_stacks:
> [...]
>> Couldn't an option be added as an alternative to db_stack that indicates a
>> desire for random ordering, thereby eliminating the need to precreate all
>> possible permutations? I'm imaging something like this (modified version of
>> your example):
>>
>> $DATABASE::conf{'test'} = {
>> max_retries => 2,
>> db_pool => [
>> [ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1', $attrib ],
>> [ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2', $attrib ],
>> [ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3', $attrib ],
>> ], ...
>
> How about just using shuffle from the standard module List::Util?
>
> use List::Util qw(shuffle);
>
> $DATABASE::conf{'test'} = {
> max_retries => 2,
> db_stack => [
> shuffle (
> [ 'dbi:Sybase:server=prod1;database=test', 'user1', 'pass1', $attrib ],
> [ 'dbi:Sybase:server=prod2;database=test', 'user2', 'pass2', $attrib ],
> [ 'dbi:Sybase:server=prod3;database=test', 'user3', 'pass3', $attrib ],
> )
> ], ...
>
> hp
>

I like it!

-ofer