Combining Database Connections

Question

I created a small chat application in Sinatra and jQuery on heroku. It simply inserts a message into the database when the user sends it. It also downloads new messages every 2 seconds. After several minutes of testing, it stopped working and I received the heroku email form:

Hello,

We noticed that the gisekchat application has a large number of connections open to a common database. We had to limit the number of connections to the shared database for performance reasons. Can you reduce the number of shared connections to a common db or go to a dedicated database?

It seems that you are not using the connection pool and are opening a new database connection for each request from your application.

Thank you, Chris

This is an action that supports sending 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 do I change it to enable pooling?

Answer 1

Yes, it’s true, you really open a new database connection every time a “send” message is sent.

So you need to change that. One possibility is to open a global connection:

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

This must be done after initializing the $ dbname ... variables, but before using any routes.

However, if you are using a modular version of synatra and not a classic version, you can declare an instance variable with

 attr_accessor :con 

and initialize it before launching the application.

Answer 2

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

You want to create a thread-safe api data warehouse that will use the mutex to handle access to your connection objects (s). This is a control mechanism that uses ActiveRecord in rails.

I created a ruby ​​stone called "q", which provides a simple mechanism for this. The Pearl is here: https://github.com/jacobsimeon/q

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

then add this to any file that initializes your application: require 'q/resourcepool'

then create a data warehouse 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 are interested in following this route.

Answer 3

I used the "classic" sinatra application, and I got the same result by declaring class variables: for example, @@ db_connection. This always uses the same connection and works for me.