|
If you want to test whether the server is still answering queries, why
not use something like "SELECT 1" for the query. You should see the
following benefits:
* Independent of the particular database or table structure;
* Should always return exactly one row;
* That row should contain one field with the value "1" which you can
also test for;
* Requires minimal resources from the database server to execute and
return the result.
This seems to meet the stated requirements, although it does not test
that a particular database still exists and is readable, much less any
particular data in that database. It should still test that the
database server is accessible using the given username and password
though.
On Tue, 11 Jan 2005 17:30:47 -0800, Todd Lyons <tlyons@xxxxxxxxxx> 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!
> --
> Regards... Todd
> OS X: We've been fighting the "It's a mac" syndrome with upper management
> for years now. Lately we've taken to just referring to new mac
> installations as "Unix" installations when presenting proposals and
> updates. For some reason, they have no problem with that. -- /.
> Linux kernel 2.6.8.1-12mdkenterprise 2 users, load average: 1.05, 1.12,
> 1.02
>
>
> _______________________________________________
> LinuxVirtualServer.org mailing list - lvs-users@xxxxxxxxxxxxxxxxxxxxxx
> Send requests to lvs-users-request@xxxxxxxxxxxxxxxxxxxxxx
> or go to http://www.in-addr.de/mailman/listinfo/lvs-users
>
>
>
>
--
sockmonk
|
