X-Git-Url: https://arthur.barton.de/gitweb/?a=blobdiff_plain;f=python.d%2Fmysql.chart.py;h=0e3a032998c2535f10a0d46a08408f0519254f09;hb=a00c8217dca7180462a470c1a3a5d883e6069af4;hp=4cd9b6d413370bf7b0b833d5e1c90305a02e2845;hpb=d1786059c9947bdbe522b18dc3a53c64baac7ee8;p=netdata.git diff --git a/python.d/mysql.chart.py b/python.d/mysql.chart.py index 4cd9b6d4..0e3a0329 100644 --- a/python.d/mysql.chart.py +++ b/python.d/mysql.chart.py @@ -1,13 +1,10 @@ # -*- coding: utf-8 -*- -# Description: MySQL netdata python.d plugin +# Description: MySQL netdata python.d module # Author: Pawel Krupa (paulfantom) -import os from base import SimpleService import msg -NAME = os.path.basename(__file__).replace(".chart.py", "") - # import 3rd party library to handle MySQL communication try: import MySQLdb @@ -25,24 +22,25 @@ except ImportError: raise ImportError # default module values (can be overridden per job in `config`) -update_every = 3 +# update_every = 3 priority = 90000 -retries = 7 +retries = 60 # default configuration (overridden by python.d.plugin) -config = { - 'local': { - 'user': 'root', - 'password': '', - 'socket': '/var/run/mysqld/mysqld.sock', - 'update_every': update_every, - 'retries': retries, - 'priority': priority - } -} +# config = { +# 'local': { +# 'user': 'root', +# 'pass': '', +# 'socket': '/var/run/mysqld/mysqld.sock', +# 'update_every': update_every, +# 'retries': retries, +# 'priority': priority +# } +#} # query executed on MySQL server -QUERY = "SHOW GLOBAL STATUS" +QUERY = "SHOW GLOBAL STATUS;" +QUERY_SLAVE = "SHOW SLAVE STATUS;" ORDER = ['net', 'queries', @@ -58,7 +56,7 @@ ORDER = ['net', 'innodb_buffer_pool_read_ahead', 'innodb_buffer_pool_reqs', 'innodb_buffer_pool_ops', 'qcache_ops', 'qcache', 'qcache_freemem', 'qcache_memblocks', 'key_blocks', 'key_requests', 'key_disk_ops', - 'files', 'files_rate'] + 'files', 'files_rate', 'slave_behind', 'slave_status'] CHARTS = { 'net': { @@ -70,81 +68,81 @@ CHARTS = { 'queries': { 'options': [None, 'mysql Queries', 'queries/s', 'queries', 'mysql.queries', 'line'], 'lines': [ - ["Queries", "queries"], - ["Questions", "questions"], - ["Slow_queries", "slow_queries"] + ["Queries", "queries", "incremental"], + ["Questions", "questions", "incremental"], + ["Slow_queries", "slow_queries", "incremental"] ]}, 'handlers': { 'options': [None, 'mysql Handlers', 'handlers/s', 'handlers', 'mysql.handlers', 'line'], 'lines': [ - ["Handler_commit", "commit"], - ["Handler_delete", "delete"], - ["Handler_prepare", "prepare"], - ["Handler_read_first", "read_first"], - ["Handler_read_key", "read_key"], - ["Handler_read_next", "read_next"], - ["Handler_read_prev", "read_prev"], - ["Handler_read_rnd", "read_rnd"], - ["Handler_read_rnd_next", "read_rnd_next"], - ["Handler_rollback", "rollback"], - ["Handler_savepoint", "savepoint"], - ["Handler_savepoint_rollback", "savepoint_rollback"], - ["Handler_update", "update"], - ["Handler_write", "write"] + ["Handler_commit", "commit", "incremental"], + ["Handler_delete", "delete", "incremental"], + ["Handler_prepare", "prepare", "incremental"], + ["Handler_read_first", "read_first", "incremental"], + ["Handler_read_key", "read_key", "incremental"], + ["Handler_read_next", "read_next", "incremental"], + ["Handler_read_prev", "read_prev", "incremental"], + ["Handler_read_rnd", "read_rnd", "incremental"], + ["Handler_read_rnd_next", "read_rnd_next", "incremental"], + ["Handler_rollback", "rollback", "incremental"], + ["Handler_savepoint", "savepoint", "incremental"], + ["Handler_savepoint_rollback", "savepoint_rollback", "incremental"], + ["Handler_update", "update", "incremental"], + ["Handler_write", "write", "incremental"] ]}, 'table_locks': { 'options': [None, 'mysql Tables Locks', 'locks/s', 'locks', 'mysql.table_locks', 'line'], 'lines': [ - ["Table_locks_immediate", "immediate"], + ["Table_locks_immediate", "immediate", "incremental"], ["Table_locks_waited", "waited", "incremental", -1, 1] ]}, 'join_issues': { 'options': [None, 'mysql Select Join Issues', 'joins/s', 'issues', 'mysql.join_issues', 'line'], 'lines': [ - ["Select_full_join", "full_join"], - ["Select_full_range_join", "full_range_join"], - ["Select_range", "range"], - ["Select_range_check", "range_check"], - ["Select_scan", "scan"] + ["Select_full_join", "full_join", "incremental"], + ["Select_full_range_join", "full_range_join", "incremental"], + ["Select_range", "range", "incremental"], + ["Select_range_check", "range_check", "incremental"], + ["Select_scan", "scan", "incremental"] ]}, 'sort_issues': { - 'options': [None, 'mysql Sort Issues', 'issues/s', 'issues', 'mysql.sort.issues', 'line'], + 'options': [None, 'mysql Sort Issues', 'issues/s', 'issues', 'mysql.sort_issues', 'line'], 'lines': [ - ["Sort_merge_passes", "merge_passes"], - ["Sort_range", "range"], - ["Sort_scan", "scan"] + ["Sort_merge_passes", "merge_passes", "incremental"], + ["Sort_range", "range", "incremental"], + ["Sort_scan", "scan", "incremental"] ]}, 'tmp': { 'options': [None, 'mysql Tmp Operations', 'counter', 'temporaries', 'mysql.tmp', 'line'], 'lines': [ - ["Created_tmp_disk_tables", "disk_tables"], - ["Created_tmp_files", "files"], - ["Created_tmp_tables", "tables"] + ["Created_tmp_disk_tables", "disk_tables", "incremental"], + ["Created_tmp_files", "files", "incremental"], + ["Created_tmp_tables", "tables", "incremental"] ]}, 'connections': { 'options': [None, 'mysql Connections', 'connections/s', 'connections', 'mysql.connections', 'line'], 'lines': [ - ["Connections", "all"], - ["Aborted_connects", "aborted"] + ["Connections", "all", "incremental"], + ["Aborted_connects", "aborted", "incremental"] ]}, 'binlog_cache': { 'options': [None, 'mysql Binlog Cache', 'transactions/s', 'binlog', 'mysql.binlog_cache', 'line'], 'lines': [ - ["Binlog_cache_disk_use", "disk"], - ["Binlog_cache_use", "all"] + ["Binlog_cache_disk_use", "disk", "incremental"], + ["Binlog_cache_use", "all", "incremental"] ]}, 'threads': { 'options': [None, 'mysql Threads', 'threads', 'threads', 'mysql.threads', 'line'], 'lines': [ ["Threads_connected", "connected", "absolute"], - ["Threads_created", "created"], + ["Threads_created", "created", "incremental"], ["Threads_cached", "cached", "absolute", -1, 1], ["Threads_running", "running", "absolute"], ]}, 'thread_cache_misses': { 'options': [None, 'mysql Threads Cache Misses', 'misses', 'threads', 'mysql.thread_cache_misses', 'area'], 'lines': [ - ["Thread_cache_misses", "misses", "misses", "absolute", 1, 100] + ["Thread_cache_misses", "misses", "absolute", 1, 100] ]}, 'innodb_io': { 'options': [None, 'mysql InnoDB I/O Bandwidth', 'kilobytes/s', 'innodb', 'mysql.innodb_io', 'area'], @@ -153,30 +151,30 @@ CHARTS = { ["Innodb_data_written", "write", "incremental", -1, 1024] ]}, 'innodb_io_ops': { - 'lines': [None, 'mysql InnoDB I/O Operations', 'operations/s', 'innodb', 'mysql.innodb_io_ops', 'line'], - 'options': [ - ["Innodb_data_reads", "reads"], + 'options': [None, 'mysql InnoDB I/O Operations', 'operations/s', 'innodb', 'mysql.innodb_io_ops', 'line'], + 'lines': [ + ["Innodb_data_reads", "reads", "incremental"], ["Innodb_data_writes", "writes", "incremental", -1, 1], - ["Innodb_data_fsyncs", "fsyncs"] + ["Innodb_data_fsyncs", "fsyncs", "incremental"] ]}, 'innodb_io_pending_ops': { 'options': [None, 'mysql InnoDB Pending I/O Operations', 'operations', 'innodb', 'mysql.innodb_io_pending_ops', 'line'], 'lines': [ ["Innodb_data_pending_reads", "reads", "absolute"], ["Innodb_data_pending_writes", "writes", "absolute", -1, 1], - ["Innodb_data_pending_fsyncs", "fsyncs"] + ["Innodb_data_pending_fsyncs", "fsyncs", "absolute"] ]}, 'innodb_log': { 'options': [None, 'mysql InnoDB Log Operations', 'operations/s', 'innodb', 'mysql.innodb_log', 'line'], 'lines': [ - ["Innodb_log_waits", "waits"], + ["Innodb_log_waits", "waits", "incremental"], ["Innodb_log_write_requests", "write_requests", "incremental", -1, 1], - ["Innodb_log_writes", "incremental", -1, 1], + ["Innodb_log_writes", "writes", "incremental", -1, 1], ]}, 'innodb_os_log': { 'options': [None, 'mysql InnoDB OS Log Operations', 'operations', 'innodb', 'mysql.innodb_os_log', 'line'], 'lines': [ - ["Innodb_os_log_fsyncs", "fsyncs"], + ["Innodb_os_log_fsyncs", "fsyncs", "incremental"], ["Innodb_os_log_pending_fsyncs", "pending_fsyncs", "absolute"], ["Innodb_os_log_pending_writes", "pending_writes", "absolute", -1, 1], ]}, @@ -193,7 +191,7 @@ CHARTS = { 'innodb_rows': { 'options': [None, 'mysql InnoDB Row Operations', 'operations/s', 'innodb', 'mysql.innodb_rows', 'area'], 'lines': [ - ["Innodb_rows_inserted", "read"], + ["Innodb_rows_inserted", "read", "incremental"], ["Innodb_rows_read", "deleted", "incremental", -1, 1], ["Innodb_rows_updated", "inserted", "incremental", 1, 1], ["Innodb_rows_deleted", "updated", "incremental", -1, 1], @@ -211,34 +209,34 @@ CHARTS = { 'innodb_buffer_pool_bytes': { 'options': [None, 'mysql InnoDB Buffer Pool Bytes', 'MB', 'innodb', 'mysql.innodb_buffer_pool_bytes', 'area'], 'lines': [ - ["Innodb_buffer_pool_bytes_data", "data", "absolute"], - ["Innodb_buffer_pool_bytes_dirty", "dirty", "absolute", -1, 1] + ["Innodb_buffer_pool_bytes_data", "data", "absolute", 1, 1024 * 1024], + ["Innodb_buffer_pool_bytes_dirty", "dirty", "absolute", -1, 1024 * 1024] ]}, 'innodb_buffer_pool_read_ahead': { 'options': [None, 'mysql InnoDB Buffer Pool Read Ahead', 'operations/s', 'innodb', 'mysql.innodb_buffer_pool_read_ahead', 'area'], 'lines': [ - ["Innodb_buffer_pool_read_ahead", "all"], + ["Innodb_buffer_pool_read_ahead", "all", "incremental"], ["Innodb_buffer_pool_read_ahead_evicted", "evicted", "incremental", -1, 1], - ["Innodb_buffer_pool_read_ahead_rnd", "random"] + ["Innodb_buffer_pool_read_ahead_rnd", "random", "incremental"] ]}, 'innodb_buffer_pool_reqs': { 'options': [None, 'mysql InnoDB Buffer Pool Requests', 'requests/s', 'innodb', 'mysql.innodb_buffer_pool_reqs', 'area'], 'lines': [ - ["Innodb_buffer_pool_read_requests", "reads"], + ["Innodb_buffer_pool_read_requests", "reads", "incremental"], ["Innodb_buffer_pool_write_requests", "writes", "incremental", -1, 1] ]}, 'innodb_buffer_pool_ops': { 'options': [None, 'mysql InnoDB Buffer Pool Operations', 'operations/s', 'innodb', 'mysql.innodb_buffer_pool_ops', 'area'], 'lines': [ - ["Innodb_buffer_pool_reads", "disk reads"], + ["Innodb_buffer_pool_reads", "disk reads", "incremental"], ["Innodb_buffer_pool_wait_free", "wait free", "incremental", -1, 1] ]}, 'qcache_ops': { 'options': [None, 'mysql QCache Operations', 'queries/s', 'qcache', 'mysql.qcache_ops', 'line'], 'lines': [ - ["Qcache_hits", "hits"], + ["Qcache_hits", "hits", "incremental"], ["Qcache_lowmem_prunes", "lowmem prunes", "incremental", -1, 1], - ["Qcache_inserts", "inserts"], + ["Qcache_inserts", "inserts", "incremental"], ["Qcache_not_cached", "not cached", "incremental", -1, 1] ]}, 'qcache': { @@ -249,7 +247,7 @@ CHARTS = { 'qcache_freemem': { 'options': [None, 'mysql QCache Free Memory', 'MB', 'qcache', 'mysql.qcache_freemem', 'area'], 'lines': [ - ["Qcache_free_memory", "free", "absolute"] + ["Qcache_free_memory", "free", "absolute", 1, 1024 * 1024] ]}, 'qcache_memblocks': { 'options': [None, 'mysql QCache Memory Blocks', 'blocks', 'qcache', 'mysql.qcache_memblocks', 'line'], @@ -267,13 +265,13 @@ CHARTS = { 'key_requests': { 'options': [None, 'mysql MyISAM Key Cache Requests', 'requests/s', 'myisam', 'mysql.key_requests', 'area'], 'lines': [ - ["Key_read_requests", "reads"], + ["Key_read_requests", "reads", "incremental"], ["Key_write_requests", "writes", "incremental", -1, 1] ]}, 'key_disk_ops': { 'options': [None, 'mysql MyISAM Key Cache Disk Operations', 'operations/s', 'myisam', 'mysql.key_disk_ops', 'area'], 'lines': [ - ["Key_reads", "reads"], + ["Key_reads", "reads", "incremental"], ["Key_writes", "writes", "incremental", -1, 1] ]}, 'files': { @@ -284,25 +282,35 @@ CHARTS = { 'files_rate': { 'options': [None, 'mysql Opened Files Rate', 'files/s', 'files', 'mysql.files_rate', 'line'], 'lines': [ - ["Opened_files", "files"] + ["Opened_files", "files", "incremental"] ]}, 'binlog_stmt_cache': { 'options': [None, 'mysql Binlog Statement Cache', 'statements/s', 'binlog', 'mysql.binlog_stmt_cache', 'line'], 'lines': [ - ["Binlog_stmt_cache_disk_use", "disk"], - ["Binlog_stmt_cache_use", "all"] + ["Binlog_stmt_cache_disk_use", "disk", "incremental"], + ["Binlog_stmt_cache_use", "all", "incremental"] ]}, 'connection_errors': { 'options': [None, 'mysql Connection Errors', 'connections/s', 'connections', 'mysql.connection_errors', 'line'], 'lines': [ - ["Connection_errors_accept", "accept"], - ["Connection_errors_internal", "internal"], - ["Connection_errors_max_connections", "max"], - ["Connection_errors_peer_address", "peer_addr"], - ["Connection_errors_select", "select"], - ["Connection_errors_tcpwrap", "tcpwrap"] + ["Connection_errors_accept", "accept", "incremental"], + ["Connection_errors_internal", "internal", "incremental"], + ["Connection_errors_max_connections", "max", "incremental"], + ["Connection_errors_peer_address", "peer_addr", "incremental"], + ["Connection_errors_select", "select", "incremental"], + ["Connection_errors_tcpwrap", "tcpwrap", "incremental"] + ]}, + 'slave_behind': { + 'options': [None, 'Slave Behind Seconds', 'seconds', 'slave', 'mysql.slave_behind', 'line'], + 'lines': [ + ["slave_behind", "seconds", "absolute"] + ]}, + 'slave_status': { + 'options': [None, 'Slave Status', 'status', 'slave', 'mysql.slave_status', 'line'], + 'lines': [ + ["slave_sql", "sql_running", "absolute"], + ["slave_io", "io_running", "absolute"] ]} - } @@ -313,6 +321,7 @@ class Service(SimpleService): self.order = ORDER self.definitions = CHARTS self.connection = None + self.do_slave = -1 def _parse_config(self, configuration): """ @@ -320,44 +329,98 @@ class Service(SimpleService): :param configuration: dict :return: dict """ + parameters = {} if self.name is None: self.name = 'local' - if 'user' not in configuration: - self.configuration['user'] = 'root' - if 'password' not in configuration: - self.configuration['password'] = '' + if 'user' in configuration: + parameters['user'] = self.configuration['user'] + if 'pass' in configuration: + parameters['passwd'] = self.configuration['pass'] if 'my.cnf' in configuration: - self.configuration['socket'] = '' - self.configuration['host'] = '' - self.configuration['port'] = 0 + parameters['read_default_file'] = self.configuration['my.cnf'] elif 'socket' in configuration: - self.configuration['my.cnf'] = '' - self.configuration['host'] = '' - self.configuration['port'] = 0 + parameters['unix_socket'] = self.configuration['socket'] elif 'host' in configuration: - self.configuration['my.cnf'] = '' - self.configuration['socket'] = '' + parameters['host'] = self.configuration['host'] if 'port' in configuration: - self.configuration['port'] = int(configuration['port']) - else: - self.configuration['port'] = 3306 + parameters['port'] = int(self.configuration['port']) + self.connection_parameters = parameters def _connect(self): """ Try to connect to MySQL server """ try: - self.connection = MySQLdb.connect(user=self.configuration['user'], - passwd=self.configuration['password'], - read_default_file=self.configuration['my.cnf'], - unix_socket=self.configuration['socket'], - host=self.configuration['host'], - port=self.configuration['port'], - connect_timeout=self.update_every) + self.connection = MySQLdb.connect(connect_timeout=self.update_every, **self.connection_parameters) + except MySQLdb.OperationalError as e: + self.error("Cannot establish connection to MySQL.") + self.debug(str(e)) + raise RuntimeError except Exception as e: - self.error(NAME + " has problem connecting to server:", e) + self.error("problem connecting to server:", e) raise RuntimeError + def _get_data_slave(self): + """ + Get slave raw data from MySQL server + :return: dict + """ + if self.connection is None: + try: + self._connect() + except RuntimeError: + return None + + slave_data = None + slave_raw_data = None + try: + cursor = self.connection.cursor() + if cursor.execute(QUERY_SLAVE): + slave_raw_data = dict(list(zip([elem[0] for elem in cursor.description], cursor.fetchone()))) + + except MySQLdb.OperationalError as e: + self.debug("Reconnecting for query", QUERY_SLAVE, ":", str(e)) + try: + self._connect() + cursor = self.connection.cursor() + if cursor.execute(QUERY_SLAVE): + slave_raw_data = dict(list(zip([elem[0] for elem in cursor.description], cursor.fetchone()))) + except Exception as e: + self.error("retried, but cannot execute query", QUERY_SLAVE, ":", str(e)) + self.connection.close() + self.connection = None + return None + + except Exception as e: + self.error("cannot execute query", QUERY_SLAVE, ":", str(e)) + self.connection.close() + self.connection = None + return None + + if slave_raw_data is not None: + slave_data = { + 'slave_behind': None, + 'slave_sql': None, + 'slave_io': None + } + + try: + slave_data['slave_behind'] = int(slave_raw_data.setdefault('Seconds_Behind_Master', -1)) + except: + slave_data['slave_behind'] = None + + try: + slave_data['slave_sql'] = 1 if slave_raw_data.get('Slave_SQL_Running') == 'Yes' else -1 + except: + slave_data['slave_sql'] = None + + try: + slave_data['slave_io'] = 1 if slave_raw_data.get('Slave_IO_Running') == 'Yes' else -1 + except: + slave_data['slave_io'] = None + + return slave_data + def _get_data(self): """ Get raw data from MySQL server @@ -372,16 +435,49 @@ class Service(SimpleService): cursor = self.connection.cursor() cursor.execute(QUERY) raw_data = cursor.fetchall() + + except MySQLdb.OperationalError as e: + self.debug("Reconnecting for query", QUERY, ":", str(e)) + try: + self._connect() + cursor = self.connection.cursor() + cursor.execute(QUERY) + raw_data = cursor.fetchall() + except Exception as e: + self.error("retried, but cannot execute query", QUERY, ":", str(e)) + self.connection.close() + self.connection = None + return None + except Exception as e: - self.error(NAME + ": cannot execute query.", e) + self.error("cannot execute query", QUERY, ":", str(e)) self.connection.close() self.connection = None return None - return dict(raw_data) + data = dict(raw_data) + + # check for slave data + # the first time is -1 (so we do it) + # then it is set to 1 or 0 and we keep it like that + if self.do_slave != 0: + slave_data = self._get_data_slave() + if slave_data is not None: + data.update(slave_data) + if self.do_slave == -1: + self.do_slave = 1 + else: + if self.do_slave == -1: + self.error("replication metrics will be disabled - please allow netdata to collect them.") + self.do_slave = 0 + + # do calculations + try: + data["Thread_cache_misses"] = round(float(data["Threads_created"]) / float(data["Connections"]) * 10000) + except: + data["Thread_cache_misses"] = None - def _formatted_data(self): - return self._get_data() + return data def check(self): """ @@ -394,64 +490,3 @@ class Service(SimpleService): except RuntimeError: self.connection = None return False - - # def create(self): - # """ - # Create graphs - # :return: boolean - # """ - # for name in ORDER: - # self.defs[name] = [] - # for line in CHARTS[name][1]: - # self.defs[name].append(line[0]) - # - # idx = 0 - # data = self._get_data() - # if data is None: - # return False - # for name in ORDER: - # header = "CHART mysql_" + \ - # str(self.name) + "." + \ - # name + " " + \ - # CHARTS[name][0] + " " + \ - # str(self.priority + idx) + " " + \ - # str(self.update_every) - # content = "" - # # check if server has this data point - # for line in CHARTS[name][1]: - # if line[0] in data: - # content += "DIMENSION " + line[0] + " " + line[1] + "\n" - # if len(content) > 0: - # print(header) - # print(content) - # idx += 1 - # - # if idx == 0: - # return False - # return True - # - # def update(self, interval): - # """ - # Update data on graphs - # :param interval: int - # :return: boolean - # """ - # data = self._get_data() - # if data is None: - # return False - # try: - # data['Thread cache misses'] = int(int(data['Threads_created']) * 10000 / int(data['Connections'])) - # except Exception: - # pass - # for chart, dimensions in self.defs.items(): - # header = "BEGIN mysql_" + str(self.name) + "." + chart + " " + str(interval) + '\n' - # lines = "" - # for d in dimensions: - # try: - # lines += "SET " + d + " = " + data[d] + '\n' - # except KeyError: - # pass - # if len(lines) > 0: - # print(header + lines + "END") - # - # return True