Since putting our application into production, I''ve been seeing random lost connections to our MySQL server, always when the application is accessing the session store, and at no other time. Apparently randomly, I get the error: Mysql::Error: Lost connection to MySQL server during query: SELECT * FROM sessions WHERE (session_id = ''xxxxxxxx'') LIMIT 1 I''ve spent quite a bit of time googling around and searching appropriate forums, without finding a definitive answer to tackling this problem. I''ve found several posts that indicate moving to AR session store starts this problem. I''ve investigated the possibility of packet size causing problems, but sessions are typically averaging around 300 bytes, and one error that I was able to track down specifically only had a session store of 322 bytes. I''ve seen many suggestions to use the mysql gem driver to fix the problem, however, we''re already using it, so that isn''t the problem. Setting the Interactive timeout was another common suggestion, but the AR verification timeout is set to 1/2 of the interactive timeout set in MySQL. I''m tempted to just set the max_allowed_packet to maybe double the size of the current 16MB and see what happens, but I don''t see how that would help because the session stores seem so small, even with overhead, I don''t see how a session store of 300-400 bytes could add up to a packet that is over 16MB. Further, I''m unsure if all I have to do is set max_allowed_packet in MySQL and be done, or if there is some further configuration in Rails that has to be done . Some googling suggest that it is automatically calculated from the server setting, but I''m unsure of that. I''m wondering whether I should just ditch AR session store and go with mem-cached. It seems like a lot of people have run into this, but aside from the standard answers that I''ve already checked, except for the packet size, there doesn''t seem to be a lot of other answers out there. Has anyone else run into this? Can anyone tell me anything else to check? How about the mem-cache option, any thought there? Thanks for any help in this regard DZ --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Bump. On Apr 24, 2:04 pm, DZ <goo...-jcml4Hf9Um/1P9xLtpHBDw@public.gmane.org> wrote:> Since putting our application into production, I''ve been seeing random > lost connections to our MySQL server, always when the application is > accessing the session store, and at no other time. Apparently > randomly, I get the error: > > Mysql::Error: Lost connection to MySQL server during query: SELECT * > FROM sessions WHERE (session_id = ''xxxxxxxx'') LIMIT 1 > > I''ve spent quite a bit of time googling around and searching > appropriate forums, without finding a definitive answer to tackling > this problem. > > I''ve found several posts that indicate moving to AR session store > starts this problem. > > I''ve investigated the possibility of packet size causing problems, but > sessions are typically averaging around 300 bytes, and one error that > I was able to track down specifically only had a session store of 322 > bytes. > > I''ve seen many suggestions to use the mysql gem driver to fix the > problem, however, we''re already using it, so that isn''t the problem. > > Setting the Interactive timeout was another common suggestion, but the > AR verification timeout is set to 1/2 of the interactive timeout set > in MySQL. > > I''m tempted to just set the max_allowed_packet to maybe double the > size of the current 16MB and see what happens, but I don''t see how > that would help because the session stores seem so small, even with > overhead, I don''t see how a session store of 300-400 bytes could add > up to a packet that is over 16MB. Further, I''m unsure if all I have to > do is set max_allowed_packet in MySQL and be done, or if there is some > further configuration in Rails that has to be done . Some googling > suggest that it is automatically calculated from the server setting, > but I''m unsure of that. > > I''m wondering whether I should just ditch AR session store and go with > mem-cached. > > It seems like a lot of people have run into this, but aside from the > standard answers that I''ve already checked, except for the packet > size, there doesn''t seem to be a lot of other answers out there. > > Has anyone else run into this? Can anyone tell me anything else to > check? How about the mem-cache option, any thought there? > > Thanks for any help in this regard > > DZ--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Bump. On Apr 24, 2:04 pm, DZ <goo...-jcml4Hf9Um/1P9xLtpHBDw@public.gmane.org> wrote:> Since putting our application into production, I''ve been seeing random > lost connections to our MySQL server, always when the application is > accessing the session store, and at no other time. Apparently > randomly, I get the error: > > Mysql::Error: Lost connection to MySQL server during query: SELECT * > FROM sessions WHERE (session_id = ''xxxxxxxx'') LIMIT 1 > > I''ve spent quite a bit of time googling around and searching > appropriate forums, without finding a definitive answer to tackling > this problem. > > I''ve found several posts that indicate moving to AR session store > starts this problem. > > I''ve investigated the possibility of packet size causing problems, but > sessions are typically averaging around 300 bytes, and one error that > I was able to track down specifically only had a session store of 322 > bytes. > > I''ve seen many suggestions to use the mysql gem driver to fix the > problem, however, we''re already using it, so that isn''t the problem. > > Setting the Interactive timeout was another common suggestion, but the > AR verification timeout is set to 1/2 of the interactive timeout set > in MySQL. > > I''m tempted to just set the max_allowed_packet to maybe double the > size of the current 16MB and see what happens, but I don''t see how > that would help because the session stores seem so small, even with > overhead, I don''t see how a session store of 300-400 bytes could add > up to a packet that is over 16MB. Further, I''m unsure if all I have to > do is set max_allowed_packet in MySQL and be done, or if there is some > further configuration in Rails that has to be done . Some googling > suggest that it is automatically calculated from the server setting, > but I''m unsure of that. > > I''m wondering whether I should just ditch AR session store and go with > mem-cached. > > It seems like a lot of people have run into this, but aside from the > standard answers that I''ve already checked, except for the packet > size, there doesn''t seem to be a lot of other answers out there. > > Has anyone else run into this? Can anyone tell me anything else to > check? How about the mem-cache option, any thought there? > > Thanks for any help in this regard > > DZ--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
DZ, Have you tried removing/uncommenting your AR verification timeout? This (I think) fixed the same issue for me. Cheers, Rob On 4/24/07, DZ <google-jcml4Hf9Um/1P9xLtpHBDw@public.gmane.org> wrote:> > Since putting our application into production, I''ve been seeing random > lost connections to our MySQL server, always when the application is > accessing the session store, and at no other time. Apparently > randomly, I get the error: > > Mysql::Error: Lost connection to MySQL server during query: SELECT * > FROM sessions WHERE (session_id = ''xxxxxxxx'') LIMIT 1 > > I''ve spent quite a bit of time googling around and searching > appropriate forums, without finding a definitive answer to tackling > this problem. > > I''ve found several posts that indicate moving to AR session store > starts this problem. > > I''ve investigated the possibility of packet size causing problems, but > sessions are typically averaging around 300 bytes, and one error that > I was able to track down specifically only had a session store of 322 > bytes. > > I''ve seen many suggestions to use the mysql gem driver to fix the > problem, however, we''re already using it, so that isn''t the problem. > > Setting the Interactive timeout was another common suggestion, but the > AR verification timeout is set to 1/2 of the interactive timeout set > in MySQL. > > I''m tempted to just set the max_allowed_packet to maybe double the > size of the current 16MB and see what happens, but I don''t see how > that would help because the session stores seem so small, even with > overhead, I don''t see how a session store of 300-400 bytes could add > up to a packet that is over 16MB. Further, I''m unsure if all I have to > do is set max_allowed_packet in MySQL and be done, or if there is some > further configuration in Rails that has to be done . Some googling > suggest that it is automatically calculated from the server setting, > but I''m unsure of that. > > I''m wondering whether I should just ditch AR session store and go with > mem-cached. > > It seems like a lot of people have run into this, but aside from the > standard answers that I''ve already checked, except for the packet > size, there doesn''t seem to be a lot of other answers out there. > > Has anyone else run into this? Can anyone tell me anything else to > check? How about the mem-cache option, any thought there? > > Thanks for any help in this regard > > DZ > > > > >--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Rob, After trying several permutations of MySQL server settings and even changing some bits of code without success, I tried your suggestion and removed the verification timeout from our production environment. it seems like this would not have made a difference, but it did. We went from having 5-10 lost connections when looking up session information to zero. It has been like this for several days now, so I''d say that this is now fixed. We probably didn''t even need the timeout verification, since our production environment is behind a load balancer that does health checks every few seconds. In theory, (I haven''t confirmed, bad me) this would mean that the mongrel processes hit the database at least once during the 8 hour timeout period we have set on the MySQL server. Thanks for your suggestion! I don''t think I would have looked in that direction without it. DZ On Apr 25, 5:11 pm, "Rob Bevan" <robbe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> DZ, > > Have you tried removing/uncommenting your AR verification timeout? > This (I think) fixed the same issue for me. > > Cheers, > Rob > > On 4/24/07, DZ <goo...-jcml4Hf9Um/1P9xLtpHBDw@public.gmane.org> wrote: > > > > > Since putting our application into production, I''ve been seeing random > > lost connections to our MySQL server, always when the application is > > accessing the session store, and at no other time. Apparently > > randomly, I get the error: > > > Mysql::Error: Lost connection to MySQL server during query: SELECT * > > FROM sessions WHERE (session_id = ''xxxxxxxx'') LIMIT 1 > > > I''ve spent quite a bit of time googling around and searching > > appropriate forums, without finding a definitive answer to tackling > > this problem. > > > I''ve found several posts that indicate moving to AR session store > > starts this problem. > > > I''ve investigated the possibility of packet size causing problems, but > > sessions are typically averaging around 300 bytes, and one error that > > I was able to track down specifically only had a session store of 322 > > bytes. > > > I''ve seen many suggestions to use the mysql gem driver to fix the > > problem, however, we''re already using it, so that isn''t the problem. > > > Setting the Interactive timeout was another common suggestion, but the > > AR verification timeout is set to 1/2 of the interactive timeout set > > in MySQL. > > > I''m tempted to just set the max_allowed_packet to maybe double the > > size of the current 16MB and see what happens, but I don''t see how > > that would help because the session stores seem so small, even with > > overhead, I don''t see how a session store of 300-400 bytes could add > > up to a packet that is over 16MB. Further, I''m unsure if all I have to > > do is set max_allowed_packet in MySQL and be done, or if there is some > > further configuration in Rails that has to be done . Some googling > > suggest that it is automatically calculated from the server setting, > > but I''m unsure of that. > > > I''m wondering whether I should just ditch AR session store and go with > > mem-cached. > > > It seems like a lot of people have run into this, but aside from the > > standard answers that I''ve already checked, except for the packet > > size, there doesn''t seem to be a lot of other answers out there. > > > Has anyone else run into this? Can anyone tell me anything else to > > check? How about the mem-cache option, any thought there? > > > Thanks for any help in this regard > > > DZ--~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---
Ezra Zygmuntowicz
2007-May-02 23:08 UTC
Re: Lost MySQL connections accessing session store
I''ve solved this same problem beofre in and outside of rails using a
low priority sleeper thread at the bottom of env.rb:
Thread.new{ loop{ sleep(60*60);
ActiveRecord::Base.verify_active_connections! } }.priority = -10
Cheers-
-Ezra
On May 2, 2007, at 3:54 PM, DZ wrote:
>
> Rob,
>
> After trying several permutations of MySQL server settings and even
> changing some bits of code without success, I tried your suggestion
> and removed the verification timeout from our production environment.
> it seems like this would not have made a difference, but it did. We
> went from having 5-10 lost connections when looking up session
> information to zero. It has been like this for several days now, so
> I''d say that this is now fixed. We probably didn''t even
need the
> timeout verification, since our production environment is behind a
> load balancer that does health checks every few seconds. In theory, (I
> haven''t confirmed, bad me) this would mean that the mongrel
processes
> hit the database at least once during the 8 hour timeout period we
> have set on the MySQL server.
>
> Thanks for your suggestion! I don''t think I would have looked in
that
> direction without it.
>
> DZ
>
> On Apr 25, 5:11 pm, "Rob Bevan"
<robbe...-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:
>> DZ,
>>
>> Have you tried removing/uncommenting your AR verification timeout?
>> This (I think) fixed the same issue for me.
>>
>> Cheers,
>> Rob
>>
>> On 4/24/07, DZ <goo...-jcml4Hf9Um/1P9xLtpHBDw@public.gmane.org>
wrote:
>>
>>
>>
>>> Since putting our application into production, I''ve been
seeing
>>> random
>>> lost connections to our MySQL server, always when the application
is
>>> accessing the session store, and at no other time. Apparently
>>> randomly, I get the error:
>>
>>> Mysql::Error: Lost connection to MySQL server during query: SELECT
*
>>> FROM sessions WHERE (session_id = ''xxxxxxxx'')
LIMIT 1
>>
>>> I''ve spent quite a bit of time googling around and
searching
>>> appropriate forums, without finding a definitive answer to
tackling
>>> this problem.
>>
>>> I''ve found several posts that indicate moving to AR
session store
>>> starts this problem.
>>
>>> I''ve investigated the possibility of packet size causing
>>> problems, but
>>> sessions are typically averaging around 300 bytes, and one error
>>> that
>>> I was able to track down specifically only had a session store of
>>> 322
>>> bytes.
>>
>>> I''ve seen many suggestions to use the mysql gem driver to
fix the
>>> problem, however, we''re already using it, so that
isn''t the
>>> problem.
>>
>>> Setting the Interactive timeout was another common suggestion,
>>> but the
>>> AR verification timeout is set to 1/2 of the interactive timeout
set
>>> in MySQL.
>>
>>> I''m tempted to just set the max_allowed_packet to maybe
double the
>>> size of the current 16MB and see what happens, but I don''t
see how
>>> that would help because the session stores seem so small, even with
>>> overhead, I don''t see how a session store of 300-400 bytes
could add
>>> up to a packet that is over 16MB. Further, I''m unsure if
all I
>>> have to
>>> do is set max_allowed_packet in MySQL and be done, or if there is
>>> some
>>> further configuration in Rails that has to be done . Some googling
>>> suggest that it is automatically calculated from the server
setting,
>>> but I''m unsure of that.
>>
>>> I''m wondering whether I should just ditch AR session store
and go
>>> with
>>> mem-cached.
>>
>>> It seems like a lot of people have run into this, but aside from
the
>>> standard answers that I''ve already checked, except for the
packet
>>> size, there doesn''t seem to be a lot of other answers out
there.
>>
>>> Has anyone else run into this? Can anyone tell me anything else to
>>> check? How about the mem-cache option, any thought there?
>>
>>> Thanks for any help in this regard
>>
>>> DZ
>
>
> >
-- Ezra Zygmuntowicz
-- Lead Rails Evangelist
-- ez-NLltGlunAUd/unjJdyJNww@public.gmane.org
-- Engine Yard, Serious Rails Hosting
-- (866) 518-YARD (9273)
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"Ruby on Rails: Talk" group.
To post to this group, send email to
rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
To unsubscribe from this group, send email to
rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org
For more options, visit this group at
http://groups.google.com/group/rubyonrails-talk?hl=en
-~----------~----~----~----~------~----~------~--~---