Search Postgresql Archives

Re: "timer" script from SAMS book or equivalent?

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



My Ruby version is found below. Note it requires the postgres-pr ruby driver. Also note I didn't really ruby-ize it to much...for the most part it's a one-to-one translation. 

One different thing to note...this script expects you to have postgres-pr installed via rubygems. You can modify the require statements (actually just comment out the rubygems line) if you're is installed in a non-rubygems way.

Also note, this requires you to define your connection parameters in env variables, which works in my situation.

PG_TIMER_DB   - name of the database
PG_TIMER_USER - name of database user
PG_TIMER_PASS - password of database user
PG_TIMER_URI  - connection uri that postgres-pr understands.
  Examples:
    tcp://localhost:5432
    unix:/tmp/.s.PGSQL.5432


Hope it helps someone else.

###################################################
require 'rubygems'
require 'postgres-pr/connection'


$tupleQuery = "SELECT relname, seq_scan, 
                      seq_tup_read, idx_scan, 
                      idx_tup_fetch 
               FROM pg_stat_all_tables"


$blockQuery = "SELECT relname, heap_blks_read, 
                      heap_blks_hit, idx_blks_read, 
                      idx_blks_hit 
               FROM pg_statio_all_tables"

$use_jdbc = false

def usage
  usstr = <<-EOL
    #{$0} <query>
    Example: #{$0} "select * from users"
    
    Note: the following environment variables MUST be set:
    PG_TIMER_DB   - name of the database
    PG_TIMER_USER - name of database user
    PG_TIMER_PASS - password of database user
    PG_TIMER_URI  - connection uri that postgres-pr understands.
      Examples:
        tcp://localhost:5432
        unix:/tmp/.s.PGSQL.5432
  EOL
  puts usstr
  exit()
end

$dbname = ENV['PG_TIMER_DB']
$dbuser = ENV['PG_TIMER_USER']
$dbpass = ENV['PG_TIMER_PASS']
$dburi  = ENV['PG_TIMER_URI']

[$dbname, $dbuser, $dbpass, $dburi].each do |one|
  one.nil? && usage()
end

class PostgresPR::Connection::Result
  def get_field_at_row(field, row)  
    idx = @fields.collect{|f|f.name}.index field
    @rows[row][idx]
  end
end


class PureDBUtil  
	def initialize()		
          @conn = PostgresPR::Connection.new($dbname, 
                                       $dbuser, 
                                       $dbpass, 
                                       $dburi)		
	end
  def start_tran 
  	@conn.query("BEGIN")
	end
	def commit
		@conn.query("COMMIT")
	end
	def exec(query)      
		@conn.query(query)
	end
end

class JDBCDBUtil
	def initialize(dbname=nil)
    raise Exception, "not implemented"
	end  
end

def getTupleValues(tran, table_name=nil)
	if !table_name.nil?
		return tran.exec($tupleQuery + " ORDER by relname")
	else
		return tran.exec($tupleQuery + " WHERE relname = '#{table_name}' ")
	end
end

def getBlockValues(tran, table_name)
	if !table_name.nil?
		return tran.exec($blockQuery + " ORDER by relname")
	else
		return tran.exec($blockQuery + " WHERE relname = '#{table_name}' ")
	end
end

def getDelta(n, beginning, ending, col)
  endVal = 0
  begVal = 0 
  endVal = ending.get_field_at_row(col, n)
  begVal = beginning.get_field_at_row(col, n)
  return endVal.to_f - begVal.to_f;
end


def getColumnWidth(res, col)
	max = 0
	res.rows.each do |one|
		if one[col].size > max
			max = one[col].size
		end
	end
	return max
end

def fill(len, c)
	c * len
end


def printDeltas(begTuples, endTuples, begBlocks, endBlocks)
	nameWidth = 15
  str = ""
  str << '+' << fill( nameWidth, '-' ) << "+-----------------------------------+----------------------------------+" << "\n";
  str << '|' << fill( nameWidth, ' ' ) << "|         SEQUENTIAL I/O            |        INDEXED I/O               |" << "\n";
  str << '|' << fill( nameWidth, ' ' ) << "| scans | tuples |heap_blks |cached | scans | tuples | idx_blks |cached|" << "\n";
  str << '|' << fill( nameWidth, '-' ) << "+-------+--------+-----------+------+-------+--------+----------+------+" << "\n";
  totSeqScans  = 0
  totSeqTuples = 0
  totHeapBlks  = 0
  totHeapHits  = 0
  totIdxScans  = 0
  totIdxTuples = 0
  totIdxBlks   = 0
  totIdxHits   = 0
  tableCount   = 0

  0.upto begTuples.rows.size-1 do |row|
    seqScans  = getDelta(row, begTuples, endTuples, "seq_scan")
    seqTuples = getDelta(row, begTuples, endTuples, "seq_tup_read")
    heapBlks  = getDelta(row, begBlocks, endBlocks, "heap_blks_read")
    heapHits  = getDelta(row, begBlocks, endBlocks, "heap_blks_hit")
    idxScans  = getDelta(row, begTuples, endTuples, "idx_scan")
    idxTuples = getDelta(row, begTuples, endTuples, "idx_tup_fetch")
    idxBlks   = getDelta(row, begBlocks, endBlocks, "idx_blks_read")
    idxHits   = getDelta(row, begBlocks, endBlocks, "idx_blks_hit")  

    if(( seqScans + seqTuples + heapBlks + 
         heapHits + idxScans + idxTuples + 
         idxBlks + idxHits ) > 0 )
      str << '|' << begTuples.get_field_at_row("relname", row).ljust(nameWidth);
      str << '|' << seqScans.to_s.rjust(6)  << ' '
      str << '|' << seqTuples.to_s.rjust(7) << ' '
      str << '|' << heapBlks.to_s.rjust(10) << ' '
      str << '|' << heapHits.to_s.rjust(5)  << ' '
      str << '|' << idxScans.to_s.rjust(6)  << ' '
      str << '|' << idxTuples.to_s.rjust(7) << ' '
      str << '|' << idxBlks.to_s.rjust(9)   << ' '
      str << '|' << idxHits.to_s.rjust(5)   << ' '
      str << '|' << "\n"

      tableCount   += 1
      totSeqScans  += seqScans
      totSeqTuples += seqTuples
      totHeapBlks  += heapBlks
      totHeapHits  += heapHits
      totIdxScans  += idxScans
      totIdxTuples += idxTuples      
      totIdxBlks   += idxBlks
      totIdxHits   += idxHits
    end
  end
  str << '+' << fill( nameWidth, '-' ) << 
         "+-------+--------+-----------+" << 
         "------+-------+--------+----------+------+\n";

  if( tableCount > 1 )
    str << '|' << "Totals".ljust(nameWidth)
    str << '|' << totSeqScans.to_s.rjust(6)  << ' ';
    str << '|' << totSeqTuples.to_s.rjust(7) << ' ';
    str << '|' << totHeapBlks.to_s.rjust(10) << ' ';
    str << '|' << totHeapHits.to_s.rjust(5)  << ' ';
  
    str << '|' << totIdxScans.to_s.rjust(6)  << ' ';
    str << '|' << totIdxTuples.to_s.rjust(7) << ' ';
    str << '|' << totIdxBlks.to_s.rjust(9)   << ' ';
    str << '|' << totIdxHits.to_s.rjust(5)   << ' ';
    str << '|' << "\n";
    str << '+' << fill( nameWidth, '-' ) << 
         "+-------+--------+-----------+" << 
         "------+-------+--------+----------+------+\n";    
  end
  puts str
end  
     

def main(args)
  arg = args[0]
  
  usage() if arg.nil?
  if $use_jdbc
    db_class = JDBCDBUtil
  else
    db_class = PureDBUtil
  end
  tran1 = db_class.new()
  tran1.start_tran()
  begTupleValues = getTupleValues(tran1, arg)
  begBlockValues = getBlockValues(tran1, arg)
  ignore = tran1.exec(args[0])
  tran1.commit()

  sleep 1

  tran2 = db_class.new()
  endTupleValues = getTupleValues(tran2, arg)
  endBlockValues = getBlockValues(tran2, arg)

  printDeltas(begTupleValues, 
              endTupleValues, 
              begBlockValues, 
              endBlockValues)            
end

main(ARGV)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux