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