Infrastructure (Server, Network, Database)

cancel
Showing results for 
Search instead for 
Did you mean: 

Database Custom Metrics get patchy results

Database Custom Metrics get patchy results

Hi,

 

We have implemented multiple Database Custom Metrics with controller and db agent in version v4.3. However we are getting very patchy values - every 3-5 minutes the metrics are missing.

 

While I was checking the db agent logs I found these kind of errors:

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)

That seemed to indicate that there was a timeout reading the metric according to IBM page: https://www.ibm.com/support/knowledgecenter/SSEPGG_10.5.0/com.ibm.db2.luw.messages.sql.doc/doc/msql0... .

 

I then tried to add "-DcommandTimeout=30" to the dbagent startup parameters and that seemed to prevent the errors but still the metrics are not coming regularly. Any idea what could be changed/tried to get the metrics consistently?

 

-Antti

Database Custom Metrics get patchy results
6 REPLIES 6
Highlighted
Employee

Re: Database Custom Metrics get patchy results

Hi Antti,

 

Could you please enable debug logging? 

Please edit the file <MachineAgent>/conf/logging/log4j.xml. Change the level value of the following <logger> elements to debug.

<logger name="com.singularity"

<logger name="com.appdynamics"

Please let it run for 10 mins and then zip and upload the All the logs in the directory MachineAgent/logs/*

 

Also, can you please share screenshot of custom metric and screenshot from metric browser?

 

Thanks,

Preet

Re: Database Custom Metrics get patchy results

Hi,

 

We are not using the SQLExtension for machineagent but rather we are using the new functionality in DB Agent introduced with v4.3. I enabled debugging for DB Agent but that didn't produce anything that tells more about the problem. I'm attaching the full stack trace for the error we are seeing but this error isn't there always when the metrics are missing - the metrics are missing more frequently than what is written to the DB Agent log.

 

In the attached screenshot you can see how the metrics fluctuate and the count is zero (no measurement) almost every other minute. We are collecting maybe 20 different metrics from the same table - each with their own custom metric query. The error seems to be some timeout in the client (DB Agent) so I'm just wondering how the agent schedules the custom metric collection.

 

Error from log:

13 Jun 2017 15:05:48,027 ERROR [<Database_name>-Scheduler-2] CustomMetricsCollector:130 - Error collecting data for database 'Database_name'
java.sql.SQLException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=4.16.53 (Custom Metric : Metric_name)
	at com.singularity.ee.agent.dbagent.collector.db.CustomMetricsCollector.collectCustomMetrics(CustomMetricsCollector.java:175)
	at com.singularity.ee.agent.dbagent.collector.db.CustomMetricsCollector.collect(CustomMetricsCollector.java:119)
	at com.singularity.ee.agent.dbagent.collector.db.CustomMetricsCollector.run(CustomMetricsCollector.java:109)
	at com.singularity.ee.util.javaspecific.scheduler.AgentScheduledExecutorServiceImpl$SafeRunnable.run(AgentScheduledExecutorServiceImpl.java:122)
	at java.util.concurrent.Executors$RunnableAdapter.call(Unknown Source)
	at com.singularity.ee.util.javaspecific.scheduler.ADFutureTask$Sync.innerRunAndReset(ADFutureTask.java:335)
	at com.singularity.ee.util.javaspecific.scheduler.ADFutureTask.runAndReset(ADFutureTask.java:152)
	at com.singularity.ee.util.javaspecific.scheduler.ADScheduledThreadPoolExecutor$ADScheduledFutureTask.access$101(ADScheduledThreadPoolExecutor.java:119)
	at com.singularity.ee.util.javaspecific.scheduler.ADScheduledThreadPoolExecutor$ADScheduledFutureTask.runPeriodic(ADScheduledThreadPoolExecutor.java:206)
	at com.singularity.ee.util.javaspecific.scheduler.ADScheduledThreadPoolExecutor$ADScheduledFutureTask.run(ADScheduledThreadPoolExecutor.java:236)
	at com.singularity.ee.util.javaspecific.scheduler.ADThreadPoolExecutor$Worker.runTask(ADThreadPoolExecutor.java:694)
	at com.singularity.ee.util.javaspecific.scheduler.ADThreadPoolExecutor$Worker.run(ADThreadPoolExecutor.java:726)
	at java.lang.Thread.run(Unknown Source)
Caused by: com.ibm.db2.jcc.am.SqlTimeoutException: DB2 SQL Error: SQLCODE=-952, SQLSTATE=57014, SQLERRMC=null, DRIVER=4.16.53
	at com.ibm.db2.jcc.am.fd.a(fd.java:741)
	at com.ibm.db2.jcc.am.fd.a(fd.java:60)
	at com.ibm.db2.jcc.am.fd.a(fd.java:127)
	at com.ibm.db2.jcc.am.ResultSet.completeSqlca(ResultSet.java:4100)
	at com.ibm.db2.jcc.am.ResultSet.earlyCloseComplete(ResultSet.java:4082)
	at com.ibm.db2.jcc.t4.ab.a(ab.java:899)
	at com.ibm.db2.jcc.t4.ab.n(ab.java:864)
	at com.ibm.db2.jcc.t4.ab.j(ab.java:254)
	at com.ibm.db2.jcc.t4.ab.d(ab.java:55)
	at com.ibm.db2.jcc.t4.o.c(o.java:44)
	at com.ibm.db2.jcc.t4.tb.j(tb.java:157)
	at com.ibm.db2.jcc.am.to.mb(to.java:2214)
	at com.ibm.db2.jcc.am.to.a(to.java:3261)
	at com.ibm.db2.jcc.am.to.a(to.java:697)
	at com.ibm.db2.jcc.am.to.executeQuery(to.java:676)
	at com.singularity.ee.agent.dbagent.collector.db.CustomMetricsCollector.collectCustomMetrics(CustomMetricsCollector.java:152)
	... 12 more

br, Antti

Employee

Re: Database Custom Metrics get patchy results

Hi Antti,

 

Applogies for confusion .A custom metrics allows the user to specify a SQL query to run on the monitored DB every minute, and collect the results of the query in a custom metric. From the error it seems like query might be taking longer than that leading to metrics not being collected for that minute. Complex long running statement may fail with SQL0952N error message. I am not sure about the query as it's not shared but Can we try and index the query further? 

 

Also, while searching for the error, I could see two alternatives

 

1. Increasing QUERYTIMEOUTINTERVAL to a larger value based on how long it is expected for the SQL statement to complete.

2. Disable query timeouts itself. QUERYTIMEOUTINTERVAL=0

 

Please let us know if this helps. Also,it would be great if you could share db agent logs in debug mode and screenshot of config or query pattern with us.

 

Thanks,

Preet

Re: Database Custom Metrics get patchy results

Hi,

 

I think the problem might be that the queries take too long to execute. I'm checked with the team that had asked for those and it took multiple seconds for each query so overall time will be too long if they are executed in sequence. I'm asking them to check the status of indexing and have told to either change the queries or build indexes to speed up the queries.

 

Do those Custom Metric queries run in parallel or in sequence? Are they using single connection or opening a new connection for each query?

 

Where can the QUERYTIMEOUTINTERVAL be set with the AppDynamics DB Agent?

 

-Antti

Employee

Re: Database Custom Metrics get patchy results

Hi Antti,

 

We will no be able to update QUERYTIMEOUTINTERVAL under DBagent. Is it possible for you to run it through DB2 Command line? 


db2 UPDATE CLI CFG FOR SECTION COMMON USING QUERYTIMEOUTINTERVAL 0

 

I would not recommend it to do if databtase is production but would prefer that query can be modified to validate. 

 

Also, I believe it must be single connection as numeric metrics are created on per database instance basis but I will get confirmation from our dev team to be 100% sure

 

Please keep us posted on how it goes once the query execution time is minimzed. For testing purpose, you can also create a simple custom metrics and validated. We believe it should report seamlessly. 

 

Thanks,

Preet

 

 

Employee

Re: Database Custom Metrics get patchy results

Hi Antti,

 

Not sure if you are still facing the issue but can you please try below property and see how it goes. 

 
-Ddbagent.custommetric.query.timeout.seconds=120
 
you May still see patchy results, but lets see if the query still timesout!