killing persisent conections on backends marked down?

Re: killing persisent conections on backends marked down?

Siva — Tue, 28 Feb 2012 10:40:01 +0100

Hi Willy,

>> is it possible for haproxy to failover to a backup,
>> but not failback even if the server is alive again?

I have the same problem.
I would like for my primary to be detected as UP, but not do the failback
immediately. Is it possible to failback to the primary only when the backup
becomes unhealthy?

I use HAProxy with two mysql servers in Active/Backup configuration.
Sometimes, the primary mysql-server's CPU utilization increases a lot and it is
unable to service queries quickly enough. The queries and connections pile up
and exceed max_connections limit.
My HAProxy detects this as an unhealthy condition and fails over to the
mysql-server backup.
At this point, the primary mysql-server slowly processes a couple of queries and
the number of connections reduces to a value that is just below max_connections.
HAPRoxy's health check goes through and determines the primary to be healthy and
failback happens to an already overloaded primary.
When this happens, inevitably the primary chokes again in a couple of seconds
and a failover happens once again to the backup .. and the process repeats.

I think that in this case, it would be great if HAProxy stayed with the
mysql-server backup and switched back only when the backup becomes unhealthy.

Is it possible to configure HAProxy to stick with the backup and not failback ?

Thanks
Siva

Willy Tarreau 1wt.eu> writes:

>
> On Thu, Feb 25, 2010 at 10:05:02PM -0500, Greg Gard wrote:
> > sorry willy i sent this to wrong address. here is my other question:
> >
> > one more thing. is it possible for haproxy to failover to a backup,
> > but not failback even if the server is alive again? for example, if i
> > could set rise to be infinite so that once it was down, it was down
> > for good? currently, i am handling this with a text file that gets
> > written and once there forces my ruby db checker to return 5xx to
> > haproxy. can you all think of any better way to deal with this withing
> > haproxy?
>
> Well, there's no easy way to do this, but more importantly you don't
> describe how you want to proceed to put the service back online after
> the issue. Obviously you won't leave your server dead forever in a
> rack and through it to the bin every time it fails. So "infinite"
> here is still the wrong solution. I have a feeling that in fact
> you're trying to use haproxy more as a fuse or an on/off switch
> than for what it's designed.
>
> I really believe that you have to rethink the whole lifecycle of
> your components and write down rules indicating after what event
> you consider them dead, and after what event you consider them
> usable again. You also have to identify if certain maintenance
> operations are needed during the up->dead or dead->up transition.
> Think of it just as if someone else was managing your haproxy and
> you couldn't call him every day to stop/start/change configs.
>
> That's the only way you'll manage to get a reliable and easily
> manageable application.
>
> Regards,
> Willy
>
>

Re: killing persisent conections on backends marked down?

Greg Gard — Fri, 26 Feb 2010 15:20:01 +0100

hi,

just a followup with a solution that might save some other rails dude some
time. the fix below allows you to set the same timeouts for db connections
as you have for your webserver backends without major invasive surgery to
what rails expects (ie a persistent connection to db).

turns out that ActiveRecord has a verify_active_connections! method that
will cycle through all connections/pools defined by your database.yml config
file and restart them if needed. not quite sure whether this actually dumps
an existing one or not, but it does restart a connection that has timed
out.

so i hacked the default rails session store (rails 2.3.4) like so:
/vendor/rails/active_record/lib/active_record/session_store.rb (line

def find_by_session_id(session_id)
begin
find :first, :conditions => {:session_id=>session_id}
rescue
verify_active_connections!
find :first, :conditions => {:session_id=>session_id}
end
end

anyway, this makes for a pretty smooth transition when doing failover as
well as avoiding 5xx errors the first request to an inactive server for
which haproxy has timed out the db connection.

thanks for the input and keep up the great work. once i get my drivers
working, i will post a link to the code.

....gg

2010/2/26 XANi

> Hi
> Dnia 2010-02-25, czw o godzinie 21:07 -0500, Greg Gard pisze:
>
> hi guys,
>
> thanks for the feedback. yet another rails issue that makes deployment
> a hassle. my biggest issue with setting the timeouts is that when a
> user hits the page after a timeout, they will get a 5xx error, but
> perhaps i can teach rails to catch the exception and retry the
> connection. anyway, thanks for clarifying that i wasn't missing some
> simple config option.
>
> ...gg
>
>
> That's certainly is a good thing even if u don't use proxy between ur SQL
> and app :)
>
> Regards
> Mariusz
>
>
>
>
> --
> Mariusz Gronczewski (XANi)
> GnuPG: 0xEA8ACE64http://devrandom.pl
>
>

--
greg gard, psyd
www.carepaths.com

Re: killing persisent conections on backends marked down?

XANi — Fri, 26 Feb 2010 10:50:01 +0100

Hi
Dnia 2010-02-25, czw o godzinie 21:07 -0500, Greg Gard pisze:

> hi guys,
>
> thanks for the feedback. yet another rails issue that makes deployment
> a hassle. my biggest issue with setting the timeouts is that when a
> user hits the page after a timeout, they will get a 5xx error, but
> perhaps i can teach rails to catch the exception and retry the
> connection. anyway, thanks for clarifying that i wasn't missing some
> simple config option.
>
> ...gg
>

That's certainly is a good thing even if u don't use proxy between ur
SQL and app :)

Regards
Mariusz

--
Mariusz Gronczewski (XANi)
GnuPG: 0xEA8ACE64
http://devrandom.pl

Re: killing persisent conections on backends marked down?

Willy Tarreau — Fri, 26 Feb 2010 08:10:01 +0100

On Thu, Feb 25, 2010 at 10:05:02PM -0500, Greg Gard wrote:
> sorry willy i sent this to wrong address. here is my other question:
>
> one more thing. is it possible for haproxy to failover to a backup,
> but not failback even if the server is alive again? for example, if i
> could set rise to be infinite so that once it was down, it was down
> for good? currently, i am handling this with a text file that gets
> written and once there forces my ruby db checker to return 5xx to
> haproxy. can you all think of any better way to deal with this withing
> haproxy?

Well, there's no easy way to do this, but more importantly you don't
describe how you want to proceed to put the service back online after
the issue. Obviously you won't leave your server dead forever in a
rack and through it to the bin every time it fails. So "infinite"
here is still the wrong solution. I have a feeling that in fact
you're trying to use haproxy more as a fuse or an on/off switch
than for what it's designed.

I really believe that you have to rethink the whole lifecycle of
your components and write down rules indicating after what event
you consider them dead, and after what event you consider them
usable again. You also have to identify if certain maintenance
operations are needed during the up->dead or dead->up transition.
Think of it just as if someone else was managing your haproxy and
you couldn't call him every day to stop/start/change configs.

That's the only way you'll manage to get a reliable and easily
manageable application.

Regards,
Willy

Re: killing persisent conections on backends marked down?

Greg Gard — Fri, 26 Feb 2010 04:10:02 +0100

sorry willy i sent this to wrong address. here is my other question:

one more thing. is it possible for haproxy to failover to a backup,
but not failback even if the server is alive again? for example, if i
could set rise to be infinite so that once it was down, it was down
for good? currently, i am handling this with a text file that gets
written and once there forces my ruby db checker to return 5xx to
haproxy. can you all think of any better way to deal with this withing
haproxy?

thanks...gg

On Thu, Feb 25, 2010 at 9:07 PM, Greg Gard wrote:
> hi guys,
>
> thanks for the feedback. yet another rails issue that makes deployment
> a hassle. my biggest issue with setting the timeouts is that when a
> user hits the page after a timeout, they will get a 5xx error, but
> perhaps i can teach rails to catch the exception and retry the
> connection. anyway, thanks for clarifying that i wasn't missing some
> simple config option.
>
> ...gg
>
> On Thu, Feb 25, 2010 at 6:24 PM, Willy Tarreau wrote:
>> Hi,
>>
>> On Thu, Feb 25, 2010 at 10:54:35PM +0100, XANi wrote:
>>> Dnia 2010-02-25, czw o godzinie 16:27 -0500, Greg Gard pisze:
>>>
>>> > hi willy and friends,
>>> >
>>> > i am working on a set of ruby scripts to do database failover and
>>> > stonith. so far all is working pretty well, but i have a few issues:
>>> >
>>> > 1) rails makes persistent connections to the backend database so when
>>> > a server is marked down, the connection remains ongoing. currently, i
>>> > deal with this by issuing a "stonith" command in my ruby "driver"
>>> > script for haproxy that shuts the backend down explicitly via ssh, but
>>> > it would be nice if i could rely on haproxy to kill the connection
>>> > explicitly. is there a setting to make haproxy kill existing
>>> > connections on a backend going down?
>>> >
>>> > 2) for rails i have tcp timeout set to 0 so it seems to be handling
>>> > the persistent connections ok, but when i do a reload using the
>>> > haproxy init script in the debian packages, i end up with two haproxy
>>> > backends as the persistent connections aren't killed. essentially the
>>> > original process is waiting for the connections to end before it kills
>>> > itself, but that will never happen with rails db connection. any ideas
>>> > or suggestions?
>>> >
>>> > ps: having rails not use persistent connections is not really what i
>>> > would like to do right now. i have run that in the past on production
>>> > and had wierd timeout problems and choppy connectivity.
>>> >
>>> > thanks...gg
>>> >
>>>
>>>
>>> 1) If i remember correctly client/server timeouts only trigger when
>>> there is no activity (no data send) so setting client and server timeout
>>> to like 5 min could solve problem
>>> so as long as app do some queries connection won't be dropped
>>
>> Exactly. It is wrong to set infinite timeouts, it will always cause
>> trouble. Still we see people using them from time to time, despite
>> the huge annoying warning upon config reload.
>>
>>> 2) u can do "/etc/init.d/haproxy stop ; /etc/init.d/haproxy start"
>>
>> The correct timeout will also take care of that because the connections
>> will eventually die and the old process exit.
>>
>> This situation is the perfect illustration of why it's wrong to run
>> with no timeouts.
>>
>> Regards,
>> Willy
>>
>>
>
>
>
> --
> greg gard, psyd
> www.carepaths.com
>

--
greg gard, psyd
www.carepaths.com

Re: killing persisent conections on backends marked down?

Greg Gard — Fri, 26 Feb 2010 03:10:02 +0100

hi guys,

thanks for the feedback. yet another rails issue that makes deployment
a hassle. my biggest issue with setting the timeouts is that when a
user hits the page after a timeout, they will get a 5xx error, but
perhaps i can teach rails to catch the exception and retry the
connection. anyway, thanks for clarifying that i wasn't missing some
simple config option.

....gg

On Thu, Feb 25, 2010 at 6:24 PM, Willy Tarreau wrote:
> Hi,
>
> On Thu, Feb 25, 2010 at 10:54:35PM +0100, XANi wrote:
>> Dnia 2010-02-25, czw o godzinie 16:27 -0500, Greg Gard pisze:
>>
>> > hi willy and friends,
>> >
>> > i am working on a set of ruby scripts to do database failover and
>> > stonith. so far all is working pretty well, but i have a few issues:
>> >
>> > 1) rails makes persistent connections to the backend database so when
>> > a server is marked down, the connection remains ongoing. currently, i
>> > deal with this by issuing a "stonith" command in my ruby "driver"
>> > script for haproxy that shuts the backend down explicitly via ssh, but
>> > it would be nice if i could rely on haproxy to kill the connection
>> > explicitly. is there a setting to make haproxy kill existing
>> > connections on a backend going down?
>> >
>> > 2) for rails i have tcp timeout set to 0 so it seems to be handling
>> > the persistent connections ok, but when i do a reload using the
>> > haproxy init script in the debian packages, i end up with two haproxy
>> > backends as the persistent connections aren't killed. essentially the
>> > original process is waiting for the connections to end before it kills
>> > itself, but that will never happen with rails db connection. any ideas
>> > or suggestions?
>> >
>> > ps: having rails not use persistent connections is not really what i
>> > would like to do right now. i have run that in the past on production
>> > and had wierd timeout problems and choppy connectivity.
>> >
>> > thanks...gg
>> >
>>
>>
>> 1) If i remember correctly client/server timeouts only trigger when
>> there is no activity (no data send) so setting client and server timeout
>> to like 5 min could solve problem
>> so as long as app do some queries connection won't be dropped
>
> Exactly. It is wrong to set infinite timeouts, it will always cause
> trouble. Still we see people using them from time to time, despite
> the huge annoying warning upon config reload.
>
>> 2) u can do "/etc/init.d/haproxy stop ; /etc/init.d/haproxy start"
>
> The correct timeout will also take care of that because the connections
> will eventually die and the old process exit.
>
> This situation is the perfect illustration of why it's wrong to run
> with no timeouts.
>
> Regards,
> Willy
>
>

--
greg gard, psyd
www.carepaths.com

Re: killing persisent conections on backends marked down?

Willy Tarreau — Fri, 26 Feb 2010 00:30:01 +0100

Hi,

On Thu, Feb 25, 2010 at 10:54:35PM +0100, XANi wrote:
> Dnia 2010-02-25, czw o godzinie 16:27 -0500, Greg Gard pisze:
>
> > hi willy and friends,
> >
> > i am working on a set of ruby scripts to do database failover and
> > stonith. so far all is working pretty well, but i have a few issues:
> >
> > 1) rails makes persistent connections to the backend database so when
> > a server is marked down, the connection remains ongoing. currently, i
> > deal with this by issuing a "stonith" command in my ruby "driver"
> > script for haproxy that shuts the backend down explicitly via ssh, but
> > it would be nice if i could rely on haproxy to kill the connection
> > explicitly. is there a setting to make haproxy kill existing
> > connections on a backend going down?
> >
> > 2) for rails i have tcp timeout set to 0 so it seems to be handling
> > the persistent connections ok, but when i do a reload using the
> > haproxy init script in the debian packages, i end up with two haproxy
> > backends as the persistent connections aren't killed. essentially the
> > original process is waiting for the connections to end before it kills
> > itself, but that will never happen with rails db connection. any ideas
> > or suggestions?
> >
> > ps: having rails not use persistent connections is not really what i
> > would like to do right now. i have run that in the past on production
> > and had wierd timeout problems and choppy connectivity.
> >
> > thanks...gg
> >
>
>
> 1) If i remember correctly client/server timeouts only trigger when
> there is no activity (no data send) so setting client and server timeout
> to like 5 min could solve problem
> so as long as app do some queries connection won't be dropped

Exactly. It is wrong to set infinite timeouts, it will always cause
trouble. Still we see people using them from time to time, despite
the huge annoying warning upon config reload.

> 2) u can do "/etc/init.d/haproxy stop ; /etc/init.d/haproxy start"

The correct timeout will also take care of that because the connections
will eventually die and the old process exit.

This situation is the perfect illustration of why it's wrong to run
with no timeouts.

Regards,
Willy

Re: killing persisent conections on backends marked down?

XANi — Thu, 25 Feb 2010 23:00:01 +0100

Dnia 2010-02-25, czw o godzinie 16:27 -0500, Greg Gard pisze:

> hi willy and friends,
>
> i am working on a set of ruby scripts to do database failover and
> stonith. so far all is working pretty well, but i have a few issues:
>
> 1) rails makes persistent connections to the backend database so when
> a server is marked down, the connection remains ongoing. currently, i
> deal with this by issuing a "stonith" command in my ruby "driver"
> script for haproxy that shuts the backend down explicitly via ssh, but
> it would be nice if i could rely on haproxy to kill the connection
> explicitly. is there a setting to make haproxy kill existing
> connections on a backend going down?
>
> 2) for rails i have tcp timeout set to 0 so it seems to be handling
> the persistent connections ok, but when i do a reload using the
> haproxy init script in the debian packages, i end up with two haproxy
> backends as the persistent connections aren't killed. essentially the
> original process is waiting for the connections to end before it kills
> itself, but that will never happen with rails db connection. any ideas
> or suggestions?
>
> ps: having rails not use persistent connections is not really what i
> would like to do right now. i have run that in the past on production
> and had wierd timeout problems and choppy connectivity.
>
> thanks...gg
>

1) If i remember correctly client/server timeouts only trigger when
there is no activity (no data send) so setting client and server timeout
to like 5 min could solve problem
so as long as app do some queries connection won't be dropped
2) u can do "/etc/init.d/haproxy stop ; /etc/init.d/haproxy start"
--
Mariusz Gronczewski (XANi)
GnuPG: 0xEA8ACE64
http://devrandom.pl

killing persisent conections on backends marked down?

Greg Gard — Thu, 25 Feb 2010 22:40:01 +0100

hi willy and friends,

i am working on a set of ruby scripts to do database failover and
stonith. so far all is working pretty well, but i have a few issues:

1) rails makes persistent connections to the backend database so when
a server is marked down, the connection remains ongoing. currently, i
deal with this by issuing a "stonith" command in my ruby "driver"
script for haproxy that shuts the backend down explicitly via ssh, but
it would be nice if i could rely on haproxy to kill the connection
explicitly. is there a setting to make haproxy kill existing
connections on a backend going down?

2) for rails i have tcp timeout set to 0 so it seems to be handling
the persistent connections ok, but when i do a reload using the
haproxy init script in the debian packages, i end up with two haproxy
backends as the persistent connections aren't killed. essentially the
original process is waiting for the connections to end before it kills
itself, but that will never happen with rails db connection. any ideas
or suggestions?

ps: having rails not use persistent connections is not really what i
would like to do right now. i have run that in the past on production
and had wierd timeout problems and choppy connectivity.

thanks...gg

--
greg gard, psyd
www.carepaths.com