MySQL Table Level Metrics for Datadog in Python
Datadog does not provide MySQL Table Level metrics out of the box, so I used Python and Datadog's API to achieve getting table statistics into Datadog as a custom metric.
You will need a datadog account, api/app keys, mysql server and the python packages
Create API and APP keys:
Check out the API Documentation:
$ pip install mysqlclient $ pip install datadog The sys schema objects can be used for typical tuning and diagnosis use cases:
mysql> select * from sys.`x$schema_table_statistics` where total_latency > 0\G *************************** 1. row *************************** table_schema: foo table_name: bar total_latency: 3730128452604 rows_fetched: 3929224 fetch_latency: 1998830290976 rows_inserted: 19967 insert_latency: 1731298161628 rows_updated: 0 update_latency: 0 rows_deleted: 0 delete_latency: 0 io_read_requests: 14 io_read: 972 io_read_latency: 51442220 io_write_requests: 43 io_write: 414182 io_write_latency: 1079346324 io_misc_requests: 76 io_misc_latency: 92505469736 The metrics end-point allows you to post time-series data that can be graphed on Datadog’s dashboards:
# Submit multiple metrics api.Metric.send([{ 'metric': 'my.series', 'points': 15 }, { 'metric': 'my1.series', 'points': 16 }]) Map the returned data to json:
mysql_querydata = cursor.fetchall() json_data=[] for row_data in mysql_querydata: json_data.append(dict(zip(row_headers, row_data))) Preview the data:
>>> print(json_data) [{'rows_inserted': 19967, }, {'rows_updated': 0}] metrics = [] for each in payload: table_name = each['table_name'] for k in each.keys(): if type(each[k]) == str: pass else: metrics.append({ 'metric': 'mysql.custom_metric.{}.{}'.format(table_name, k), 'points': each[k], 'host': hostname, 'tags': tags }) preview data:
>>> print(metrics) [{'metric': 'mysql.custom_metric.foo.io_misc_requests', 'points': Decimal('93'), 'host': 'datadog-mysql', 'tags': ['mysql:performance_data']},..] Metrics:
Dashboard:
