Révision 0866999f
Make it usable
Fixed the display issue for formerly active users that wore no longer showing once they closed all connections.
Also made other fixes like the negative others and improved performance.
It could still be improved but for now it should do.
| plugins/mysql/mysql_connections_per_user | ||
|---|---|---|
| 23 | 23 |
# |
| 24 | 24 |
# To enable, link mysql_connections to this file. E.g. |
| 25 | 25 |
# |
| 26 |
# ln -s /usr/share/node/node/plugins/mysql_connections /etc/munin/plugins/mysql_connections
|
|
| 26 |
# ln -s /usr/share/node/node/plugins/mysql_connections_per_user /etc/munin/plugins/mysql_connections_per_user
|
|
| 27 | 27 |
# |
| 28 | 28 |
# Revision 1.0 2007/08/03 |
| 29 | 29 |
# Created by Justin Shepherd <galstrom21@gmail.com> |
| ... | ... | |
| 31 | 31 |
# Revision 2.0 2013/01/02 |
| 32 | 32 |
# Per user support by anarcat@koumbit.org |
| 33 | 33 |
# |
| 34 |
# Revision 3.0 2014/06/11 |
|
| 35 |
# Fix now showing if not connected by transilvlad@gmail.com |
|
| 36 |
# Other fixes and performance by transilvlad@gmail.com |
|
| 37 |
# |
|
| 34 | 38 |
# Parameters: |
| 35 | 39 |
# |
| 36 | 40 |
# config |
| ... | ... | |
| 39 | 43 |
# Configuration variables |
| 40 | 44 |
# |
| 41 | 45 |
# mysqlopts - Options to pass to mysql |
| 42 |
# mysqladmin - Override location of mysqladmin
|
|
| 46 |
# mysqlcli - Override location of mysql
|
|
| 43 | 47 |
# numusers - Override maximum number of users to display |
| 44 |
# warning - Override default warning limit |
|
| 45 |
# critical - Override default critical limit |
|
| 48 |
# warning - Override default warning limit
|
|
| 49 |
# critical - Override default critical limit
|
|
| 46 | 50 |
# |
| 47 | 51 |
#%# family=auto |
| 48 | 52 |
#%# capabilities=autoconf |
| ... | ... | |
| 50 | 54 |
use strict; |
| 51 | 55 |
|
| 52 | 56 |
# Define the mysqladmin paths, and commands |
| 53 |
my $MYSQLADMIN = $ENV{mysqladmin} || "mysqladmin";
|
|
| 54 |
my $TEST_COMMAND = "$MYSQLADMIN $ENV{mysqlopts} processlist";
|
|
| 55 |
my $MYSQL_VARIABLES = "$MYSQLADMIN $ENV{mysqlopts} extended-status variables";
|
|
| 56 |
my $warning = $ENV{warning} || "80";
|
|
| 57 |
my $critical = $ENV{critical} || "90";
|
|
| 58 |
my $numusers = $ENV{numusers} || 10;
|
|
| 57 |
my $MYSQLCLI = $ENV{mysqlcli} || "mysql";
|
|
| 58 |
my $TEST_COMMAND = "$MYSQLCLI $ENV{mysqlopts} -N -B -e \"SELECT NOW();\"";
|
|
| 59 |
my $MYSQL_QUERY = "$MYSQLCLI $ENV{mysqlopts} -N -B -e \"SELECT SUM(NO) AS NO, USER FROM (SELECT 1 AS NO, USER FROM INFORMATION_SCHEMA.PROCESSLIST UNION ALL SELECT DISTINCT 0 AS NO, User AS USER FROM mysql.user WHERE User != '') AS Q GROUP BY USER ORDER BY NO DESC;\"";
|
|
| 60 |
my $warning = $ENV{warning} || "80";
|
|
| 61 |
my $critical = $ENV{critical} || "90";
|
|
| 62 |
my $numusers = $ENV{numusers} || 10;
|
|
| 63 |
my $numthreads = 0; |
|
| 59 | 64 |
|
| 60 | 65 |
# Pull in any arguments |
| 61 | 66 |
my $arg = shift(); |
| ... | ... | |
| 72 | 77 |
exit; |
| 73 | 78 |
|
| 74 | 79 |
sub print_graph_data() {
|
| 75 |
# Define the values that are returned to munin
|
|
| 80 |
# Define the values that are returned to munin
|
|
| 76 | 81 |
|
| 77 |
# Return the values to Munin
|
|
| 78 |
my $counts = count_thread_users();
|
|
| 79 |
my %counts = %{$counts};
|
|
| 82 |
# Return the values to Munin
|
|
| 83 |
my $counts = count_thread_users(); |
|
| 84 |
my %counts = %{$counts};
|
|
| 80 | 85 |
|
| 81 |
sub valsort {
|
|
| 82 |
return $counts{$a} <=> $counts{$b};
|
|
| 83 |
} |
|
| 84 |
my $i = 0; |
|
| 85 |
my $total = 0; |
|
| 86 |
foreach my $user (sort valsort keys(%counts)) {
|
|
| 87 |
last if $i++ >= $numusers; |
|
| 88 |
$total += $counts{$user};
|
|
| 89 |
print "$user.value $counts{$user}\n";
|
|
| 86 |
sub valsort {
|
|
| 87 |
return $counts{$a} <=> $counts{$b};
|
|
| 88 |
} |
|
| 89 |
my $i = 0; |
|
| 90 |
my $total = 0; |
|
| 91 |
my $print_user = ""; |
|
| 92 |
foreach my $user (reverse sort { $counts{$a} <=> $counts{$b} } keys %counts) {
|
|
| 93 |
last if $i++ >= $numusers; |
|
| 94 |
$total += $counts{$user};
|
|
| 95 |
$print_user = $user; |
|
| 96 |
if($print_user eq "root") {
|
|
| 97 |
$print_user = "root_"; |
|
| 90 | 98 |
} |
| 91 |
my $other = poll_variables($MYSQL_VARIABLES,"Threads_connected") - $total; |
|
| 92 |
print "other.value $other\n"; |
|
| 93 |
} |
|
| 94 |
|
|
| 95 |
sub poll_variables {
|
|
| 96 |
my $command = shift; |
|
| 97 |
my $expression = shift; |
|
| 98 |
my $ret = 0; |
|
| 99 |
open(SERVICE, "$command |") |
|
| 100 |
or die("Coult not execute '$command': $!");
|
|
| 101 |
while (<SERVICE>) {
|
|
| 102 |
my ($field, $value) = (m/(\w+).*?(\d+(?:\.\d+)?)/); |
|
| 103 |
next unless ($field); |
|
| 104 |
if ($field eq $expression ) {
|
|
| 105 |
$ret = "$value"; |
|
| 106 |
} |
|
| 107 |
} |
|
| 108 |
close(SERVICE); |
|
| 109 |
return $ret; |
|
| 99 |
print "$print_user.value $counts{$user}\n";
|
|
| 100 |
} |
|
| 101 |
my $other = $numthreads - $total; |
|
| 102 |
if($other < 0) {
|
|
| 103 |
$other = 0; |
|
| 104 |
} |
|
| 105 |
print "other.value $other\n"; |
|
| 110 | 106 |
} |
| 111 | 107 |
|
| 112 |
|
|
| 113 | 108 |
sub print_graph_information {
|
| 114 |
print <<EOM; |
|
| 109 |
print <<EOM;
|
|
| 115 | 110 |
graph_title MySQL Connections per user |
| 116 | 111 |
graph_args --base 1000 --lower-limit 0 |
| 117 | 112 |
graph_vlabel Connections |
| ... | ... | |
| 120 | 115 |
graph_total Total |
| 121 | 116 |
EOM |
| 122 | 117 |
|
| 123 |
my $counts = count_thread_users(); |
|
| 124 |
my %counts = %{$counts};
|
|
| 125 |
my $stacked = 0; |
|
| 118 |
my $counts = count_thread_users();
|
|
| 119 |
my %counts = %{$counts};
|
|
| 120 |
my $stacked = 0;
|
|
| 126 | 121 |
|
| 127 |
sub valsort {
|
|
| 128 |
return $counts{$a} <=> $counts{$b};
|
|
| 129 |
} |
|
| 130 |
my $i = 0; |
|
| 131 |
foreach my $user (sort valsort keys(%counts)) {
|
|
| 132 |
last if $i++ >= $numusers; |
|
| 133 |
print <<EOM; |
|
| 134 |
$user.label $user |
|
| 135 |
$user.info Number of connexions used by user $user |
|
| 136 |
EOM |
|
| 137 |
print "$user.draw "; |
|
| 138 |
# if we already printed an entry, make the next ones stacked |
|
| 139 |
if ($i > 1) {
|
|
| 140 |
print "STACK\n"; |
|
| 122 |
sub valsort {
|
|
| 123 |
return $counts{$a} <=> $counts{$b};
|
|
| 141 | 124 |
} |
| 142 |
else {
|
|
| 143 |
print "AREA\n"; |
|
| 125 |
my $i = 0; |
|
| 126 |
foreach my $user (reverse sort { $counts{$a} <=> $counts{$b} } keys %counts) {
|
|
| 127 |
last if $i++ >= $numusers; |
|
| 128 |
my $print_user = $user; |
|
| 129 |
if($print_user eq "root") {
|
|
| 130 |
$print_user = "root_"; |
|
| 131 |
} |
|
| 132 |
print <<EOM; |
|
| 133 |
$print_user.label $user |
|
| 134 |
$print_user.info Number of connexions used by user $print_user |
|
| 135 |
EOM |
|
| 136 |
print "$print_user.draw "; |
|
| 137 |
|
|
| 138 |
# if we already printed an entry, make the next ones stacked |
|
| 139 |
if ($i > 1) {
|
|
| 140 |
print "STACK\n"; |
|
| 141 |
} |
|
| 142 |
else {
|
|
| 143 |
print "AREA\n"; |
|
| 144 |
} |
|
| 144 | 145 |
} |
| 145 |
} |
|
| 146 | 146 |
|
| 147 |
print <<EOM; |
|
| 147 |
print <<EOM;
|
|
| 148 | 148 |
other.label Others |
| 149 | 149 |
other.draw STACK |
| 150 | 150 |
other.info Other connected threads not in the top $numusers |
| ... | ... | |
| 153 | 153 |
|
| 154 | 154 |
sub count_thread_users {
|
| 155 | 155 |
my %counts = (); |
| 156 |
my $command = 'mysql -N -B -e "SHOW PROCESSLIST;"'; |
|
| 157 |
open(SERVICE, "$command |") |
|
| 158 |
or die("Could not execute '$command': $!");
|
|
| 156 |
open(SERVICE, "$MYSQL_QUERY |") |
|
| 157 |
or die("Could not execute '$MYSQL_QUERY': $!");
|
|
| 159 | 158 |
while (<SERVICE>) {
|
| 160 |
my ($threadid, $user) = split "\t";
|
|
| 161 |
next unless ($threadid);
|
|
| 162 |
$counts{$user} = 0 unless defined($counts{$user});
|
|
| 163 |
$counts{$user}++;
|
|
| 159 |
my ($no, $user) = split "\t";
|
|
| 160 |
$user =~ s/^\s+|\s+$//g;
|
|
| 161 |
$counts{$user} = $no;
|
|
| 162 |
$numthreads += $no;
|
|
| 164 | 163 |
} |
| 165 | 164 |
return \%counts; |
| 166 | 165 |
} |
| 167 | 166 |
|
| 168 | 167 |
sub test_service {
|
| 169 | 168 |
my $return = 1; |
| 170 |
system ("$MYSQLADMIN --version >/dev/null 2>/dev/null");
|
|
| 171 |
if ($? == 0) |
|
| 172 |
{
|
|
| 173 |
system ("$TEST_COMMAND >/dev/null 2>/dev/null");
|
|
| 174 |
if ($? == 0) |
|
| 175 |
{
|
|
| 176 |
print "yes\n"; |
|
| 177 |
$return = 0; |
|
| 178 |
} |
|
| 179 |
else |
|
| 180 |
{
|
|
| 181 |
print "no (could not connect to mysql)\n"; |
|
| 182 |
} |
|
| 169 |
system ("$MYSQLCLI --version >/dev/null 2>/dev/null");
|
|
| 170 |
if ($? == 0) {
|
|
| 171 |
system ("$TEST_COMMAND >/dev/null 2>/dev/null");
|
|
| 172 |
if ($? == 0) {
|
|
| 173 |
print "yes\n"; |
|
| 174 |
$return = 0; |
|
| 175 |
} |
|
| 176 |
else {
|
|
| 177 |
print "no (could not connect to mysql)\n"; |
|
| 178 |
} |
|
| 183 | 179 |
} |
| 184 |
else |
|
| 185 |
{
|
|
| 186 |
print "no (mysqladmin not found)\n"; |
|
| 180 |
else {
|
|
| 181 |
print "no (mysql not found)\n"; |
|
| 187 | 182 |
} |
| 188 | 183 |
exit $return; |
| 189 | 184 |
} |
Formats disponibles : Unified diff