BugsSite/contrib/mysqld-watcher.pl

   1 #!/usr/bin/env perl -w
   2 #
   3 # The contents of this file are subject to the Mozilla Public
   4 # License Version 1.1 (the "License"); you may not use this file
   5 # except in compliance with the License. You may obtain a copy of
   6 # the License at http://www.mozilla.org/MPL/
   7 #
   8 # Software distributed under the License is distributed on an "AS
   9 # IS" basis, WITHOUT WARRANTY OF ANY KIND, either express or
  10 # implied. See the License for the specific language governing
  11 # rights and limitations under the License.
  12 #
  13 # The Original Code is the Bugzilla Bug Tracking System.
  14 #
  15 # The Initial Developer of the Original Code is Netscape Communications
  16 # Corporation. Portions created by Netscape are
  17 # Copyright (C) 2000 Netscape Communications Corporation.  All
  18 # Rights Reserved.
  19 #
  20 # Contributor(s): Dan Mosedale <dmose@mozilla.org>
  21 #
  22
  23 # mysqld-watcher.pl - a script that watches the running instance of
  24 # mysqld and kills off any long-running SELECTs against the shadow_db
  25 #
  26 use strict;
  27
  28 # some configurables:
  29
  30 # length of time before a thread is eligible to be killed, in seconds
  31 #
  32 my $long_query_time = 180;
  33 #
  34 # the From header for any messages sent out
  35 #
  36 my $mail_from = "root\@mothra.mozilla.org";
  37 #
  38 # the To header for any messages sent out
  39 #
  40 my $mail_to = "root";
  41 #
  42 # mail transfer agent.  this should probably really be converted to a Param().
  43 #
  44 my $mta_program = "/usr/lib/sendmail -t -ODeliveryMode=deferred";
  45
  46 # The array of long-running queries
  47 #
  48 my $long = {};
  49
  50 # Run mysqladmin processlist twice, the first time getting complete queries
  51 # and the second time getting just abbreviated queries.  We want complete
  52 # queries so we know which queries are taking too long to run, but complete
  53 # queries with line breaks get missed by this script, so we get abbreviated
  54 # queries as well to make sure we don't miss any.
  55 foreach my $command ("/opt/mysql/bin/mysqladmin --verbose processlist",
  56                      "/opt/mysql/bin/mysqladmin processlist")
  57 {
  58     close(STDIN);
  59     open(STDIN, "$command |");
  60
  61     # iterate through the running threads
  62     #
  63     while ( <STDIN> ) {
  64         my @F = split(/\|/);
  65
  66         # if this line is not the correct number of fields, or if the thread-id
  67         # field contains Id, skip this line.  both these cases indicate that this
  68         # line contains pretty-printing gunk and not thread info.
  69         #
  70         next if ( $#F != 9 || $F[1] =~ /Id/);
  71
  72         if ( $F[4] =~ /shadow_bugs/             # shadowbugs database in use
  73              && $F[5] =~ /Query/                # this is actually a query
  74              && $F[6] > $long_query_time        # this query has taken too long
  75              && $F[8] =~ /(select|SELECT)/      # only kill a select
  76              && !defined($long->{$F[1]}) )      # haven't seen this one already
  77         {
  78             $long->{$F[1]} = \@F;
  79             system("/opt/mysql/bin/mysqladmin", "kill", $F[1]);
  80         }
  81     }
  82 }
  83
  84 # send an email message
  85 #
  86 # should perhaps be moved to somewhere more global for use in bugzilla as a
  87 # whole; should also do more error-checking
  88 #
  89 sub sendEmail($$$$) {
  90     ($#_ == 3) || die("sendEmail: invalid number of arguments");
  91     my ($from, $to, $subject, $body) = @_;
  92
  93     open(MTA, "|$mta_program");
  94     print MTA "From: $from\n";
  95     print MTA "To: $to\n";
  96     print MTA "Subject: $subject\n";
  97     print MTA "\n";
  98     print MTA $body;
  99     print MTA "\n";
 100     close(MTA);
 101
 102 }
 103
 104 # if we found anything, kill the database thread and send mail about it
 105 #
 106 if (scalar(keys(%$long))) {
 107     my $message = "";
 108     foreach my $process_id (keys(%$long)) {
 109         my $qry = $long->{$process_id};
 110         $message .= join(" ", @$qry) . "\n\n";
 111     }
 112
 113     # fire off an email telling the maintainer that we had to kill some threads
 114     #
 115     sendEmail($mail_from, $mail_to, "long running MySQL thread(s) killed", $message);
 116 }
 117