Help with thread and database connection

rubyonrails-talk
1

Hi guys, i need help with this:

# enviroment.rb

config.active_record.allow_concurrency = true

# billing.rb

pid = Fork.begin(:detach => true) do # Fork.begin is a custom module, makes a fork with the properly mysql connection and detach it ...

  while service.status # while status remains true... search for incoming billing (MAIN LOOP)

    messages = self.find(:first, :select => "count(id) as messages_count", :conditions => "etc etc etc")     if messages.messages_count.to_i > 0 # if nothing, search again for incoming billing

      self.connect_billing_socket(service, operator) # attempt to connect to the billing socket

      10.times do |i|         Thread.new do           while true             break unless message = self.get_message(service, operator) rescue false # get a message to make the bill             message.cobro_socket(service, operator) # send through socket the message, phone number of remitent, and others info to make the billing transaction with the operator           end         end       end

    end     sleep 5     service.reload   end

end

So.. basically this is the most important part of the script. I skipped many things to simplify this example. The script connects to a socket to send a billing info, and this socket can process up to 10 asynchronous request. The problem is than each thread creates a new connection to the database, with the time, the database crash for have too many connections open, this happens when all threads finish and the "MAIN LOOP" start over and find more incoming billings, then "10.times do" makes 10 threads again and each thread creates a new database connection...

How i can re-use open connections, or best, can multiples threads share a single database connection??

I really not whant to close the old connections manually..

Thanks

2

So.. basically this is the most important part of the script. I skipped many things to simplify this example. The script connects to a socket to send a billing info, and this socket can process up to 10 asynchronous request. The problem is than each thread creates a new connection to the database, with the time, the database crash for have too many connections open, this happens when all threads finish and the "MAIN LOOP" start over and find more incoming billings, then "10.times do" makes 10 threads again and each thread creates a new database connection...

How i can re-use open connections, or best, can multiples threads share a single database connection??

What you're asking for is connection pooling and rails doesn't have
that. Nick Sieger is working on implementing that (last I heard it was
basically done - it may already have been merged into edge). As far as I know for now you'll have to tear down the connections
yourself. (That said it's not clear to me why you need those 10 threads)

Fred

3

thanks for you reply frederick... the 10 thread is because i need to send 10 asynchronous request to the socket at the same time in the same connection.. the operator socket accept up to 10 asynchronous request per session, each request to the socket have a response of 4 to 10 seconds, sending only one request, means that in one minute the system will process in good conditions 15 request... in the other hand, if i have 10 asynchronous thread, sending 10 request at the same time, the system will process 150 request... it's a big diference..

i have this working on php, and works fine, but i want to migrate the my old php system to rails...

4

Rails is not (yet) threadsafe, so I don't doubt you are having trouble using it in a threaded manner. This being the case, there's little we can do to help support your use of threads in rails.

5

okdk... many thanks Rein

6

ActiveRecord can be used in a thread, but as you've found will leak connections. You could probably get around it by doing the activerecord stuff in your main thread and only spinning off the secondary threads for the network stuff.

Fred

7

Or you can use something like Backgroundrb to spawn new processes which will at least safeguard against this situation. Multiple processes instead of threads is an expensive trade-off though, but can work if you throw enough resources at it.

Bharat

8

i found an exit.. not elegant, but works fine...

first at all "allow_concurrency = true" sucks... really.. it creates too many connections, and never close it... so, manually i have to creates a new connection for each Thread

10.times do |i|   config = Fork::thread do     etc..etc..etc...   end end

ActiveRecord::Base.establish_connection(config) unless con = ActiveRecord::Base.connection.active? rescue false

very similar to "http://blog.ardes.com/2006/12/12/testing-concurrency- in-rails-using-fork", but, when the thread finish, close the connection, and the parent process stay with no db conection.. so.. at the en of the "10.times do |i|" i have to reopen the db connection... it seemed that it works, at least the db connections remains always.. I will continue trying to do of another way.. but, i do not have many hopes... create, an manipulate forks and thread it´s very easy and elegant in rails, but Active Record seems to be very fragile in matters of parallelism..