# ================================ SUMMARY ==================================== # # File : check_mysqld_queries.ncfg # Version : 0.22 # Date : Dec 19, 2011 # Author : William Leibzon - william@leibzon.org # Summary : Nagiosgrapher template for mysql check done with check_mysqld.pl # Licence : GPL - summary below, text at http://www.fsf.org/licenses/gpl.txt # # This is considered a supplemental file to check_mysqld.pl plugin and though # not distributed in unified package it is distributed under same licencing # terms (GNU 2.0 license). Any redisribution of this file in modified form must # reproduce this summary (modified if appropriate) and must either include # entire GNU license in a package or list URL where it can be found if # distributed as single file. # # ===================== INFORMATION ABOUT THIS TEMPLATE ======================= # # This is a nagiosgrapher template for mysql check done with check_mysqld.pl. # This is a version of the template that only provides data for first two # screens (from the list below) i.e. Queries and Data Traffic. Unlike full # template it should work with nagios installations with small buffers and # should not require specialized version of nagiosgrapher. # # The template tries to graph number of MYSQL variables returned with # "SHOW STATUS" (for mysql 5.x - "SHOW GLOBAL STATUS"), which are described at: # http://dev.mysql.com/doc/refman/4.1/en/server-status-variables.html # # Data graphs are separated into the following pages # (for those using ngraphs.cgi these "Metric Groups"): # # 1. Queries (rate of queries being processed) # This is default screen when you first enter graph and it shows how many # DELETE, INSERT, UPDATE and SELECT queries are processed. For select it # also tries to show how many queries are being answered with data from # query cache and for those that are causing db read it shows if query # causes cache to be updated or not. This mysql documentation page # explains a little about qcache variables and totals for SELECT queries: # http://dev.mysql.com/doc/refman/4.1/en/query-cache-status-and-maintenance.html # 2. Data Traffic (in MB/sec) # No explantion needed - just your typical network traffic graph. # 3. Connections and Threads # Current number of connections to the server, maximum number of connections # and number threads in use. # 4. Tables and Files # Number of open files, open tables. Number of temp files & tables created. # Also Number of table locks per second. # 5. Errors and Issues # Various parameters (far from all possible to be retrieved) that indicate # a problem - all should be 0 or close to it. Rate of slow queries are one # of the probably most well known and tracked of the variables graphed. # 6. Key Cache Efficiency # This is based on key_ variables and supposed to indicate percent of key # requests queries that are answered from memory. For more info see comment # under 'key_reads' variable of mysql documentation page on how to calculate # "cache miss rate". Efficiency percent I show is just 100%-cache_miss%. # 7. Key Cache Data # Raw data used in calculating efficiency. This is graphed separately # to track total number of requests. # 8. Handler Row Requests # This tracks requests for next row, previous row and associated update, # delete,insert and other rwo requests that you do when keeping open # handle. This is all very interesting data, but I do not entirely # understand it and feel that numbers reported are too high. # 9. Query Cache Memory # This will show used and free blocks and total number of queries in cache. # 10. Query Cache Hits # Number of hits and update of query cache. Most of these numbers are # already shown as part of first "Queries" screeen. # 11. Binlog Cache Transactions # Graphed are binlog_cache_use and binlog_cache_disk_use variables. # # ============================= SETUP NOTES ==================================== # # # 1. Copy this template into your ngraph.d directory # 2. Make sure all nagios service you want to graph have names # that start with 'MYSQL' (case should not matter) # 3. Make sure you specify all attributes as below listed under # '$USER21$' as a '-A' parameter to check_mysqld.pl plugin # 4. Make sure you have copy of nagios that accepts without being # cut performance data of up to 1500 bytes (best 2k) in size # ('threads_running=??' should be the last performance variable seen # under 'Performance Data:" in nagios 'Service State Information') # # What is below are what I have for full verison of the template. For # this limited one that only shows statistics on queries, it is enough # that you have the following in command definition: # # command_line $USER1$/check_mysqld.pl -H $HOSTADDRESS$ -u nagios -p $ USER7$ -a uptime,threads_connected,questions,slow_queries, open_tables -w ',,,,' -c ',,,,' -A com_select,com_update,com_insert,com_insert_select,com_commit,com_delete,com_rollback,bytes_received,bytes_sent,qcache_hits,qcache_inserts,qcache_not_cached,questions # # ---------------------------------------------------------------------- # # For reference the following is how I defined mysql check in nagios config: # define command{ # command_name check_mysql # command_line $USER1$/check_mysqld.pl -H $HOSTADDRESS$ -u nagios -p $USER7$ -a uptime,threads_connected,questions,slow_queries, open_tables -w ',,,,' -c ',,,,' -A $USER21$ # } # This service definition is just: # define service{ # use db-service # servicegroups dbservices # hostgroup_name mysql # service_description MySQL # check_command check_mysql # } # And most important (for the graphing) my resource.cfg has the following: # # Mysql 'nagios' user password # $USER7$=public_example # # # List of variables to be retrieved for mysqld (here mostly for convinience so as not to put in commmands.cfg) # $USER21$='com_select,com_update,com_insert,com_insert_select,com_commit,com_delete,com_rollback,aborted_clients,aborted_connects,binlog_cache_disk_use,binlog_cache_use,bytes_received,bytes_sent,connections,created_tmp_disk_tables,created_tmp_files,created_tmp_tables,delayed_errors,delayed_insert_threads,delayed_writes,handler_update,handler_write,handler_delete,handler_read_first,handler_read_key,handler_read_next,handler_read_prev,handler_read_rnd,handler_read_rnd_next,key_blocks_not_flushed,key_blocks_unused,key_blocks_used,key_read_requests,key_reads,key_write_requests,key_writes,max_used_connections,not_flushed_delayed_rows,open_files,open_streams,open_tables,opened_tables,prepared_stmt_count,qcache_free_blocks,qcache_free_memory,qcache_hits,qcache_inserts,qcache_lowmem_prunes,qcache_not_cached,qcache_queries_in_cache,qcache_total_blocks,questions,select_full_join,select_rangle_check,slow_launch_threads,slow_queries,table_locks_immediate,table_locks_waited,threads_cached,threads_connected,threads_created,threads_running' # # ----------------------------------------------------------------------------- # # You will need nagios with larger buffers (as compared to usual 2.x # distrubutions) for strong performance variable in order to fully # utilize this template. Doing so requires recompile after modifying # MAX_INPUT_BUFFER, MAX_COMMAND_BUFFER, MAX_PLUGINOUTPUT_LENGTH # which are defined in objects.h and common.h. # Patches for some versions of nagios is available at # http://william.leibzon.org/nagios/ # # Additionally this template relies on features that may not with standard # nagiosgrapher, in particular it requires treating definition with no # or empty page as being shared among configs for all pages (this is # used for defining variable from performance data and thereafter more # then one page of graphs uses it in calculating local CDEF). I have # not checked on latest versions of nagiosgrapher and do not know for # certain if that would or would not work properly. If not either try # to use simpler version of this template (only first two pages are defined) # or see my site (URL in last paragraph) for modified nagiosgrapher code. # # ========================= VERSION HISTORY and TODO ========================== # # v0.2 - 01/02/2008 : This is initial public release # (I'll not go into details about my work on it prior) # v0.21 - 01/03/2008 : Fixed bug in calculation of total number of queries without counting cache hits # v0.22 - 12/19/2011 : Changed so that first STACK is now AREA, # otherwise newer rrd gives error # # TODO: a. Testing under newest 5.x and 6.0 alpha versions of mysql # b. Better documentation of what graphed data means. # c. Information from mysql developers about 'handler' data # and confirmation that it is being displayed properly # # =============================== END OF HEADER =============================== define ngraph{ service_name MYSQL graph_perf_regex com_commit=(\d+) graph_value com_commit graph_units commands/sec graph_legend Commit Commands : rrd_type COUNTER rrd_plottype AREA rrd_color DDA0DD page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_commit print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_commit print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_commit print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL graph_perf_regex com_rollback=(\d+) graph_value com_rollback graph_units commands/sec graph_legend Rollback Commands : rrd_type COUNTER rrd_plottype STACK rrd_color FF8C00 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_rollback print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_rollback print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_rollback print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL graph_perf_regex com_delete=(\d+) graph_value com_delete graph_units commands/sec graph_legend Delete Commands : rrd_type COUNTER rrd_plottype STACK rrd_color 8B4513 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_delete print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_delete print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_delete print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL graph_perf_regex com_update=(\d+) graph_value com_update graph_units commands/sec graph_legend Update Commands : rrd_type COUNTER rrd_plottype STACK rrd_color FF1493 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_update print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_update print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_update print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL graph_perf_regex com_insert=(\d+) graph_value com_insert graph_units commands/sec graph_legend Insert Commands : rrd_type COUNTER rrd_plottype STACK rrd_color 1E90FF page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_insert print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_insert print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_insert print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL graph_perf_regex com_insert_select=(\d+) graph_value com_inselect graph_units commands/sec graph_legend Insert_Select Commands : rrd_type COUNTER rrd_plottype STACK rrd_color 00FFFF page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_inselect print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_inselect print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_inselect print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL type COMMENT print_description print_repeat 0 page SQL Queries print_eol left } define ngraph{ service_name MYSQL graph_perf_regex com_select=(\d+) graph_value com_select rrd_type COUNTER page SQL Queries hide yes } define ngraph{ service_name MYSQL graph_perf_regex qcache_hits=(\d+) graph_value qc_hits rrd_type COUNTER page SQL Queries hide yes } define ngraph{ service_name MYSQL graph_perf_regex qcache_inserts=(\d+) graph_value qc_inserts rrd_type COUNTER page SQL Queries hide yes } define ngraph{ service_name MYSQL graph_perf_regex qcache_not_cached=(\d+) graph_value qc_not_cached rrd_type COUNTER page SQL Queries hide yes } define ngraph{ service_name MYSQL type CDEF graph_value select_graph graph_units commands/sec graph_legend Select - from DB : graph_calc com_select,qc_inserts,-,qc_not_cached,- rrd_plottype STACK rrd_color 7FFF00 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_select print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_select print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_select print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL type CDEF graph_value com_qc_notcached graph_units commands/sec graph_legend - of that not cached : graph_calc qc_not_cached rrd_plottype STACK rrd_color 32CD32 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_notcached print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_notcached print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_notcached print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL type CDEF graph_value com_qc_inserts graph_units commands/sec graph_legend - of that added to cache : graph_calc qc_inserts rrd_plottype STACK rrd_color 98FB98 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_inserts print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_inserts print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_inserts print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL type CDEF graph_value com_qc_select graph_units commands/sec graph_legend Select - from Cache : graph_calc qc_hits rrd_plottype STACK rrd_color F0E68C page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_select print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_select print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_qc_select print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL type CDEF graph_value com_select_total graph_calc qc_hits,com_select,+ page SQL Queries hide yes } define ngraph{ service_name MYSQL type COMMENT print_description = Total Select Queries : print_repeat 0 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_select_total print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_select_total print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source com_select_total print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL type COMMENT print_description print_repeat 0 page SQL Queries print_eol left } define ngraph{ service_name MYSQL graph_perf_regex questions=(\d+) graph_value questions rrd_type COUNTER page SQL Queries hide yes } define ngraph{ service_name MYSQL type CDEF graph_value queries_nocache graph_units commands/sec graph_legend All DB Queries (except cache hits) : graph_calc questions,qc_hits,- rrd_plottype LINE1 rrd_color 696969 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source queries_nocache print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source queries_nocache print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source queries_nocache print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL type CDEF graph_value queries_all graph_units commands/sec graph_legend All Questions (counting cache hits) : graph_calc questions rrd_plottype LINE2 rrd_color 000000 page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source queries_all print_description latest = print_function LAST print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source queries_all print_description max = print_function MAX print_format %2.1lf\t page SQL Queries } define ngraph{ service_name MYSQL type GPRINT print_source queries_all print_description average = print_function AVERAGE print_format %2.1lf print_eol left page SQL Queries } define ngraph{ service_name MYSQL graph_perf_regex bytes_sent=(\d+) graph_value bytes_sent graph_units bytes/sec graph_legend Traffic Out : rrd_type COUNTER rrd_plottype AREA rrd_color 00FF00 page Data Traffic } define ngraph{ service_name MYSQL type GPRINT print_source bytes_sent print_description latest = print_function LAST print_format %2.1lf\t page Data Traffic } define ngraph{ service_name MYSQL type GPRINT print_source bytes_sent print_description max = print_function MAX print_format %2.1lf\t page Data Traffic } define ngraph{ service_name MYSQL type GPRINT print_source bytes_sent print_description average = print_function AVERAGE print_format %2.1lf print_eol left page Data Traffic } define ngraph{ service_name MYSQL graph_perf_regex bytes_received=(\d+) graph_value bytes_received graph_units bytes/sec graph_legend Traffic In : rrd_type COUNTER rrd_plottype LINE2 rrd_color 0000FF page Data Traffic } define ngraph{ service_name MYSQL type GPRINT print_source bytes_received print_description latest = print_function LAST print_format %2.1lf\t page Data Traffic } define ngraph{ service_name MYSQL type GPRINT print_source bytes_received print_description max = print_function MAX print_format %2.1lf\t page Data Traffic } define ngraph{ service_name MYSQL type GPRINT print_source bytes_received print_description average = print_function AVERAGE print_format %2.1lf print_eol left page Data Traffic }