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

The version sent yesterday was creating a LOT of unnecessary objects when running queries with large result sets. I tweaked the postpres-pr classes (via Ruby's very nice open class mechanism). The updated version is below...it took queries running 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)