Re: "timer" script from SAMS book or equivalent? - Mailing list pgsql-general

The version sent yesterday was creating a LOT of unnecessary objects when running queries with large result sets. I
tweakedthe postpres-pr classes (via Ruby's very nice open class mechanism). The updated version is below...it took
queriesrunning 10+ minutes down to around 20 seconds. 

Thanks,
John

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


$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
  app = File.basename $0
  usstr = <<-EOL
    #{app} <query> [tablename]
      or
    #{app} /path/to/file/containing/query.sql [tablename]

    Example: #{app} "select * from users" "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

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 PostgresPR::Connection
  def query_no_results(sql)
    puts "Running query in background. Waiting..."
    @conn << PostgresPR::Query.dump(sql)
    loop do
      msg = PostgresPR::Message.read_without_buffer(@conn)
      case msg
      when PostgresPR::ReadyForQuery
        break
      end
    end
  end
end

class PostgresPR::Message
  def self.read_without_buffer(stream, startup=false)
    type = stream.readbytes(1).unpack('C').first unless startup
    length = stream.readbytes(4).unpack('N').first  # FIXME: length should be signed, not unsigned
    if type==?Z
      buffer = Buffer.of_size(startup ? length : 1+length)
      buffer.write_byte(type) unless startup
      buffer.write_int32_network(length)
      buffer.copy_from_stream(stream, length-4)
      (startup ? StartupMessage : MsgTypeMap[type]).create(buffer)
    else
      stream.read(length - 4)
      return nil
    end
  end
end

class PureDBUtil
    def initialize()

        @conn = PostgresPR::Connection.new($dbname,
                                       $dbuser,
                                       $dbpass,
                                       $dburi)
    end
  def start_tran
      @conn.query("BEGIN")
    end
    def commit(results=true)
        if results
      @conn.query("COMMIT")
    else
      @conn.query_no_results("COMMIT")
    end
    end
    def exec(query, results=true)
        if results
      @conn.query(query)
    else
      @conn.query_no_results(query)
    end
    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 gcw(res, col)
    max = 0
    0.upto res.rows.size-1 do |n|
    fld_size = res.get_field_at_row(col, n).size
        if fld_size > max
            max = fld_size
        end
    end
    return max
end

def fill(len, c)
    c * len
end


def printDeltas(begTuples, endTuples, begBlocks, endBlocks)
    nameWidth = gcw(begTuples, "relname")
  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)
  $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

  first = args[0]
  query = nil
  if !first.nil? and File.exists?(first)
    File.open(first, "r") do |f|
      query = f.read
    end
  else
    query = first
  end

  table = args[1]

  usage() if args.size < 1

  if $use_jdbc
    db_class = JDBCDBUtil
  else
    db_class = PureDBUtil
  end

  tran1 = db_class.new()
  tran1.start_tran()
  begTupleValues = getTupleValues(tran1, table)
  begBlockValues = getBlockValues(tran1, table)
  tran1.exec(query,false)
  tran1.commit(false)

  sleep 1

  tran2 = db_class.new()
  tran2.start_tran()
  endTupleValues = getTupleValues(tran2, table)
  endBlockValues = getBlockValues(tran2, table)
  tran2.commit()

  printDeltas(begTupleValues,
              endTupleValues,
              begBlockValues,
              endBlockValues)
end

main(ARGV)