Better connection limit logic for ActiveRecord

[nandayadav]

Here's a thread for bit of context:
https://groups.google.com/d/msg/goliath-io/smLYeLYWHOA/WX9Fq6gj_O4J

Basically i am trying to use activerecord with em-synchrony/activerecord in a Goliath app. Problem is unlike rails, I can't just do establish_connection for each request and not expect some exceptions in case of too many connections at once or long running queries at once. Now that the the activerecord driver has moved into this repo from mysql2, need to add some sort of connection pool logic within context of fiber. Anyway, I don't understand nuts and bolts of it underneath, but here's some sample code to reproduce the problem:
https://gist.github.com/1185433 (app with endpoint and db config)
https://gist.github.com/1185438 (gems used and script to hit the endpoint in parallel to invoke this).

Running the script I get intermitted exceptions like:

Mysql2::Error: This connection is still waiting for a result, try again once you have the result: SELECT sleep(1.0)

I cant reproduce now but I also got 'Too many connections' exception earlier.
*This looks very similar to issue mentioned here, #51
Feel free to delete if dup.

[whitequark]

This bug also affects my application, which is bare EM + Synchrony + redis + mysql2 (all official gems, and current git master of synchrony). The count of connections tends to explode and use up all unix sockets. Also, somehow mysql2 returns its result when I actually query redis.

[jnak]

Ok, I'm having the same issue. I dug into AR code, but I'm not sure how to monkey patch this. The best solution would be to have AR uses a Mysql2 connection pool behind the curtains. I've been thinking about that for some time, but I'm not sure yet of the strategy to adopt.

Any suggestions are welcome :)

[rud]

@whitequark: are you sure you are creating separate fibers for each outgoing service-call? If not, it is my understanding you will get a .resume from whatever service responds first, which may be a different place than you expected..

[whitequark]

@rud, no, I'm wrapping all the data input callback (or timer callback) body in the EM::Synchrony do...end. If I'd wrap only each service call in EM::Synchrony do..end, then I would be not able to do sequental service calls.

[rud]

You can create your own fiber instances to do multiple concurrent requests, but without a bit of code, I can't really give better hints. Did you mean EM::Synchrony.sync inside of a running EM instance?

[whitequark]

I meant EM::synchrony do..end. For example, I need to do several sequental requests in EM::Connection(descendant)#unbind. If I'd do each one in separate EM::synchrony block, I could not use the results of first in the second.

I could share the code (actually, sharing the changesets is more meaningful), but it's not all open. Can I send it via email or something like that?

[rud]

When you call EM::synchrony with a block, a new EventMachine engine is started. You probably want to move all your work inside of a single EM instance, instead of starting/stopping multiple instances.

[whitequark]

@rud, I don't quite understand how that could work with callbacks. As far as I observe it, if I execute binders in EM::synchrony blocks, only they are executed in the synchrony mode, and callbacks (i.e. post_init, receive) are executed in plain EM.

[rud]

@whitequark Please open the source for em-synchrony and look at how it's implemented. Actually, look here:
https://github.com/igrigorik/em-synchrony/blob/master/lib/em-synchrony.rb#L21-29

[whitequark]

@rud, well, I've seen that. Isn't the goal of Synchrony to make me able to write pseudo-synchronous code by making it asynchronous under the hood?

I've thinked about how it can be implemented. I've came with the following solution: each pseudo-thread runs in its own Fiber, and each time it has to wait, it yields a Proc which can be called to determine if its parent Fiber should be scheduled again. (Or it may be an file descriptor to be passed to EM.) Each callback will be wrapped to start a Fiber and add it to a global pool, which will eventually get scheduled.

After all, I am almost certainly sure that EM implements this scheme. I just don't understand how should I use it. EM::schedule? No such method exists.

[rud]

@whitequark to a degree, sure. But to completely ignore the fact just leads to issues. Anyway, not sure issue#56 is the place to discuss this :)

@nandayadav regarding your original issue, you might be able to find some ideas or ready-baked solutions in the rainbows server - http://rainbows.rubyforge.org/

[whitequark]

@rud, can you suggest a more appropriate place for this discussion? IRC maybe, or whatever.

[igrigorik]

Reference from #78 (merging):

---

While experimenting with with em_mysql2 adapater I stumbled upon something annoying, while I agree it is an edge case I wonder if someone has a fix, here is my test case: https://gist.github.com/1330609

The problem is that by replacing the synchronize call with simply calling the block if you create multiple fibers asking for the connection they will all get one (I did not looked into this but I suppose the fiber is suspended during the actual connection to mysql). the net result is that you can can have more connections than your maximum in the connection pool, while this is not a major problem it still annoys me.

In my test case I defined a pool of 1 connection with activerecord and by setting the DELAY constant to 0 (or removing the add_timer call) you can get as many connections as you want in the pool although the limit is still 1, just change the "5.times" part.

[igrigorik]

Should be fixed in master!

#91

[jnak]

Awesome !