Canary Query Details

Viewpoint
Enthusiast

Canary Query Details

Can anyone give me details of how the canary query frequency works in viewpoint?

The issue I'm having is that we want to measure response times for all workloads using the canary queries in viewpoint. I can get it to work for short and medium queries, but our long workload can have very long delays, often over an hour (I know this isn't good for the users, but that's how it is!).

I want to run a canary query in this workload every 15 minutes. The problems I have are:

1) A new canary query doesn't start running whilst one is in progress (i.e. in the delay queue)
2) The queries are aborted by the application after 30 minutes.

Is there any way I can achieve what I'm trying to do?
Tags (2)
19 REPLIES
Teradata Employee

Re: Canary Query Details

The fact that only one execution of each canary query is running at a given time is a fundamental aspect of the Viewpoint Data Collection Service that is responsible for running these queries. I believe that making that limitless, or even allowing the user to enter the number of concurrent queries, would be a dangerous change that could potentially impact Viewpoint's ability to get connections to the database to execute other queries.

It is true that there is a hard-coded timeout of 30 minutes for any non-heartbeat canary query. Is this something you would like to be able to customize per query? This seems like a reasonable change. Anyone else running into a similar problem?

And to answer your final question, I can't think of a way to achieve what you want currently in Viewpoint.
Enthusiast

Re: Canary Query Details

Thanks for your response.
I think it would be useful to have a customizable timeout value. However I do need some way to run overlapping queries, i.e. I want to see that a query which started at 06:00 took 65 minutes to complete, one at 6:15 took 55 minutes, one at 6:30 took 38 minutes, etc... I guess I will have to write that myself in a unix script which starts bteq jobs running in the background.
Fan

Re: Canary Query Details

If you implement the performance COE PDCR queries you can utilize them the attain what you are looking for - you need to configure those outside of viewpoint sorry but it works better for this shop as I can control the process more.
Enthusiast

Re: Canary Query Details

We are in the process now of moving our canaries, which are implemented using the PDCR Toolkit, over to Viewpoint.  Our concern was there is logic in the PDCR script that looks to make sure there isn't another canary of the same type running before it runs another one.  Is my understanding correct that Viewpoint has this same check built into it to wait until the last canary3 finishes before it kicks off another canary3?  And if that wait time exceeds 30 minutes, it then aborts both canary3 processes?  The one that is running as well as the one waiting?

I read through the Teradata Viewpoint Users Guide in the Canary section and saw nothing on this topic.

Thanks

Teradata Employee

Re: Canary Query Details

Viewpoint will only allow one execution of a particular canary query at a time.  If it becomes time to run the canary query again and the previous execution is still running, the next execution will be delayed until the previous execution completes.  As soon as it completes, the query will be submitted to Teradata.

As for timeouts, the system heartbeat has a timeout of 1 minute.  All other canary queries have a timeout of 30 minutes.  The JDBC driver will abort any query that exceeds these timeouts.  Only the running query will be aborted.  The next query is run as it would normally run.

Enthusiast

Re: Canary Query Details

Thanks!  Just curious, the query that is aborted after the 30 minute timeout...does it appear in Viewpoint's chart?  I believe DBQL only captures queries that complete...correct?

Teradata Employee

Re: Canary Query Details

The query that is aborted after the timeout will not appear in the Viewpoint Canary Response Time portlet.  You will find an exception in the dcs log on the Viewpoint server.  Viewpoint does its own timing of the canary queries and does not rely upon DBQL for this data.  However, I believe you are correct that DBQL does not currently capture aborted queries.

Enthusiast

Re: Canary Query Details

Hi there,

Is there some default Canary Query which can be runed from time to time by user not via viewpoint?

Junior Supporter

Re: Canary Query Details

Hi,

I want ti run a macro - PDCRINFO.CapCanarySUMRT_Rpt. This macro will give me max and avg canary response time. I see that this macro uses PDCRDATA."Canary_Hst" and PDCRDATA."ShiftHour". However, both of these tables are empty.

Can anyone help me to identify how these tables are populated ? Do i need to enable logging for these ? I tried to see if canary query logging can be enabled, however, i dont see any option to log the canary query.