May 10: Adaptive Thresholds in 10g - Part 3 (Setting Thresholds)
Some features in this post require a Diagnostics Pack license.
One of my favourite aspects of blogging is that if I were to write a proper article or conference paper, I'd be likely to fix any problems with it as I go (well, the ones I notice!). With blogging, it's more likely that I'll just post something quite quickly without significant editing. Most of the time that's not a good thing, but occasionally a bad decision throws up an interesting point to expand on and the fluidity of the medium means I can. When I chose the two graphs towards the end of the last post, it would probably have been better if both graphs had been for 'Response Time (per transaction)' so that you could compare the same statistic over different time groupings, but I accidentally chose 'Number of Transactions (per second)' (which is first on the list in the Statistics Preview screen). For completeness, here are the two graphs I should have used
... and to save you having to go back to the previous post, here's the Number of Transactions graph I used. (Oh, and for anyone who can't stand looking at graphs, there might be some later posts that are more query based because everything in the graphs is available in basic dictionary views that I've probably played around with as much. Just let me use some pretty pictures for a change, eh?)
So why do I think the misjudgement (I wouldn't go as far as calling it a mistake) is interesting? Because it allows me to move away from the narrow focus of transaction response time and highlight the metric choices, which I think are good, and their different focus. The statistics computation and preview screen doesn't group the metrics as well as the threshold setting screen does.
There are three different groups of metrics as indicators of different, but often related variations in system behaviour.
Performance ... hopefully that one is self-explanatory. But there are many reasons why performance could become worse, two possibilities are :-
Workload Type ... has the behaviour of the application changed or is someone running a workload that they shouldn't be, perhaps the query from hell during your usual OLTP day?
Workload Volume ... or is the system just much busier than normal - more users, more transactions?
That's my informal translation and I think that the groups are a useful aid to help you consider what the metrics mean, but there are no hard and fast rules, here. For example, redo size could change because of an increase in the volume of transactions or because of a change in the type of transactions. (As I'm writing, I remember saying something about redo generation and User Commits in a blog post a while ago about workload metrics and redo size is certainly something I've used in the past to track the 'busy-ness' of systems before I had the options of setting adaptive thresholds. I know from my own experience how useful some of these metrics can be.)
The relationship between the different metrics could be quite complex, though. If you look back at the Response Time and Number of Transactions graphs for the Weekdays and Weekend grouping above, you'll see that there were many fewer transactions at weekends, but their individual response time was higher than on weekdays - not what you'd expect, but adapting to complex system behaviour is precisely what makes this approach useful. (The complexity here is likely to be a direct result of this being an intermittently very busy laptop running a wide variety of transactions.)
Anyway, how do I go about setting thresholds to alert against? There's only one screen to deal with. (I'll use a thumbnail of the tresholds setting screen here because it didn't scale down too well.)
What do the values that I set here mean? If you look back at the second graph, you'll see that the 95th percentile is difficult to see, but looks somewhere in the low hundreds for both weekends and weekdays. To a certain extent, the absolute value isn't the key, but we can see it by looking at the underlying statistics for the two time groups. (Warning - query thrown together based on a basic guess at the underlying data model.)
The weekday time group statistics are based on 1239 values for this metric, 269 for the weekend group and, although the thresholds are stored as .95 and .99, I can see those thresholds translate into 207 centiseconds for a weekend warning and 3692 centiseconds for a critical alert. Remember that, because this is a Static Baseline, these statistics were computed once and will stay the same, so the absolute values this query returns are used as the thresholds. If I was using a Moving Window baseline, the percentile settings would stay the same, but the statistics would be recalculated and the absolute threshold values will change.
As Moving Window baselines are likely to prove more useful for more people (but, I'll repeat, aren't as easy to experiment with on a home system), I'll move on to those in the next post, as well as switching to 11g.
Oh, but before I do, the proof that these alerts do work. (Because there were times when I wondered
)
One of my favourite aspects of blogging is that if I were to write a proper article or conference paper, I'd be likely to fix any problems with it as I go (well, the ones I notice!). With blogging, it's more likely that I'll just post something quite quickly without significant editing. Most of the time that's not a good thing, but occasionally a bad decision throws up an interesting point to expand on and the fluidity of the medium means I can. When I chose the two graphs towards the end of the last post, it would probably have been better if both graphs had been for 'Response Time (per transaction)' so that you could compare the same statistic over different time groupings, but I accidentally chose 'Number of Transactions (per second)' (which is first on the list in the Statistics Preview screen). For completeness, here are the two graphs I should have used
... and to save you having to go back to the previous post, here's the Number of Transactions graph I used. (Oh, and for anyone who can't stand looking at graphs, there might be some later posts that are more query based because everything in the graphs is available in basic dictionary views that I've probably played around with as much. Just let me use some pretty pictures for a change, eh?)
So why do I think the misjudgement (I wouldn't go as far as calling it a mistake) is interesting? Because it allows me to move away from the narrow focus of transaction response time and highlight the metric choices, which I think are good, and their different focus. The statistics computation and preview screen doesn't group the metrics as well as the threshold setting screen does.
There are three different groups of metrics as indicators of different, but often related variations in system behaviour.
Performance ... hopefully that one is self-explanatory. But there are many reasons why performance could become worse, two possibilities are :-
Workload Type ... has the behaviour of the application changed or is someone running a workload that they shouldn't be, perhaps the query from hell during your usual OLTP day?
Workload Volume ... or is the system just much busier than normal - more users, more transactions?
That's my informal translation and I think that the groups are a useful aid to help you consider what the metrics mean, but there are no hard and fast rules, here. For example, redo size could change because of an increase in the volume of transactions or because of a change in the type of transactions. (As I'm writing, I remember saying something about redo generation and User Commits in a blog post a while ago about workload metrics and redo size is certainly something I've used in the past to track the 'busy-ness' of systems before I had the options of setting adaptive thresholds. I know from my own experience how useful some of these metrics can be.)
The relationship between the different metrics could be quite complex, though. If you look back at the Response Time and Number of Transactions graphs for the Weekdays and Weekend grouping above, you'll see that there were many fewer transactions at weekends, but their individual response time was higher than on weekdays - not what you'd expect, but adapting to complex system behaviour is precisely what makes this approach useful. (The complexity here is likely to be a direct result of this being an intermittently very busy laptop running a wide variety of transactions.)
Anyway, how do I go about setting thresholds to alert against? There's only one screen to deal with. (I'll use a thumbnail of the tresholds setting screen here because it didn't scale down too well.)
The screen itself is simple to use and I guess that people will find the Percentage of Maxmimum thresholds simple to understand, but Significance Level thresholds can be a little trickier.
In essence, they're designed to trap unusual values. There's a lot more detail on the mathematical foundations of their design in this paper I posted a link to previously and here's an AskTom thread where Graham Wood and John Bereniewicz offer their thoughts which I liked. Personally, I have a hunch that when people first experiment with Significance Level thresholds they find that they don't generate any alerts because the higher levels of significance will only catch truly extreme events and so, having been unable to get the damn things to throw alerts, they give them up as a lost cause. The default values in the DB/Grid Control screen are a Critical alert for Severe (.999, or one in a thousand occurrences) and a Warning for Very High (.99, or one in a hundred). Add in the fact that two consecutive occurrences are required before an alert is triggered and it could be a long wait for a Critical alert.
What do the values that I set here mean? If you look back at the second graph, you'll see that the 95th percentile is difficult to see, but looks somewhere in the low hundreds for both weekends and weekdays. To a certain extent, the absolute value isn't the key, but we can see it by looking at the underlying statistics for the two time groups. (Warning - query thrown together based on a basic guess at the underlying data model.)
SYS@TEST1020> select n.metric_name, n.metric_unit, p.threshold_method,
2 p.num_occurrences, p.warning_param, p.critical_param,
3 s.compute_date, s.sample_count,
4 s.average, s.minimum, s.maximum, s.sdev,
5 s.pctile_25, s.pctile_50, s.pctile_75, s.pctile_90, s.pctile_95,
6 s.est_pctile_99, s.est_pctile_999, s.est_pctile_9999
7 from dbsnmp.MGMT_BSLN_STATISTICS s, dbsnmp.MGMT_BSLN_THRESHOLD_PARMS p,
8 dbsnmp.MGMT_BSLN_DATASOURCES d, dbsnmp.MGMT_BSLN_BASELINES b,
9 V$METRICNAME n
10 where p.datasource_guid = d.datasource_guid
11 and s.datasource_guid = d.datasource_guid
12 and s.bsln_guid = p.bsln_guid
13 and p.bsln_guid = b.bsln_guid
14 and d.metric_id = n.metric_id
15 and b.name = 'Doug Test';
METRIC_NAME
----------------------------------------------------------------
METRIC_UNIT THRESHOLD_METHOD NUM_OCCURRENCES WARNING_PARAM
-------------------- ---------------- --------------- -------------
CRITICAL_PARAM COMPUTE_DATE SAMPLE_COUNT AVERAGE MINIMUM MAXIMUM
-------------- ----------------- ------------ ---------- ---------- ----------
SDEV PCTILE_25 PCTILE_50 PCTILE_75 PCTILE_90 PCTILE_95 EST_PCTILE_99
---------- ---------- ---------- ---------- ---------- ---------- -------------
EST_PCTILE_999 EST_PCTILE_9999
-------------- ---------------
Response Time Per Txn
CentiSeconds Per Txn SIGLVL 2 .95
.99 10/05/09 18:40:10 1239 71.6869897 .021090732 5146.63499
369.551876 1.88866288 3.97931111 6.94699364 24.1417503 254.782416 1882.14226
4041.34033 6266.98777
Response Time Per Txn
CentiSeconds Per Txn SIGLVL 2 .95
.99 10/05/09 18:40:10 269 112.962703 0 7658.297
746.769388 .339025 .4427 1.8753 11.62364 207.996409 3692.6721
5575.31008 8696.60199The weekday time group statistics are based on 1239 values for this metric, 269 for the weekend group and, although the thresholds are stored as .95 and .99, I can see those thresholds translate into 207 centiseconds for a weekend warning and 3692 centiseconds for a critical alert. Remember that, because this is a Static Baseline, these statistics were computed once and will stay the same, so the absolute values this query returns are used as the thresholds. If I was using a Moving Window baseline, the percentile settings would stay the same, but the statistics would be recalculated and the absolute threshold values will change.
As Moving Window baselines are likely to prove more useful for more people (but, I'll repeat, aren't as easy to experiment with on a home system), I'll move on to those in the next post, as well as switching to 11g.
Oh, but before I do, the proof that these alerts do work. (Because there were times when I wondered
)
Trackbacks
Trackback specific URI for this entry
No Trackbacks
Comments
Display comments as
(Linear | Threaded)
#3 - Marco Gralike said:
2009-05-12 20:40 - (Reply)
Although I think, DB Console (11.1.0.7.0) is ahead regarding its big brother Database Grid Control 10g Release 5 would be an interesting alternative, but can't compare it for you due to the fact that I haven't implemented it yet. Too busy regarding testing stuff
