Debian Codegraph @

gmond/python_modules/db/mysql.py

raw
1 """
2 The MIT License
3
4 Copyright (c) 2008 Gilad Raphaelli <[email protected]>
5
6 Permission is hereby granted, free of charge, to any person obtaining a copy
7 of this software and associated documentation files (the "Software"), to deal
8 in the Software without restriction, including without limitation the rights
9 to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
10 copies of the Software, and to permit persons to whom the Software is
11 furnished to do so, subject to the following conditions:
12
13 The above copyright notice and this permission notice shall be included in
14 all copies or substantial portions of the Software.
15
16 THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
17 IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
18 FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
19 AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
20 LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
21 OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN
22 THE SOFTWARE.
23 """
24
25 ### Changelog:
26 ### v1.0.1 - 2010-07-21
27 ### * Initial version
28 ###
29 ### v1.0.2 - 2010-08-04
30 ### * Added system variables: max_connections and query_cache_size
31 ### * Modified some innodb status variables to become deltas
32 ###
33 ### v1.0.3 - 2011-12-02
34 ### * Support custom UNIX sockets
35 ###
36 ### Requires:
37 ### * yum install MySQL-python
38 ### * DBUtil.py
39
40 ### Copyright Jamie Isaacs. 2010
41 ### License to use, modify, and distribute under the GPL
42 ### http://www.gnu.org/licenses/gpl.txt
43
44 import time
45 import MySQLdb
46
47 from DBUtil import parse_innodb_status, defaultdict
48
49 import logging
50
51 descriptors = []
52
53 logging.basicConfig(level=logging.ERROR, format="%(asctime)s - %(name)s - %(levelname)s\t Thread-%(thread)d - %(message)s", filename='/tmp/mysqlstats.log', filemode='w')
54 logging.debug('starting up')
55
56 last_update = 0
57 mysql_conn_opts = {}
58 mysql_stats = {}
59 mysql_stats_last = {}
60 delta_per_second = False
61
62 REPORT_INNODB = True
63 REPORT_MASTER = True
64 REPORT_SLAVE = True
65
66 MAX_UPDATE_TIME = 15
67
68
69 def update_stats(get_innodb=True, get_master=True, get_slave=True):
70 """
71
72 """
73 logging.debug('updating stats')
74 global last_update
75 global mysql_stats, mysql_stats_last
76
77 cur_time = time.time()
78 time_delta = cur_time - last_update
79 if time_delta <= 0:
80 #we went backward in time.
81 logging.debug(" system clock set backwards, probably ntp")
82
83 if cur_time - last_update < MAX_UPDATE_TIME:
84 logging.debug(' wait ' + str(int(MAX_UPDATE_TIME - (cur_time - last_update))) + ' seconds')
85 return True
86 else:
87 last_update = cur_time
88
89 logging.debug('refreshing stats')
90 mysql_stats = {}
91
92 # Get info from DB
93 try:
94 conn = MySQLdb.connect(**mysql_conn_opts)
95
96 cursor = conn.cursor(MySQLdb.cursors.DictCursor)
97 cursor.execute("SELECT GET_LOCK('gmetric-mysql', 0) as ok")
98 lock_stat = cursor.fetchone()
99 cursor.close()
100
101 if lock_stat['ok'] == 0:
102 return False
103
104 cursor = conn.cursor(MySQLdb.cursors.Cursor)
105 cursor.execute("SHOW VARIABLES")
106 #variables = dict(((k.lower(), v) for (k,v) in cursor))
107 variables = {}
108 for (k, v) in cursor:
109 variables[k.lower()] = v
110 cursor.close()
111
112 cursor = conn.cursor(MySQLdb.cursors.Cursor)
113 cursor.execute("SHOW /*!50002 GLOBAL */ STATUS")
114 #global_status = dict(((k.lower(), v) for (k,v) in cursor))
115 global_status = {}
116 for (k, v) in cursor:
117 global_status[k.lower()] = v
118 cursor.close()
119
120 cursor = conn.cursor(MySQLdb.cursors.Cursor)
121 cursor.execute("SELECT PLUGIN_STATUS, PLUGIN_VERSION FROM `information_schema`.Plugins WHERE PLUGIN_NAME LIKE '%innodb%' AND PLUGIN_TYPE LIKE 'STORAGE ENGINE';")
122
123 have_innodb = False
124 innodb_version = 1.0
125 row = cursor.fetchone()
126
127 if row[0] == "ACTIVE":
128 have_innodb = True
129 innodb_version = row[1]
130 cursor.close()
131
132 # try not to fail ?
133 get_innodb = get_innodb and have_innodb
134 get_master = get_master and variables['log_bin'].lower() == 'on'
135
136 innodb_status = defaultdict(int)
137 if get_innodb:
138 cursor = conn.cursor(MySQLdb.cursors.Cursor)
139 cursor.execute("SHOW /*!50000 ENGINE*/ INNODB STATUS")
140 innodb_status = parse_innodb_status(cursor.fetchone()[2].split('\n'), innodb_version)
141 cursor.close()
142 logging.debug('innodb_status: ' + str(innodb_status))
143
144 master_logs = tuple
145 if get_master:
146 cursor = conn.cursor(MySQLdb.cursors.Cursor)
147 cursor.execute("SHOW MASTER LOGS")
148 master_logs = cursor.fetchall()
149 cursor.close()
150
151 slave_status = {}
152 if get_slave:
153 cursor = conn.cursor(MySQLdb.cursors.DictCursor)
154 cursor.execute("SHOW SLAVE STATUS")
155 res = cursor.fetchone()
156 if res:
157 for (k,v) in res.items():
158 slave_status[k.lower()] = v
159 else:
160 get_slave = False
161 cursor.close()
162
163 cursor = conn.cursor(MySQLdb.cursors.DictCursor)
164 cursor.execute("SELECT RELEASE_LOCK('gmetric-mysql') as ok")
165 cursor.close()
166
167 conn.close()
168 except MySQLdb.OperationalError, (errno, errmsg):
169 logging.error('error updating stats')
170 logging.error(errmsg)
171 return False
172
173 # process variables
174 # http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html
175 mysql_stats['version'] = variables['version']
176 mysql_stats['max_connections'] = variables['max_connections']
177 mysql_stats['query_cache_size'] = variables['query_cache_size']
178
179 # process global status
180 # http://dev.mysql.com/doc/refman/5.0/en/server-status-variables.html
181 interesting_global_status_vars = (
182 'aborted_clients',
183 'aborted_connects',
184 'binlog_cache_disk_use',
185 'binlog_cache_use',
186 'bytes_received',
187 'bytes_sent',
188 'com_delete',
189 'com_delete_multi',
190 'com_insert',
191 'com_insert_select',
192 'com_load',
193 'com_replace',
194 'com_replace_select',
195 'com_select',
196 'com_update',
197 'com_update_multi',
198 'connections',
199 'created_tmp_disk_tables',
200 'created_tmp_files',
201 'created_tmp_tables',
202 'key_reads',
203 'key_read_requests',
204 'key_writes',
205 'key_write_requests',
206 'max_used_connections',
207 'open_files',
208 'open_tables',
209 'opened_tables',
210 'qcache_free_blocks',
211 'qcache_free_memory',
212 'qcache_hits',
213 'qcache_inserts',
214 'qcache_lowmem_prunes',
215 'qcache_not_cached',
216 'qcache_queries_in_cache',
217 'qcache_total_blocks',
218 'questions',
219 'select_full_join',
220 'select_full_range_join',
221 'select_range',
222 'select_range_check',
223 'select_scan',
224 'slave_open_temp_tables',
225 'slave_retried_transactions',
226 'slow_launch_threads',
227 'slow_queries',
228 'sort_range',
229 'sort_rows',
230 'sort_scan',
231 'table_locks_immediate',
232 'table_locks_waited',
233 'threads_cached',
234 'threads_connected',
235 'threads_created',
236 'threads_running',
237 'uptime',
238 )
239
240 non_delta = (
241 'max_used_connections',
242 'open_files',
243 'open_tables',
244 'qcache_free_blocks',
245 'qcache_free_memory',
246 'qcache_total_blocks',
247 'slave_open_temp_tables',
248 'threads_cached',
249 'threads_connected',
250 'threads_running',
251 'uptime'
252 )
253
254 # don't put all of global_status in mysql_stats b/c it's so big
255 for key in interesting_global_status_vars:
256 if key in non_delta:
257 mysql_stats[key] = global_status[key]
258 else:
259 # Calculate deltas for counters
260 if time_delta <= 0:
261 #systemclock was set backwards, nog updating values.. to smooth over the graphs
262 pass
263 elif key in mysql_stats_last:
264 if delta_per_second:
265 mysql_stats[key] = (int(global_status[key]) - int(mysql_stats_last[key])) / time_delta
266 else:
267 mysql_stats[key] = int(global_status[key]) - int(mysql_stats_last[key])
268 else:
269 mysql_stats[key] = float(0)
270
271 mysql_stats_last[key] = global_status[key]
272
273 mysql_stats['open_files_used'] = int(global_status['open_files']) / int(variables['open_files_limit'])
274
275 innodb_delta = (
276 'data_fsyncs',
277 'data_reads',
278 'data_writes',
279 'log_writes'
280 )
281
282 # process innodb status
283 if get_innodb:
284 for istat in innodb_status:
285 key = 'innodb_' + istat
286
287 if istat in innodb_delta:
288 # Calculate deltas for counters
289 if time_delta <= 0:
290 #systemclock was set backwards, nog updating values.. to smooth over the graphs
291 pass
292 elif key in mysql_stats_last:
293 if delta_per_second:
294 mysql_stats[key] = (int(innodb_status[istat]) - int(mysql_stats_last[key])) / time_delta
295 else:
296 mysql_stats[key] = int(innodb_status[istat]) - int(mysql_stats_last[key])
297 else:
298 mysql_stats[key] = float(0)
299
300 mysql_stats_last[key] = innodb_status[istat]
301
302 else:
303 mysql_stats[key] = innodb_status[istat]
304
305 # process master logs
306 if get_master:
307 mysql_stats['binlog_count'] = len(master_logs)
308 mysql_stats['binlog_space_current'] = master_logs[-1][1]
309 #mysql_stats['binlog_space_total'] = sum((long(s[1]) for s in master_logs))
310 mysql_stats['binlog_space_total'] = 0
311 for s in master_logs:
312 mysql_stats['binlog_space_total'] += int(s[1])
313 mysql_stats['binlog_space_used'] = float(master_logs[-1][1]) / float(variables['max_binlog_size']) * 100
314
315 # process slave status
316 if get_slave:
317 mysql_stats['slave_exec_master_log_pos'] = slave_status['exec_master_log_pos']
318 #mysql_stats['slave_io'] = 1 if slave_status['slave_io_running'].lower() == "yes" else 0
319 if slave_status['slave_io_running'].lower() == "yes":
320 mysql_stats['slave_io'] = 1
321 else:
322 mysql_stats['slave_io'] = 0
323 #mysql_stats['slave_sql'] = 1 if slave_status['slave_sql_running'].lower() =="yes" else 0
324 if slave_status['slave_sql_running'].lower() == "yes":
325 mysql_stats['slave_sql'] = 1
326 else:
327 mysql_stats['slave_sql'] = 0
328 mysql_stats['slave_lag'] = slave_status['seconds_behind_master']
329 mysql_stats['slave_relay_log_pos'] = slave_status['relay_log_pos']
330 mysql_stats['slave_relay_log_space'] = slave_status['relay_log_space']
331
332 logging.debug('success updating stats')
333 logging.debug('mysql_stats: ' + str(mysql_stats))
334
335
336 def get_stat(name):
337 logging.info("getting stat: %s" % name)
338 global mysql_stats
339 #logging.debug(mysql_stats)
340
341 global REPORT_INNODB
342 global REPORT_MASTER
343 global REPORT_SLAVE
344
345 ret = update_stats(REPORT_INNODB, REPORT_MASTER, REPORT_SLAVE)
346
347 if ret:
348 if name.startswith('mysql_'):
349 label = name[6:]
350 else:
351 label = name
352
353 logging.debug("fetching %s" % name)
354 try:
355 return mysql_stats[label]
356 except:
357 logging.error("failed to fetch %s" % name)
358 return 0
359 else:
360 return 0
361
362 def metric_init(params):
363 global descriptors
364 global mysql_conn_opts
365 global mysql_stats
366 global delta_per_second
367
368 global REPORT_INNODB
369 global REPORT_MASTER
370 global REPORT_SLAVE
371
372 REPORT_INNODB = str(params.get('get_innodb', True)) == "True"
373 REPORT_MASTER = str(params.get('get_master', True)) == "True"
374 REPORT_SLAVE = str(params.get('get_slave', True)) == "True"
375
376 logging.debug("init: " + str(params))
377
378 mysql_conn_opts = dict(
379 host = params.get('host', 'localhost'),
380 user = params.get('user'),
381 passwd = params.get('passwd'),
382 port = int(params.get('port', 3306)),
383 connect_timeout = int(params.get('timeout', 30)),
384 )
385 if params.get('unix_socket', '') != '':
386 mysql_conn_opts['unix_socket'] = params.get('unix_socket')
387
388 if params.get("delta_per_second", '') != '':
389 delta_per_second = True
390
391 master_stats_descriptions = {}
392 innodb_stats_descriptions = {}
393 slave_stats_descriptions = {}
394
395 misc_stats_descriptions = dict(
396 aborted_clients = {
397 'description': 'The number of connections that were aborted because the client died without closing the connection properly',
398 'value_type': 'float',
399 'units': 'clients',
400 },
401
402 aborted_connects = {
403 'description': 'The number of failed attempts to connect to the MySQL server',
404 'value_type': 'float',
405 'units': 'conns',
406 },
407
408 binlog_cache_disk_use = {
409 'description': 'The number of transactions that used the temporary binary log cache but that exceeded the value of binlog_cache_size and used a temporary file to store statements from the transaction',
410 'value_type': 'float',
411 'units': 'txns',
412 },
413
414 binlog_cache_use = {
415 'description': ' The number of transactions that used the temporary binary log cache',
416 'value_type': 'float',
417 'units': 'txns',
418 },
419
420 bytes_received = {
421 'description': 'The number of bytes received from all clients',
422 'value_type': 'float',
423 'units': 'bytes',
424 },
425
426 bytes_sent = {
427 'description': ' The number of bytes sent to all clients',
428 'value_type': 'float',
429 'units': 'bytes',
430 },
431
432 com_delete = {
433 'description': 'The number of DELETE statements',
434 'value_type': 'float',
435 'units': 'stmts',
436 },
437
438 com_delete_multi = {
439 'description': 'The number of multi-table DELETE statements',
440 'value_type': 'float',
441 'units': 'stmts',
442 },
443
444 com_insert = {
445 'description': 'The number of INSERT statements',
446 'value_type': 'float',
447 'units': 'stmts',
448 },
449
450 com_insert_select = {
451 'description': 'The number of INSERT ... SELECT statements',
452 'value_type': 'float',
453 'units': 'stmts',
454 },
455
456 com_load = {
457 'description': 'The number of LOAD statements',
458 'value_type': 'float',
459 'units': 'stmts',
460 },
461
462 com_replace = {
463 'description': 'The number of REPLACE statements',
464 'value_type': 'float',
465 'units': 'stmts',
466 },
467
468 com_replace_select = {
469 'description': 'The number of REPLACE ... SELECT statements',
470 'value_type': 'float',
471 'units': 'stmts',
472 },
473
474 com_select = {
475 'description': 'The number of SELECT statements',
476 'value_type': 'float',
477 'units': 'stmts',
478 },
479
480 com_update = {
481 'description': 'The number of UPDATE statements',
482 'value_type': 'float',
483 'units': 'stmts',
484 },
485
486 com_update_multi = {
487 'description': 'The number of multi-table UPDATE statements',
488 'value_type': 'float',
489 'units': 'stmts',
490 },
491
492 connections = {
493 'description': 'The number of connection attempts (successful or not) to the MySQL server',
494 'value_type': 'float',
495 'units': 'conns',
496 },
497
498 created_tmp_disk_tables = {
499 'description': 'The number of temporary tables on disk created automatically by the server while executing statements',
500 'value_type': 'float',
501 'units': 'tables',
502 },
503
504 created_tmp_files = {
505 'description': 'The number of temporary files mysqld has created',
506 'value_type': 'float',
507 'units': 'files',
508 },
509
510 created_tmp_tables = {
511 'description': 'The number of in-memory temporary tables created automatically by the server while executing statement',
512 'value_type': 'float',
513 'units': 'tables',
514 },
515
516 #TODO in graphs: key_read_cache_miss_rate = key_reads / key_read_requests
517
518 key_read_requests = {
519 'description': 'The number of requests to read a key block from the cache',
520 'value_type': 'float',
521 'units': 'reqs',
522 },
523
524 key_reads = {
525 'description': 'The number of physical reads of a key block from disk',
526 'value_type': 'float',
527 'units': 'reads',
528 },
529
530 key_write_requests = {
531 'description': 'The number of requests to write a key block to the cache',
532 'value_type': 'float',
533 'units': 'reqs',
534 },
535
536 key_writes = {
537 'description': 'The number of physical writes of a key block to disk',
538 'value_type': 'float',
539 'units': 'writes',
540 },
541
542 max_used_connections = {
543 'description': 'The maximum number of connections that have been in use simultaneously since the server started',
544 'units': 'conns',
545 'slope': 'both',
546 },
547
548 open_files = {
549 'description': 'The number of files that are open',
550 'units': 'files',
551 'slope': 'both',
552 },
553
554 open_tables = {
555 'description': 'The number of tables that are open',
556 'units': 'tables',
557 'slope': 'both',
558 },
559
560 # If Opened_tables is big, your table_cache value is probably too small.
561 opened_tables = {
562 'description': 'The number of tables that have been opened',
563 'value_type': 'float',
564 'units': 'tables',
565 },
566
567 qcache_free_blocks = {
568 'description': 'The number of free memory blocks in the query cache',
569 'units': 'blocks',
570 'slope': 'both',
571 },
572
573 qcache_free_memory = {
574 'description': 'The amount of free memory for the query cache',
575 'units': 'bytes',
576 'slope': 'both',
577 },
578
579 qcache_hits = {
580 'description': 'The number of query cache hits',
581 'value_type': 'float',
582 'units': 'hits',
583 },
584
585 qcache_inserts = {
586 'description': 'The number of queries added to the query cache',
587 'value_type': 'float',
588 'units': 'queries',
589 },
590
591 qcache_lowmem_prunes = {
592 'description': 'The number of queries that were deleted from the query cache because of low memory',
593 'value_type': 'float',
594 'units': 'queries',
595 },
596
597 qcache_not_cached = {
598 'description': 'The number of non-cached queries (not cacheable, or not cached due to the query_cache_type setting)',
599 'value_type': 'float',
600 'units': 'queries',
601 },
602
603 qcache_queries_in_cache = {
604 'description': 'The number of queries registered in the query cache',
605 'value_type': 'float',
606 'units': 'queries',
607 },
608
609 qcache_total_blocks = {
610 'description': 'The total number of blocks in the query cache',
611 'units': 'blocks',
612 },
613
614 questions = {
615 'description': 'The number of statements that clients have sent to the server',
616 'value_type': 'float',
617 'units': 'stmts',
618 },
619
620 # If this value is not 0, you should carefully check the indexes of your tables.
621 select_full_join = {
622 'description': 'The number of joins that perform table scans because they do not use indexes',
623 'value_type': 'float',
624 'units': 'joins',
625 },
626
627 select_full_range_join = {
628 'description': 'The number of joins that used a range search on a reference table',
629 'value_type': 'float',
630 'units': 'joins',
631 },
632
633 select_range = {
634 'description': 'The number of joins that used ranges on the first table',
635 'value_type': 'float',
636 'units': 'joins',
637 },
638
639 # If this is not 0, you should carefully check the indexes of your tables.
640 select_range_check = {
641 'description': 'The number of joins without keys that check for key usage after each row',
642 'value_type': 'float',
643 'units': 'joins',
644 },
645
646 select_scan = {
647 'description': 'The number of joins that did a full scan of the first table',
648 'value_type': 'float',
649 'units': 'joins',
650 },
651
652 slave_open_temp_tables = {
653 'description': 'The number of temporary tables that the slave SQL thread currently has open',
654 'value_type': 'float',
655 'units': 'tables',
656 'slope': 'both',
657 },
658
659 slave_retried_transactions = {
660 'description': 'The total number of times since startup that the replication slave SQL thread has retried transactions',
661 'value_type': 'float',
662 'units': 'count',
663 },
664
665 slow_launch_threads = {
666 'description': 'The number of threads that have taken more than slow_launch_time seconds to create',
667 'value_type': 'float',
668 'units': 'threads',
669 },
670
671 slow_queries = {
672 'description': 'The number of queries that have taken more than long_query_time seconds',
673 'value_type': 'float',
674 'units': 'queries',
675 },
676
677 sort_range = {
678 'description': 'The number of sorts that were done using ranges',
679 'value_type': 'float',
680 'units': 'sorts',
681 },
682
683 sort_rows = {
684 'description': 'The number of sorted rows',
685 'value_type': 'float',
686 'units': 'rows',
687 },
688
689 sort_scan = {
690 'description': 'The number of sorts that were done by scanning the table',
691 'value_type': 'float',
692 'units': 'sorts',
693 },
694
695 table_locks_immediate = {
696 'description': 'The number of times that a request for a table lock could be granted immediately',
697 'value_type': 'float',
698 'units': 'count',
699 },
700
701 # If this is high and you have performance problems, you should first optimize your queries, and then either split your table or tables or use replication.
702 table_locks_waited = {
703 'description': 'The number of times that a request for a table lock could not be granted immediately and a wait was needed',
704 'value_type': 'float',
705 'units': 'count',
706 },
707
708 threads_cached = {
709 'description': 'The number of threads in the thread cache',
710 'units': 'threads',
711 'slope': 'both',
712 },
713
714 threads_connected = {
715 'description': 'The number of currently open connections',
716 'units': 'threads',
717 'slope': 'both',
718 },
719
720 #TODO in graphs: The cache miss rate can be calculated as Threads_created/Connections
721
722 # Threads_created is big, you may want to increase the thread_cache_size value.
723 threads_created = {
724 'description': 'The number of threads created to handle connections',
725 'value_type': 'float',
726 'units': 'threads',
727 },
728
729 threads_running = {
730 'description': 'The number of threads that are not sleeping',
731 'units': 'threads',
732 'slope': 'both',
733 },
734
735 uptime = {
736 'description': 'The number of seconds that the server has been up',
737 'units': 'secs',
738 'slope': 'both',
739 },
740
741 version = {
742 'description': "MySQL Version",
743 'value_type': 'string',
744 'format': '%s',
745 },
746
747 max_connections = {
748 'description': "The maximum permitted number of simultaneous client connections",
749 'slope': 'zero',
750 },
751
752 query_cache_size = {
753 'description': "The amount of memory allocated for caching query results",
754 'slope': 'zero',
755 }
756 )
757
758 if REPORT_MASTER:
759 master_stats_descriptions = dict(
760 binlog_count = {
761 'description': "Number of binary logs",
762 'units': 'logs',
763 'slope': 'both',
764 },
765
766 binlog_space_current = {
767 'description': "Size of current binary log",
768 'units': 'bytes',
769 'slope': 'both',
770 },
771
772 binlog_space_total = {
773 'description': "Total space used by binary logs",
774 'units': 'bytes',
775 'slope': 'both',
776 },
777
778 binlog_space_used = {
779 'description': "Current binary log size / max_binlog_size",
780 'value_type': 'float',
781 'units': 'percent',
782 'slope': 'both',
783 },
784 )
785
786 if REPORT_SLAVE:
787 slave_stats_descriptions = dict(
788 slave_exec_master_log_pos = {
789 'description': "The position of the last event executed by the SQL thread from the master's binary log",
790 'units': 'bytes',
791 'slope': 'both',
792 },
793
794 slave_io = {
795 'description': "Whether the I/O thread is started and has connected successfully to the master",
796 'value_type': 'uint8',
797 'units': 'True/False',
798 'slope': 'both',
799 },
800
801 slave_lag = {
802 'description': "Replication Lag",
803 'units': 'secs',
804 'slope': 'both',
805 },
806
807 slave_relay_log_pos = {
808 'description': "The position up to which the SQL thread has read and executed in the current relay log",
809 'units': 'bytes',
810 'slope': 'both',
811 },
812
813 slave_sql = {
814 'description': "Slave SQL Running",
815 'value_type': 'uint8',
816 'units': 'True/False',
817 'slope': 'both',
818 },
819 )
820
821 if REPORT_INNODB:
822 innodb_stats_descriptions = dict(
823 innodb_active_transactions = {
824 'description': "Active InnoDB transactions",
825 'value_type':'uint',
826 'units': 'txns',
827 'slope': 'both',
828 },
829
830 innodb_current_transactions = {
831 'description': "Current InnoDB transactions",
832 'value_type':'uint',
833 'units': 'txns',
834 'slope': 'both',
835 },
836
837 innodb_buffer_pool_pages_data = {
838 'description': "InnoDB",
839 'value_type':'uint',
840 'units': 'pages',
841 },
842
843 innodb_data_fsyncs = {
844 'description': "The number of fsync() operations",
845 'value_type':'float',
846 'units': 'fsyncs',
847 },
848
849 innodb_data_reads = {
850 'description': "The number of data reads",
851 'value_type':'float',
852 'units': 'reads',
853 },
854
855 innodb_data_writes = {
856 'description': "The number of data writes",
857 'value_type':'float',
858 'units': 'writes',
859 },
860
861 innodb_buffer_pool_pages_free = {
862 'description': "InnoDB",
863 'value_type':'uint',
864 'units': 'pages',
865 'slope': 'both',
866 },
867
868 innodb_history_list = {
869 'description': "InnoDB",
870 'units': 'length',
871 'slope': 'both',
872 },
873
874 innodb_ibuf_inserts = {
875 'description': "InnoDB",
876 'value_type':'uint',
877 'units': 'inserts',
878 },
879
880 innodb_ibuf_merged = {
881 'description': "InnoDB",
882 'value_type':'uint',
883 'units': 'recs',
884 },
885
886 innodb_ibuf_merges = {
887 'description': "InnoDB",
888 'value_type':'uint',
889 'units': 'merges',
890 },
891
892 innodb_log_bytes_flushed = {
893 'description': "InnoDB",
894 'value_type':'uint',
895 'units': 'bytes',
896 },
897
898 innodb_log_bytes_unflushed = {
899 'description': "InnoDB",
900 'value_type':'uint',
901 'units': 'bytes',
902 'slope': 'both',
903 },
904
905 innodb_log_bytes_written = {
906 'description': "InnoDB",
907 'value_type':'uint',
908 'units': 'bytes',
909 },
910
911 innodb_log_writes = {
912 'description': "The number of physical writes to the log file",
913 'value_type':'float',
914 'units': 'writes',
915 },
916
917 innodb_buffer_pool_pages_dirty = {
918 'description': "InnoDB",
919 'value_type':'uint',
920 'units': 'pages',
921 'slope': 'both',
922 },
923
924 innodb_os_waits = {
925 'description': "InnoDB",
926 'value_type':'uint',
927 'units': 'waits',
928 },
929
930 innodb_pages_created = {
931 'description': "InnoDB",
932 'value_type':'uint',
933 'units': 'pages',
934 },
935
936 innodb_pages_read = {
937 'description': "InnoDB",
938 'value_type':'uint',
939 'units': 'pages',
940 },
941
942 innodb_pages_written = {
943 'description': "InnoDB",
944 'value_type':'uint',
945 'units': 'pages',
946 },
947
948 innodb_pending_aio_log_ios = {
949 'description': "InnoDB",
950 'value_type':'uint',
951 'units': 'ops',
952 },
953
954 innodb_pending_aio_sync_ios = {
955 'description': "InnoDB",
956 'value_type':'uint',
957 'units': 'ops',
958 },
959
960 innodb_pending_buffer_pool_flushes = {
961 'description': "The number of pending buffer pool page-flush requests",
962 'value_type':'uint',
963 'units': 'reqs',
964 'slope': 'both',
965 },
966
967 innodb_pending_chkp_writes = {
968 'description': "InnoDB",
969 'value_type':'uint',
970 'units': 'writes',
971 },
972
973 innodb_pending_ibuf_aio_reads = {
974 'description': "InnoDB",
975 'value_type':'uint',
976 'units': 'reads',
977 },
978
979 innodb_pending_log_flushes = {
980 'description': "InnoDB",
981 'value_type':'uint',
982 'units': 'reqs',
983 },
984
985 innodb_pending_log_writes = {
986 'description': "InnoDB",
987 'value_type':'uint',
988 'units': 'writes',
989 },
990
991 innodb_pending_normal_aio_reads = {
992 'description': "InnoDB",
993 'value_type':'uint',
994 'units': 'reads',
995 },
996
997 innodb_pending_normal_aio_writes = {
998 'description': "InnoDB",
999 'value_type':'uint',
1000 'units': 'writes',
1001 },
1002
1003 innodb_buffer_pool_pages_bytes = {
1004 'description': "The total size of buffer pool, in bytes",
1005 'value_type':'uint',
1006 'units': 'bytes',
1007 'slope': 'both',
1008 },
1009
1010 innodb_buffer_pool_pages_total = {
1011 'description': "The total size of buffer pool, in pages",
1012 'value_type':'uint',
1013 'units': 'pages',
1014 'slope': 'both',
1015 },
1016
1017 innodb_queries_inside = {
1018 'description': "InnoDB",
1019 'value_type':'uint',
1020 'units': 'queries',
1021 },
1022
1023 innodb_queries_queued = {
1024 'description': "InnoDB",
1025 'value_type':'uint',
1026 'units': 'queries',
1027 'slope': 'both',
1028 },
1029
1030 innodb_read_views = {
1031 'description': "InnoDB",
1032 'value_type':'uint',
1033 'units': 'views',
1034 },
1035
1036 innodb_rows_deleted = {
1037 'description': "InnoDB",
1038 'value_type':'uint',
1039 'units': 'rows',
1040 },
1041
1042 innodb_rows_inserted = {
1043 'description': "InnoDB",
1044 'value_type':'uint',
1045 'units': 'rows',
1046 },
1047
1048 innodb_rows_read = {
1049 'description': "InnoDB",
1050 'value_type':'uint',
1051 'units': 'rows',
1052 },
1053
1054 innodb_rows_updated = {
1055 'description': "InnoDB",
1056 'value_type':'uint',
1057 'units': 'rows',
1058 },
1059
1060 innodb_spin_rounds = {
1061 'description': "InnoDB",
1062 'value_type':'uint',
1063 'units': 'spins',
1064 'slope': 'both',
1065 },
1066
1067 innodb_spin_waits = {
1068 'description': "InnoDB",
1069 'value_type':'uint',
1070 'units': 'spins',
1071 'slope': 'both',
1072 },
1073
1074 innodb_transactions = {
1075 'description': "InnoDB",
1076 'value_type':'uint',
1077 'units': 'txns',
1078 },
1079
1080 innodb_transactions_purged = {
1081 'description': "InnoDB",
1082 'value_type':'uint',
1083 'units': 'txns',
1084 },
1085
1086 innodb_transactions_unpurged = {
1087 'description': "InnoDB",
1088 'value_type':'uint',
1089 'units': 'txns',
1090 },
1091 )
1092
1093 update_stats(REPORT_INNODB, REPORT_MASTER, REPORT_SLAVE)
1094
1095 time.sleep(MAX_UPDATE_TIME)
1096 update_stats(REPORT_INNODB, REPORT_MASTER, REPORT_SLAVE)
1097
1098 for stats_descriptions in (innodb_stats_descriptions, master_stats_descriptions, misc_stats_descriptions, slave_stats_descriptions):
1099 for label in stats_descriptions:
1100 if mysql_stats.has_key(label):
1101 format = '%u'
1102 if stats_descriptions[label].has_key('value_type'):
1103 if stats_descriptions[label]['value_type'] == "float":
1104 format = '%f'
1105
1106 d = {
1107 'name': 'mysql_' + label,
1108 'call_back': get_stat,
1109 'time_max': 60,
1110 'value_type': "uint",
1111 'units': "",
1112 'slope': "both",
1113 'format': format,
1114 'description': "http://search.mysql.com/search?q=" + label,
1115 'groups': 'mysql',
1116 }
1117
1118 d.update(stats_descriptions[label])
1119
1120 descriptors.append(d)
1121
1122 else:
1123 logging.error("skipped " + label)
1124
1125 #logging.debug(str(descriptors))
1126 return descriptors
1127
1128
1129 def metric_cleanup():
1130 logging.shutdown()
1131 # pass
1132
1133 if __name__ == '__main__':
1134 from optparse import OptionParser
1135 import os
1136
1137 logging.debug('running from cmd line')
1138 parser = OptionParser()
1139 parser.add_option("-H", "--Host", dest="host", help="Host running mysql", default="localhost")
1140 parser.add_option("-u", "--user", dest="user", help="user to connect as", default="")
1141 parser.add_option("-p", "--password", dest="passwd", help="password", default="")
1142 parser.add_option("-P", "--port", dest="port", help="port", default=3306, type="int")
1143 parser.add_option("-S", "--socket", dest="unix_socket", help="unix_socket", default="")
1144 parser.add_option("--no-innodb", dest="get_innodb", action="store_false", default=True)
1145 parser.add_option("--no-master", dest="get_master", action="store_false", default=True)
1146 parser.add_option("--no-slave", dest="get_slave", action="store_false", default=True)
1147 parser.add_option("-b", "--gmetric-bin", dest="gmetric_bin", help="path to gmetric binary", default="/usr/bin/gmetric")
1148 parser.add_option("-c", "--gmond-conf", dest="gmond_conf", help="path to gmond.conf", default="/etc/ganglia/gmond.conf")
1149 parser.add_option("-g", "--gmetric", dest="gmetric", help="submit via gmetric", action="store_true", default=False)
1150 parser.add_option("-q", "--quiet", dest="quiet", action="store_true", default=False)
1151
1152 (options, args) = parser.parse_args()
1153
1154 metric_init({
1155 'host': options.host,
1156 'passwd': options.passwd,
1157 'user': options.user,
1158 'port': options.port,
1159 'get_innodb': options.get_innodb,
1160 'get_master': options.get_master,
1161 'get_slave': options.get_slave,
1162 'unix_socket': options.unix_socket,
1163 })
1164
1165 for d in descriptors:
1166 v = d['call_back'](d['name'])
1167 if not options.quiet:
1168 print ' %s: %s %s [%s]' % (d['name'], v, d['units'], d['description'])
1169
1170 if options.gmetric:
1171 if d['value_type'] == 'uint':
1172 value_type = 'uint32'
1173 else:
1174 value_type = d['value_type']
1175
1176 cmd = "%s --conf=%s --value='%s' --units='%s' --type='%s' --name='%s' --slope='%s'" % \
1177 (options.gmetric_bin, options.gmond_conf, v, d['units'], value_type, d['name'], d['slope'])
1178 os.system(cmd)