Re: HAProxy and MySQL

Hi Jill,

On Mon, Oct 08, 2007 at 01:17:35PM -0400, Jill Rochelle wrote:
> I have a couple of issues with HAProxy and MySQL:
>
>
>
> 1) Implementing HAProxy to send connections to multiple SQL servers
> that are part of a MySQL cluster and have the check option work?

Some people have been doing this with some limited success. The major reason is that after a server down/up sequence, the load takes a long time to spread between the servers due to the round robin balancing algorithm. I'm currently implementing leastconn to solve this.

However, the check methods currently are :

• bare tcp connect
• http request
• smtp hello
• ssl hello

There is nothing specific to mysql. Maybe someone wants to propose a simple yet efficient check ?

> We had 2 sql servers . seemed to be working fine . one server went down and
> haproxy still kept trying to send connections to it. I added the check
> parameter
>
> thinking this would allow haproxy to know that the server is
> dead . however I got this error after a short time
>
> Caused by: java.sql.SQLException: null, message from server: "Host
> 'domU-12-31-35-00-52-44.z-2.compute-1.internal' is blocked because of many
> connection errors; unblock with 'mysqladmin flush-hosts'"
>
> My guess is that the check is not making a valid connection with a user,
> password and database and that's why I get this. Possible?

Not only possible, but sure! As you have noticed, nowhere in your config you filled a user/password combination, simply because SQL checks are not implemented. It means that your server was frozen (the worst case), but the system was still accepting TCP connections.

Until an SQL check is implemented, I would suggest that you connect to an alternate port managed by inetd starting a trivial script on the servers. The script would be responsible for trying to connect to the database using something representative (login, password, doing a request maybe), and return "HTTP/1.0 200" if it's OK, and "HTTP/1.0 500" (or nothing) if it fails. You could even think about putting the valid response in a table and requesting that table to ensure that everything works as expected.

To achieve this, you have to use the "port XXX" parameter after the "check" keyword on the server line, and enable HTTP health checks. In fact, many people exploit the ability to HTTP check another port because it helps them perform the most advanced check techniques on their servers.

> 2) I believe it may be timeout issues on connection. This is what I
> get after I log in and then wait a bit before doing anything ..
>
>
>
> Could not open Hibernate Session for transaction; nested exception is
> org.hibernate.TransactionException: JDBC begin failed
>
>
>
> Once I keep moving it's fine . I also had to remove all the
> connection timeouts for this error not to occur

Well, I'm fairly sure that you had a big freeze on the server. I don't know mysql well, but I seem to remember there is a limit on the number of concurrent processes. Maybe you reached that limit and mysqld became somewhat ill ? If so, you should set the "maxconn" parameter on your "server" lines to a value lower than what your server accepts (so that there are still free connections for the checks).

Hoping this helps,
Willy Received on 2007/10/08 19:44