We're going to be doing mysql load balancing with a new system we're
bringing up. We wanted to be able to do more than just checking for an
open socket, so I put together a little patch for ldirectord that can
perform a user specified SQL query in order to test that the database
hasn't hung and is still answering queries.
This code attempts to be clean and lean. The basic theory is you
specify one query and if it returns greater than zero rows, all is ok.
Zero is specifically excluded because I cannot think of a benefit of
searching an empty table. However, since I cannot possibly comprehend
every possible scenario, an example is provided that will allow you to
do effectively the same thing.
Disclaimers:
1) This code works in my test environment. It has not been run in
production yet, so it's unknown of the effect on the load of directors.
It should be minimal though.
2) I'm curious to see if this meets the coding standards of the project.
All syntax/structure changes are welcome.
3) Feature changes are ok if there is a clear need and clear explanation
of how to utilize it.
4) Ignoring this is ok too if it doesn't do things the "LVS way". My
feelings won't be hurt.
Documentation:
The quickest way to explain is a sample. From the ldirectord.cf file:
virtual = 192.168.10.74:3306
real = sql01->sql03:3306 gate 10
checktype = negotiate
login = "readuser"
passwd = "genericpassword"
database = "portal"
request = "SELECT * FROM link"
scheduler = wrr
In this sample, the real machines are sql01, sql02, and sql03. The
daemon will connect to each host, login with user "readuser" and
password "genericpassword" and select database "portal". The default
database if one is not configured is "mysql". It will perform the query
specified in the request setting.
The output of ipvsadm will look similar to this:
TCP 192.168.10.74:mysql wrr
-> sql01.ivenue.net:mysql Route 10 0 0
-> sql02.ivenue.net:mysql Route 10 0 0
-> sql03.ivenue.net:mysql Route 10 0 0
Internally, the code only checks to see that a response of one line or
more is received. As stated above, this excludes doing searches from
empty tables. If you must do something like this, the solution is to
set the query to:
request = "DESCRIBE link"
which will always return something since there must always be at least
one field in a table.
I originally was going to add code to check that the value returned was
equal to some number, but could not justify a use for code that did
this. As such, I left in the commented code that I was considering
along these lines.
Troubleshooting this when it doesn't work properly is not very fun. I
put a log sequence that will say "Error: Must specify a login, passwd,
and request string for mysql" if any of them are not set in the config
file. However, you must note that there is a default password sequence
that is set in ldirectord. Maybe an additional check to see if the
password has not been altered from the default would be in order here.
There are also a couple of debug options that spit out bits of info if
it can't connect to the database and how many rows the query returned.
Hopefully you'll never need to use them.
All constructive criticism welcome!
------------------------------------------------------------------------
--- /usr/sbin/ldirectord.orig 2004-10-14 12:18:13.000000000 -0700
+++ /usr/sbin/ldirectord 2005-01-11 16:35:36.751157200 -0800
@@ -666,6 +666,7 @@
$vsrv{receive} = "";
$vsrv{login} = "";
$vsrv{passwd} = "ldirectord\@$ENV{HOSTNAME}";
+ $vsrv{database} = "mysql";
$vsrv{checktimeout} = 0;
$vsrv{connecttimeout} = 0;
$vsrv{negotiatetimeout} = 0;
@@ -723,6 +724,9 @@
} elsif ($rcmd =~ /^passwd\s*=\s*\"(.*)\"/) {
$1 =~ /(.+)/ or &config_error($line,
"invalid password");
$vsrv{passwd} = $1;
+ } elsif ($rcmd =~ /^database\s*=\s*\"(.*)\"/) {
+ $1 =~ /(.+)/ or &config_error($line,
"invalid database");
+ $vsrv{database} = $1;
} elsif ($rcmd =~ /^load\s*=\s*\"(.*)\"/) {
$1 =~ /(\w+)/ or &config_error($line,
"invalid string for load testing");
$vsrv{load} = $1;
@@ -946,6 +950,9 @@
elsif ($vsrv->{port} eq "53") {
$vsrv->{service} = "dns";
}
+ elsif ($vsrv->{port} eq "3306") {
+ $vsrv->{service} = "mysql";
+ }
else {
$vsrv->{service} = "none";
}
@@ -1519,6 +1526,8 @@
$$r{num_connects} = 0 if
(check_nntp($v, $r));
} elsif ($$v{service} eq "dns") {
$$r{num_connects} = 0 if
(check_dns($v, $r));
+ } elsif ($$v{service} eq "mysql") {
+ $$r{num_connects} = 0 if
(check_mysql($v, $r));
} else {
$$r{num_connects} = 0 if
(check_none($v, $r));
}
@@ -1843,6 +1852,57 @@
}
+sub check_mysql
+{
+ require DBI;
+ my ($v, $r) = @_;
+ my $port=(defined $$v{checkport}?$$v{checkport}:$$r{port});
+ my ($dbh, $sth, $query, $rows, $result); # Local variables
+ $query = $$r{request};
+ $query =~ s#^/##;
+ unless ($$v{login} && $$v{passwd} && $query) {
+ service_set($v, $r, "down");
+ &ld_log("Error: Must specify a login, passwd, and request string for
mysql. Not adding $$r{server}.\n");
+ return 1;
+ }
+ $result=2; # Set result flag. Only ok if ends up at zero.
+ &ld_debug(2, "Checking mysql server=$$r{server} port=$port\n");
+ $dbh =
DBI->connect("dbi:mysql:database=$$v{database};host=$$r{server};port=$port",
$$v{login}, $$v{passwd});
+ unless ($dbh) {
+ &ld_debug(4, "Failed to bind to $$r{server} with $dbh->err");
+ service_set($v, $r, "down");
+ return 1;
+ }
+ $result--;
+ $sth = $dbh->prepare($query);
+ $rows = $sth->execute;
+ ld_debug(4, "Database search returned $rows rows");
+ if ($rows gt 0) {
+ # If it returns with a number, it is ok.
+ # Disallows query of an empty table.
+ $result--;
+ }
+ # If user defined a receive string (number of rows returned), only do
+ # the check if the previous fetchall_arrayref succeeded.
+ #if (defined $$r{receive} && $result eq 0) {
+ # # Receive string specifies an exact number of rows
+ # if ($rows ne $$r{receive}) {
+ # ld_debug(2,"Service down, receive=$$r{receive}");
+ # $result=1;
+ # }
+ #}
+ if ($result == 1) {
+ # Should never get here
+ service_set($v, $r, "down");
+ return 1;
+ }
+ service_set($v, $r, "up");
+ $sth->finish;
+ $dbh->disconnect;
+ return 0;
+}
+
+
sub check_connect
{
my ($v, $r) = @_;
------------------------------------------------------------------------
