ldirectord mysql "negotiate" patch
Malcolm Turnbull
malcolm at loadbalancer.org
Wed Jan 12 08:58:24 GMT 2005
Thats good news, just thought I'd say thanks.
Todd Lyons wrote:
>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) = @_;
>
>
>------------------------------------------------------------------------
>
>_______________________________________________
>LinuxVirtualServer.org mailing list - lvs-users at LinuxVirtualServer.org
>Send requests to lvs-users-request at LinuxVirtualServer.org
>or go to http://www.in-addr.de/mailman/listinfo/lvs-users
>
>
--
Regards,
Malcolm Turnbull.
Loadbalancer.org Limited
Office: +44 (0)870 443 8779
Mobile: +44 (0)7715 770523
http://www.loadbalancer.org/
" When a single point of failure is not an option"
Why not try our online demonstration
<http://www.loadbalancer.org/demo.html> ? Or get answers to common
questions <http://www.loadbalancer.org/fud.html> ?
More information about the lvs-users
mailing list