summaryrefslogtreecommitdiffstats
path: root/collectors/python.d.plugin/mysql
diff options
context:
space:
mode:
Diffstat (limited to 'collectors/python.d.plugin/mysql')
-rw-r--r--collectors/python.d.plugin/mysql/README.md496
-rw-r--r--collectors/python.d.plugin/mysql/mysql.chart.py165
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
---
-[![analytics](https://www.google-analytics.com/collect?v=1&aip=1&t=pageview&_s=1&ds=github&dr=https%3A%2F%2Fgithub.com%2Fnetdata%2Fnetdata&dl=https%3A%2F%2Fmy-netdata.io%2Fgithub%2Fcollectors%2Fpython.d.plugin%2Fmysql%2FREADME&_u=MAC~&cid=5792dfd7-8dc4-476b-af31-da2fdb9f93d2&tid=UA-64295674-3)]()
+[![analytics](https://www.google-analytics.com/collect?v=1&aip=1&t=pageview&_s=1&ds=github&dr=https%3A%2F%2Fgithub.com%2Fnetdata%2Fnetdata&dl=https%3A%2F%2Fmy-netdata.io%2Fgithub%2Fcollectors%2Fpython.d.plugin%2Fmysql%2FREADME&_u=MAC~&cid=5792dfd7-8dc4-476b-af31-da2fdb9f93d2&tid=UA-64295674-3)](<>)
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']