qa/778

   1 #!/bin/sh
   2 # PCP QA Test No. 778
   3 # Install/Remove postgresql PMDA and check some basic metrics
   4 #
   5 # Copyright (c) 2015 Ken McDonell.  All Rights Reserved.
   6 #
   7
   8 seq=`basename $0`
   9 echo "QA output created by $seq"
  10
  11 # get standard environment, filters and checks
  12 . ./common.product
  13 . ./common.filter
  14 . ./common.check
  15
  16 echo '\q' | $sudo -u postgres psql >/dev/null 2>&1
  17 [ $? -eq 0 ] || _notrun "Cannot run psql as the postgres user, postgresql not installed or running?"
  18
  19 status=1        # failure is the default!
  20 $sudo rm -rf $tmp.* $seq.full
  21 trap "cd $here; rm -rf $tmp.*; exit \$status" 0 1 2 3 15
  22
  23 pmdapostgresql_remove()
  24 {
  25     echo | tee -a $here/$seq.full
  26     echo "=== remove postgresql agent ===" | tee -a $here/$seq.full
  27     $sudo ./Remove >$tmp.out 2>&1
  28     cat $tmp.out >>$here/$seq.full
  29     _filter_pmda_remove <$tmp.out
  30 }
  31
  32 pmdapostgresql_install()
  33 {
  34     # start from known starting points
  35     cd $PCP_PMDAS_DIR/postgresql
  36     $sudo ./Remove >/dev/null 2>&1
  37     $sudo $PCP_RC_DIR/pmcd stop 2>&1 | _filter_pcp_stop
  38
  39     echo | tee -a $here/$seq.full
  40     echo "=== postgresql agent installation ===" | tee -a $here/$seq.full
  41     $sudo ./Install </dev/null >$tmp.out 2>&1
  42     cat $tmp.out >>$here/$seq.full
  43     # filter lines like ...
  44     # Check postgresql metrics have appeared ... 4 warnings, 208 metrics and 6839 values
  45     # into
  46     # Check postgresql metrics have appeared ... X metrics and Y values
  47     _filter_pmda_install <$tmp.out \
  48     | sed \
  49         -e '/^Waiting for pmcd/s/\.\.\.[. ]*$/DOTS/' \
  50         -e 's/[0-9][0-9]* warnings, //' \
  51     | $PCP_AWK_PROG '
  52 /Check postgresql metrics have appeared/   { if ($7 >= 200) $7 = "X"
  53                                           if ($10 >= 5000) $10 = "Y"
  54                                         }
  55                                         { print }'
  56 }
  57
  58 _prepare_pmda postgresql
  59 trap "_cleanup_pmda postgresql; exit \$status" 0 1 2 3 15
  60
  61
  62 _do_sql()
  63 {
  64     $sudo -u postgres psql -c "$*" >$tmp.out 2>$tmp.err
  65     _sts=$?
  66     if [ -s $tmp.err ]
  67     then
  68         cat $tmp.err
  69         echo "Warning: stderr from psql"
  70     fi
  71     if [ $_sts -ne 0 ]
  72     then
  73         echo "Error: psql exit status: $_sts"
  74         exit
  75     fi
  76     cat $tmp.out >>$here/$seq.full
  77     sed <$tmp.out \
  78         -e 's/ *| */|/g' \
  79         -e 's/^  *//' \
  80         -e 's/  *$//' \
  81         -e '/^([0-9][0-9]* row/d' \
  82     | $PCP_AWK_PROG -F\| '
  83 NR == 1 { for (i = 1; i <= NF; i++) name[i] = $i }
  84 NR >= 3 { for (i = 1; i <= NF; i++) print NR "|" name[i] "|" $i }'
  85 }
  86
  87 # aim is to match values ... the sql values are lines like
  88 # this in $tmp.db
  89 # 3|client_port|-1
  90 # 4|client_port|1
  91 # and the pminfo values are in lines like this in $tmp.pcp
  92 # postgresql.stat.activity.client_port 3 -1 1 4
  93 #
  94 # Usage: _match dbpattern [[pcppattern|-] [width [fuzz]]]
  95 #
  96 # if pcppattern is missing or "-", use dbpattern
  97 # if width specified use only the first n characters of each value
  98 # if width is missing or "-", use 0 (match full field width)
  99 # the optional fuzz specifies a +/- % tolerance that is allowed
 100 #
 101 # "match" is weakly defined as some value that is not "" and not 0
 102 # and that occurs in both sets of values (non determinism in the
 103 # queries being run on the DB engine dictate this as close as we can
 104 # get ... major PMDA botches fail even this weak test!)
 105 #
 106 _match()
 107 {
 108     rm -f $tmp.match $tmp.nomatch
 109     pat="$1"
 110     egrep "\\|$pat\\|" $tmp.db >$tmp.val.db
 111     if [ ! -s $tmp.val.db ]
 112     then
 113         echo "_match: failed to pick any values from DB using pattern \"|$pat|\""
 114         return
 115     fi
 116     [ "$2" != "-" -a -n "$2" ] && pat="$2"
 117     width=0     # use full field width
 118     [ "$3" != "-" -a -n "$3" ] && width="$3"
 119     fuzz=0
 120     [ -n "$4" ] && fuzz="$4"
 121     grep "\\.$pat " $tmp.pcp >$tmp.val.pcp
 122     if [ ! -s $tmp.val.pcp ]
 123     then
 124         echo "_match: failed to pick any values from PCP using pattern \"\\.$pat \""
 125         return
 126     fi
 127     nlines=`wc -l <$tmp.val.pcp | sed -e 's/ //g'`
 128     if [ "$nlines" != 1 ]
 129     then
 130         echo "_match: picked $nlines lines of values from PCP, not 1 as expected"
 131         return
 132     fi
 133     # pmprobe output is a bit tricky ... string values have enclosing "
 134     if grep '"' $tmp.val.pcp >/dev/null
 135     then
 136         sed <$tmp.val.pcp \
 137             -e 's/[^"]*"//' \
 138             -e 's/" "/|/g' \
 139             -e 's/"$//'
 140     else
 141         $PCP_AWK_PROG <$tmp.val.pcp '
 142     { for (i = 3; i <= NF; i++) {
 143         if (i > 3) printf "|"
 144         printf "%s",$i
 145       }
 146       print ""
 147     }'
 148     fi >$tmp.tmp
 149
 150     ( echo "$width|$fuzz" ; cat $tmp.tmp $tmp.val.db ) \
 151     | $PCP_AWK_PROG -F\| '
 152 NR == 1 { # options: field_width fuzz_pct
 153           width = $1
 154           fuzz = $2
 155           next
 156         }
 157 NR == 2 { # PCP values
 158           j = 0
 159           for (i = 1; i <= NF; i++) {
 160               if ($i == 0 || $i == "")
 161                 continue
 162               if (width == 0)
 163                   pcp[j] = $i
 164               else
 165                   pcp[j] = substr($i, 1, width)
 166 #debug# print "pcp[" j "]=\"" pcp[j] "\""
 167               j++
 168           }
 169           npcp = j
 170           next
 171         }
 172         { if ($3 == 0 || $3 == "") next
 173           if (width != 0)
 174               $3 = substr($3, 1, width)
 175           for (j = 0; j < npcp; j++) {
 176 #debug# print "pcp[" j "]=\"" pcp[j] "\" : db=\"" $3 "\""
 177               if ($3 == pcp[j]) {
 178                   print "$3" >"'$tmp.match'"
 179                   exit
 180               }
 181               else {
 182                   print "pcp[" j "]=\"" pcp[j] "\" : db=\"" $3 "\"" >"'$tmp.nomatch'"
 183               }
 184           }
 185         }'
 186
 187     if [ -f $tmp.match ]
 188     then
 189         echo "$1: match"
 190     else
 191         echo "$1: no match" | tee -a $here/$seq.full
 192         cat $tmp.nomatch >>$here/$seq.full
 193     fi
 194
 195 }
 196
 197 # real QA test starts here
 198 pmdapostgresql_install
 199
 200 echo
 201 echo "=== check values with pmie ==="
 202 cat <<End-of-File | pmie -t 2sec -T 5sec 2>$tmp.err >$tmp.out
 203 // metrics chosen almost at random .. sort of 1 per cluster and
 204 // metrics where at least one instance is expected to have a
 205 // value > 0
 206 //
 207
 208 ruleset
 209     postgresql.active.xlog_current_location_offset > 0
 210     -> print "postgresql.active.xlog_current_location_offset: OK"
 211 else
 212     postgresql.active.xlog_current_location_offset <= 0
 213     -> print "postgresql.active.xlog_current_location_offset: BAD" " %v"
 214 ;
 215
 216 ruleset
 217     sum_inst instant(postgresql.statio.sys_tables.idx_blks_read) > 0
 218     -> print "postgresql.statio.sys_tables.idx_blks_read: OK"
 219 otherwise
 220     -> print "postgresql.statio.sys_tables.idx_blks_read: BAD" & shell "pminfo -f postgresql.statio.sys_tables.idx_blks_read >>$tmp.bad"
 221 ;
 222
 223 ruleset
 224     sum_inst instant(postgresql.statio.sys_indexes.idx_blks_hit) > 0
 225     -> print "postgresql.statio.sys_indexes.idx_blks_hit: OK"
 226 otherwise
 227     -> print "postgresql.statio.sys_indexes.idx_blks_hit: BAD" & shell "pminfo -f postgresql.statio.sys_indexes.idx_blks_hit >>$tmp.bad"
 228 ;
 229
 230 ruleset
 231     postgresql.stat.activity.datid > 0
 232     -> print "postgresql.stat.activity.datid: OK"
 233 else
 234     postgresql.stat.activity.datid <= 0
 235     -> print "postgresql.stat.activity.datid: BAD" " %v"
 236 ;
 237
 238 ruleset
 239     sum_inst instant(postgresql.stat.sys_indexes.idx_scan) > 0
 240     -> print "postgresql.stat.sys_indexes.idx_scan: OK"
 241 otherwise
 242     -> print "postgresql.stat.sys_indexes.idx_scan: BAD" & shell "pminfo -f postgresql.stat.sys_indexes.idx_scan >>$tmp.bad"
 243 ;
 244
 245 ruleset
 246     sum_inst instant(postgresql.stat.database.tup_returned) > 0
 247     -> print "postgresql.stat.database.tup_returned: OK"
 248 otherwise
 249     -> print "postgresql.stat.database.tup_returned: BAD" & shell "pminfo -f postgresql.stat.database.tup_returned >>$tmp.bad"
 250 ;
 251
 252 ruleset
 253     sum_inst instant(postgresql.stat.sys_tables.idx_tup_fetch) > 0
 254     -> print "postgresql.stat.sys_tables.idx_tup_fetch: OK"
 255 otherwise
 256     -> print "postgresql.stat.sys_tables.idx_tup_fetch: BAD" & shell "pminfo -f postgresql.stat.sys_tables.idx_tup_fetch >>$tmp.bad"
 257 ;
 258
 259 ruleset
 260     sum_inst instant(postgresql.stat.xact.all_tables.seq_scan) > 0
 261     -> print "postgresql.stat.xact.all_tables.seq_scan: OK"
 262 otherwise
 263     -> print "postgresql.stat.xact.all_tables.seq_scan: BAD" & shell "pminfo -f postgresql.stat.xact.all_tables.seq_scan >>$tmp.bad"
 264 ;
 265
 266 ruleset
 267     sum_inst instant(postgresql.stat.xact.sys_tables.idx_scan) > 0
 268     -> print "postgresql.stat.xact.sys_tables.idx_scan: OK"
 269 otherwise
 270     -> print "postgresql.stat.xact.sys_tables.idx_scan: BAD" & shell "pminfo -f postgresql.stat.xact.sys_tables.idx_scan >>$tmp.bad"
 271 ;
 272
 273 ruleset
 274     sum_inst instant(postgresql.stat.all_tables.idx_tup_fetch) > 0
 275     -> print "postgresql.stat.all_tables.idx_tup_fetch: OK"
 276 otherwise
 277     -> print "postgresql.stat.all_tables.idx_tup_fetch: BAD" & shell "pminfo -f postgresql.stat.all_tables.idx_tup_fetch >>$tmp.bad"
 278 ;
 279
 280 ruleset
 281     sum_inst instant(postgresql.stat.all_indexes.idx_tup_read) > 0
 282     -> print "postgresql.stat.all_indexes.idx_tup_read: OK"
 283 otherwise
 284     -> print "postgresql.stat.all_indexes.idx_tup_read: BAD" & shell "pminfo -f postgresql.stat.all_indexes.idx_tup_read >>$tmp.bad"
 285 ;
 286
 287 End-of-File
 288
 289 cat $tmp.out >>$here/$seq.full
 290 cat $tmp.err >>$here/$seq.full
 291 _filter_pmie_log <$tmp.out \
 292 | LC_COLLATE=POSIX sort \
 293 | uniq
 294 [ -f $tmp.bad ] && cat $tmp.bad
 295
 296 echo "validate values ..."
 297
 298 echo | tee -a $here/$seq.full
 299 echo "=== pg_stat_activity ===" | tee -a $here/$seq.full
 300 _do_sql 'select * from pg_stat_activity' >$tmp.db
 301 pmprobe -v postgresql.stat.activity >$tmp.pcp
 302 echo "--- DB values ---" >>$here/$seq.full
 303 cat $tmp.db >>$here/$seq.full
 304 echo "--- PCP values ---" >>$here/$seq.full
 305 cat $tmp.pcp >>$here/$seq.full
 306 _match client_port
 307 _match backend_start
 308 _match "(current_query|query)" current_query
 309
 310 echo | tee -a $here/$seq.full
 311 echo "=== pg_stat_bgwriter ===" | tee -a $here/$seq.full
 312 _do_sql 'select * from pg_stat_bgwriter' >$tmp.db
 313 pmprobe -v postgresql.stat.bgwriter >$tmp.pcp
 314 echo "--- DB values ---" >>$here/$seq.full
 315 cat $tmp.db >>$here/$seq.full
 316 echo "--- PCP values ---" >>$here/$seq.full
 317 cat $tmp.pcp >>$here/$seq.full
 318 _match checkpoints_timed
 319 _match buffers_alloc
 320
 321 echo | tee -a $here/$seq.full
 322 echo "=== pg_stat_database ===" | tee -a $here/$seq.full
 323 _do_sql 'select * from pg_stat_database' >$tmp.db
 324 pmprobe -v postgresql.stat.database >$tmp.pcp
 325 echo "--- DB values ---" >>$here/$seq.full
 326 cat $tmp.db >>$here/$seq.full
 327 echo "--- PCP values ---" >>$here/$seq.full
 328 cat $tmp.pcp >>$here/$seq.full
 329 _match stats_reset - 10
 330 _match tup_fetched - - 25
 331
 332 echo | tee -a $here/$seq.full
 333 echo "=== pg_stat_all_tables ===" | tee -a $here/$seq.full
 334 _do_sql 'select * from pg_stat_all_tables' >$tmp.db
 335 pmprobe -v postgresql.stat.all_tables >$tmp.pcp
 336 echo "--- DB values ---" >>$here/$seq.full
 337 cat $tmp.db >>$here/$seq.full
 338 echo "--- PCP values ---" >>$here/$seq.full
 339 cat $tmp.pcp >>$here/$seq.full
 340 _match seq_tup_read
 341 _match seq_scan
 342 _match idx_tup_fetch
 343 _match idx_scan
 344
 345 echo | tee -a $here/$seq.full
 346 echo "=== pg_stat_sys_tables ===" | tee -a $here/$seq.full
 347 _do_sql 'select * from pg_stat_sys_tables' >$tmp.db
 348 pmprobe -v postgresql.stat.sys_tables >$tmp.pcp
 349 echo "--- DB values ---" >>$here/$seq.full
 350 cat $tmp.db >>$here/$seq.full
 351 echo "--- PCP values ---" >>$here/$seq.full
 352 cat $tmp.pcp >>$here/$seq.full
 353 _match seq_tup_read
 354 _match seq_scan
 355 _match idx_tup_fetch
 356 _match idx_scan
 357
 358 echo | tee -a $here/$seq.full
 359 echo "=== pg_stat_all_indexes ===" | tee -a $here/$seq.full
 360 _do_sql 'select * from pg_stat_all_indexes' >$tmp.db
 361 pmprobe -v postgresql.stat.all_indexes >$tmp.pcp
 362 echo "--- DB values ---" >>$here/$seq.full
 363 cat $tmp.db >>$here/$seq.full
 364 echo "--- PCP values ---" >>$here/$seq.full
 365 cat $tmp.pcp >>$here/$seq.full
 366 _match idx_scan
 367 _match idx_tup_read
 368 _match idx_tup_fetch
 369 _match relname
 370
 371 echo | tee -a $here/$seq.full
 372 echo "=== pg_stat_sys_indexes ===" | tee -a $here/$seq.full
 373 _do_sql 'select * from pg_stat_sys_indexes' >$tmp.db
 374 pmprobe -v postgresql.stat.sys_indexes >$tmp.pcp
 375 echo "--- DB values ---" >>$here/$seq.full
 376 cat $tmp.db >>$here/$seq.full
 377 echo "--- PCP values ---" >>$here/$seq.full
 378 cat $tmp.pcp >>$here/$seq.full
 379 _match idx_scan
 380 _match idx_tup_read
 381 _match idx_tup_fetch
 382 _match relname
 383
 384 echo "PMDA log file ..." >>$here/$seq.full
 385 $sudo cat $PCP_LOG_DIR/pmcd/postgresql.log >>$here/$seq.full
 386
 387 pmdapostgresql_remove
 388
 389 status=0
 390 exit