Limitations of Database Monitoring Custom Metrics

[#1]

Custom metrics allows the user to specify a SQL query to run on a monitored DB every minute, and collect the results of the query.

This document specifies restrictions and limitations, as well as some examples and debugging techniques. 

Restrictions and Limitations:

• The query must always return a positive integer number.
• The query should not return more than one column.
• The user can define up to 20 custom metrics per monitored DB.
  • This limit is configurable through admin.jsp with the following property; however, it should be increased graciously.
  • 
• For Security reasons, we only allow SELECT queries.

How to debug?  

• If the Agent encounters an error while running the query, the Agent will send an error event to the Controller.
  • View these error events by clicking on Database -> Events in the Controller UI.
• For more details on an error, view the database Agent logs located at <db_install_dir>/logs.
  • The errors are self-descriptive and can be searched using the keywords "CustomMetricsCollector," "ERROR," or "WARN."
  • Examples:

  • [<MyTestMysql>-Scheduler-3] 29 May 2017 11:04:59,702 ERROR CustomMetricsCollector - Error collecting data for database 'MyTestMysql'
    java.sql.SQLException: Invalid custom metric: test4 , actual data type is non-numeric and expected data type is numeric (Custom Metric : test4)

  • 09 Jun 2017 13:40:08,889 ERROR [<Database>-Scheduler-1] CustomMetricsCollector:130 - Error collecting data for database 'Database' java.sql.SQLException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=4.16.53 (Custom Metric : TestMetric)

Query Examples with MySQL:

1.select COUNT(1) "total" from dual; - Worked
Output: 1

2. select 9+ ' ' +':00' num from dual; - Worked.
Output: 9 

3. select name from user where id=3 - Not working as expected
Output: root

4. select id as node_id from application_component_node where id NOT in (select distinct(node_id) from metricdata_hour where ts_min > ((select max(ts_min) from metricdata_hour)) - 60*2) - Worked/Not Worked based on output

Output: Fails if returns multiple records

Related Links:

• Database monitoring metrics
• Configuring custom metrics

[Raymond_McCabe]

Are there any restrictions on Joins or Table aliases? I think queries terminating with a semi-colon fail as far as I've observed.

[jeremyGoldstein]

What are the functions allowed/disallowed in SELECT queries? Looks like COALESCE is restricted for some reason? "Query validation failed because it has keyword 'COALESCE' that is trying to manipulate DB."