MySql

Complexity

Total Complexity

27

Size/Duplication

Total Lines	420
Duplicated Lines	0 %

Importance

Changes	5
Bugs	0	Features	0

7 Methods

Rating	Name	Duplication	Size	Complexity
A	connect()	0	19	2
B	table_status()	0	48	6
A	onstop()	0	4	1
F	poll()	0	50	11
B	disconnect()	0	12	5
A	onstart()	0	5	1
A	__init__()	0	16	1

# -*- coding: utf-8 -*-
"""Basic MySQL reader plugin."""

import re
from collections import deque, namedtuple

import pymysql

The import pymysql could not be resolved.

import plumd
from plumd.util import Filter

Unused Filter imported from plumd.util

__author__ = 'Kenny Freeman'
__email__ = 'kenny.freeman@gmail.com'
__license__ = "ISCL"
__docformat__ = 'reStructuredText'


TableStatus = namedtuple('TableStatus',
                         ['name', 'engine', 'version', 'row_format', 'rows',
                          'avg_row_length', 'data_length', 'max_data_length',
                          'index_length', 'data_free', 'auto_increment',
                          'create_time', 'update_time', 'check_time',
                          'collation', 'checksum', 'create_options',
                          'comment'])


class MySql(plumd.Reader):
    """Plugin to measure various MySQL metrics."""

    defaults = {
        'poll.interval': 10,
        'host': '127.0.0.1',
        'port': 3306,
        'user': 'monitor',
        'pass': 'monitor',
        'db': 'mysql',
        'table_status_re': ".*",
        'record_table_status': False,
        'status_record': {
            'errs': [
                "Aborted_clients",
                "Aborted_connects",
                "Access_denied_errors"
            ],
            'binlog': [
                "Binlog_commits",
                "Binlog_group_commits",
                "Binlog_group_commit_trigger_count",
                "Binlog_group_commit_trigger_lock_wait",
                "Binlog_group_commit_trigger_timeout",
                "Binlog_snapshot_position",
                "Binlog_bytes_written",
                "Binlog_cache_disk_use",
                "Binlog_cache_use",
                "Binlog_stmt_cache_disk_use",
                "Binlog_stmt_cache_use"
            ],
            'com': [
                "Com_admin_commands",
                "Com_begin",
                "Com_commit",
                "Com_delete",
                "Com_delete_multi",
                "Com_empty_query",
                "Com_execute_sql",
                "Com_insert",
                "Com_insert_select",
                "Com_kill",
                "Com_prepare_sql",
                "Com_replace",
                "Com_replace_select",
                "Com_reset",
                "Com_rollback",
                "Com_select",
                "Com_stmt_close",
                "Com_stmt_execute",
                "Com_stmt_fetch",
                "Com_stmt_prepare",
                "Com_stmt_reprepare",
                "Com_stmt_reset",
                "Com_stmt_send_long_data",
                "Com_truncate",
                "Com_update",
                "Com_update_multi"
            ],
            'conn': [
                "Connection_errors_accept",
                "Connection_errors_internal",
                "Connection_errors_max_connections",
                "Connection_errors_peer_address",
                "Connection_errors_select",
                "Connection_errors_tcpwrap",
                "Connections",
                "Max_used_connections"
            ],
            'tmp': [
                "Created_tmp_disk_tables",
                "Created_tmp_files",
                "Created_tmp_tables"
            ],
            'handler': [
                "Handler_commit",
                "Handler_delete",
                "Handler_discover",
                "Handler_external_lock",
                "Handler_icp_attempts",
                "Handler_icp_match",
                "Handler_mrr_init",
                "Handler_mrr_key_refills",
                "Handler_mrr_rowid_refills",
                "Handler_prepare",
                "Handler_read_first",
                "Handler_read_key",
                "Handler_read_last",
                "Handler_read_next",
                "Handler_read_prev",
                "Handler_read_rnd",
                "Handler_read_rnd_deleted",
                "Handler_read_rnd_next",
                "Handler_rollback",
                "Handler_savepoint",
                "Handler_savepoint_rollback",
                "Handler_tmp_update",
                "Handler_tmp_write",
                "Handler_update",
                "Handler_write"
            ],
            'inno': [
                "Innodb_buffer_pool_bytes_data",
                "Innodb_buffer_pool_bytes_dirty",
                "Innodb_buffer_pool_pages_data",
                "Innodb_buffer_pool_pages_dirty",
                "Innodb_buffer_pool_pages_flushed",
                "Innodb_buffer_pool_pages_free",
                "Innodb_buffer_pool_pages_lru_flushed",
                "Innodb_buffer_pool_pages_made_not_young",
                "Innodb_buffer_pool_pages_made_young",
                "Innodb_buffer_pool_pages_misc",
                "Innodb_buffer_pool_pages_old",
                "Innodb_buffer_pool_pages_total",
                "Innodb_buffer_pool_read_ahead",
                "Innodb_buffer_pool_read_ahead_evicted",
                "Innodb_buffer_pool_read_ahead_rnd",
                "Innodb_buffer_pool_read_requests",
                "Innodb_buffer_pool_reads",
                "Innodb_buffer_pool_wait_free",
                "Innodb_buffer_pool_write_requests",
                "Innodb_checkpoint_age",
                "Innodb_checkpoint_max_age",
                "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_deadlocks",
                "Innodb_history_list_length",
                "Innodb_ibuf_discarded_delete_marks",
                "Innodb_ibuf_discarded_deletes",
                "Innodb_ibuf_discarded_inserts",
                "Innodb_ibuf_free_list",
                "Innodb_ibuf_merged_delete_marks",
                "Innodb_ibuf_merged_deletes",
                "Innodb_ibuf_merged_inserts",
                "Innodb_ibuf_merges",
                "Innodb_ibuf_segment_size",
                "Innodb_ibuf_size",
                "Innodb_log_waits",
                "Innodb_log_write_requests",
                "Innodb_log_writes",
                "Innodb_lsn_current",
                "Innodb_lsn_flushed",
                "Innodb_lsn_last_checkpoint",
                "Innodb_master_thread_active_loops",
                "Innodb_master_thread_idle_loops",
                "Innodb_mem_adaptive_hash",
                "Innodb_mem_dictionary",
                "Innodb_mem_total",
                "Innodb_mutex_os_waits",
                "Innodb_mutex_spin_rounds",
                "Innodb_mutex_spin_waits",
                "Innodb_os_log_fsyncs",
                "Innodb_os_log_pending_fsyncs",
                "Innodb_os_log_pending_writes",
                "Innodb_os_log_written",
                "Innodb_pages_created",
                "Innodb_pages_read",
                "Innodb_pages_written",
                "Innodb_purge_trx_id",
                "Innodb_purge_undo_no",
                "Innodb_read_views_memory",
                "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",
                "Innodb_system_rows_deleted",
                "Innodb_system_rows_inserted",
                "Innodb_system_rows_read",
                "Innodb_system_rows_updated",
                "Innodb_s_lock_os_waits",
                "Innodb_s_lock_spin_rounds",
                "Innodb_s_lock_spin_waits",
                "Innodb_x_lock_os_waits",
                "Innodb_x_lock_spin_rounds",
                "Innodb_x_lock_spin_waits",
                "Innodb_num_index_pages_written",
                "Innodb_num_non_index_pages_written",
                "Innodb_secondary_index_triggered_cluster_reads",
                "Innodb_secondary_index_triggered_cluster_reads_avoided"
            ],
            'key': [
                "Key_blocks_not_flushed",
                "Key_blocks_unused",
                "Key_blocks_used",
                "Key_blocks_warm",
                "Key_read_requests",
                "Key_reads",
                "Key_write_requests",
                "Key_writes"
            ],
            'open': [
                "Open_files",
                "Open_streams",
                "Open_table_definitions",
                "Open_tables",
                "Opened_files",
                "Opened_plugin_libraries",
                "Opened_table_definitions",
                "Opened_tables",
                "Opened_views"
            ],
            'qps': [
                "Prepared_stmt_count",
                "Queries",
                "Questions",
                "Rows_read",
                "Rows_sent",
                "Rows_tmp_read",
                "Select_full_join",
                "Select_full_range_join",
                "Select_range",
                "Select_range_check",
                "Select_scan",
            ],
            'sort': [
                "Sort_merge_passes",
                "Sort_priority_queue_sorts",
                "Sort_range",
                "Sort_rows",
                "Sort_scan"
            ],
            'misc': [
                "Bytes_received",
                "Bytes_sent",
                "Cpu_time",
                "Slow_queries",
                "Busy_time",
                "Empty_queries",
                "Executed_triggers",
                "Max_statement_time_exceeded",
                "Memory_used",
                "Uptime",
                "Syncs",
                "Table_locks_immediate",
                "Table_locks_waited"
            ],
            'threads': [
                "Threadpool_idle_threads",
                "Threadpool_threads",
                "Threads_cached",
                "Threads_connected",
                "Threads_created",
                "Threads_running"
            ]
        }
    }

    def __init__(self, log, config):
        """Plugin to measure various MySQL metrics.

        :param log: A logger
        :type log: logging.RootLogger
        :param config: a plumd.config.Conf configuration helper instance.
        :type config: plumd.config.Conf
        """
        super(MySql, self).__init__(log, config)
        self.config.defaults(MySql.defaults)
        self.db = self.config.get('db')
        self.dbconn = None
        self.dbcur = None
        self.record = self.config.get('status_record')
        self.record_table_status = self.config.get('record_table_status')
        self.table_status_re = re.compile(self.config.get('table_status_re'))

    def onstart(self):
        """Connect to our db - use a persistent connection - ensures
        metrics even when all connections are used.
        """
        self.connect()

    def onstop(self):
        """Disconnect from our db."""
        self.dbcur.close()
        self.dbconn.close()

    def disconnect(self):
        """Disconnect from the database, if connected."""
        if self.dbcur:
            try:
                self.dbcur.close()
            except Exception:

This except handler seems to be unused and could be removed.

Catching very general exceptions such as Exception is usually not recommended.

                pass
        if self.dbconn:
            try:
                self.dbconn.close()
            except Exception:

This except handler seems to be unused and could be removed.

Catching very general exceptions such as Exception is usually not recommended.

                pass

    def connect(self):
        """Connect to the configured database."""
        ret = True
        self.disconnect()
        host = self.config.get('host')
        port = int(self.config.get('port'))
        user = self.config.get('user')
        passwd = self.config.get('passwd')
        db_name = self.config.get('db')
        try:
            self.dbconn = pymysql.connect(host=host, port=port, user=user,
                                          passwd=passwd, db=db_name)
            self.dbcur = self.dbconn.cursor()
        except Exception as exc:

Catching very general exceptions such as Exception is usually not recommended.

            msg = "MySql: exception connecting to {0}:{1}:{2}: {3}"
            self.log.error(msg.format(host, port, db_name, exc))
            ret = False
            self.disconnect()
        return ret

    def poll(self):
        """Poll for MySQL metrics.

        :rtype: ResultSet
        """
        result = plumd.Result("mysql")
        # add db name as prefix to all metrics
        db_name = self.config.get('db')
        # currently records selected values from status
        # future versions to support calculated values + others
        try:
            self.dbcur.execute("SHOW GLOBAL STATUS")
        except Exception as exc:

Catching very general exceptions such as Exception is usually not recommended.

            self.log.error("MySql: exception getting status: {0}".format(exc))
            if not self.connect():
                return plumd.ResultSet([result])

        try:
            status = dict(self.dbcur.fetchall())
        except Exception as exc:

Catching very general exceptions such as Exception is usually not recommended.

            self.log.error("MySql: exception fetching status: {0}".format(exc))
            self.connect()
            return plumd.ResultSet([result])

        # all values recorded as is - todo: gauges and rates
        del_metrics = {}
        for prefix, metrics in self.record.items():
            mfmt_str = "{0}.{1}.{2}"
            for metric in metrics:
                try:
                    val = status[metric]
                    mname = mfmt_str.format(db_name, prefix, metric)
                    result.add(plumd.Float(mname, val))
                except (KeyError, ValueError) as e:
                    msg = "MySql: disabling unknown/invalid metric: {0}: {1}"
                    self.log.warn(msg.format(metric, e))
                    if prefix not in del_metrics:
                        del_metrics[prefix] = deque()
                    del_metrics[prefix].append(metric)

        # remove missing/unknown metrics - do outside of above for loop
        # as we can't change self.record in the loop
        for pfx, metrics in del_metrics.items():
            for metric in metrics:
                self.record[pfx].remove(metric)

        if self.record_table_status:
            self.table_status(result)

        return plumd.ResultSet([result])

    def table_status(self, result):
        """Record table metrics from 'show table status'. Note: innodb metrics
        for table status can be off by as much as 50%.

        :param result: A plumd.Result object to add metrics to.
        :type result: plumd.Result
        """
        # currently records selected values from status
        # future versions to support calculated values + others
        try:
            self.dbcur.execute("SHOW TABLE STATUS")
        except Exception as exc:

Catching very general exceptions such as Exception is usually not recommended.

            self.log.error(
                "MySql: exception getting table status: {0}".format(exc))
            if not self.connect():
                return

        try:
            tbl_status_res = self.dbcur.fetchall()
        except Exception as exc:

Catching very general exceptions such as Exception is usually not recommended.

            msg = "MySql: exception fetching table status: {0}"
            self.log.error(msg.format(exc))
            self.connect()
            return

        # add db name as prefix to all metrics
        db = self.config.get('db')

        regexp = self.table_status_re
        # all values recorded as is - todo: gauges and rates
        for tbl_status_vals in tbl_status_res:
            tstatus = TableStatus(*list(tbl_status_vals))
            if not regexp.match(tstatus.name):
                continue
            mfmt_str = "{0}.tables.{1}.{2}"
            table = tstatus.name
            result.add(plumd.Float(mfmt_str.format(db, table, "rows"),
                                   tstatus.rows))
            result.add(plumd.Float(mfmt_str.format(db, table, "avg_row_length"),
                                   tstatus.avg_row_length))
            result.add(plumd.Float(mfmt_str.format(db, table, "data_length"),
                                   tstatus.data_length))
            result.add(plumd.Float(mfmt_str.format(db, table, "max_data_length"),
                                   tstatus.max_data_length))
            result.add(plumd.Float(mfmt_str.format(db, table, "index_length"),
                                   tstatus.index_length))
            result.add(plumd.Float(mfmt_str.format(db, table, "data_free"),
                                   tstatus.data_free))