Analyzer for postgresql.log

Attached is a little AWK script that you may find of some use.

It takes as standard input a file with the format of the Postgresql log
(level 2, for PostgreSQL 7.0.3 tested).

On standard output, you get a list of times and a mangled form of every
executed request.

Something like

13;select * from my_table where field=''
15;select * from other_table where id= and test>

All number are removed from requests. Also removed are the contents
between single quotes.

This allows me to sort by request and then either count them or add up
the times. That way I know what I need to optimize.

The script is far from perfect. It just works for me ;-)

Antonio Fiol
#!/usr/bin/awk -f

# Copyright 2001 Antonio Fiol - W3ping S.A.
# E-mail: fiol@w3ping.com

#  This program is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.

#  This program is distributed in the hope that it will be useful,
#  but WITHOUT ANY WARRANTY; without even the implied warranty of
#  MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#  GNU General Public License for more details.

#  You should have received a copy of the GNU General Public License
#  along with this program; if not, write to the Free Software
#  Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA



function datediff ( start, end,    sf, ef, diff ) {
    split (start, sf, /\.|:/);
    split (end,   ef,   /\.|:/);
    diff= (ef[5]-sf[5]);
    diff+=(ef[4]-sf[4])*1000;
    diff+=(ef[3]-sf[3])*1000*60;
    diff+=(ef[2]-sf[2])*1000*60*60;
    diff+=(ef[1]-sf[1])*1000*60*60*24;
    #Month change not controlled
    return diff;
}

BEGIN {
    OFS=";"
}
/StartTransactionCommand/ {
    start[$2]=$1;
}
/query:/ {
    record=$0;
    gsub(/.*query: /,"",record);
    gsub(/[m-]?([0-9]+\.?|\.[0-9])[0-9]*/,"",record); # Consider numbers like 123, -123.4 or m123
    gsub(/'[^']*'/,"''",record);
    gsub(/ *;.*$/,"",record); # Hope that query was not like query1;query2;. We will delete query2 :-(
    query[$2]=record;
}
/CommitTransactionCommand/ {
    if($2 in start) {
      print datediff(start[$2], $1), "\""query[$2]"\"";
      fflush();
    }
    delete start[$2];
    delete query[$2];
}