Application monitor: mysql

[Juan Carlos]

Hi, regards from Barcelona!!

I want to share my script to intensive monitor mysql servers:

/etc/zabbix/zabbix_agentd.conf

(see attach zabbix_agentd.conf.txt)

source code for /usr/local/ackstorm/zabbix_agentd/ack_zabbix_mysql.pl :

#!/usr/bin/perl

my($mysql)=`which mysql`;
my($username)="username_with_process_privileges";
my($password)="passwrod";
my($server)="localhost";
my($line,$var);

chomp($mysql);
my($mysql_command)="$mysql -u $username --password=$password -h $server --execute='SHOW STATUS' --skip-column-names";
my(@result)=`$mysql_command 2>/dev/null`;
my(@status_vars)=("Aborted_clients","Aborted_conne cts","Binlog_cache_disk_use","Binlog_cache_use","B ytes_received","Bytes_sent","Com_admin_commands",
"Com_alter_db","Com_alter_table","Com_analyze","Co m_backup_table","Com_begin","Com_change_db","Com_c hange_master","Com_check","Com_checksum",
"Com_commit","Com_create_db","Com_create_function" ,"Com_create_index","Com_create_table","Com_deallo c_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_ha_read","Com_help","Com_insert ","Com_insert_select","Com_kill","Com_load","Com_l oad_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","Co m_restore_table","Com_revoke","Com_revoke_all","Co m_rollback","Com_savepoint","Com_select",
"Com_set_option","Com_show_binlog_events","Com_sho w_binlogs","Com_show_charsets","Com_show_collation s","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_st orage_engines","Com_show_tables",
"Com_show_variables","Com_show_warnings","Com_slav e_start","Com_slave_stop","Com_stmt_close","Com_st mt_execute","Com_stmt_prepare",
"Com_stmt_reset","Com_stmt_send_long_data","Com_tr uncate","Com_unlock_tables","Com_update","Com_upda te_multi","Connections",
"Created_tmp_disk_tables","Created_tmp_files","Cre ated_tmp_tables","Delayed_errors","Delayed_insert_ threads","Delayed_writes","Flush_commands",
"Handler_commit","Handler_delete","Handler_discove r","Handler_read_first","Handler_read_key","Handle r_read_next","Handler_read_prev",
"Handler_read_rnd","Handler_read_rnd_next","Handle r_rollback","Handler_update","Handler_write","Key_ blocks_not_flushed","Key_blocks_unused",
"Key_blocks_used","Key_read_requests","Key_reads", "Key_write_requests","Key_writes","Max_used_connec tions","Not_flushed_delayed_rows",
"Open_files","Open_streams","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","Question s","Rpl_status","Select_full_join",
"Select_full_range_join","Select_range","Select_ra nge_check","Select_scan","Slave_open_temp_tables", "Slave_retried_transactions",
"Slave_running","Slow_launch_threads","Slow_querie s","Sort_merge_passes","Sort_range","Sort_rows","S ort_scan","Table_locks_immediate",
"Table_locks_waited","Threads_cached","Threads_con nected","Threads_created","Threads_running","Uptim e");

exit(1) unless(@result);

foreach $line (@result) {
chomp($line);
foreach $var (@status_vars) {
if ($line =~ /$var[\t|\ ]+(.+)/) {
$$var=$1;
last;
}
}
}

foreach $var (@status_vars) {
print "$$var\:";
}
print"\n";
exit(0);

Hope that helps!

[Juan Carlos]

New version of "ack_zabbix_mysql.pl", 6 times faster.

before:
execution time: 0m0.174s (2 CPU XEON 3.0 with 8 GB RAM)

now:
execution time: 0m0.028s (2 CPU XEON 3.0 with 8 GB RAM)

Regads
Juan Carlos Moreno

http://www.ackstorm.es

[Juan Carlos]

don't use the last one, use this.

Juan Carlos Moreno

http://www.ackstorm.es

[stelios]

Would you please post instructions how to use this script in order to get stats into my hosts for example.

Thanks

[bbrendon]

you should use some userparam variables and simplify this.

[noxis]

How is this different to "show status" or mysqladmin extended-status?

[nelsonab]

you should use some userparam variables and simplify this.

I agree, it would make your config file much easier to read.

You could pass the variable you're looking for as a command line arg and then do something like "print $$argument" in the script. Your user parameter would then look something like this "mysql.com[*],script.pl $1". You'll also find maintenance *much* easier. :-)