PluginPortrait/mysql_query_cache: mysql_querycache

File mysql_querycache, 2.3 KB (added by lordelph@…, at 2006-08-22T23:02:12+02:00)

mysql_query_cache plugin perl script

Line 
1#!/usr/bin/perl
2#
3# mysql_querycache - written by Paul Dixon (paul@elphin.com)
4# adapted from mysql_queries by Per Andreas Buer
5#
6# This munin plugin graphs the efficiency of the mysql query
7# cache, showing the percentage of select queries which were
8# served directly from the cache
9#
10# Parameters:
11#
12#   config
13#   autoconf
14#
15# Configuration variables
16#
17#   mysqlopts     - Options to pass to mysql
18#   mysqladmin    - Override location of mysqladmin
19#
20#%# family=auto
21#%# capabilities=autoconf
22
23use strict;
24
25my $MYSQLADMIN = $ENV{mysqladmin} || "mysqladmin";
26my $COMMAND    =      "$MYSQLADMIN $ENV{mysqlopts} extended-status";
27
28#status variables we want to fetch...
29my %mysql_status = ( 
30               "Com_select"  => 0, 
31               "Qcache_hits" => 0,
32             );
33
34my $arg = shift();
35
36if ($arg eq 'config') {
37    print_config();
38    exit();
39} elsif ($arg eq 'autoconf') {
40    unless (test_service() ) {
41        print "yes\n";
42    } else {
43        print "no\n";
44    }
45    exit;
46}
47
48
49#no special argument to process, let's gather our data!
50
51open(SERVICE, "$COMMAND |")
52  or die("Coult not execute '$COMMAND': $!");
53
54while (<SERVICE>) {
55    my ($k, $v) = (m/(\w+).*?(\d+(?:\.\d+)?)/);
56    next unless ($k);
57    if (exists $mysql_status{$k} ) {
58         $mysql_status{$k}=$v;
59    }
60}
61
62close(SERVICE);
63
64#cache hits do not increment Com_select - figure out how many queries we've serviced...
65my $total_queries = $mysql_status{'Com_select'} + $mysql_status{'Qcache_hits'};
66
67#figure out efficiency in percent
68my $efficiency=$total_queries?(($mysql_status{'Qcache_hits'}/$total_queries)*100):0;
69
70print "efficiency.value $efficiency\n";
71
72exit;
73
74
75
76sub print_config {
77
78    my $num = 0;
79
80    print("graph_title MySQL Query Cache Efficiency
81graph_args --upper-limit 100 -l 0
82graph_vlabel Cache hit %
83graph_category mysql
84graph_info This graph shows the percentage of queries which were served from the query cache
85efficiency.label Efficiency %
86efficiency.type GAUGE
87efficiency.draw LINE2
88");
89
90   
91}
92
93
94sub test_service {
95
96    my $return = 1;
97
98    system ("$MYSQLADMIN --version >/dev/null 2>/dev/null");
99    if ($? == 0)
100    {
101        system ("$COMMAND >/dev/null 2>/dev/null");
102        if ($? == 0)
103        {
104            print "yes\n";
105            $return = 0;
106        }
107        else
108        {
109            print "no (could not connect to mysql)\n";
110        }
111    }
112    else
113    {
114        print "no (mysqadmin not found)\n";
115    }
116    exit $return;
117}