def __init__(self, filename): self.mysqldb = MySQL_Interface(db_mysql_user, db_mysql_password, db_mysql_hostname, 'mysql', db_mysql_socket) self.wcache = WriteCache(host_name, filename)
class MySQLCollector(object): ''' classdocs ''' mysqldb = None wcache = None datadir = None def __init__(self, filename): self.mysqldb = MySQL_Interface(db_mysql_user, db_mysql_password, db_mysql_hostname, 'mysql', db_mysql_socket) self.wcache = WriteCache(host_name, filename) def disconnect(self): self.mysqldb.disconnect() def write_key(self, key, value, ts=str(int(time.time()))): log.debug("get: %s" % key) self.wcache.toWrite(key, value, ts) def collect_show_status(self, priority): ts = str(int(time.time())) ''' show status ''' status = self.mysqldb.get_show_status() #http://dev.mysql.com/doc/refman/5.1/en/server-status-variables.html#statvar_Com_xxx self.write_key("db.mysql.ping", self.mysqldb.get_mysql_ping(), ts) if bool(self.mysqldb.get_mysql_ping()): if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: self.write_key("db.mysql.show_status.network.sent", status.bytes_sent, ts) self.write_key("db.mysql.show_status.network.received", status.bytes_received, ts) self.write_key("db.mysql.show_status.max_used_connections", status.max_used_connections, ts) self.write_key("db.mysql.show_status.aborted_clients", status.aborted_clients, ts) self.write_key("db.mysql.show_status.number_select", status.com_select, ts) self.write_key("db.mysql.show_status.number_select_qcache", status.qcache_hits, ts) self.write_key("db.mysql.show_status.number_querys_cached", status.qcache_inserts, ts) self.write_key("db.mysql.show_status.number_insert", status.com_insert, ts) self.write_key("db.mysql.show_status.number_insert_select", status.com_insert_select, ts) self.write_key("db.mysql.show_status.number_update", status.com_update, ts) self.write_key("db.mysql.show_status.number_update_multi", status.com_update_multi, ts) self.write_key("db.mysql.show_status.number_delete", status.com_delete, ts) self.write_key("db.mysql.show_status.number_delete_multi", status.com_delete, ts) self.write_key("db.mysql.show_status.number_replace", status.com_replace, ts) self.write_key("db.mysql.show_status.number_replace_select", status.com_replace_select, ts) self.write_key("db.mysql.show_status.number_rollback", status.com_rollback, ts) self.write_key("db.mysql.show_status.number_call_procedure", status.com_call_procedure, ts) self.write_key("db.mysql.show_status.connected_user", status.threads_connected, ts) self.write_key("db.mysql.show_status.connected_user_running", status.threads_running, ts) self.write_key("db.mysql.show_status.aborted_connects", status.aborted_connects, ts) self.write_key("db.mysql.show_status.created_tmp_files", status.created_tmp_files, ts) self.write_key("db.mysql.show_status.created_tmp_disk_tables", status.created_tmp_disk_tables, ts) self.write_key("db.mysql.show_status.created_tmp_tables", status.created_tmp_tables, ts) self.write_key("db.mysql.show_status.innodb_rows_inserted", status.innodb_rows_inserted, ts) self.write_key("db.mysql.show_status.innodb_rows_deleted", status.innodb_rows_deleted, ts) self.write_key("db.mysql.show_status.innodb_rows_updated", status.innodb_rows_updated, ts) self.write_key("db.mysql.show_status.innodb_rows_read", status.innodb_rows_read, ts) ''' ------- INNODB STATUS (show status) ------ ''' self.write_key("db.mysql.show_status.innodb_data_writes", status.innodb_data_writes, ts) self.write_key("db.mysql.show_status.innodb_data_written", status.innodb_data_written, ts) self.write_key("db.mysql.show_status.innodb_data_reads", status.innodb_data_reads, ts) self.write_key("db.mysql.show_status.innodb_data_read", status.innodb_data_read, ts) self.write_key("db.mysql.show_status.innodb_data_read", status.innodb_data_read, ts) self.write_key("db.mysql.show_status.innodb_page_size", status.innodb_page_size, ts) def collect_show_variables(self, priority=1): ts = str(int(time.time())) if bool(self.mysqldb.get_mysql_ping()): ''' Show Variables Status ''' status = self.mysqldb.get_show_variables() self.datadir = status.datadir if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: self.write_key("db.mysql.show_variables.cfg_max_connections", status.max_connections, ts) self.write_key("db.mysql.show_variables.version", status.version, ts) self.write_key("db.mysql.show_variables.datadir", self.datadir, ts) def collect_mysql_memory_usage(self, priority=1): ts = str(int(time.time())) if bool(self.mysqldb.get_mysql_ping()): ''' Show Variables Status ''' if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: status = self.mysqldb.get_mysql_mem_usage() self.write_key("db.mysql.mem_usage", status.mysql_usage, ts) self.write_key("db.mysql.mem_usage_free_size", status.usage_ram_free_size, ts) self.write_key("db.mysql.mem_usage_percent", status.usagem_ram_percent, ts) # status2 try: status2 = self.mysqldb.get_mysql_memory_report() self.write_key("db.mysql.os_vm_usage", status2.vmsize, ts) self.write_key("db.mysql.os_vm_resident", status2.vmrss, ts) self.write_key("db.mysql.os_vm_swap_usage", status2.vmswap, ts) except: pass def collect_size_status(self, priority=1): ''' mysql_version = self.mysqldb.get_mysql_version() priority_select = None if mysql_version < (5,5,0): priority_select = CONS_COLLECT_FOR["INVOKED"] else: priority_select = CONS_COLLECT_FOR["CONTINUOUS"] ''' ts = str(int(time.time())) get_os = MetaOS() if bool(self.mysqldb.get_mysql_ping()): # Others Status # MySQL 5.1.x is very very slower to get SELECT sum(.... from information_schema.tables ''' mysql_version = self.mysqldb.get_mysql_version() priority_select = None if mysql_version < (5,5,0): priority_select = CONS_COLLECT_FOR["INVOKED"] else: priority_select = CONS_COLLECT_FOR["CONTINUOUS"] ''' total_data = 0 number_database = 0 file_system_used = 0 if priority == CONS_COLLECT_FOR["INVOKED"]: try: status = self.mysqldb.get_databases_status() total_data = status.total_data number_database = status.number_database except: pass self.write_key("db.mysql.total_size", total_data, ts) self.write_key("db.mysql.number_database", number_database, ts) ''' get file usage on Operational System viwer ''' if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: try: file_system_used = get_os.get_folder_size(self.datadir) except: pass self.write_key("db.mysql.filesystem_used", file_system_used, ts) def collect_slave_status(self, priority=2): ts = str(int(time.time())) if bool(self.mysqldb.get_mysql_ping()): if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: try: status = self.mysqldb.get_show_slave_status() self.write_key("db.mysql.slave_lagging", status.seconds_behind_master, ts) self.write_key("db.mysql.slave_io_thread", convert_bool_to_int(status.slave_io_running), ts) self.write_key("db.mysql.slave_sql_thread", convert_bool_to_int(status.slave_sql_running), ts) self.write_key("db.mysql.slave_error_number", status.last_errno, ts) self.write_key("db.mysql.slave_error", str(status.last_error).replace("", " "), ts) self.write_key("db.mysql.slave_master_log_file", str(status.master_log_file), ts) self.write_key("db.mysql.slave_read_master_log_pos", str(status.read_master_log_pos), ts) self.write_key("db.mysql.slave_relay_log_file", str(status.relay_log_file), ts) self.write_key("db.mysql.slave_relay_log_pos", str(status.relay_log_pos), ts) self.write_key("db.mysql.slave_relay_master_log_file", str(status.relay_master_log_file), ts) # Log Lagging master_log_file = long(str(status.master_log_file).split('.')[1]) relay_master_log_file = long(str(status.relay_master_log_file).split('.')[1]) log_laggin = master_log_file - relay_master_log_file self.write_key("db.mysql.slave_relay_log_lagging", log_laggin, ts) except: pass def collector_master_status(self, priority=1): ts = str(int(time.time())) if bool(self.mysqldb.get_mysql_ping()): if priority == CONS_COLLECT_FOR["INVOKED"] or priority == CONS_COLLECT_FOR["BOOTH"]: status = self.mysqldb.get_show_master_status() try: self.write_key("db.mysql.master_file", status.file, ts) except: self.write_key("db.mysql.master_file", 'None', ts) try: self.write_key("db.mysql.master_position", status.position, ts) except: self.write_key("db.mysql.master_position", 'None', ts) def collect_show_innodb_status(self, priority=1): ts = str(int(time.time())) if bool(self.mysqldb.get_mysql_ping()): if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: status = self.mysqldb.get_show_innodb_status() try: self.write_key("db.mysql.innodb_srv_master_thread_log", status.innodb_srv_master_thread_log, ts) except: pass try: self.write_key("db.mysql.innodb_os_wait_reservation_count", status.innodb_os_wait_reservation_count, ts) except: pass try: self.write_key("db.mysql.innodb_os_wait_signal_count", status.innodb_os_wait_signal_count, ts) except: pass try: self.write_key("db.mysql.innodb_mutex_spin_waits", status.innodb_mutex_spin_waits, ts) except: pass try: self.write_key("db.mysql.innodb_mutex_spin_rounds", status.innodb_mutex_spin_rounds, ts) except: pass try: self.write_key("db.mysql.innodb_mutex_spin_os_waits", status.innodb_mutex_spin_os_waits, ts) except: pass try: self.write_key("db.mysql.innodb_rw_shared_spins", status.innodb_rw_shared_spins, ts) except: pass try: self.write_key("db.mysql.innodb_rw_shared_os_waits", status.innodb_rw_shared_os_waits, ts) except: pass try: self.write_key("db.mysql.innodb_rw_excl_spins", status.innodb_rw_excl_spins, ts) except: pass try: self.write_key("db.mysql.innodb_rw_excl_os_waits", status.innodb_rw_excl_os_waits, ts) except: pass try: self.write_key("db.mysql.innodb_spin_rounds_per_wait_mutex", status.innodb_spin_rounds_per_wait_mutex, ts) except: pass try: self.write_key("db.mysql.innodb_spin_rounds_per_wait_rw_shared", status.innodb_spin_rounds_per_wait_rw_shared, ts) except: pass try: self.write_key("db.mysql.innodb_spin_rounds_per_wait_rw_excl", status.innodb_spin_rounds_per_wait_rw_excl, ts) except: pass try: self.write_key("db.mysql.innodb_lastest_deadlock", status.innodb_lastest_deadlock, ts) except: pass try: self.write_key("db.mysql.innodb_file_read", status.innodb_file_read, ts) except: pass try: self.write_key("db.mysql.innodb_file_write", status.innodb_file_write, ts) except: pass try: self.write_key("db.mysql.innodb_file_fsync", status.innodb_file_fsync, ts) except: pass try: self.write_key("db.mysql.innodb_iops_read", status.innodb_iops_read, ts) except: pass try: self.write_key("db.mysql.innodb_read_bytes_seconds", status.innodb_read_bytes_seconds, ts) except: pass try: self.write_key("db.mysql.innodb_ios_write", status.innodb_ios_write, ts) except: pass try: self.write_key("db.mysql.innodb_fsyncs_seconds", status.innodb_fsyncs_seconds, ts) except: pass try: self.write_key("db.mysql.innodb_insert_buffer_size", status.innodb_insert_buffer_size, ts) except: pass try: self.write_key("db.mysql.innodb_insert_buffer_number", status.innodb_insert_buffer_number, ts) except: pass try: self.write_key("db.mysql.innodb_insert_buffer_merged", status.innodb_insert_buffer_merged, ts) except: pass try: self.write_key("db.mysql.innodb_insert_buffer_merges", status.innodb_insert_buffer_merges, ts) except: pass try: self.write_key("db.mysql.innodb_insert_hash_searches_seconds", status.innodb_insert_hash_searches_seconds, ts) except: pass try: self.write_key("db.mysql.innodb_insert_non_hash_searches_seconds", status.innodb_insert_non_hash_searches_seconds, ts) except: pass try: self.write_key("db.mysql.innodb_log_sequence_number", status.innodb_log_sequence_number, ts) except: pass try: self.write_key("db.mysql.innodb_log_sequence_number", status.innodb_log_sequence_number, ts) except: pass try: self.write_key("db.mysql.innodb_log_iops", status.innodb_log_iops, ts) except: pass try: self.write_key("db.mysql.innodb_total_memory_allocated", status.innodb_total_memory_allocated, ts) except: pass try: self.write_key("db.mysql.innodb_additional_pool_allocated", status.innodb_additional_pool_allocated, ts) except: pass try: self.write_key("db.mysql.innodb_dictinoary_memory_allocated", status.innodb_dictinoary_memory_allocated, ts) except: pass try: buffer_pool = status.innodb_page_buffer_pool_size self.write_key("db.mysql.innodb_page_buffer_pool_size", buffer_pool, ts) except: pass try: self.write_key("db.mysql.innodb_free_buffers", status.innodb_free_buffers, ts) except: pass try: self.write_key("db.mysql.innodb_database_pages", status.innodb_database_pages, ts) except: pass try: self.write_key("db.mysql.innodb_old_database_pages", status.innodb_old_database_pages, ts) except: pass try: self.write_key("db.mysql.innodb_modified_db_pages", status.innodb_modified_db_pages, ts) except: pass try: self.write_key("db.mysql.innodb_pending_reads", status.innodb_pending_reads, ts) except: pass try: self.write_key("db.mysql.innodb_pending_write_lru", status.innodb_pending_write_lru, ts) except: pass try: self.write_key("db.mysql.innodb_pending_write_flush_list", status.innodb_pending_write_flush_list, ts) except: pass try: self.write_key("db.mysql.innodb_pending_write_single_page", status.innodb_pending_write_single_page, ts) except: pass try: self.write_key("db.mysql.innodb_pages_made_young", status.innodb_pages_made_young, ts) except: pass try: self.write_key("db.mysql.innodb_pages_made_not_young", status.innodb_pages_made_not_young, ts) except: pass try: self.write_key("db.mysql.innodb_pages_young_seconds", status.innodb_pages_young_seconds, ts) except: pass try: self.write_key("db.mysql.innodb_pages_non_young_seconds", status.innodb_pages_non_young_seconds, ts) except: pass try: self.write_key("db.mysql.innodb_pages_read", status.innodb_pages_read, ts) except: pass try: self.write_key("db.mysql.innodb_pages_created", status.innodb_pages_created, ts) except: pass try: self.write_key("db.mysql.innodb_pages_written", status.innodb_pages_written, ts) except: pass try: self.write_key("db.mysql.innodb_pages_read_seconds", status.innodb_pages_read_seconds, ts) except: pass try: self.write_key("db.mysql.innodb_pages_created_seconds", status.innodb_pages_created_seconds, ts) except: pass try: self.write_key("db.mysql.innodb_pages_written_seconds", status.innodb_pages_written_seconds, ts) except: pass #print status def collect_qps(self, priority=1): ts = str(int(time.time())) if bool(self.mysqldb.get_mysql_ping()): status = self.mysqldb.get_gpq() if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: self.write_key("db.mysql.show_qps", status.qps, ts) def collect_mysql_tcp_stats(self, priority=1): ts = str(int(time.time())) if bool(self.mysqldb.get_mysql_ping()): status = self.mysqldb.get_mysql_tcp_state() if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: self.write_key("db.mysql.tcp_timewait", status.time_wait, ts) self.write_key("db.mysql.tcp_established", status.established, ts) def collect_time(self, priority=1): ts = str(int(time.time())) if bool(self.mysqldb.get_mysql_ping()): status = self.mysqldb.get_now() if priority == CONS_COLLECT_FOR["CONTINUOUS"] or priority == CONS_COLLECT_FOR["BOOTH"]: try: self.write_key("db.mysql.time_now", fix_time_now(status.now), ts) self.write_key("db.mysql.timezone", status.timezone, ts) self.write_key("db.mysql.time_unixtime", convert_now2unixtime(status.now), ts) mysql_time = int(convert_now2unixtime(status.now)) except: mysql_time = 0 try: if mysql_time is not None and mysql_time > 0: ntp_time = get_unixtime_ntp_server(ntp_server) time_error = ntp_time - (mysql_time + 3) if time_error > int(ntp_grace_time * -1) and time_error < int( ntp_grace_time * 1): # Time grace - Delay between get now() and get ntp date time_error = 0 if time_error is not None: self.write_key("db.mysql.time_unixtime_ntp_time", time_error, ts) except: pass def collect_all(self, priority): self.collect_show_innodb_status(priority) self.collect_show_status(priority) self.collect_show_variables(priority) self.collect_size_status(priority) self.collect_slave_status(priority) self.collector_master_status(priority) self.collect_time(priority) self.disconnect()