Database connection pooling

Question

I created a small chat application in Sinatra and jQuery on heroku. It simply inserts a message to a database when user submits it. And also downloads new messages every 2 seconds. After a few minutes of testing it stopped working and I received an email form heroku:

Hi,

We noticed that the gisekchat app had a large number of connections open to the shared database. We had to limit the number of connections to the shared database due to performance reasons. Can you either reduce the number of overall connections to the shared db or move to a dedicated database?

It does appear that you're not taking advantage of connection pooling and are opening a new connection to the database for each request from your app.

Thanks, -Chris

This is the action supporting submitting a message (receiving is very similar):

post '/send' do
  con = con = PGconn.connect($dbhost, 5432, "","",$dbname, $dbuser, $dbpass)
  con.exec("insert into messages(usr, msg, date) values('#{params[:usr]}','#{params[:msg]}', now())")    
end

How should I change it to enable connection pooling?

Answer 1

Yeah well it's true, you really are opening a new connection to the database each time there is a 'send'-post send.

So you'll need to change that. One Possibility may be, to open the connection globally:

 $con = PGconn.connect($dbhost, 5432, "","",$dbname, $dbuser, $dbpass)

This should be done after you initialized your $dbname... variables, but before you use any routes.

However, if you are using the modular sinatra version, instead of the classic one, you could declare an instance variable with

attr_accessor :con

and initialize it before the app is started.

Answer 2

Robustus is halfway there creating an instance variable will create a connection for each instance of your class.

What you want is a thread-safe data store api that will use a mutex to handle access to your connection object(s). This is the control mechanism that ActiveRecord uses in rails.

I have created a ruby gem called 'q' that provides a simple mechanism for doing this. The gem is located here: https://github.com/jacobsimeon/q

install like this git clone https://github.com/jacobsimeon/q

then add this to whatever file initializes your app: require 'q/resourcepool'

then, create your datastore during initialization:

class DataConnection < ResourcePool
  def create_resource
    PGConn.connect(@config)
  end
end
@datasource = ResourcePool.new(your_connection_info_here)

then, you can use @datasource to execute commands against your database

post '/send' do
  @datasource.exec(standard_postgres_params)
end

Contact me on github or twitter (@jacobsimeon on both) if you're interested in going this route.