ASE Database forum - Need your feedback

Author

Need your feedback

Sudipto Chowdhuri

2005-10-28, 8:24 pm

Hello all,
I want some input from you folks on a problem
and the solution that we're proposing. This is related to QP
metrics, a new feature in 15.0, whereby we can capture all
queries in an application into a system catalog along with
all kinds of execution metrics. The problem is that this
causes catalog bloat and we may run out of space. One
obvious thing is to filter out what we want to store. It may
not be appropriate to save "low cost" queries. The problem
is how do we define "high cost" queries. The metrics that we
have are:
- exec time (min/max/avg)
- lio (min/max/avg)
- pio (min/max/avg)
- user id

My questions are the following:

1. Do you think we could just use two config parameters, one
for avg exec time and the other for avg pio time and use an
OR semantics ?
2. Config parameters are a bad idea and you would like to
see a stored procedure where we say

sp_blah_blah @pio_threshold = k1, @exec_time = k2
3. Just a simple OR is not enough and you would prefer to
have the flexibility of using a combination of ANDs/ORs for
one or more user ids.
4. Would you prefer to have a session level filtering as
well ?

Thanks for your time,

Sudipto R. Chowdhuri
Sr. Staff Eng. (ASE Query
Optimizer)
-

Mark A. Parsons

2005-10-28, 8:24 pm

I've been working on something silimar (pre-15.0 servers) that would
utilize the MDA tables.

What you're proposing sounds very similar so I'd be curious as to how
you'd address a few issues:

----------------

1 - do you perform any hashing of queries that would allow you to group
'like' queries?

Assuming a unique index on the 'id' column I currently consider all of
the following queries to be the same:

select * from t1 where id = 5
select * from t1 where id = 37
select * from t1 where id = 3425

But I do not consider the following queries to be the same:

select count(*) from t1
select min(id) from t1
select sum(id) from t1 where id between 24 and 4529

At this point I would have 4 'unique' queries in my catalog. The first
query would have an exec-count = 3, the other 3 would have an exec-count
= 1.

----------------

2 - 'avg' implies that you are maintaining a) a list of all counters
over (all?) time or b) a running sum of all counters along with a count
of said counters (ie, avg = sum/count); how do you plan to maintain
avg's? how do you plan to maintain avg's in relation to time?

My current plan is to save a date/time stamp with each query I pull/hash
from the MDA tables.

At this point I'm not concerned with user id.

My current plan is to store actual values (exec time, lio, phio) along
with the date/time stamp. min/max/avg can be computed from these values
.... with the date/time stamp I can also determine min/max/avg for a
given time frame (eg, last night's batch run from 22:00 to 02:15).

----------------

3 - Depending on how/what you're saving I would think you could allow
the catalog to be setup similar to the MDA queue-like tables (eg,
monSysSQLText); allow the user to define a max queue size; then as time
progresses 'old' query info will roll off the end of the catalog/queue;
if users want to maintain a history they would need to periodically pull
the info from your catalog/queue and store in a permanent table.

This should keep your catalog from getting too bloated. It's then up to
the user to decide how much they want to save and for how long.
'course, at this point you're almost back to providing the same thing
currently available via the MDA tables so the question becomes ...
what's the purpose of your catalog?

Sudipto Chowdhuri wrote:
> Hello all,
> I want some input from you folks on a problem
> and the solution that we're proposing. This is related to QP
> metrics, a new feature in 15.0, whereby we can capture all
> queries in an application into a system catalog along with
> all kinds of execution metrics. The problem is that this
> causes catalog bloat and we may run out of space. One
> obvious thing is to filter out what we want to store. It may
> not be appropriate to save "low cost" queries. The problem
> is how do we define "high cost" queries. The metrics that we
> have are:
> - exec time (min/max/avg)
> - lio (min/max/avg)
> - pio (min/max/avg)
> - user id
>
> My questions are the following:
>
> 1. Do you think we could just use two config parameters, one
> for avg exec time and the other for avg pio time and use an
> OR semantics ?
> 2. Config parameters are a bad idea and you would like to
> see a stored procedure where we say
>
> sp_blah_blah @pio_threshold = k1, @exec_time = k2
> 3. Just a simple OR is not enough and you would prefer to
> have the flexibility of using a combination of ANDs/ORs for
> one or more user ids.
> 4. Would you prefer to have a session level filtering as
> well ?
>
> Thanks for your time,
>
> Sudipto R. Chowdhuri
> Sr. Staff Eng. (ASE Query
> Optimizer)
> -

Sponsored Links