Comments and changes to this ticket

• You flagged this item as spam.
  

  Ian Leitch July 29th, 2009 @ 04:33 PM

  I'm having a similar problem which is probably related. Each mongrel process will over time end up creating as many connections as the pool will allow. So 5 connections for each of our 6 mongrels leaves us with 30 connections and 30 PostgreSQL processes. That's a lot of memory.

  I spent a fair amount of time reading the mongrel and activerecord code to figure out what was going on, I came to the conclusion that simply reducing the pool size would work, because from what I could see, each connection should be checked back into the pool when the request has been handled due to the thread associated with the connection dieing.

  Mongrel dispatches each request within a ctrical region and I see no code outside this region that should cause activerecord to attempt to checkout a new connection. So, given there's only a single RailsHandler instance (which as far as I can see there is) it should be true that there's only ever a single thread at any one time trying to checkout a connection from the pool. However when I reduced my pool size to 1 or 2 I'd end up with pool checkout timeout errors.

  Mongrel doesn't explicity kill its threads, they just die once the request has been handled and thus get automatically removed from the ThreadGroup in which they live. Mongrel spawns a new thread for every request.

  I also noticed that Mongrel will always retain an open connection that can't be used by other threads. When Mongrel boots it requires the rails environment which results in database activity. However once the environment is loaded database activity is only performed within a request thread, so the connection associated with the base process thread just sits there doing nothing. You can save yourself a connection by adding ActiveRecord::Base.clear_all_connections! in RailsConfigurator#rails after the environment is loaded.

• You flagged this item as spam.
  

  Ian Leitch July 29th, 2009 @ 05:14 PM

  I think I've identified a race condition here. Only a small portion of the Mongrel processing code is synchronized, so it's possible that whilst thread A has left the synchronized block, thread B has entered and is now attempting to checkout a new connection, yet thread A may still be alive executing the code outside the synchronized block. So whether thread B will get a connection or not depends on if thread A is able to die before the timeout triggers.

  I also think there's a deadlock in activerecord connection pooling. Say we have a pool size of 1 and two threads. If Thread A checks out a connection and then Thread B attempts to checkout a connection, it's going to wait for the @queue to be signaled, yet Thread A can't check the connection back into the pool because it's blocked waiting for the @connection_mutex which is currently held by Thread B waiting on the @queue signal. Thread B is doomed to fail with a ConnectionTimeoutError.

• You flagged this item as spam.
  

  Laurent Farcy July 29th, 2009 @ 05:34 PM

  • Tag changed from “2.3.2, connection_pooling, mongrel” to “2.3.2, connection_pooling, mongrel, sql_session_store”

  Ian,

  Thank you so much for sharing your experience with Mongrel and connection pooling.

  On our side, we also got caught by surprise with the default pool size being 5. With 10 Mongrel servers on our production site, we ended up with 50 MySQL connections instead of 10 beforehand. It simply was the max number of connections allowed on our MySQL server.

  Since Mongrel is single-threaded, we considered the pool should be reduced to 1 connection. That's when we started observing the 5 seconds timeout due to the single connection not being released.

  As stated in the title of this ticket, we firmly believed the issue was with Mongrel, since it uses one (different) thread by request and does nothing to release the db connection. After going through the Rails code (especially the initialiazer) and googling (a lot), we realized the culprit was more the sql_session_store plugin.

  If you look at the Rails initializer, you will find that Rails racks a middleware to manage the db connections, mainly to release any active connections after the request is processed. The thing is it's racked after the session store except when the latter is the ActiveRecod session store. In this case, it's racked before.

  The consequence is that the connection is released too early when you use the sql_session_store. Hopefully, there's a workaround that consists in undoing what Rails does with connection management at initialization time and redoing it the proper way. In 'config/environment.rb', you get

  Specifies gem version of Rails to use when vendor/rails is not present

  = '2.3.2' unless defined? RAILS_GEM_VERSION

  Bootstrap the Rails environment, frameworks, and default configuration

  require File.join(File.dirname(FILE), 'boot')

  Rails::Initializer.run do |config|
  ...

  # Use SQL session store for sessions config.action_controller.session_store = :sql_session_store

  ... end

  Specific Session class for MySQL

  SqlSessionStore.session_class = MysqlSession

  ##

  Re-arrange middleware stack to properly release connection used by

  SqlSessionStore

  ## ActionController::Dispatcher.middleware.delete(
  ActiveRecord::ConnectionAdapters::ConnectionManagement) ActionController::Dispatcher.middleware.insert_before(
  :'SqlSessionStore', ActiveRecord::ConnectionAdapters::ConnectionManagement)

  ActionController::Dispatcher.middleware.delete(
  ActiveRecord::QueryCache) ActionController::Dispatcher.middleware.insert_before(
  :'SqlSessionStore', ActiveRecord::QueryCache)

  ...

  With this code in place, we no more have timeout issue with the connection pool. IMHO, the responsability of the defect is shared b/w Rails and the SQL session store. On one hand, Rails somehow assumes that only the AR session store makes use of a db connection. On the other hand, the sql_session_store does not take care of the connection pool.

• You flagged this item as spam.
  

  Laurent Farcy July 29th, 2009 @ 05:37 PM

  Sorry for the wrong formatting.

  # Specifies gem version of Rails to use when vendor/rails is not present
  RAILS_GEM_VERSION = '2.3.2' unless defined? RAILS_GEM_VERSION
  
  # Bootstrap the Rails environment, frameworks, and default configuration
  require File.join(File.dirname(__FILE__), 'boot')
  
  Rails::Initializer.run do |config|
    # ...
  
    # Use SQL session store for sessions
    config.action_controller.session_store = :sql_session_store
  
    # ...
  end
  
  # Specific Session class for MySQL
  SqlSessionStore.session_class = MysqlSession
  
  ActionController::Dispatcher.middleware.delete(
    ActiveRecord::ConnectionAdapters::ConnectionManagement)
  ActionController::Dispatcher.middleware.insert_before(
    :'SqlSessionStore', ActiveRecord::ConnectionAdapters::ConnectionManagement)
  
  ActionController::Dispatcher.middleware.delete(
    ActiveRecord::QueryCache)
  ActionController::Dispatcher.middleware.insert_before(
    :'SqlSessionStore', ActiveRecord::QueryCache)
    
  # ...
  

• You flagged this item as spam.
  

  Ian Leitch July 29th, 2009 @ 09:49 PM

  I can't really comment on sessions and db connections as I use cookie based sessions.

  To clarify how Mongrel and ActiveRecord work together:

  Mongrel isn't responsible for releasing databasse connections, all it has to ensure is that the thread that has handled a request dies. It's ActiveRecord's job to return connections associated with dead threads back to the pool. Also note that once a thread has died, activerecord doesn't disconnect the connection, it simply returns it to a pool of available connections. So if your pool size is 5 and you had 5 threads all trying to obtain a connection at the same time, you'd have 5 open connections to your database when the threads have finished.

  Without my earlier comment about patching Mongrel with ActiveRecord::Base.clear_all_connections! the minimum you should be able to set your pool size to is 2. Since 1 of those connections will never become available for use by requests.

  By the way, Mongrel itself IS multi-threaded, only the Rails dispatch call is synchronized.

• You flagged this item as spam.
  

  Laurent Farcy July 30th, 2009 @ 10:18 AM

  Ian,

  Thanks for continuing the discussion. We definitely discuss the same topic but have experienced different issues.

  On my side, I am now sure that the sql_session_store is a source of trouble for the connection pool of Rails 2.3.2 because the connection it uses is not released. Once it has been fixed, everything goes right.

  You apparently found another issue b/w Mongrel and the Rails connection pool. Let me comment on that.

  As you say, Mongrel isn't responsible for releasing database connections, BUT Rails takes charge of that for Mongrel. It's done at the end of request dispatching by a Rack middleware dedicated to db connection management. It's synchronized from a Mongrel standpoint since it's done in the Rails dispatching.

  I also confirm that, whenever a connection would not be released at the end of a request processing, Rails connection pool is still able to get it back by inspecting the connections still allocated to dead threads. But at the price of waiting for 5 seconds (by default) if there's no more connection available in the pool. Again, it's done in the Mongrel synchronized block.

  Having said that, I was not aware that Mongrel uses a connection to initialize Rails environment. Which could have consequences on connection pooling, obviously.

  I can only testify that all our Mongrel servers now use a single connection in their pool and the default wait_timeout (5s). And we do not experience any connection shortage or timeout anymore.

  HTH.

• You flagged this item as spam.
  

  Laurent Farcy July 30th, 2009 @ 10:40 AM

  Ian,

  I think I know why you must set your pool to 2. When your Rails app needs a connecion to initialize, it is never released unless you do it explicitly. We made a simple test where our application is in severe trouble when it uses a connection in config/environment.rb but does not release it. With a pool of 1, the application is unavailable.

  I assume Rails is initialized by Mongrel master thread which never dies, therefore the connection cannot be brought back in the pool. Have a look at your initialization code and surround any db connection usage with a ActiveRecord::Base.connection_pool.with_connection call.

• You flagged this item as spam.
  

  Ian Leitch July 30th, 2009 @ 02:58 PM

  This is correct, however in my application atleast, clearing the connections inside after_initialize wasn't the solution as something after after_initialize in the Rails initialization was connecting to the database. I've identified what that was now and forced a load in after_initialize so my Mongrel patch is no longer needed and instead I can just clear connections in after_initialize.

  I'm eager to here how you get on with a pool size of 1. When I tried a while back it wasn't long before I started getting connection timeout errors again.

• You flagged this item as spam.
  

  Laurent Farcy July 30th, 2009 @ 03:40 PM

  Ian,

  After fixing the middleware stack for the Sql Session Store, we simply decreased the pool to a single connection and since then, everything goes fine so far. I cannot guarantee we won't get timeout issues with the connection pool in the future.

  Our Mongrel servers may not be as stressed as yours, therfore we may not raise the conditions necessary to get the issue you describe.

• You flagged this item as spam.
  

  Ian Leitch July 30th, 2009 @ 03:40 PM

  This is what I have in my after_initialize callback in environment.rb:

  module Rails
    class Initializer
      def prepare_dispatcher_with_connection_clearing
        prepare_dispatcher_without_connection_clearing
        ActiveRecord::Base.clear_all_connections!
      end
      alias_method_chain :prepare_dispatcher, :connection_clearing
    end
  end
  

• You flagged this item as spam.
  

  Laurent Farcy July 31st, 2009 @ 11:21 AM

  Ian,

  I'm not used to the usage of the after_initialize callback in the context of environment.rb. No more to the alias_method_chain directive.

  To release db connections after Rails initialization, I would have simply called ActiveRecord::Base.clear_all_connections! at the very end of config/environment.rb (not at the end of the Rails::Initializer.run block).

  Are you 100% sure you release db connections at the very end of the initialization process with your method ?

• 

  Mike Riley July 29th, 2010 @ 02:41 PM

  • State changed from “new” to “resolved”
  • Importance changed from “” to “”

  Hello Ian,

  I wanted to update this ticket to see if there was any more information you had on this or if this issue is no longer valid. Let us know so we can help. I am going to mark it resolved since it is so old, but feel free to update and reopen this if necessary.

  Mike Riley

• You flagged this item as spam.
  

  Laurent Farcy July 29th, 2010 @ 02:54 PM

  Mike,

  I don't know for Ian but on my side, it's been solved once I worked around the 'flaw' of the SQL Session store by re-arranging the middleware stack. Actually, we did see the issue once again recently in production but no more for last year.

  Please note that I have not looked up for fixes in a new release of the plugin for a while.

  Laurent Farcy

• 

  Jeff Kreeftmeijer November 8th, 2010 @ 08:53 AM

  • Tag cleared.

  Automatic cleanup of spam.