#!/opt/kenai/bin/ruby require 'rubygems' require 'mysql' require 'json' # Changelog: # Version 0.4-ruby. # Version 0.4: Added round() to Joins_without_indexes_per_day to remove exponents for large query sets. # Version 0.3: 11:57 AM 10/13/2008 # - Correct where Percent_innodb_cache_hit_rate and/or Percent_innodb_cache_write_waits_required could have been 0 # Version 0.2: 10:43 AM 10/13/2008 # - Corrected error where replication not enabled stopped the monitoring completely (If you had your warning on strict, # the server could see things like: Notice: Undefined variable: Slave_IO_Running) # - Ditto for Last_Errno, Last_Error DEBUG = false SLOWINNODB = false # If you set this to 1, then the script is very careful about status # variables to avoid http://bugs.mysql.com/bug.php?id=36600 SYSTEM = "mysql" + (DEBUG ? "-debug" : "") LOG = "/tmp/zabbix_#{SYSTEM}.log" DAT = "/tmp/zabbix_#{SYSTEM}.dat" UTIME = "/tmp/.zabbix_#{SYSTEM}.utime" DTIME = "/tmp/.zabbix_#{SYSTEM}.dtime" AGENT_CONF = "/export/home/marcow/agentd.conf" MYSQL = "/usr/bin/mysql" ZABBIX_SENDER = "echo" type = $*[0] user = $*[1] pass = $*[2] cred = "-u#{user} -p#{pass}" puts "cred= #{cred}\n" def close(a, b) return 0 if a == 0 && b > 1 return 0 if b == 0 && a > 1 delta = abs(b - a) * 100/a delta < 90 end def kb(a) a * 1024 end def mb(a) a * 1024 * 1024 end def gb(a) a * 1024 * 1024 * 1024 end def logk(x) ret = 0; return ret if x.nil? while (x >= 1024) ret += 1 x /= 1024 end ret end def abs(a) return a if a > 0 return -1 * a end def max(a, b) return b if a.nil? return a if b.nil? return a if a.to_i >= b.to_i b end def min(*args) return nil if args.nil? || args.length == 0 while (a = args.delete_at(0)).nil? && args.length > 0 end return a if args.length == 0 while ((b = args.delete_at(0)) || args.length > 0) if !b.nil? if a.to_i > b.to_i a = b end end end a end def byte_size(size) filesizename = ["", "K", "M", "G", "T", "P", "E", "Z", "Y"] i = logk(size) size ? (size.to_f / (1024 ** i)).round.to_s + filesizename[i] : '0' end def pct2hi(a) @values['Uptime'] < 86400 ? 100 : percent(a) end def percent(a) (100 * a).to_f.round end def value(a) return a if a.nil? return a.to_i if a.match(/^\d+$/) return a.to_f if a.match(/^[\d.]+$/) return a end def zabbix_config() File.delete(DAT) if File.exists?(DAT) File.delete(LOG) if File.exists?(LOG) # Get server information for zabbix_sender config = File.read(AGENT_CONF) host = config.match(/Hostname\s*=\s*(.*)/i)[0] server = config.match(/Server\s*=\s*(.*)/i)[0] return host, server end def find_valid_localhosts() # Gather localhost aliases valids = ["localhost", "127.0.0.1" ] `grep 127.0.0.1 /etc/hosts`.lines("\n") do |line| line.split(/[ \t,]+/).each do |part| valids.push part unless part.nil? || part == '' end end return valids end def zabbix_post(var, val) if val.is_a? String val = case val when "yes" then 1 when "on" then 1 when "no" then 0 when "" then 0 when "off" then 0 else val end end val = '"' + val +'"' if val.is_a? String File.open(DAT, "a+") { |f| f.puts "#{@server} #{@host} 10051 #{SYSTEM}.#{var} #{val}\n" } cmd = "#{ZABBIX_SENDER} -z #{@server} -p 10051 -s #{@host} -k #{SYSTEM}.#{var} -o #{val}" if DEBUG puts "#{cmd}\n" else system("#{cmd} 2>&1 >> " + LOG) end end def elapsed(val) now = Time.new elapsed = -1 if File.exists?(UTIME) # we have a base data = JSON.parse(File.read(UTIME)) seconds = now - data["start"].to_f elapsed = (val - data["value"]).to_f/( seconds.nil? || seconds == 0 ? 1 : seconds).to_f end data = { "value" => val, "start" => now } File.open(UTIME, "w") { |f| f.puts data.to_json } elapsed < 0 ? 0 : elapsed end @host, @server = zabbix_config() if `uname -o` =~ /Linux/ physical_memory = `free -b | grep Mem | awk '{print \$2}'`.to_i swap_memory = `free -b | grep Swap | awk '{print \$2}'`.to_i; bit64 = ! `uname -m`.match(/64/).nil? # Is this a 64bit machine? else physical_memory = `/usr/sbin/prtconf | grep Memory | awk '{print \$3}'`.to_i * 1024 * 1024 # it's returned in MB swap_memory = (`/usr/sbin/swap -s | awk '{ print substr($9, 0, length($9) - 1); }'`.to_i + `/usr/sbin/swap -s | awk '{ print substr($11, 0, length($11) - 1); }'`.to_i) * 1024 bit64 = ! `isalist`.match(/64|v9/).nil? # Is this a 64bit machine? end puts("Physical Memory: #{byte_size(physical_memory)}, Swap: #{byte_size(swap_memory)}\n") if DEBUG valid_localhosts = find_valid_localhosts() # Connect to the MySQL server connection = Mysql.new("localhost", user, pass, "mysql") # Get the version number version = `#{MYSQL} --version`.split()[4] # Get server variables engines = { :have_myisam => 'YES', :have_memory => 'YES'} # these are auto enabled. no config necessary @values = {} connection.query("show global variables;").each_hash do |row| var = row["Variable_name"] val = row["Value"] @values[var] = value(val) puts "values[" + var + "]= " + @values[var].to_s + "\n" if (var[0,5] == "have_" && val == "YES") engines[var] = val end end if SLOWINNODB # Global status variables we use: [ "Aborted_clients", "Aborted_connects", "Binlog_cache_disk_use", "Binlog_cache_use", "Bytes_received", "Bytes_sent", "Com_alter_db", "Com_alter_table", "Com_create_db", "Com_create_function", "Com_create_index", "Com_create_table", "Com_delete", "Com_drop_db", "Com_drop_function", "Com_drop_index", "Com_drop_table", "Com_drop_user", "Com_grant", "Com_insert", "Com_replace", "Com_revoke", "Com_revoke_all", "Com_select", "Com_update", "Connections", "Created_tmp_disk_tables", "Created_tmp_tables", "Handler_read_first", "Handler_read_key", "Handler_read_next", "Handler_read_prev", "Handler_read_rnd", "Handler_read_rnd_next", "Innodb_buffer_pool_read_requests", "Innodb_buffer_pool_reads", "Innodb_buffer_pool_wait_free", "Innodb_buffer_pool_write_requests", "Innodb_log_waits", "Innodb_log_writes", "Key_blocks_unused", "Key_read_requests", "Key_reads", "Key_write_requests", "Key_writes", "Max_used_connections", "Open_files", "Open_tables", "Opened_tables", "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_range", "Select_range_check", "Select_scan", "Slave_running", "Slow_launch_threads", "Slow_queries", "Sort_merge_passes", "Sort_range", "Sort_rows", "Sort_scan", "Table_locks_immediate", "Table_locks_waited", "Threads_cached", "Threads_connected", "Threads_created", "Threads_running", "Uptime" ].each do |var| connection.query("show global status like '#{var}';").each_hash do |row| @values[row["Variable_name"]] = value(row["Value"]) end end else connection.query("show global status;").each_hash do |row| @values[row["Variable_name"]] = value(row["Value"]) puts "values[" + row["Variable_name"] + "]= " + @values[row["Variable_name"]].to_s + "\n" end end # Replication master status connection.query("show master status;").each_hash do |row| row.each do |key, val| key = "Master_Status_#{key}" @values[key] = value(val) end end connection.query("show slave status;").each_hash do |row| row.each do |key, val| @values[key] = value(val) end end # Did not error above, so Available @values['Available'] = 1 if type == "daily" if File.exists?(DTIME) diff = (Time.new - File.ctime(DTIME))/60/60/24 if diff < 1 puts "Skipping daily gathering\n" if DEBUG puts 1 exit(0) end File.delete(DTIME) end File.open(DTIME, "w") { |f| f.puts "Ran at " + Time.new().strftime("%Y%m%dT%H%M%S") + "\n"} # These are dangerous privileges privs = { "Insert_priv" => "Insert_priv_count", "Update_priv" => "Update_priv_count", "Delete_priv" => "Delete_priv_count", "Drop_priv" => "Drop_priv_count", "Shutdown_priv" => "Shut_down_priv_count", "Process_priv" => "Process_priv_count", "File_priv" => "File_priv_count", "Grant_priv" => "Grant_priv_count", "Alter_priv" => "Alter_priv_count", "Super_priv" => "Super_priv_count", "Lock_tables_priv" => "Lock_tables_priv_count", } privs.each do |key, var| @values[var] = 0 end @values['Root_accounts_count'] = @values['Accounts_without_password'] = @values['Accounts_with_broad_host_specifier'] = @values['Anonymous_accounts_count'] = 0 # Now, load users and let's see what's there connection.query("select * from user").each_hash do |row| @values['Accounts_with_broad_host_specifier'] += 1 if (row['Host'] == "" || row['Host'] == '%') if row['User'] == "root" @values['Root_accounts_count'] += 1 invalid = false @values['Is_root_remote_login_enabled'] = 1 if row['Host'] == "" || row['Host'] == '%' || valid_localhosts.index(row['Host']).nil? @values['Is_root_without_password'] = 1 if row['Password'] == "" end @values['Accounts_without_password'] += 1 if row['Password'] == "" @values['Anonymous_accounts_count'] += 1 if row['User'] == "" privs.each do |key, var| @values[var] += 1 if row[key] == "Y" end end # How many fragmented tables to we have? @values['Fragmented_table_count'] = 0 connection.query("SELECT COUNT(TABLE_NAME) as Frag FROM information_schema.TABLES WHERE TABLE_SCHEMA NOT IN ('information_schema','mysql') AND Data_free > 10000").each_hash do |row| @values['Fragmented_table_count'] = row["Frag"] end # Get the engines in use connection.query("SELECT DISTINCT ENGINE FROM information_schema.TABLES").each_hash do |row| row.each do |key, var| key = "have_" + var.downcase engines[key] = "USED" if engines.has_key?(key) end end @values['Innodb_only_install'] = (engines['have_myisam'] == "YES" && engines['have_innodb'] == "USED") ? 1 : 0 @values['Myisam_only_install'] = (engines['have_myisam'] == "USED" && engines['have_innodb'] == "YES") ? 1 : 0 @values['Suggested_table_cache'] = max(256, @values['max_connections'] * 2) @values['Maximum_memory_possible'] = (@values['innodb_buffer_pool_size'] + @values['key_buffer_size'] + @values['max_connections'] * (@values['sort_buffer_size'] + @values['read_buffer_size'] + @values['binlog_cache_size']) + @values['max_connections'] * mb(2)) @values['Available_memory'] = physical_memory * 9 / 10 @values['Available_memory'] -= @values['max_connections'] * kb(256) @values['Suggested_query_cache_size'] = (@values['Available_memory'] / 10 < mb(8) ? mb(8) : @values['Available_memory'] / 10) @values['Available_memory'] -= @values['Suggested_query_cache_size'] bm = @values['Available_memory'] * 7 / 10 tbm = @values['Available_memory'] * 3 / 10 mpt = tbm * 10 / @values['max_connections'] bm -= @values['Suggested_table_cache'] * kb(8) btb = mpt * 10 @values['Suggested_tmp_table_size'] = max(mb(16), btb) tc = max(@values['max_connections'] / 2, 16) @values['Suggested_thread_cache_size'] = min(tc, 64) msbs = max(btb, mb(8)) @values['Suggested_myisam_sort_buffer_size'] = min(msbs, physical_memory * 2 / 10) mb = bm * (@values['innodb_only_install'] ? 0.05 : (@values['myisam_only_install'] ? 1 : 0.5)) @values['Suggested_key_buffer_size'] = max(mb / 2, mb(8)) @values['Suggested_read_buffer_size'] = min(physical_memory / 100, mpt * 20 / 100, kb(64)) @values['Suggested_read_rnd_buffer_size'] = min(physical_memory * 4 / 100, mpt * 40 / 100, kb(256)) @values['Suggested_sort_buffer_size'] = min(physical_memory * 2 / 100, mpt * 30 / 100, kb(256)) if @values['myisam_only_install'] @values.delete('Suggested_innodb_additional_mem_pool_size') @values.delete('Suggested_innodb_log_buffer_size') @values.delete('Suggested_innodb_buffer_pool_size') @values['Suggested_innodb_log_file_size'] = 0 else ib = bm * (1 - (@values['innodb_only_install'] ? 0.05 : (@values['myisam_only_install'] ? 1 : 0.5))) @values['Suggested_innodb_additional_mem_pool_size'] = max(mb(2), ib * 2 / 100) ib -= @values['Suggested_innodb_additional_mem_pool_size'] @values['Suggested_innodb_log_buffer_size'] = min(max(mb(1), ib / 100), mb(16)) ib -= @values['Suggested_innodb_log_buffer_size'] @values['Suggested_innodb_buffer_pool_size'] = ibps = max(ib, mb(8)) ib -= @values['Suggested_innodb_buffer_pool_size'] @values['Suggested_innodb_log_file_size'] = min(ibps * 2 / 10, gb(1)) end @values['table_cache'] = @values['table_open_cache'] if @values['table_cache'].nil? tosend = { 'Architecture_handles_all_memory' => (physical_memory <= 2147483648 || bit64 ? 1 : 0), 'Maximum_memory_total' => @values['Maximum_memory_possible'], 'Maximum_memory_over_physical' => @values['Maximum_memory_possible'] > physical_memory ? 1 :0, 'Maximum_memory_exceeds_32bit_capabilities' => !bit64 ? @values['Maximum_memory_possible'] > gb(2) : 0, # Suggested configuration settings 'Suggested_table_cache' => byte_size(@values['Suggested_table_cache']), 'Change_table_cache' => !close(@values['Suggested_table_cache'], @values['table_cache']), 'table_cache' => @values['table_cache'], 'Suggested_query_cache_size' => byte_size(@values['Suggested_query_cache_size']), 'Change_query_cache_size' => !close(@values['Suggested_query_cache_size'], @values['query_cache_size']), 'query_cache_size' => @values['query_cache_size'], 'Suggested_tmp_table_size' => byte_size(@values['Suggested_tmp_table_size']), 'Change_tmp_table_size' => !close(@values['Suggested_tmp_table_size'], @values['tmp_table_size']), 'tmp_table_size' => @values['tmp_table_size'], 'Suggested_myisam_sort_buffer_size' => byte_size(@values['Suggested_myisam_sort_buffer_size']), 'Change_myisam_sort_buffer_size' => !close(@values['Suggested_myisam_sort_buffer_size'], @values['myisam_sort_buffer_size']), 'myisam_sort_buffer_size' => @values['myisam_sort_buffer_size'], 'Suggested_key_buffer_size' => byte_size(@values['Suggested_key_buffer_size']), 'Change_key_buffer_size' => !close(@values['Suggested_key_buffer_size'], @values['key_buffer_size']), 'key_buffer_size' => @values['key_buffer_size'], 'Suggested_read_buffer_size' => byte_size(@values['Suggested_read_buffer_size']), 'Change_read_buffer_size' => !close(@values['Suggested_read_buffer_size'], @values['read_buffer_size']), 'read_buffer_size' => @values['read_buffer_size'], 'Suggested_read_rnd_buffer_size' => byte_size(@values['Suggested_read_rnd_buffer_size']), 'Change_read_rnd_buffer_size' => !close(@values['Suggested_read_rnd_buffer_size'], @values['read_rnd_buffer_size']), 'read_rnd_buffer_size' => @values['read_rnd_buffer_size'], 'Suggested_sort_buffer_size' => byte_size(@values['Suggested_sort_buffer_size']), 'Change_sort_buffer_size' => !close(@values['Suggested_sort_buffer_size'], @values['sort_buffer_size']), 'sort_buffer_size' => @values['sort_buffer_size'], 'Suggested_innodb_additional_mem_pool_size' => byte_size(@values['Suggested_innodb_additional_mem_pool_size']), 'Change_innodb_additional_mem_pool_size' => !close(@values['Suggested_innodb_additional_mem_pool_size'], @values['innodb_additional_mem_pool_size']), 'innodb_additional_mem_pool_size' => @values['innodb_additional_mem_pool_size'], 'Suggested_innodb_log_buffer_size' => byte_size(@values['Suggested_innodb_log_buffer_size']), 'Change_innodb_log_buffer_size' => !close(@values['Suggested_innodb_log_buffer_size'], @values['innodb_log_buffer_size']), 'innodb_log_buffer_size' => @values['innodb_log_buffer_size'], 'Suggested_innodb_buffer_pool_size' => byte_size(@values['Suggested_innodb_buffer_pool_size']), 'Change_innodb_buffer_pool_size' => !close(@values['Suggested_innodb_buffer_pool_size'], @values['innodb_buffer_pool_size']), 'innodb_buffer_pool_size' => @values['innodb_buffer_pool_size'], 'Suggested_innodb_log_file_size' => byte_size(@values['Suggested_innodb_log_file_size']), 'Change_innodb_log_file_size' => !close(@values['Suggested_innodb_log_file_size'], @values['innodb_log_file_size']), 'innodb_log_file_size' => @values['innodb_log_file_size'], # Setup/Security parameters 'Accounts_with_broad_host_specifier' => @values['Accounts_with_broad_host_specifier'], 'Accounts_without_password' => @values['Accounts_without_password'], 'Anonymous_accounts_count' => @values['Anonymous_accounts_count'], 'Alter_priv_count' => @values['Alter_priv_count'], 'Delete_priv_count' => @values['Delete_priv_count'], 'Drop_priv_count' => @values['Drop_priv_count'], 'File_priv_count' => @values['File_priv_count'], 'Grant_priv_count' => @values['Grant_priv_count'], 'Insert_priv_count' => @values['Insert_priv_count'], 'Lock_tables_priv_count' => @values['Lock_tables_priv_count'], 'Process_priv_count' => @values['Process_priv_count'], 'Shut_down_priv_count' => @values['Shut_down_priv_count'], 'Super_priv_count' => @values['Super_priv_count'], 'Update_priv_count' => @values['Update_priv_count'], 'Is_root_remote_login_enabled' => @values['Is_root_remote_login_enabled'], 'Is_root_without_password' => @values['Is_root_without_password'], 'Root_accounts_count' => @values['Root_accounts_count'], 'have_symlink' => @values['have_symlink'], 'old_passwords' => @values['old_passwords'], 'secure_auth' => @values['secure_auth'], 'skip_show_database' => @values['skip_show_database'], 'myisam_recover_options' => @values['myisam_recover_options'] == "OFF" ? 0 : 1, 'wait_timeout' => @values['wait_timeout'], 'slow_launch_time' => @values['slow_launch_time'], 'local_infile' => @values['local_infile'], 'log_bin' => @values['log_bin'], 'log_queries_not_using_indexes' => @values['log_queries_not_using_indexes'], 'log_slow_queries' => @values['log_slow_queries'], 'long_query_time' => @values['long_query_time'], 'Version' => @values['version'], 'binlog_cache_size' => @values['binlog_cache_size'], 'sync_binlog' => @values['sync_binlog'], 'have_query_cache' => @values['have_query_cache'], 'query_cache_limit' => @values['query_cache_limit'], 'query_cache_min_res_unit' => @values['query_cache_min_res_unit'], 'query_cache_type' => @values['query_cache_type'], 'query_prealloc_size' => @values['query_prealloc_size'], 'join_buffer_size' => @values['join_buffer_size'], 'key_cache_block_size' => @values['key_cache_block_size'], 'max_heap_table_size' => @values['max_heap_table_size'], 'sql_mode' => @values['sql_mode'], 'max_connections' => @values['max_connections'], 'thread_cache_size' => @values['thread_cache_size'], 'innodb_flush_log_at_trx_commit' => @values['innodb_flush_log_at_trx_commit'], 'innodb_log_files_in_group' => @values['innodb_log_files_in_group'], 'expire_logs_days' => @values['expire_logs_days'], # Types of queries and usage 'Com_alter_db' => @values['Com_alter_db'], 'Com_alter_table' => @values['Com_alter_table'], 'Com_create_db' => @values['Com_create_db'], 'Com_create_function' => @values['Com_create_function'], 'Com_create_index' => @values['Com_create_index'], 'Com_create_table' => @values['Com_create_table'], 'Com_drop_db' => @values['Com_drop_db'], 'Com_drop_function' => @values['Com_drop_function'], 'Com_drop_index' => @values['Com_drop_index'], 'Com_drop_table' => @values['Com_drop_table'], 'Com_drop_user' => @values['Com_drop_user'], 'Com_grant' => @values['Com_grant'], 'Excessive_revokes' => @values['Com_revoke'] + @values['Com_revoke_all'], 'Percent_writes_vs_total' => percent((@values['Com_insert'] + @values['Com_replace'] + @values['Com_update'] + @values['Com_delete']) / @values['Questions']), 'Percent_inserts_vs_total' => percent((@values['Com_insert'] + @values['Com_replace']) / @values['Questions']), 'Percent_selects_vs_total' => percent((@values['Com_select'] + @values['Qcache_hits']) / @values['Questions']), 'Percent_deletes_vs_total' => percent( @values['Com_delete'] / @values['Questions']), 'Percent_updates_vs_total' => percent( @values['Com_update'] / @values['Questions']), 'Recent_schema_changes' => (@values['Com_create_db'] > 0 || @values['Com_alter_db'] > 0 || @values['Com_drop_db'] > 0 || @values['Com_create_function'] > 0 || @values['Com_drop_function'] > 0 || @values['Com_create_index'] > 0 || @values['Com_drop_index'] > 0 || @values['Com_alter_table'] > 0 || @values['Com_create_table'] > 0 || @values['Com_drop_table'] > 0 || @values['Com_drop_user'] > 0), 'Fragmented_table_count' => @values['Fragmented_table_count'], } elsif @values['Uptime'] < 3600 # wait 1h before sending data after a restart puts 1 exit(0) else # The bi-minute # Make a pretty uptime string seconds = @values['Uptime'] % 60 minutes = ((@values['Uptime'] % 3600) / 60).floor hours = ((@values['Uptime'] % 86400) / 3600).floor days = (@values['Uptime'] / 86400).floor @values['Uptimestring'] = '' if days > 0 @values['Uptimestring'] = "#{days}d " end if hours > 0 || @values['Uptimestring'].length > 0 @values['Uptimestring'] += "#{hours}h " end if minutes > 0 || @values['Uptimestring'].length > 0 @values['Uptimestring'] += "#{minutes}m " end @values['Uptimestring'] += "#{seconds}s" if days == 0 days = 100000000 # force percentage to be low on calculation end if @values['Innodb_buffer_pool_read_requests'].nil? || @values['Innodb_buffer_pool_read_requests'] == 0 @values['Percent_innodb_cache_hit_rate'] = 0 else @values['Percent_innodb_cache_hit_rate'] = pct2hi(1 - (@values['Innodb_buffer_pool_reads'] / @values['Innodb_buffer_pool_read_requests'])) end if @values['Innodb_buffer_pool_write_requests'].nil? || @values['Innodb_buffer_pool_write_requests'] == 0 @values['Percent_innodb_cache_write_waits_required'] = 0 else @values['Percent_innodb_cache_write_waits_required'] = percent(@values['Innodb_buffer_pool_wait_free'] / @values['Innodb_buffer_pool_write_requests']) end tosend = { 'Available' => @values['Available'], 'Uptime' => @values['Uptimestring'], 'Last_Errno' => @values['Last_Errno'] ? @values['Last_Errno'] : 0, 'Last_Error' => @values['Last_Error'] ? @values['Last_Error'] : "", # Binlog parameters 'Binlog_cache_disk_use' => @values['Binlog_cache_disk_use'], 'Binlog_cache_use' => @values['Binlog_cache_use'], # Performance/Usage/Queries 'Questions' => @values['Questions'], 'Queries_per_sec' => elapsed(@values['Questions'].to_f), 'Bytes_received' => @values['Bytes_received'], 'Bytes_sent' => @values['Bytes_sent'], 'Select_full_join' => @values['Select_full_join'], 'Select_scan' => @values['Select_scan'], 'Slow_queries' => @values['Slow_queries'], 'Qcache_free_blocks' => @values['Qcache_free_blocks'], 'Qcache_free_memory' => @values['Qcache_free_memory'], 'Qcache_hits' => @values['Qcache_hits'], 'Qcache_inserts' => @values['Qcache_inserts'], 'Qcache_lowmem_prunes' => @values['Qcache_lowmem_prunes'], 'Qcache_lowmem_prunes_per_day' => @values['Qcache_lowmem_prunes']/days, 'Qcache_not_cached' => @values['Qcache_not_cached'], 'Qcache_queries_in_cache' => @values['Qcache_queries_in_cache'], 'Qcache_total_blocks' => @values['Qcache_total_blocks'], 'Average_rows_per_query' => (@values['Handler_read_first'] + @values['Handler_read_key'] + @values['Handler_read_next'] + @values['Handler_read_prev'] + @values['Handler_read_rnd'] + @values['Handler_read_rnd_next'] + @values['Sort_rows']) / @values['Questions'], 'Total_rows_returned' => (@values['Handler_read_first'] + @values['Handler_read_key'] + @values['Handler_read_next'] + @values['Handler_read_prev'] + @values['Handler_read_rnd'] + @values['Handler_read_rnd_next'] + @values['Sort_rows']), 'Indexed_rows_returned' => (@values['Handler_read_first'] + @values['Handler_read_key'] + @values['Handler_read_next'] + @values['Handler_read_prev']), 'Sort_merge_passes' => @values['Sort_merge_passes'], 'Sort_range' => @values['Sort_range'], 'Sort_scan' => @values['Sort_scan'], 'Total_sort' => @values['Sort_range'] + @values['Sort_scan'], 'Joins_without_indexes' => @values['Select_range_check'] + @values['Select_full_join'], 'Joins_without_indexes_per_day' => ((@values['Select_range_check'] + @values['Select_full_join'])/days).round, 'Percent_full_table_scans' => percent((@values['Handler_read_rnd_next'] + @values['Handler_read_rnd']) / (@values['Handler_read_rnd_next'] + @values['Handler_read_rnd'] + @values['Handler_read_first'] + @values['Handler_read_next'] + @values['Handler_read_key'] + @values['Handler_read_prev'])), 'Percent_query_cache_fragmentation' => percent(@values['Qcache_free_blocks'] / @values['Qcache_total_blocks']), 'Percent_query_cache_hit_rate' => percent(@values['Qcache_hits'] / (@values['Qcache_inserts'] + @values['Qcache_hits'] > 0 ? @values['Qcache_inserts'] + @values['Qcache_hits'] : 1)), 'Percent_query_cache_pruned_from_inserts' => percent(@values['Qcache_lowmem_prunes'] / (@values['Qcache_inserts'] > 0 ? @values['Qcache_inserts'] : 1)), 'Percent_myisam_key_cache_in_use' => percent((1 - (@values['Key_blocks_unused'] / (@values['key_buffer_size'] / @values['key_cache_block_size'])))), 'Percent_myisam_key_cache_hit_rate' => pct2hi((1 - (@values['Key_reads'] / (@values['Key_read_requests'] > 0 ? @values['Key_read_requests'] : 1)))), 'Percent_myisam_key_cache_write_ratio' => percent(@values['Key_writes'] / (@values['Key_write_requests'] > 0 ? @values['Key_write_requests'] : 1)), 'Number_myisam_key_blocks' => @values['key_buffer_size'] / @values['key_cache_block_size'], 'Used_myisam_key_cache_blocks' => (@values['key_buffer_size'] / @values['key_cache_block_size']) - @values['Key_blocks_unused'], 'Key_read_requests' => @values['Key_read_requests'], 'Key_reads' => @values['Key_reads'], 'Key_write_requests' => @values['Key_write_requests'], 'Key_writes' => @values['Key_writes'], # Tables and Temp Tables stats 'Open_tables' => @values['Open_tables'], 'Opened_tables' => @values['Opened_tables'], 'Table_locks_immediate' => @values['Table_locks_immediate'], 'Table_locks_waited' => @values['Table_locks_waited'], 'Created_tmp_disk_tables' => @values['Created_tmp_disk_tables'], 'Created_tmp_tables' => @values['Created_tmp_tables'], 'Percent_table_cache_hit_rate' => @values['Opened_tables'] > 0 ? pct2hi(@values['Open_tables'] / @values['Opened_tables']) : 100, 'Percent_table_lock_contention' => ((@values['Table_locks_waited'] + @values['Table_locks_immediate']) > 0 ? percent(@values['Table_locks_waited'] / (@values['Table_locks_waited'] + @values['Table_locks_immediate'])) : 0), 'Percent_tmp_tables_on_disk' => ((@values['Created_tmp_disk_tables'] + @values['Created_tmp_tables']) > 0 ? percent(@values['Created_tmp_disk_tables'] / (@values['Created_tmp_disk_tables'] + @values['Created_tmp_tables'])) : 0), 'Percent_transactions_saved_tmp_file' => (@values['Binlog_cache_use'] == 0 ? 0 : percent(@values['Binlog_cache_disk_use'] / @values['Binlog_cache_use'])), 'Percent_tmp_sort_tables' => (@values['Sort_range'] + @values['Sort_scan'] > 0 ? percent(@values['Sort_merge_passes'] / (@values['Sort_range'] + @values['Sort_scan'])) : 0), 'Percent_files_open' => @values['open_files_limit'] > 0 ? percent(@values['Open_files'] / @values['open_files_limit']) : 0, # Clients, Threads, and Connections 'Aborted_clients' => @values['Aborted_clients'], 'Aborted_connects' => @values['Aborted_connects'], 'Connections' => @values['Connections'], 'Successful_connects' => @values['Connections'] - @values['Aborted_connects'], 'Max_used_connections' => @values['Max_used_connections'], 'Slow_launch_threads' => @values['Slow_launch_threads'], 'Threads_cached' => @values['Threads_cached'], 'Threads_connected' => @values['Threads_connected'], 'Threads_created' => @values['Threads_created'], 'Threads_created_rate' => @values['Threads_created'], 'Threads_running' => @values['Threads_running'], 'Percent_thread_cache_hit_rate' => pct2hi((1 - @values['Threads_created'] / @values['Connections'])), 'Percent_connections_used' => percent(@values['Threads_connected'] / @values['max_connections']), 'Percent_aborted_connections' => percent(@values['Aborted_connects'] / @values['Connections']), 'Percent_maximum_connections_ever' => percent(@values['Max_used_connections'] / @values['max_connections']), # Innodb stats 'Percent_innodb_log_size_vs_buffer_pool' => percent((@values['innodb_log_files_in_group'] * @values['innodb_log_file_size']) / @values['innodb_buffer_pool_size']), 'Percent_innodb_log_write_waits_required' => percent(@values['Innodb_log_waits'] / @values['Innodb_log_writes']), 'Percent_innodb_cache_hit_rate' => @values['Percent_innodb_cache_hit_rate'], 'Percent_innodb_cache_write_waits_required' => @values['Percent_innodb_cache_write_waits_required'], 'Innodb_log_file_size_total' => @values['innodb_log_files_in_group'] * @values['innodb_log_file_size'], } # Sometimes, replication isn't reported if not enabled. Test first before adding if @values['Master_Status_File'] && @values['Master_Status_File'].length > 0 # Replication information tosend.merge({ 'Master_Status_Position' => @values['Master_Status_Position'], 'Master_Status_File' => @values['Master_Status_File'], 'Master_Status_Binlog_Do_DB' => @values['Master_Status_Binlog_Do_DB'], 'Master_Status_Binlog_Ignore_DB' => @values['Master_Status_Binlog_Ignore_DB'], }) end if @values['Relay_Log_File'] && @values['Relay_Log_File'].length > 0 tosend.merge({ 'Master_Host' => @values['Master_Host'], 'Master_Log_File' => @values['Master_Log_File'], 'Master_Port' => @values['Master_Port'], 'Master_User' => @values['Master_User'], 'Read_Master_Log_Pos' => @values['Read_Master_Log_Pos'], 'Relay_Log_File' => @values['Relay_Log_File'], 'Relay_Log_Pos' => @values['Relay_Log_Pos'], 'Relay_Log_Space' => @values['Relay_Log_Space'], 'Relay_Master_Log_File' => @values['Relay_Master_Log_File'], 'Exec_Master_Log_Pos' => @values['Exec_Master_Log_Pos'], 'Slave_IO_Running' => @values['Slave_IO_Running'], 'Slave_IO_State' => @values['Slave_IO_State'], 'Slave_SQL_Running' => @values['Slave_SQL_Running'], 'Slave_running' => @values['Slave_IO_Running'] == "Yes" && @values['Slave_SQL_Running'] == "Yes" ? 1 : 0, 'Seconds_Behind_Master' => @values['Seconds_Behind_Master'], }) end end tosend.each do |key, val| zabbix_post(key, val) end puts 1 exit(0) # All STATUS variables: # Aborted_clients # Aborted_connects # Binlog_cache_disk_use # Binlog_cache_use # Bytes_received # Bytes_sent # Com_admin_commands # Com_alter_db # Com_alter_table # Com_analyze # Com_backup_table # Com_begin # Com_change_db # Com_change_master # Com_check # Com_checksum # Com_commit # Com_create_db # Com_create_function # Com_create_index # Com_create_table # Com_create_user # Com_dealloc_sql # Com_delete # Com_delete_multi # Com_do # Com_drop_db # Com_drop_function # Com_drop_index # Com_drop_table # Com_drop_user # Com_execute_sql # Com_flush # Com_grant # Com_ha_close # Com_ha_open # Com_help # Com_insert # Com_insert_select # Com_kill # Com_load # Com_load_master_data # Com_load_master_table # Com_lock_tables # Com_optimize # Com_preload_keys # Com_prepare_sql # Com_purge # Com_purge_before_date # Com_rename_table # Com_repair # Com_replace # Com_replace_select # Com_reset # Com_restore_table # Com_revoke # Com_revoke_all # Com_rollback # Com_savepoint # Com_select # Com_set_option # Com_show_binlog_events # Com_show_binlogs # Com_show_charsets # Com_show_collations # Com_show_column_types # Com_show_create_db # Com_show_create_table # Com_show_databases # Com_show_errors # Com_show_fields # Com_show_grants # Com_show_innodb_status # Com_show_keys # Com_show_logs # Com_show_master_status # Com_show_ndb_status # Com_show_new_master # Com_show_open_tables # Com_show_privileges # Com_show_processlist # Com_show_slave_hosts # Com_show_slave_status # Com_show_status # Com_show_storage_engines # Com_show_tables # Com_show_triggers # Com_show_variables # Com_show_warnings # Com_slave_start # Com_slave_stop # Com_stmt_close # Com_stmt_execute # Com_stmt_fetch # Com_stmt_prepare # Com_stmt_reset # Com_stmt_send_long_data # Com_truncate # Com_unlock_tables # Com_update # Com_update_multi # Com_xa_commit # Com_xa_end # Com_xa_prepare # Com_xa_recover # Com_xa_rollback # Com_xa_start # Compression # Connections # Created_tmp_disk_tables # Created_tmp_files # Created_tmp_tables # Delayed_errors # Delayed_insert_threads # Delayed_writes # Flush_commands # Handler_commit # Handler_delete # Handler_discover # Handler_prepare # Handler_read_first # Handler_read_key # Handler_read_next # Handler_read_prev # Handler_read_rnd # Handler_read_rnd_next # Handler_rollback # Handler_savepoint # Handler_savepoint_rollback # Handler_update # Handler_write # Innodb_buffer_pool_pages_data # Innodb_buffer_pool_pages_dirty # Innodb_buffer_pool_pages_flushed # Innodb_buffer_pool_pages_free # Innodb_buffer_pool_pages_misc # Innodb_buffer_pool_pages_total # Innodb_buffer_pool_read_ahead_rnd # Innodb_buffer_pool_read_ahead_seq # Innodb_buffer_pool_read_requests # Innodb_buffer_pool_reads # Innodb_buffer_pool_wait_free # Innodb_buffer_pool_write_requests # Innodb_data_fsyncs # Innodb_data_pending_fsyncs # Innodb_data_pending_reads # Innodb_data_pending_writes # Innodb_data_read # Innodb_data_reads # Innodb_data_writes # Innodb_data_written # Innodb_dblwr_pages_written # Innodb_dblwr_writes # Innodb_log_waits # Innodb_log_write_requests # Innodb_log_writes # Innodb_os_log_fsyncs # Innodb_os_log_pending_fsyncs # Innodb_os_log_pending_writes # Innodb_os_log_written # Innodb_page_size # Innodb_pages_created # Innodb_pages_read # Innodb_pages_written # Innodb_row_lock_current_waits # Innodb_row_lock_time # Innodb_row_lock_time_avg # Innodb_row_lock_time_max # Innodb_row_lock_waits # Innodb_rows_deleted # Innodb_rows_inserted # Innodb_rows_read # Innodb_rows_updated # Key_blocks_not_flushed # Key_blocks_unused # Key_blocks_used # Key_read_requests # Key_reads # Key_write_requests # Key_writes # Last_query_cost # Max_used_connections # Ndb_cluster_node_id # Ndb_config_from_host # Ndb_config_from_port # Ndb_number_of_data_nodes # 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 # Rpl_status # Select_full_join # Select_full_range_join # Select_range # Select_range_check # Select_scan # Slave_open_temp_tables # Slave_retried_transactions # Slave_running # Slow_launch_threads # Slow_queries # Sort_merge_passes # Sort_range # Sort_rows # Sort_scan # Ssl_accept_renegotiates # Ssl_accepts # Ssl_callback_cache_hits # Ssl_cipher # Ssl_cipher_list # Ssl_client_connects # Ssl_connect_renegotiates # Ssl_ctx_verify_depth # Ssl_ctx_verify_mode # Ssl_default_timeout # Ssl_finished_accepts # Ssl_finished_connects # Ssl_session_cache_hits # Ssl_session_cache_misses # Ssl_session_cache_mode # Ssl_session_cache_overflows # Ssl_session_cache_size # Ssl_session_cache_timeouts # Ssl_sessions_reused # Ssl_used_session_cache_entries # Ssl_verify_depth # Ssl_verify_mode # Ssl_version # Table_locks_immediate # Table_locks_waited # Tc_log_max_pages_used # Tc_log_page_size # Tc_log_page_waits # Threads_cached # Threads_connected # Threads_created # Threads_running # Uptime