|
1
|
|
|
# -*- coding: utf-8 -*- |
|
2
|
|
|
|
|
3
|
|
|
__author__ = 'Kenny Freeman' |
|
4
|
|
|
__email__ = '[email protected]' |
|
5
|
|
|
__license__ = "ISCL" |
|
6
|
|
|
__docformat__ = 'reStructuredText' |
|
7
|
|
|
|
|
8
|
|
|
import re |
|
9
|
|
|
import time |
|
10
|
|
|
from collections import deque, namedtuple |
|
11
|
|
|
|
|
12
|
|
|
import pymysql |
|
13
|
|
|
|
|
14
|
|
|
import plumd |
|
15
|
|
|
import plumd.plugins |
|
16
|
|
|
from plumd.calc import Differential |
|
17
|
|
|
from plumd.util import Filter |
|
18
|
|
|
|
|
19
|
|
|
|
|
20
|
|
|
TableStatus = namedtuple('TableStatus', [ 'name', 'engine', 'version', |
|
21
|
|
|
'row_format', 'rows', 'avg_row_length', |
|
22
|
|
|
'data_length', 'max_data_length', |
|
23
|
|
|
'index_length', 'data_free', |
|
24
|
|
|
'auto_increment', 'create_time', |
|
25
|
|
|
'update_time', 'check_time', |
|
26
|
|
|
'collation', 'checksum', |
|
27
|
|
|
'create_options', 'comment' ] ) |
|
28
|
|
|
|
|
29
|
|
|
|
|
30
|
|
|
class MySql(plumd.plugins.Reader): |
|
31
|
|
|
"""Plugin to measure various MySQL metrics.""" |
|
32
|
|
|
defaults = { |
|
33
|
|
|
'poll.interval': 10, |
|
34
|
|
|
'host': '127.0.0.1', |
|
35
|
|
|
'port': 3306, |
|
36
|
|
|
'user': 'monitor', |
|
37
|
|
|
'pass': 'monitor', |
|
38
|
|
|
'db': 'mysql', |
|
39
|
|
|
'table_status_re': ".*", |
|
40
|
|
|
'record_table_status': False, |
|
41
|
|
|
'status_record': { |
|
42
|
|
|
'errs': [ |
|
43
|
|
|
"Aborted_clients", |
|
44
|
|
|
"Aborted_connects", |
|
45
|
|
|
"Access_denied_errors" |
|
46
|
|
|
], |
|
47
|
|
|
'binlog': [ |
|
48
|
|
|
"Binlog_commits", |
|
49
|
|
|
"Binlog_group_commits", |
|
50
|
|
|
"Binlog_group_commit_trigger_count", |
|
51
|
|
|
"Binlog_group_commit_trigger_lock_wait", |
|
52
|
|
|
"Binlog_group_commit_trigger_timeout", |
|
53
|
|
|
"Binlog_snapshot_position", |
|
54
|
|
|
"Binlog_bytes_written", |
|
55
|
|
|
"Binlog_cache_disk_use", |
|
56
|
|
|
"Binlog_cache_use", |
|
57
|
|
|
"Binlog_stmt_cache_disk_use", |
|
58
|
|
|
"Binlog_stmt_cache_use" |
|
59
|
|
|
], |
|
60
|
|
|
'com': [ |
|
61
|
|
|
"Com_admin_commands", |
|
62
|
|
|
"Com_begin", |
|
63
|
|
|
"Com_commit", |
|
64
|
|
|
"Com_delete", |
|
65
|
|
|
"Com_delete_multi", |
|
66
|
|
|
"Com_empty_query", |
|
67
|
|
|
"Com_execute_sql", |
|
68
|
|
|
"Com_insert", |
|
69
|
|
|
"Com_insert_select", |
|
70
|
|
|
"Com_kill", |
|
71
|
|
|
"Com_prepare_sql", |
|
72
|
|
|
"Com_replace", |
|
73
|
|
|
"Com_replace_select", |
|
74
|
|
|
"Com_reset", |
|
75
|
|
|
"Com_rollback", |
|
76
|
|
|
"Com_select", |
|
77
|
|
|
"Com_stmt_close", |
|
78
|
|
|
"Com_stmt_execute", |
|
79
|
|
|
"Com_stmt_fetch", |
|
80
|
|
|
"Com_stmt_prepare", |
|
81
|
|
|
"Com_stmt_reprepare", |
|
82
|
|
|
"Com_stmt_reset", |
|
83
|
|
|
"Com_stmt_send_long_data", |
|
84
|
|
|
"Com_truncate", |
|
85
|
|
|
"Com_update", |
|
86
|
|
|
"Com_update_multi" |
|
87
|
|
|
], |
|
88
|
|
|
'conn': [ |
|
89
|
|
|
"Connection_errors_accept", |
|
90
|
|
|
"Connection_errors_internal", |
|
91
|
|
|
"Connection_errors_max_connections", |
|
92
|
|
|
"Connection_errors_peer_address", |
|
93
|
|
|
"Connection_errors_select", |
|
94
|
|
|
"Connection_errors_tcpwrap", |
|
95
|
|
|
"Connections", |
|
96
|
|
|
"Max_used_connections" |
|
97
|
|
|
], |
|
98
|
|
|
'tmp': [ |
|
99
|
|
|
"Created_tmp_disk_tables", |
|
100
|
|
|
"Created_tmp_files", |
|
101
|
|
|
"Created_tmp_tables" |
|
102
|
|
|
], |
|
103
|
|
|
'handler': [ |
|
104
|
|
|
"Handler_commit", |
|
105
|
|
|
"Handler_delete", |
|
106
|
|
|
"Handler_discover", |
|
107
|
|
|
"Handler_external_lock", |
|
108
|
|
|
"Handler_icp_attempts", |
|
109
|
|
|
"Handler_icp_match", |
|
110
|
|
|
"Handler_mrr_init", |
|
111
|
|
|
"Handler_mrr_key_refills", |
|
112
|
|
|
"Handler_mrr_rowid_refills", |
|
113
|
|
|
"Handler_prepare", |
|
114
|
|
|
"Handler_read_first", |
|
115
|
|
|
"Handler_read_key", |
|
116
|
|
|
"Handler_read_last", |
|
117
|
|
|
"Handler_read_next", |
|
118
|
|
|
"Handler_read_prev", |
|
119
|
|
|
"Handler_read_rnd", |
|
120
|
|
|
"Handler_read_rnd_deleted", |
|
121
|
|
|
"Handler_read_rnd_next", |
|
122
|
|
|
"Handler_rollback", |
|
123
|
|
|
"Handler_savepoint", |
|
124
|
|
|
"Handler_savepoint_rollback", |
|
125
|
|
|
"Handler_tmp_update", |
|
126
|
|
|
"Handler_tmp_write", |
|
127
|
|
|
"Handler_update", |
|
128
|
|
|
"Handler_write" |
|
129
|
|
|
], |
|
130
|
|
|
'inno': [ |
|
131
|
|
|
"Innodb_buffer_pool_bytes_data", |
|
132
|
|
|
"Innodb_buffer_pool_bytes_dirty", |
|
133
|
|
|
"Innodb_buffer_pool_pages_data", |
|
134
|
|
|
"Innodb_buffer_pool_pages_dirty", |
|
135
|
|
|
"Innodb_buffer_pool_pages_flushed", |
|
136
|
|
|
"Innodb_buffer_pool_pages_free", |
|
137
|
|
|
"Innodb_buffer_pool_pages_lru_flushed", |
|
138
|
|
|
"Innodb_buffer_pool_pages_made_not_young", |
|
139
|
|
|
"Innodb_buffer_pool_pages_made_young", |
|
140
|
|
|
"Innodb_buffer_pool_pages_misc", |
|
141
|
|
|
"Innodb_buffer_pool_pages_old", |
|
142
|
|
|
"Innodb_buffer_pool_pages_total", |
|
143
|
|
|
"Innodb_buffer_pool_read_ahead", |
|
144
|
|
|
"Innodb_buffer_pool_read_ahead_evicted", |
|
145
|
|
|
"Innodb_buffer_pool_read_ahead_rnd", |
|
146
|
|
|
"Innodb_buffer_pool_read_requests", |
|
147
|
|
|
"Innodb_buffer_pool_reads", |
|
148
|
|
|
"Innodb_buffer_pool_wait_free", |
|
149
|
|
|
"Innodb_buffer_pool_write_requests", |
|
150
|
|
|
"Innodb_checkpoint_age", |
|
151
|
|
|
"Innodb_checkpoint_max_age", |
|
152
|
|
|
"Innodb_data_fsyncs", |
|
153
|
|
|
"Innodb_data_pending_fsyncs", |
|
154
|
|
|
"Innodb_data_pending_reads", |
|
155
|
|
|
"Innodb_data_pending_writes", |
|
156
|
|
|
"Innodb_data_read", |
|
157
|
|
|
"Innodb_data_reads", |
|
158
|
|
|
"Innodb_data_writes", |
|
159
|
|
|
"Innodb_data_written", |
|
160
|
|
|
"Innodb_dblwr_pages_written", |
|
161
|
|
|
"Innodb_dblwr_writes", |
|
162
|
|
|
"Innodb_deadlocks", |
|
163
|
|
|
"Innodb_history_list_length", |
|
164
|
|
|
"Innodb_ibuf_discarded_delete_marks", |
|
165
|
|
|
"Innodb_ibuf_discarded_deletes", |
|
166
|
|
|
"Innodb_ibuf_discarded_inserts", |
|
167
|
|
|
"Innodb_ibuf_free_list", |
|
168
|
|
|
"Innodb_ibuf_merged_delete_marks", |
|
169
|
|
|
"Innodb_ibuf_merged_deletes", |
|
170
|
|
|
"Innodb_ibuf_merged_inserts", |
|
171
|
|
|
"Innodb_ibuf_merges", |
|
172
|
|
|
"Innodb_ibuf_segment_size", |
|
173
|
|
|
"Innodb_ibuf_size", |
|
174
|
|
|
"Innodb_log_waits", |
|
175
|
|
|
"Innodb_log_write_requests", |
|
176
|
|
|
"Innodb_log_writes", |
|
177
|
|
|
"Innodb_lsn_current", |
|
178
|
|
|
"Innodb_lsn_flushed", |
|
179
|
|
|
"Innodb_lsn_last_checkpoint", |
|
180
|
|
|
"Innodb_master_thread_active_loops", |
|
181
|
|
|
"Innodb_master_thread_idle_loops", |
|
182
|
|
|
"Innodb_mem_adaptive_hash", |
|
183
|
|
|
"Innodb_mem_dictionary", |
|
184
|
|
|
"Innodb_mem_total", |
|
185
|
|
|
"Innodb_mutex_os_waits", |
|
186
|
|
|
"Innodb_mutex_spin_rounds", |
|
187
|
|
|
"Innodb_mutex_spin_waits", |
|
188
|
|
|
"Innodb_os_log_fsyncs", |
|
189
|
|
|
"Innodb_os_log_pending_fsyncs", |
|
190
|
|
|
"Innodb_os_log_pending_writes", |
|
191
|
|
|
"Innodb_os_log_written", |
|
192
|
|
|
"Innodb_pages_created", |
|
193
|
|
|
"Innodb_pages_read", |
|
194
|
|
|
"Innodb_pages_written", |
|
195
|
|
|
"Innodb_purge_trx_id", |
|
196
|
|
|
"Innodb_purge_undo_no", |
|
197
|
|
|
"Innodb_read_views_memory", |
|
198
|
|
|
"Innodb_row_lock_current_waits", |
|
199
|
|
|
"Innodb_row_lock_time", |
|
200
|
|
|
"Innodb_row_lock_time_avg", |
|
201
|
|
|
"Innodb_row_lock_time_max", |
|
202
|
|
|
"Innodb_row_lock_waits", |
|
203
|
|
|
"Innodb_rows_deleted", |
|
204
|
|
|
"Innodb_rows_inserted", |
|
205
|
|
|
"Innodb_rows_read", |
|
206
|
|
|
"Innodb_rows_updated", |
|
207
|
|
|
"Innodb_system_rows_deleted", |
|
208
|
|
|
"Innodb_system_rows_inserted", |
|
209
|
|
|
"Innodb_system_rows_read", |
|
210
|
|
|
"Innodb_system_rows_updated", |
|
211
|
|
|
"Innodb_s_lock_os_waits", |
|
212
|
|
|
"Innodb_s_lock_spin_rounds", |
|
213
|
|
|
"Innodb_s_lock_spin_waits", |
|
214
|
|
|
"Innodb_x_lock_os_waits", |
|
215
|
|
|
"Innodb_x_lock_spin_rounds", |
|
216
|
|
|
"Innodb_x_lock_spin_waits", |
|
217
|
|
|
"Innodb_num_index_pages_written", |
|
218
|
|
|
"Innodb_num_non_index_pages_written", |
|
219
|
|
|
"Innodb_secondary_index_triggered_cluster_reads", |
|
220
|
|
|
"Innodb_secondary_index_triggered_cluster_reads_avoided" |
|
221
|
|
|
], |
|
222
|
|
|
'key': [ |
|
223
|
|
|
"Key_blocks_not_flushed", |
|
224
|
|
|
"Key_blocks_unused", |
|
225
|
|
|
"Key_blocks_used", |
|
226
|
|
|
"Key_blocks_warm", |
|
227
|
|
|
"Key_read_requests", |
|
228
|
|
|
"Key_reads", |
|
229
|
|
|
"Key_write_requests", |
|
230
|
|
|
"Key_writes" |
|
231
|
|
|
], |
|
232
|
|
|
'open': [ |
|
233
|
|
|
"Open_files", |
|
234
|
|
|
"Open_streams", |
|
235
|
|
|
"Open_table_definitions", |
|
236
|
|
|
"Open_tables", |
|
237
|
|
|
"Opened_files", |
|
238
|
|
|
"Opened_plugin_libraries", |
|
239
|
|
|
"Opened_table_definitions", |
|
240
|
|
|
"Opened_tables", |
|
241
|
|
|
"Opened_views" |
|
242
|
|
|
], |
|
243
|
|
|
'qps': { |
|
244
|
|
|
"Prepared_stmt_count", |
|
245
|
|
|
"Queries", |
|
246
|
|
|
"Questions", |
|
247
|
|
|
"Rows_read", |
|
248
|
|
|
"Rows_sent", |
|
249
|
|
|
"Rows_tmp_read", |
|
250
|
|
|
"Select_full_join", |
|
251
|
|
|
"Select_full_range_join", |
|
252
|
|
|
"Select_range", |
|
253
|
|
|
"Select_range_check", |
|
254
|
|
|
"Select_scan", |
|
255
|
|
|
}, |
|
256
|
|
|
'sort': [ |
|
257
|
|
|
"Sort_merge_passes", |
|
258
|
|
|
"Sort_priority_queue_sorts", |
|
259
|
|
|
"Sort_range", |
|
260
|
|
|
"Sort_rows", |
|
261
|
|
|
"Sort_scan" |
|
262
|
|
|
], |
|
263
|
|
|
'misc': [ |
|
264
|
|
|
"Bytes_received", |
|
265
|
|
|
"Bytes_sent", |
|
266
|
|
|
"Cpu_time", |
|
267
|
|
|
"Slow_queries", |
|
268
|
|
|
"Busy_time", |
|
269
|
|
|
"Empty_queries", |
|
270
|
|
|
"Executed_triggers", |
|
271
|
|
|
"Max_statement_time_exceeded", |
|
272
|
|
|
"Memory_used", |
|
273
|
|
|
"Uptime", |
|
274
|
|
|
"Syncs", |
|
275
|
|
|
"Table_locks_immediate", |
|
276
|
|
|
"Table_locks_waited" |
|
277
|
|
|
], |
|
278
|
|
|
'threads': [ |
|
279
|
|
|
"Threadpool_idle_threads", |
|
280
|
|
|
"Threadpool_threads", |
|
281
|
|
|
"Threads_cached", |
|
282
|
|
|
"Threads_connected", |
|
283
|
|
|
"Threads_created", |
|
284
|
|
|
"Threads_running" |
|
285
|
|
|
] |
|
286
|
|
|
} |
|
287
|
|
|
} |
|
288
|
|
|
|
|
289
|
|
|
def __init__(self, log, config): |
|
290
|
|
|
"""Plugin to measure various MySQL metrics. |
|
291
|
|
|
|
|
292
|
|
|
:param log: A logger |
|
293
|
|
|
:type log: logging.RootLogger |
|
294
|
|
|
:param config: a plumd.config.Conf configuration helper instance. |
|
295
|
|
|
:type config: plumd.config.Conf |
|
296
|
|
|
""" |
|
297
|
|
|
super(MySql, self).__init__(log, config) |
|
298
|
|
|
self.config.defaults(MySql.defaults) |
|
299
|
|
|
self.db = self.config.get('db') |
|
300
|
|
|
self.dbconn = None |
|
301
|
|
|
self.dbcur = None |
|
302
|
|
|
self.record = self.config.get('status_record') |
|
303
|
|
|
self.record_table_status = self.config.get('record_table_status') |
|
304
|
|
|
self.table_status_re = re.compile(self.config.get('table_status_re')) |
|
305
|
|
|
|
|
306
|
|
|
|
|
307
|
|
|
def onstart(self): |
|
308
|
|
|
"""Connect to our db - use a persistent connection - ensures |
|
309
|
|
|
metrics even when all connections are used.""" |
|
310
|
|
|
self.connect() |
|
311
|
|
|
|
|
312
|
|
|
|
|
313
|
|
|
def onstop(self): |
|
314
|
|
|
"""Disconnect from our db.""" |
|
315
|
|
|
self.dbcur.close() |
|
316
|
|
|
self.dbconn.close() |
|
317
|
|
|
|
|
318
|
|
|
def disconnect(self): |
|
319
|
|
|
"""Disconnect from the database, if connected.""" |
|
320
|
|
|
if self.dbcur: |
|
321
|
|
|
try: |
|
322
|
|
|
self.dbcur.close() |
|
323
|
|
|
except Exception as e: |
|
324
|
|
|
pass |
|
325
|
|
|
if self.dbconn: |
|
326
|
|
|
try: |
|
327
|
|
|
self.dbconn.close() |
|
328
|
|
|
except Exception as e: |
|
329
|
|
|
pass |
|
330
|
|
|
|
|
331
|
|
|
def connect(self): |
|
332
|
|
|
"""Connect to the configured database.""" |
|
333
|
|
|
ret = True |
|
334
|
|
|
self.disconnect() |
|
335
|
|
|
host = self.config.get('host') |
|
336
|
|
|
port = int(self.config.get('port')) |
|
337
|
|
|
user = self.config.get('user') |
|
338
|
|
|
passwd = self.config.get('passwd') |
|
339
|
|
|
db = self.config.get('db') |
|
340
|
|
|
try: |
|
341
|
|
|
self.dbconn = pymysql.connect(host=host, port=port, user=user, |
|
342
|
|
|
passwd=passwd, db=db) |
|
343
|
|
|
self.dbcur = self.dbconn.cursor() |
|
344
|
|
|
except Exception as e: |
|
345
|
|
|
msg = "MySql: exception connecting to {0}:{1}:{2}: {3}" |
|
346
|
|
|
self.log.error(msg.format(host, port, db, e)) |
|
347
|
|
|
ret = False |
|
348
|
|
|
self.disconnect() |
|
349
|
|
|
return ret |
|
350
|
|
|
|
|
351
|
|
|
|
|
352
|
|
|
def poll(self): |
|
353
|
|
|
"""Poll for MySQL metrics. |
|
354
|
|
|
|
|
355
|
|
|
:rtype: ResultSet |
|
356
|
|
|
""" |
|
357
|
|
|
result = plumd.Result("mysql") |
|
358
|
|
|
# add db name as prefix to all metrics |
|
359
|
|
|
db = self.config.get('db') |
|
360
|
|
|
# currently records selected values from status |
|
361
|
|
|
# future versions to support calculated values + others |
|
362
|
|
|
try: |
|
363
|
|
|
self.dbcur.execute("SHOW GLOBAL STATUS") |
|
364
|
|
|
except Exception as e: |
|
365
|
|
|
self.log.error("MySql: exception getting status: {0}".format(e)) |
|
366
|
|
|
if not self.connect(): |
|
367
|
|
|
return plumd.ResultSet([result]) |
|
368
|
|
|
|
|
369
|
|
|
try: |
|
370
|
|
|
status = dict(self.dbcur.fetchall()) |
|
371
|
|
|
except Exception as e: |
|
372
|
|
|
self.log.error("MySql: exception fetching status: {0}".format(e)) |
|
373
|
|
|
self.connect() |
|
374
|
|
|
return plumd.ResultSet([result]) |
|
375
|
|
|
|
|
376
|
|
|
# all values recorded as is - todo: gauges and rates |
|
377
|
|
|
for prefix, metrics in self.record.items(): |
|
378
|
|
|
mfmt_str = "{0}.{1}.{2}" |
|
379
|
|
|
for metric in metrics: |
|
380
|
|
|
try: |
|
381
|
|
|
val = status[metric] |
|
382
|
|
|
mname = mfmt_str.format(db, prefix, metric) |
|
383
|
|
|
result.add(plumd.Float(mname, val)) |
|
384
|
|
|
except (KeyError, ValueError) as e: |
|
385
|
|
|
msg = "MySql: disabling unknown/invalid metric: {0}: {1}" |
|
386
|
|
|
print(msg.format(metric, e)) |
|
387
|
|
|
self.record[prefix].remove(metric) |
|
388
|
|
|
|
|
389
|
|
|
if self.record_table_status: |
|
390
|
|
|
self.table_status(result) |
|
391
|
|
|
|
|
392
|
|
|
return plumd.ResultSet([result]) |
|
393
|
|
|
|
|
394
|
|
|
|
|
395
|
|
|
def table_status(self, result): |
|
396
|
|
|
"""Record table metrics from 'show table status'. Note: innodb metrics |
|
397
|
|
|
for table status can be off by as much as 50%. |
|
398
|
|
|
|
|
399
|
|
|
:param result: A plumd.Result object to add metrics to. |
|
400
|
|
|
:type result: plumd.Result |
|
401
|
|
|
""" |
|
402
|
|
|
# currently records selected values from status |
|
403
|
|
|
# future versions to support calculated values + others |
|
404
|
|
|
try: |
|
405
|
|
|
self.dbcur.execute("SHOW TABLE STATUS") |
|
406
|
|
|
except Exception as e: |
|
407
|
|
|
self.log.error("MySql: exception getting table status: {0}".format(e)) |
|
408
|
|
|
if not self.connect(): |
|
409
|
|
|
return |
|
410
|
|
|
|
|
411
|
|
|
try: |
|
412
|
|
|
tbl_status_res = self.dbcur.fetchall() |
|
413
|
|
|
except Exception as e: |
|
414
|
|
|
msg = "MySql: exception fetching table status: {0}" |
|
415
|
|
|
self.log.error(msg.format(e)) |
|
416
|
|
|
self.connect() |
|
417
|
|
|
return |
|
418
|
|
|
|
|
419
|
|
|
# add db name as prefix to all metrics |
|
420
|
|
|
db = self.config.get('db') |
|
421
|
|
|
|
|
422
|
|
|
regexp = self.table_status_re |
|
423
|
|
|
# all values recorded as is - todo: gauges and rates |
|
424
|
|
|
for tbl_status_vals in tbl_status_res: |
|
425
|
|
|
tstatus = TableStatus(*list(tbl_status_vals)) |
|
426
|
|
|
if not regexp.match(tstatus.name): |
|
427
|
|
|
continue |
|
428
|
|
|
mfmt_str = "{0}.tables.{1}" |
|
429
|
|
|
table = tstatus.name |
|
430
|
|
|
result.add(plumd.Float(mfmt_str.format(db, table, "rows"), |
|
431
|
|
|
tstatus.rows)) |
|
432
|
|
|
result.add(plumd.Float(mfmt_str.format(db, table, "avg_row_length"), |
|
433
|
|
|
tstatus.avg_row_length)) |
|
434
|
|
|
result.add(plumd.Float(mfmt_str.format(db, table, "data_length"), |
|
435
|
|
|
tstatus.data_length)) |
|
436
|
|
|
result.add(plumd.Float(mfmt_str.format(db, table, "max_data_length"), |
|
437
|
|
|
tstatus.max_data_length)) |
|
438
|
|
|
result.add(plumd.Float(mfmt_str.format(db, table, "index_length"), |
|
439
|
|
|
tstatus.index_length)) |
|
440
|
|
|
result.add(plumd.Float(mfmt_str.format(db, table, "data_free"), |
|
441
|
|
|
tstatus.data_free)) |
|
442
|
|
|
|
s4z /
plumd
