Beispiel #1
0
 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)
Beispiel #2
0
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()