Teradata QueryGrid Performance Monitoring

Database
Fan

Teradata QueryGrid Performance Monitoring

Dear Precursor,

 

 I use Teradata QueryGrid to get data between Teradata DB and Teradata DB.

How can I motoring  system impact is caused by QueryGrid ?

There are a problem about skew.

This is daily average from DBQLOGTBL where user = 'QueryGrid User'

 

skew.JPG

 

 

Effectiveness impact not only skew but also I/O.

Affect time is defined by query runtime that mean DBC.DBQLOGTBL.FIRSTRESPTIME - DBC.DBQLOGTBL.STARTTIME

but QueryGrid is use remote table.

Maybe I have to use DBC.DBQLOGTBL.LASTRESPTIME ?

 Unfortunately, it's sunset from DBQLOGTBL at version TD14.

 

Anyone knows ?

Thanks !

 

BR,

Morris

 

 

  • performance
  • QueryGrid
  • T2T
  • TD15

Accepted Solutions
Senior Supporter

Re: Teradata QueryGrid Performance Monitoring

Hi Morris,

 

I'm still getting a bit confused over the terminology that you're using here.

 

Normally, 'skew' in a Teradata environment is where the resources (CPU and/or IO) used by a query are not evenly distributed across the AMPs in the configuration.

 


Is it (STARTTIME to FIRSTRESPTIME) CPU-Bound ?

Is it (FIRSTRESPTIME to LASTRESPTIME) I/O-Bound ?


Whether a system is 'cpu bound' or 'io bound' cannot be determined using columns such as 'starttime', 'firstresptime' etc. Those are measurements of how long it takes to get between certain stages of processing.

 

Yes you're correct in that when retrieving data from a foreign server, the data needs to be retrieved before it can be used in a join. This is just like any normal processing in Teradata. Each step has to complete before the next one can begin - and the retrieval of data from a foriegn server can be thought of as a 'step'.

 

QueryGrid query with high skew if it's runtime is long that have resource impact.

Due to QueryGrid will use foreign table. It will spend some time on I\O.

I want to know is affect time.

If you want to know how long the query spent accessing data from the foreign server then I think you need to use the DBQLStepTbl. Find the step in the plan that does the 'foreign server' processing (I think column ServerByteCount > 0) and then calculate the length of time for that step (columns StepStartTime and StepStopTime).

   But this will have nothig to do with 'skew' (as it is normally used in a Teradata environment)

 

Does that get you further forward?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
1 ACCEPTED SOLUTION
4 REPLIES
Senior Supporter

Re: Teradata QueryGrid Performance Monitoring

Hi Morris,

 

What exactly are you trying to identify here?

 

Your post talks about a problem due to 'skew' but you are looking at columns StartTime, FirstRespTime etc. Those will not show you anything to do with skew. They will show you when a query was running, but nothing about how efficient it is.

 

If your Querygrid connection is Teradata to Teradata then you should be able to use DBQL data on both source and target systems. yes you might need to use those columns as selection criteria to identify which queries are skewed, but then you'll need columns like AMPCpuTimeNorm/MaxAMPCpuTimeNorm (useTotalIOCount/MaxAMPIO for io measurement) to measure skew.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Tags (3)
Fan

Re: Teradata QueryGrid Performance Monitoring

Hi Dave,

 

Thanks for your information.

 

QueryGrid query with high skew if it's runtime is long that have resource impact.

Due to QueryGrid will use foreign table. It will spend some time on I\O.

I want to know is affect time.

 

Is it (STARTTIME to FIRSTRESPTIME) CPU-Bound ?

Is it (FIRSTRESPTIME to LASTRESPTIME) I/O-Bound ?

 

if I use foreign table. I need to wait all data back to local before other step that like join, isn't it?

 

BR,

Morris

 

Tags (1)
Senior Supporter

Re: Teradata QueryGrid Performance Monitoring

Hi Morris,

 

I'm still getting a bit confused over the terminology that you're using here.

 

Normally, 'skew' in a Teradata environment is where the resources (CPU and/or IO) used by a query are not evenly distributed across the AMPs in the configuration.

 


Is it (STARTTIME to FIRSTRESPTIME) CPU-Bound ?

Is it (FIRSTRESPTIME to LASTRESPTIME) I/O-Bound ?


Whether a system is 'cpu bound' or 'io bound' cannot be determined using columns such as 'starttime', 'firstresptime' etc. Those are measurements of how long it takes to get between certain stages of processing.

 

Yes you're correct in that when retrieving data from a foreign server, the data needs to be retrieved before it can be used in a join. This is just like any normal processing in Teradata. Each step has to complete before the next one can begin - and the retrieval of data from a foriegn server can be thought of as a 'step'.

 

QueryGrid query with high skew if it's runtime is long that have resource impact.

Due to QueryGrid will use foreign table. It will spend some time on I\O.

I want to know is affect time.

If you want to know how long the query spent accessing data from the foreign server then I think you need to use the DBQLStepTbl. Find the step in the plan that does the 'foreign server' processing (I think column ServerByteCount > 0) and then calculate the length of time for that step (columns StepStartTime and StepStopTime).

   But this will have nothig to do with 'skew' (as it is normally used in a Teradata environment)

 

Does that get you further forward?

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Fan

Re: Teradata QueryGrid Performance Monitoring

Hi Dave,

 

Thanks ! 

It's helpful.

I find most of QueryGrid query runtime are not long.

So I try to use Viewpoint to control them.

 

Querygrid running

1) run over 30 mins

2) idle over 60 mins

3) CPU skew over 60%

 

BR,

Morris

Tags (1)