diff options
Diffstat (limited to 'collectors/python.d.plugin/mysql')
| -rw-r--r-- | collectors/python.d.plugin/mysql/README.md | 496 | ||||
| -rw-r--r-- | collectors/python.d.plugin/mysql/mysql.chart.py | 165 |
2 files changed, 385 insertions, 276 deletions
diff --git a/collectors/python.d.plugin/mysql/README.md b/collectors/python.d.plugin/mysql/README.md index f7028ab68..45f842d42 100644 --- a/collectors/python.d.plugin/mysql/README.md +++ b/collectors/python.d.plugin/mysql/README.md @@ -3,255 +3,305 @@ Module monitors one or more mysql servers **Requirements:** - * python library [MySQLdb](https://github.com/PyMySQL/mysqlclient-python) (faster) or [PyMySQL](https://github.com/PyMySQL/PyMySQL) (slower) + +- python library [MySQLdb](https://github.com/PyMySQL/mysqlclient-python) (faster) or [PyMySQL](https://github.com/PyMySQL/PyMySQL) (slower) It will produce following charts (if data is available): -1. **Bandwidth** in kilobits/s - * in - * out - -2. **Queries** in queries/sec - * queries - * questions - * slow queries - -3. **Queries By Type** in queries/s - * select - * delete - * update - * insert - * cache hits - * replace - -4. **Handlerse** in handlers/s - * commit - * delete - * prepare - * read first - * read key - * read next - * read prev - * read rnd - * read rnd next - * rollback - * savepoint - * savepoint rollback - * update - * write - -4. **Table Locks** in locks/s - * immediate - * waited - -5. **Table Select Join Issuess** in joins/s - * full join - * full range join - * range - * range check - * scan - -6. **Table Sort Issuess** in joins/s - * merge passes - * range - * scan - -7. **Tmp Operations** in created/s - * disk tables - * files - * tables - -8. **Connections** in connections/s - * all - * aborted - -9. **Connections Active** in connections/s - * active - * limit - * max active - -10. **Binlog Cache** in threads - * disk - * all - -11. **Threads** in transactions/s - * connected - * cached - * running - -12. **Threads Creation Rate** in threads/s - * created - -13. **Threads Cache Misses** in misses - * misses - -14. **InnoDB I/O Bandwidth** in KiB/s - * read - * write - -15. **InnoDB I/O Operations** in operations/s - * reads - * writes - * fsyncs - -16. **InnoDB Pending I/O Operations** in operations/s - * reads - * writes - * fsyncs - -17. **InnoDB Log Operations** in operations/s - * waits - * write requests - * writes - -18. **InnoDB OS Log Pending Operations** in operations - * fsyncs - * writes - -19. **InnoDB OS Log Operations** in operations/s - * fsyncs - -20. **InnoDB OS Log Bandwidth** in KiB/s - * write - -21. **InnoDB Current Row Locks** in operations - * current waits - -22. **InnoDB Row Operations** in operations/s - * inserted - * read - * updated - * deleted - -23. **InnoDB Buffer Pool Pagess** in pages - * data - * dirty - * free - * misc - * total - -24. **InnoDB Buffer Pool Flush Pages Requests** in requests/s - * flush pages - -25. **InnoDB Buffer Pool Bytes** in MiB - * data - * dirty - -26. **InnoDB Buffer Pool Operations** in operations/s - * disk reads - * wait free - -27. **QCache Operations** in queries/s - * hits - * lowmem prunes - * inserts - * no caches - -28. **QCache Queries in Cache** in queries - * queries - -29. **QCache Free Memory** in MiB - * free - -30. **QCache Memory Blocks** in blocks - * free - * total - -31. **MyISAM Key Cache Blocks** in blocks - * unused - * used - * not flushed - -32. **MyISAM Key Cache Requests** in requests/s - * reads - * writes +1. **Bandwidth** in kilobits/s + + - in + - out + +2. **Queries** in queries/sec + + - queries + - questions + - slow queries + +3. **Queries By Type** in queries/s + + - select + - delete + - update + - insert + - cache hits + - replace + +4. **Handlerse** in handlers/s + + - commit + - delete + - prepare + - read first + - read key + - read next + - read prev + - read rnd + - read rnd next + - rollback + - savepoint + - savepoint rollback + - update + - write + +5. **Table Locks** in locks/s + + - immediate + - waited + +6. **Table Select Join Issuess** in joins/s + + - full join + - full range join + - range + - range check + - scan + +7. **Table Sort Issuess** in joins/s + + - merge passes + - range + - scan + +8. **Tmp Operations** in created/s + + - disk tables + - files + - tables + +9. **Connections** in connections/s + + - all + - aborted + +10. **Connections Active** in connections/s + + - active + - limit + - max active + +11. **Binlog Cache** in threads + + - disk + - all + +12. **Threads** in transactions/s + + - connected + - cached + - running + +13. **Threads Creation Rate** in threads/s + + - created + +14. **Threads Cache Misses** in misses + + - misses + +15. **InnoDB I/O Bandwidth** in KiB/s + + - read + - write + +16. **InnoDB I/O Operations** in operations/s + + - reads + - writes + - fsyncs + +17. **InnoDB Pending I/O Operations** in operations/s + + - reads + - writes + - fsyncs + +18. **InnoDB Log Operations** in operations/s + + - waits + - write requests + - writes + +19. **InnoDB OS Log Pending Operations** in operations + + - fsyncs + - writes + +20. **InnoDB OS Log Operations** in operations/s + + - fsyncs + +21. **InnoDB OS Log Bandwidth** in KiB/s + + - write + +22. **InnoDB Current Row Locks** in operations + + - current waits + +23. **InnoDB Row Operations** in operations/s + + - inserted + - read + - updated + - deleted + +24. **InnoDB Buffer Pool Pagess** in pages + + - data + - dirty + - free + - misc + - total + +25. **InnoDB Buffer Pool Flush Pages Requests** in requests/s + + - flush pages + +26. **InnoDB Buffer Pool Bytes** in MiB + + - data + - dirty + +27. **InnoDB Buffer Pool Operations** in operations/s + + - disk reads + - wait free + +28. **QCache Operations** in queries/s + + - hits + - lowmem prunes + - inserts + - no caches + +29. **QCache Queries in Cache** in queries + + - queries + +30. **QCache Free Memory** in MiB + + - free + +31. **QCache Memory Blocks** in blocks + + - free + - total + +32. **MyISAM Key Cache Blocks** in blocks + + - unused + - used + - not flushed 33. **MyISAM Key Cache Requests** in requests/s - * reads - * writes -34. **MyISAM Key Cache Disk Operations** in operations/s - * reads - * writes + - reads + - writes + +34. **MyISAM Key Cache Requests** in requests/s + + - reads + - writes + +35. **MyISAM Key Cache Disk Operations** in operations/s + + - reads + - writes + +36. **Open Files** in files + + - files + +37. **Opened Files Rate** in files/s + + - files -35. **Open Files** in files - * files +38. **Binlog Statement Cache** in statements/s -36. **Opened Files Rate** in files/s - * files + - disk + - all -37. **Binlog Statement Cache** in statements/s - * disk - * all +39. **Connection Errors** in errors/s -38. **Connection Errors** in errors/s - * accept - * internal - * max - * peer addr - * select - * tcpwrap + - accept + - internal + - max + - peer addr + - select + - tcpwrap -39. **Slave Behind Seconds** in seconds - * time +40. **Slave Behind Seconds** in seconds -40. **I/O / SQL Thread Running State** in bool - * sql - * io + - time -41. **Replicated Writesets** in writesets/s - * rx - * tx +41. **I/O / SQL Thread Running State** in bool -42. **Replicated Bytes** in KiB/s - * rx - * tx + - sql + - io -43. **Galera Queue** in writesets - * rx - * tx +42. **Replicated Writesets** in writesets/s -44. **Replication Conflicts** in transactions - * bf aborts - * cert fails + - rx + - tx -45. **Flow Control** in ms - * paused +43. **Replicated Bytes** in KiB/s -46. **Users CPU time** in percentage - * users + - rx + - tx + +44. **Galera Queue** in writesets + + - rx + - tx + +45. **Replication Conflicts** in transactions + + - bf aborts + - cert fails + +46. **Flow Control** in ms + + - paused + +47. **Users CPU time** in percentage + + - users **Per user statistics:** -1. **Rows Operations** in operations/s - * read - * send - * updated - * inserted - * deleted +1. **Rows Operations** in operations/s -2. **Commands** in commands/s - * select - * update - * other + - read + - send + - updated + - inserted + - deleted +2. **Commands** in commands/s -### configuration + - select + - update + - other + +## configuration You can provide, per server, the following: -1. username which have access to database (defaults to 'root') -2. password (defaults to none) -3. mysql my.cnf configuration file -4. mysql socket (optional) -5. mysql host (ip or hostname) -6. mysql port (defaults to 3306) -7. ssl connection parameters - - key: the path name of the client private key file. - - cert: the path name of the client public key certificate file. - - ca: the path name of the Certificate Authority (CA) certificate file. This option, if used, must specify the same certificate used by the server. - - capath: the path name of the directory that contains trusted SSL CA certificate files. - - cipher: the list of permitted ciphers for SSL encryption. +1. username which have access to database (defaults to 'root') +2. password (defaults to none) +3. mysql my.cnf configuration file +4. mysql socket (optional) +5. mysql host (ip or hostname) +6. mysql port (defaults to 3306) +7. ssl connection parameters + + - key: the path name of the client private key file. + - cert: the path name of the client public key certificate file. + - ca: the path name of the Certificate Authority (CA) certificate file. This option, if used, must specify the same certificate used by the server. + - capath: the path name of the directory that contains trusted SSL CA certificate files. + - cipher: the list of permitted ciphers for SSL encryption. Here is an example for 3 servers: @@ -282,4 +332,4 @@ If no configuration is given, module will attempt to connect to mysql server via --- -[]() +[](<>) diff --git a/collectors/python.d.plugin/mysql/mysql.chart.py b/collectors/python.d.plugin/mysql/mysql.chart.py index 82bd90794..46d0712fb 100644 --- a/collectors/python.d.plugin/mysql/mysql.chart.py +++ b/collectors/python.d.plugin/mysql/mysql.chart.py @@ -601,6 +601,33 @@ CHARTS = { } +def slave_status_chart_template(channel_name): + order = [ + 'slave_behind_{0}'.format(channel_name), + 'slave_status_{0}'.format(channel_name) + ] + + charts = { + order[0]: { + 'options': [None, 'Slave Behind Seconds Channel {0}'.format(channel_name), + 'seconds', 'slave', 'mysql.slave_behind', 'line'], + 'lines': [ + ['Seconds_Behind_Master_{0}'.format(channel_name), 'seconds', 'absolute'] + ] + }, + order[1]: { + 'options': [None, 'Slave Status Channel {0}'.format(channel_name), + 'status', 'slave', 'mysql.slave_status', 'line'], + 'lines': [ + ['Slave_SQL_Running_{0}'.format(channel_name), 'sql_running', 'absolute'], + ['Slave_IO_Running_{0}'.format(channel_name), 'io_running', 'absolute'] + ] + }, + } + + return order, charts + + def userstats_chart_template(name): order = [ 'userstats_rows_{0}'.format(name), @@ -632,6 +659,10 @@ def userstats_chart_template(name): return order, charts +# https://dev.mysql.com/doc/refman/8.0/en/replication-channels.html +DEFAULT_REPL_CHANNEL = '' + + class Service(MySQLService): def __init__(self, configuration=None, name=None): MySQLService.__init__(self, configuration=configuration, name=name) @@ -643,6 +674,7 @@ class Service(MySQLService): variables=QUERY_VARIABLES, user_statistics=QUERY_USER_STATISTICS, ) + self.repl_channels = [DEFAULT_REPL_CHANNEL] def _get_data(self): @@ -651,29 +683,24 @@ class Service(MySQLService): if not raw_data: return None - to_netdata = dict() + data = dict() if 'global_status' in raw_data: global_status = dict(raw_data['global_status'][0]) for key in GLOBAL_STATS: if key in global_status: - to_netdata[key] = global_status[key] - if 'Threads_created' in to_netdata and 'Connections' in to_netdata: - to_netdata['Thread_cache_misses'] = round(int(to_netdata['Threads_created']) - / float(to_netdata['Connections']) * 10000) + data[key] = global_status[key] + if 'Threads_created' in data and 'Connections' in data: + data['Thread_cache_misses'] = round(int(data['Threads_created']) / float(data['Connections']) * 10000) if 'slave_status' in raw_data: - if raw_data['slave_status'][0]: - slave_raw_data = dict(zip([e[0] for e in raw_data['slave_status'][1]], raw_data['slave_status'][0][0])) - for key, func in SLAVE_STATS: - if key in slave_raw_data: - to_netdata[key] = func(slave_raw_data[key]) - else: - self.queries.pop('slave_status') + status = self.get_slave_status(raw_data['slave_status']) + if status: + data.update(status) if 'user_statistics' in raw_data: if raw_data['user_statistics'][0]: - to_netdata.update(self.get_userstats(raw_data)) + data.update(self.get_userstats(raw_data)) else: self.queries.pop('user_statistics') @@ -681,46 +708,75 @@ class Service(MySQLService): variables = dict(raw_data['variables'][0]) for key in VARIABLES: if key in variables: - to_netdata[key] = variables[key] - - return to_netdata or None - - # raw_data['user_statistics'] contains the following data structure: - # ( - # ( - # ('netdata', 42L, 0L, 1264L, 3.111252999999968, 2.968510299999994, 110267L, 19741424L, 0L, 0L, 1265L, 0L, - # 0L, 0L, 3L, 0L, 1301L, 0L, 0L, 7633L, 0L, 83L, 44L, 0L, 0L), - # ('root', 60L, 0L, 184L, 0.22856499999999966, 0.1601419999999998, 11605L, 1516513L, 0L, 9L, 220L, 0L, 2L, 1L, - # 6L, 4L,127L, 0L, 0L, 45L, 0L, 45L, 0L, 0L, 0L) - # ), - # ( - # ('User', 253, 9, 128, 128, 0, 0), - # ('Total_connections', 3, 2, 11, 11, 0, 0), - # ('Concurrent_connections', 3, 1, 11, 11, 0, 0), - # ('Connected_time', 3, 4, 11, 11, 0, 0), - # ('Busy_time', 5, 21, 21, 21, 31, 0), - # ('Cpu_time', 5, 18, 21, 21, 31, 0), - # ('Bytes_received', 8, 6, 21, 21, 0, 0), - # ('Bytes_sent', 8, 8, 21, 21, 0, 0), - # ('Binlog_bytes_written', 8, 1, 21, 21, 0, 0), - # ('Rows_read', 8, 1, 21, 21, 0, 0), - # ('Rows_sent', 8, 4, 21, 21, 0, 0), - # ('Rows_deleted', 8, 1, 21, 21, 0, 0), - # ('Rows_inserted', 8, 1, 21, 21, 0, 0), - # ('Rows_updated', 8, 1, 21, 21, 0, 0), - # ('Select_commands', 8, 1, 21, 21, 0, 0), - # ('Update_commands', 8, 1, 21, 21, 0, 0), - # ('Other_commands', 8, 4, 21, 21, 0, 0), - # ('Commit_transactions', 8, 1, 21, 21, 0, 0), - # ('Rollback_transactions', 8, 1, 21, 21, 0, 0), - # ('Denied_connections', 8, 4, 21, 21, 0, 0), - # ('Lost_connections', 8, 1, 21, 21, 0, 0), - # ('Access_denied', 8, 2, 21, 21, 0, 0), - # ('Empty_queries', 8, 2, 21, 21, 0, 0), - # ('Total_ssl_connections', 8, 1, 21, 21, 0, 0), - # ('Max_statement_time_exceeded', 8, 1, 21, 21, 0, 0)), - # ) + data[key] = variables[key] + + return data or None + + def get_slave_status(self, slave_status_data): + rows, description = slave_status_data[0], slave_status_data[1] + description_keys = [v[0] for v in description] + if not rows: + return + + data = dict() + for row in rows: + slave_data = dict(zip(description_keys, row)) + channel_name = slave_data.get('Channel_Name', DEFAULT_REPL_CHANNEL) + + if channel_name not in self.repl_channels and len(self.charts) > 0: + self.add_repl_channel_charts(channel_name) + self.repl_channels.append(channel_name) + + for key, func in SLAVE_STATS: + if key not in slave_data: + continue + + value = slave_data[key] + if channel_name: + key = '{0}_{1}'.format(key, channel_name) + data[key] = func(value) + + return data + + def add_repl_channel_charts(self, name): + self.add_new_charts(slave_status_chart_template, name) + def get_userstats(self, raw_data): + # raw_data['user_statistics'] contains the following data structure: + # ( + # ( + # ('netdata', 42L, 0L, 1264L, 3.111252999999968, 2.968510299999994, 110267L, 19741424L, 0L, 0L, 1265L, 0L, + # 0L, 0L, 3L, 0L, 1301L, 0L, 0L, 7633L, 0L, 83L, 44L, 0L, 0L), + # ('root', 60L, 0L, 184L, 0.22856499999999966, 0.1601419999999998, 11605L, 1516513L, 0L, 9L, 220L, 0L, 2L, 1L, + # 6L, 4L,127L, 0L, 0L, 45L, 0L, 45L, 0L, 0L, 0L) + # ), + # ( + # ('User', 253, 9, 128, 128, 0, 0), + # ('Total_connections', 3, 2, 11, 11, 0, 0), + # ('Concurrent_connections', 3, 1, 11, 11, 0, 0), + # ('Connected_time', 3, 4, 11, 11, 0, 0), + # ('Busy_time', 5, 21, 21, 21, 31, 0), + # ('Cpu_time', 5, 18, 21, 21, 31, 0), + # ('Bytes_received', 8, 6, 21, 21, 0, 0), + # ('Bytes_sent', 8, 8, 21, 21, 0, 0), + # ('Binlog_bytes_written', 8, 1, 21, 21, 0, 0), + # ('Rows_read', 8, 1, 21, 21, 0, 0), + # ('Rows_sent', 8, 4, 21, 21, 0, 0), + # ('Rows_deleted', 8, 1, 21, 21, 0, 0), + # ('Rows_inserted', 8, 1, 21, 21, 0, 0), + # ('Rows_updated', 8, 1, 21, 21, 0, 0), + # ('Select_commands', 8, 1, 21, 21, 0, 0), + # ('Update_commands', 8, 1, 21, 21, 0, 0), + # ('Other_commands', 8, 4, 21, 21, 0, 0), + # ('Commit_transactions', 8, 1, 21, 21, 0, 0), + # ('Rollback_transactions', 8, 1, 21, 21, 0, 0), + # ('Denied_connections', 8, 4, 21, 21, 0, 0), + # ('Lost_connections', 8, 1, 21, 21, 0, 0), + # ('Access_denied', 8, 2, 21, 21, 0, 0), + # ('Empty_queries', 8, 2, 21, 21, 0, 0), + # ('Total_ssl_connections', 8, 1, 21, 21, 0, 0), + # ('Max_statement_time_exceeded', 8, 1, 21, 21, 0, 0)), + # ) data = dict() userstats_vars = [e[0] for e in raw_data['user_statistics'][1]] for i, _ in enumerate(raw_data['user_statistics'][0]): @@ -742,7 +798,10 @@ class Service(MySQLService): self.charts['userstats_cpu'].add_dimension(['userstats_{0}_Cpu_time'.format(name), name, 'incremental', 100, 1]) def create_new_userstats_charts(self, tube): - order, charts = userstats_chart_template(tube) + self.add_new_charts(userstats_chart_template, tube) + + def add_new_charts(self, template, *params): + order, charts = template(*params) for chart_name in order: params = [chart_name] + charts[chart_name]['options'] |