1 # -*- coding: utf-8 -*-
2 # Description: MySQL netdata python.d plugin
3 # Author: Pawel Krupa (paulfantom)
6 from base import SimpleService
9 NAME = os.path.basename(__file__).replace(".chart.py", "")
11 # import 3rd party library to handle MySQL communication
15 # https://github.com/PyMySQL/mysqlclient-python
16 msg.info("using MySQLdb")
19 import pymysql as MySQLdb
21 # https://github.com/PyMySQL/PyMySQL
22 msg.info("using pymysql")
24 msg.error("MySQLdb or PyMySQL module is needed to use mysql.chart.py plugin")
27 # default module values (can be overridden per job in `config`)
32 # default configuration (overridden by python.d.plugin)
37 'socket': '/var/run/mysqld/mysqld.sock',
38 'update_every': update_every,
44 # query executed on MySQL server
45 QUERY = "SHOW GLOBAL STATUS"
51 'join_issues', 'sort_issues',
53 'connections', 'connection_errors',
54 'binlog_cache', 'binlog_stmt_cache',
55 'threads', 'thread_cache_misses',
56 'innodb_io', 'innodb_io_ops', 'innodb_io_pending_ops', 'innodb_log', 'innodb_os_log', 'innodb_os_log_io',
57 'innodb_cur_row_lock', 'innodb_rows', 'innodb_buffer_pool_pages', 'innodb_buffer_pool_bytes',
58 'innodb_buffer_pool_read_ahead', 'innodb_buffer_pool_reqs', 'innodb_buffer_pool_ops',
59 'qcache_ops', 'qcache', 'qcache_freemem', 'qcache_memblocks',
60 'key_blocks', 'key_requests', 'key_disk_ops',
61 'files', 'files_rate']
65 'options': [None, 'mysql Bandwidth', 'kilobits/s', 'bandwidth', 'mysql.net', 'area'],
67 ["Bytes_received", "in", "incremental", 8, 1024],
68 ["Bytes_sent", "out", "incremental", -8, 1024]
71 'options': [None, 'mysql Queries', 'queries/s', 'queries', 'mysql.queries', 'line'],
73 ["Queries", "queries"],
74 ["Questions", "questions"],
75 ["Slow_queries", "slow_queries"]
78 'options': [None, 'mysql Handlers', 'handlers/s', 'handlers', 'mysql.handlers', 'line'],
80 ["Handler_commit", "commit"],
81 ["Handler_delete", "delete"],
82 ["Handler_prepare", "prepare"],
83 ["Handler_read_first", "read_first"],
84 ["Handler_read_key", "read_key"],
85 ["Handler_read_next", "read_next"],
86 ["Handler_read_prev", "read_prev"],
87 ["Handler_read_rnd", "read_rnd"],
88 ["Handler_read_rnd_next", "read_rnd_next"],
89 ["Handler_rollback", "rollback"],
90 ["Handler_savepoint", "savepoint"],
91 ["Handler_savepoint_rollback", "savepoint_rollback"],
92 ["Handler_update", "update"],
93 ["Handler_write", "write"]
96 'options': [None, 'mysql Tables Locks', 'locks/s', 'locks', 'mysql.table_locks', 'line'],
98 ["Table_locks_immediate", "immediate"],
99 ["Table_locks_waited", "waited", "incremental", -1, 1]
102 'options': [None, 'mysql Select Join Issues', 'joins/s', 'issues', 'mysql.join_issues', 'line'],
104 ["Select_full_join", "full_join"],
105 ["Select_full_range_join", "full_range_join"],
106 ["Select_range", "range"],
107 ["Select_range_check", "range_check"],
108 ["Select_scan", "scan"]
111 'options': [None, 'mysql Sort Issues', 'issues/s', 'issues', 'mysql.sort.issues', 'line'],
113 ["Sort_merge_passes", "merge_passes"],
114 ["Sort_range", "range"],
115 ["Sort_scan", "scan"]
118 'options': [None, 'mysql Tmp Operations', 'counter', 'temporaries', 'mysql.tmp', 'line'],
120 ["Created_tmp_disk_tables", "disk_tables"],
121 ["Created_tmp_files", "files"],
122 ["Created_tmp_tables", "tables"]
125 'options': [None, 'mysql Connections', 'connections/s', 'connections', 'mysql.connections', 'line'],
127 ["Connections", "all"],
128 ["Aborted_connects", "aborted"]
131 'options': [None, 'mysql Binlog Cache', 'transactions/s', 'binlog', 'mysql.binlog_cache', 'line'],
133 ["Binlog_cache_disk_use", "disk"],
134 ["Binlog_cache_use", "all"]
137 'options': [None, 'mysql Threads', 'threads', 'threads', 'mysql.threads', 'line'],
139 ["Threads_connected", "connected", "absolute"],
140 ["Threads_created", "created"],
141 ["Threads_cached", "cached", "absolute", -1, 1],
142 ["Threads_running", "running", "absolute"],
144 'thread_cache_misses': {
145 'options': [None, 'mysql Threads Cache Misses', 'misses', 'threads', 'mysql.thread_cache_misses', 'area'],
147 ["Thread_cache_misses", "misses", "misses", "absolute", 1, 100]
150 'options': [None, 'mysql InnoDB I/O Bandwidth', 'kilobytes/s', 'innodb', 'mysql.innodb_io', 'area'],
152 ["Innodb_data_read", "read", "incremental", 1, 1024],
153 ["Innodb_data_written", "write", "incremental", -1, 1024]
156 'lines': [None, 'mysql InnoDB I/O Operations', 'operations/s', 'innodb', 'mysql.innodb_io_ops', 'line'],
158 ["Innodb_data_reads", "reads"],
159 ["Innodb_data_writes", "writes", "incremental", -1, 1],
160 ["Innodb_data_fsyncs", "fsyncs"]
162 'innodb_io_pending_ops': {
163 'options': [None, 'mysql InnoDB Pending I/O Operations', 'operations', 'innodb', 'mysql.innodb_io_pending_ops', 'line'],
165 ["Innodb_data_pending_reads", "reads", "absolute"],
166 ["Innodb_data_pending_writes", "writes", "absolute", -1, 1],
167 ["Innodb_data_pending_fsyncs", "fsyncs"]
170 'options': [None, 'mysql InnoDB Log Operations', 'operations/s', 'innodb', 'mysql.innodb_log', 'line'],
172 ["Innodb_log_waits", "waits"],
173 ["Innodb_log_write_requests", "write_requests", "incremental", -1, 1],
174 ["Innodb_log_writes", "incremental", -1, 1],
177 'options': [None, 'mysql InnoDB OS Log Operations', 'operations', 'innodb', 'mysql.innodb_os_log', 'line'],
179 ["Innodb_os_log_fsyncs", "fsyncs"],
180 ["Innodb_os_log_pending_fsyncs", "pending_fsyncs", "absolute"],
181 ["Innodb_os_log_pending_writes", "pending_writes", "absolute", -1, 1],
183 'innodb_os_log_io': {
184 'options': [None, 'mysql InnoDB OS Log Bandwidth', 'kilobytes/s', 'innodb', 'mysql.innodb_os_log_io', 'area'],
186 ["Innodb_os_log_written", "write", "incremental", -1, 1024],
188 'innodb_cur_row_lock': {
189 'options': [None, 'mysql InnoDB Current Row Locks', 'operations', 'innodb', 'mysql.innodb_cur_row_lock', 'area'],
191 ["Innodb_row_lock_current_waits", "current_waits", "absolute"]
194 'options': [None, 'mysql InnoDB Row Operations', 'operations/s', 'innodb', 'mysql.innodb_rows', 'area'],
196 ["Innodb_rows_inserted", "read"],
197 ["Innodb_rows_read", "deleted", "incremental", -1, 1],
198 ["Innodb_rows_updated", "inserted", "incremental", 1, 1],
199 ["Innodb_rows_deleted", "updated", "incremental", -1, 1],
201 'innodb_buffer_pool_pages': {
202 'options': [None, 'mysql InnoDB Buffer Pool Pages', 'pages', 'innodb', 'mysql.innodb_buffer_pool_pages', 'line'],
204 ["Innodb_buffer_pool_pages_data", "data", "absolute"],
205 ["Innodb_buffer_pool_pages_dirty", "dirty", "absolute", -1, 1],
206 ["Innodb_buffer_pool_pages_free", "free", "absolute"],
207 ["Innodb_buffer_pool_pages_flushed", "flushed", "incremental", -1, 1],
208 ["Innodb_buffer_pool_pages_misc", "misc", "absolute", -1, 1],
209 ["Innodb_buffer_pool_pages_total", "total", "absolute"]
211 'innodb_buffer_pool_bytes': {
212 'options': [None, 'mysql InnoDB Buffer Pool Bytes', 'MB', 'innodb', 'mysql.innodb_buffer_pool_bytes', 'area'],
214 ["Innodb_buffer_pool_bytes_data", "data", "absolute"],
215 ["Innodb_buffer_pool_bytes_dirty", "dirty", "absolute", -1, 1]
217 'innodb_buffer_pool_read_ahead': {
218 'options': [None, 'mysql InnoDB Buffer Pool Read Ahead', 'operations/s', 'innodb', 'mysql.innodb_buffer_pool_read_ahead', 'area'],
220 ["Innodb_buffer_pool_read_ahead", "all"],
221 ["Innodb_buffer_pool_read_ahead_evicted", "evicted", "incremental", -1, 1],
222 ["Innodb_buffer_pool_read_ahead_rnd", "random"]
224 'innodb_buffer_pool_reqs': {
225 'options': [None, 'mysql InnoDB Buffer Pool Requests', 'requests/s', 'innodb', 'mysql.innodb_buffer_pool_reqs', 'area'],
227 ["Innodb_buffer_pool_read_requests", "reads"],
228 ["Innodb_buffer_pool_write_requests", "writes", "incremental", -1, 1]
230 'innodb_buffer_pool_ops': {
231 'options': [None, 'mysql InnoDB Buffer Pool Operations', 'operations/s', 'innodb', 'mysql.innodb_buffer_pool_ops', 'area'],
233 ["Innodb_buffer_pool_reads", "disk reads"],
234 ["Innodb_buffer_pool_wait_free", "wait free", "incremental", -1, 1]
237 'options': [None, 'mysql QCache Operations', 'queries/s', 'qcache', 'mysql.qcache_ops', 'line'],
239 ["Qcache_hits", "hits"],
240 ["Qcache_lowmem_prunes", "lowmem prunes", "incremental", -1, 1],
241 ["Qcache_inserts", "inserts"],
242 ["Qcache_not_cached", "not cached", "incremental", -1, 1]
245 'options': [None, 'mysql QCache Queries in Cache', 'queries', 'qcache', 'mysql.qcache', 'line'],
247 ["Qcache_queries_in_cache", "queries", "absolute"]
250 'options': [None, 'mysql QCache Free Memory', 'MB', 'qcache', 'mysql.qcache_freemem', 'area'],
252 ["Qcache_free_memory", "free", "absolute"]
254 'qcache_memblocks': {
255 'options': [None, 'mysql QCache Memory Blocks', 'blocks', 'qcache', 'mysql.qcache_memblocks', 'line'],
257 ["Qcache_free_blocks", "free", "absolute"],
258 ["Qcache_total_blocks", "total", "absolute"]
261 'options': [None, 'mysql MyISAM Key Cache Blocks', 'blocks', 'myisam', 'mysql.key_blocks', 'line'],
263 ["Key_blocks_unused", "unused", "absolute"],
264 ["Key_blocks_used", "used", "absolute", -1, 1],
265 ["Key_blocks_not_flushed", "not flushed", "absolute"]
268 'options': [None, 'mysql MyISAM Key Cache Requests', 'requests/s', 'myisam', 'mysql.key_requests', 'area'],
270 ["Key_read_requests", "reads"],
271 ["Key_write_requests", "writes", "incremental", -1, 1]
274 'options': [None, 'mysql MyISAM Key Cache Disk Operations', 'operations/s', 'myisam', 'mysql.key_disk_ops', 'area'],
276 ["Key_reads", "reads"],
277 ["Key_writes", "writes", "incremental", -1, 1]
280 'options': [None, 'mysql Open Files', 'files', 'files', 'mysql.files', 'line'],
282 ["Open_files", "files", "absolute"]
285 'options': [None, 'mysql Opened Files Rate', 'files/s', 'files', 'mysql.files_rate', 'line'],
287 ["Opened_files", "files"]
289 'binlog_stmt_cache': {
290 'options': [None, 'mysql Binlog Statement Cache', 'statements/s', 'binlog', 'mysql.binlog_stmt_cache', 'line'],
292 ["Binlog_stmt_cache_disk_use", "disk"],
293 ["Binlog_stmt_cache_use", "all"]
295 'connection_errors': {
296 'options': [None, 'mysql Connection Errors', 'connections/s', 'connections', 'mysql.connection_errors', 'line'],
298 ["Connection_errors_accept", "accept"],
299 ["Connection_errors_internal", "internal"],
300 ["Connection_errors_max_connections", "max"],
301 ["Connection_errors_peer_address", "peer_addr"],
302 ["Connection_errors_select", "select"],
303 ["Connection_errors_tcpwrap", "tcpwrap"]
309 class Service(SimpleService):
310 def __init__(self, configuration=None, name=None):
311 SimpleService.__init__(self, configuration=configuration, name=name)
312 self._parse_config(configuration)
314 self.definitions = CHARTS
315 self.connection = None
317 def _parse_config(self, configuration):
319 Parse configuration to collect data from MySQL server
320 :param configuration: dict
323 if self.name is None:
325 if 'user' not in configuration:
326 self.configuration['user'] = 'root'
327 if 'password' not in configuration:
328 self.configuration['password'] = ''
329 if 'my.cnf' in configuration:
330 self.configuration['socket'] = ''
331 self.configuration['host'] = ''
332 self.configuration['port'] = 0
333 elif 'socket' in configuration:
334 self.configuration['my.cnf'] = ''
335 self.configuration['host'] = ''
336 self.configuration['port'] = 0
337 elif 'host' in configuration:
338 self.configuration['my.cnf'] = ''
339 self.configuration['socket'] = ''
340 if 'port' in configuration:
341 self.configuration['port'] = int(configuration['port'])
343 self.configuration['port'] = 3306
347 Try to connect to MySQL server
350 self.connection = MySQLdb.connect(user=self.configuration['user'],
351 passwd=self.configuration['password'],
352 read_default_file=self.configuration['my.cnf'],
353 unix_socket=self.configuration['socket'],
354 host=self.configuration['host'],
355 port=self.configuration['port'],
356 connect_timeout=self.update_every)
357 except Exception as e:
358 self.error(NAME + " has problem connecting to server:", e)
363 Get raw data from MySQL server
366 if self.connection is None:
372 cursor = self.connection.cursor()
373 cursor.execute(QUERY)
374 raw_data = cursor.fetchall()
375 except Exception as e:
376 self.error(NAME + ": cannot execute query.", e)
377 self.connection.close()
378 self.connection = None
381 return dict(raw_data)
383 def _formatted_data(self):
384 return self._get_data()
388 Check if service is able to connect to server
392 self.connection = self._connect()
395 self.connection = None
404 # self.defs[name] = []
405 # for line in CHARTS[name][1]:
406 # self.defs[name].append(line[0])
409 # data = self._get_data()
413 # header = "CHART mysql_" + \
414 # str(self.name) + "." + \
416 # CHARTS[name][0] + " " + \
417 # str(self.priority + idx) + " " + \
418 # str(self.update_every)
420 # # check if server has this data point
421 # for line in CHARTS[name][1]:
422 # if line[0] in data:
423 # content += "DIMENSION " + line[0] + " " + line[1] + "\n"
424 # if len(content) > 0:
433 # def update(self, interval):
435 # Update data on graphs
436 # :param interval: int
439 # data = self._get_data()
443 # data['Thread cache misses'] = int(int(data['Threads_created']) * 10000 / int(data['Connections']))
446 # for chart, dimensions in self.defs.items():
447 # header = "BEGIN mysql_" + str(self.name) + "." + chart + " " + str(interval) + '\n'
449 # for d in dimensions:
451 # lines += "SET " + d + " = " + data[d] + '\n'
455 # print(header + lines + "END")