From: Yohanes Santoso <ruby-talk@...>
Date: 2002-07-13T13:46:26+09:00
Subject: Re: cache dbi for web app

--=-=-=

Tom Sawyer <transami@transami.net> writes:

> but my web app is dog slow. one of the reasons is that i connect to the
> database on every request. is there a way to persist a database
> connection between requests?

if you're using mod_ruby or any other methods that use the same process
for serving different requests, then perhaps this connection pool
class is helpful.

YS.


--=-=-=
Content-Type: application/octet-stream
Content-Disposition: attachment; filename=dbasePool.rb

require 'config'
require 'dbi'
require 'thread'

=begin
=end

class DbasePool
  @@classMutex = Mutex.new
  @@connectionFreed = ConditionVariable.new
  @@instance = nil
  @monitorThread
  @pool
  @poolSize
  @monitorThread
  
  attr_reader :host, :dbaseName 
  attr_reader :userName, :password, :monitorInterval

  def initialize(host = nil, dbaseName = nil, userName = nil, password = nil, 
		 poolSize = 5, monitorInterval = 3600)
    if @@instance then raise "Attempted to re-init DbasePool" end

    @@classMutex.synchronize {
      @host = host
      @dbaseName = dbaseName
      @userName = userName
      @password = password
      @poolSize = poolSize
      @monitorInterval = monitorInterval #in seconds, 0 to disable monitoring.
      @pool = []
      0.upto(@poolSize-1) do |i|
	conn = @pool[i] = connect
	class << conn
	  attr_accessor :status
	  attr_accessor :parent
	  def freeConnection
	    parent.freeConnection(self)
	  end
	  #Now override several DBI's methods. 
	  #The default methods suck big time.
	  #Also, the MySQL DBD driver does not 
	  #reflect true capability of MySQL.
	  #So, the following changes allows for nested transaction,
	  #and also provide transaction for any database that support it
	  #regardless of what the DBD driver may say.
	  attr_accessor :transaction_depth
	  def begin_transaction
	    if @transaction_depth == 0
	      self.do("BEGIN")
	      puts "DBASE_POOL: Transaction started"
	    end
	    @transaction_depth += 1
	  end
	  def commit_transaction
	    if @transaction_depth == 1
	      self.do("COMMIT")
	      puts "DBASE_POOL: Transaction stopped"
	    end
	    @transaction_depth -= 1
	  end
	  def rollback_transaction
	    if @transaction_depth == 1
	      self.do("ROLLBACK")
	      puts "DBASE_POOL: Transaction rolled back"
	    end
	    @transaction_depth -= 1
          end
	  def transaction
	    raise InterfaceError, 
	      "Database connection was already closed!" if @handle.nil?
	    raise InterfaceError, "No block given" unless block_given?
	    begin_transaction
	    begin
	      yield self
	      commit_transaction
	    rescue Exception
	      rollback_transaction
	      raise
	    end
	  end
	end
	conn.status = "IDLE"
	conn.parent = self
	conn.transaction_depth = 0 
      end
      @monitorThread = Thread.new do 
	while(true) 
	  sleep(monitorInterval)
	  @@classMutex.synchronize {
	    #puts "HAI, monitoring is awake"
	    @pool.each do |conn|
	      if (conn.status == "IDLE") && (not conn.ping)
		conn = connect
	      end
	    end
	  }
	end
      end
      @@instance = self
    }
  end


  def connect
    DBI.connect("DBI:Mysql:#{@dbaseName}:#{@host}", @userName, @password)
  end 
  

  #What's the destructor name in Ruby?
  def finish
    @@classMutex.synchronize {
      @monitorThread.stop
      @pool.each do |conn| 
	conn.disconnect
      end
    }
  end


  #Get the next idle connection. If there is no idle connection, will block
  #until there is one.
  #Optionally accepts a block. If a block is given, will execute the block
  #and free up the connection afterwards.
  def getConnection
    idleConn = nil #predeclare
    @@classMutex.synchronize {
      callcc do |cont| 
	idleConn = @pool.find do |conn| 
	  conn.status == "IDLE" 
	end
	if not idleConn
	  puts "No more free connection in DBASE_POOL. Waiting..."
	  puts @@instance.inspect
	  puts caller.join("\n")
	  @@connectionFreed.wait(@@classMutex)
	  puts "DBASE_POOL: a connection is freed. Resuming."
	  cont.call
	end
      end
      idleConn.status = "BUSY"
    }
    if block_given? 
      yield idleConn
      idleConn.freeConnection
    else
      idleConn
    end
  end
  

  def freeConnection(usedConn)
    conn = nil       #predeclare
    @@classMutex.synchronize {
      conn = @pool.find do |conn| 
	conn == usedConn
      end
    }
    if conn
      conn.status = "IDLE"
      @@connectionFreed.signal
    else
      raise "Cannot found the referred connection!"
    end
  end
end		 





# Initialise DBASE_POOL
DBASE_POOL = DbasePool.new(Config::DBASE_HOST, 
			   Config::DBASE_DBNAME,
			   Config::DBASE_USER,
			   Config::DBASE_PASSWORD)









if $0 == __FILE__
  #test, and also benchmark.
  #on AMD-K62 400MHz, can do 217 SELECT NOW() statements per second.
  1.upto(1000) do |i|
    puts i if i % 100 == 0 
    dbh = []
    1.upto(5) do |i|
      dbh[i] = DBASE_POOL.getConnection
    end
    1.upto(5) do |i|
      dbh[i].execute("SELECT NOW()")
    end
    1.upto(5) do |i|
      DBASE_POOL.freeConnection dbh[i]
    end 
    1.upto(5) do 
      DBASE_POOL.getConnection do |dbh|
	dbh.execute("SELECT NOW()")
      end
    end
  end
end


--=-=-=--