postgres_activity.awk

postgres_activity.awk

#!/usr/bin/awk -f
{ pid=$2;
  time=$4;
  action=$5;
  state=$7;

  # backend states
  backend_state[0]="undefined";
  backend_state[1]="idle";
  backend_state[2]="running";
  backend_state[3]="idle in transaction";
  backend_state[4]="fastpath";
  backend_state[5]="idle in transaction aborted";
  backend_state[6]="disabled";

  # remove colon from time
  time=substr(time,1,length(time)-1);

  # get first relative timestamp in output
  if ( overall_begin_time == "" )
    overall_begin_time = time;

  # get the first relative timestamp for the pid
  if ( begin_time[pid] == "" )
    begin_time[pid] = time;

  # remove 'arg1=' from state
  sub("arg1=","",state)

  if ( action ~ /pgstat_report_activity:$/ ) {
    # if we don't have a previous timestamp, we cannot measure elapsed time.
    if ( prev_activity[pid] == "" ) {
      prev_activity[pid]=time;
      prev_state[pid]=state;
    } else {
    # but if we have a previous timestamp, we can!
      report_activity[pid][prev_state[pid]]+=time-prev_activity[pid];
      count_activity[pid][prev_state[pid]]++;
      prev_activity[pid]=time;
      prev_state[pid]=state;
    }
  }

  end_time[pid] = time;
}
END {
  total_overall_time=time-overall_begin_time;
  printf "%-50s             %15.6f s %6.2f %%\n\n", "total time in file", total_overall_time, 100;
  for ( array_pid in report_activity ) {
    printf "pid: %7d                                                   %15.6f s %6.2f %%\n", array_pid, end_time[array_pid]-begin_time[array_pid], (end_time[array_pid]-begin_time[array_pid])/total_overall_time*100;
    if ( array_pid in report_activity ) {
      for ( state in report_activity[array_pid] ) {
        printf " %-50s %10d %15.6f s %6.2f %%\n", "state " backend_state[strtonum(state)], count_activity[array_pid][state], report_activity[array_pid][state], report_activity[array_pid][state]/(end_time[array_pid]-begin_time[array_pid])*100;
      }
    }
    printf "\n";
  }
}