Lost MySQL connections accessing session store

rubyonrails-talk
1

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

2

Bump.

3

Bump.

4

DZ,

Have you tried removing/uncommenting your AR verification timeout? This (I think) fixed the same issue for me.

Cheers, Rob

5

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

6

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