Standard Deviation based alerting

[GoogleCodeExporter]

I happen to have a database load that is steadly
growing with peaks every so often. It averages out to a 1.2% daily
growth and a max peak of 4.4% (over growing 'norm') per quarter hour.
I'd like to be able to have a notice when say peak is suddenly, say
10% over (or under) norm. Setting absolute values means I have to keep
adjusting it every week or so rather then having it adapt to a steady,
growing load.

Original issue reported on code.google.com by [email protected] on 31 Oct 2009 at 11:49

[GoogleCodeExporter]

That's a great idea. If you provide an equation that we can pop into the 
alerter for
this then I'll gladly get it going for the next release.

For reference the current reporter code is located here for your review:
http://code.google.com/p/kontrollbase/source/browse/trunk/bin/kontroll-reporter-
5.0.x_linux-x86-2.0.1.pl

Original comment by [email protected] on 1 Nov 2009 at 12:01

• Changed state: Started
• Added labels: Component-Logic
• Removed labels: ****

[GoogleCodeExporter]

Original comment by [email protected] on 1 Nov 2009 at 12:01

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

Original comment by [email protected] on 1 Nov 2009 at 2:47

• Added labels: Milestone-2.1.x
• Removed labels: ****

[GoogleCodeExporter]

So it looks like the Statistics::Descriptive perl module would do the trick 
nicely:
use Statistics::Descriptive;

my $stat = Statistics::Descriptive::Full->new();
$stat->add_data(1, 1.5, 2); 

my $std  = $stat->standard_deviation();

printf "Std Dev: %10.4f\n", $std;

Std Dev: 0.5000

Would that be a fair dependency to add?

Original comment by [email protected] on 6 Nov 2009 at 12:19

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

also look at the following query functions:
http://dev.mysql.com/doc/refman/5.1/en/group-by-functions.html

MAX()   Return the maximum value
MIN()   Return the minimum value
STD()   Return the population standard deviation
STDDEV_POP()(v5.0.3)    Return the population standard deviation
STDDEV_SAMP()(v5.0.3)   Return the sample standard deviation
STDDEV()    Return the population standard deviation
SUM()   Return the sum
VAR_POP()(v5.0.3)   Return the population standard variance
VAR_SAMP()(v5.0.3)  Return the sample variance
VARIANCE()(v4.1)    Return the population standard variance

Original comment by [email protected] on 7 Nov 2009 at 6:11

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

created a view table that has std. deviation for various items. 

*************************** 1. row ***************************
  max_os_mem_used: 50.743488311768
  min_os_mem_used: 0.023044586182
  avg_os_mem_used: 1.5759673552104291
stdev_os_mem_used: 2.4063751473423332
         max_size: 283.815660957247
         min_size: 0.000492287800
         avg_size: 10.8227964413421371
       stdev_size: 39.9488283749944415
  max_connections: 435
  min_connections: 0
  avg_connections: 16.9739
stdev_connections: 37.3273
          max_qps: 9243.6533203125
          min_qps: 0.00011409764556447
          avg_qps: 216.410748109113
        stdev_qps: 1071.67274537168
1 row in set (3 min 21.12 sec)


create view view_analytics_overview as select ((((MAX(os_mem_used)) / 1024 ) / 
1024)
/ 1024) max_os_mem_used, ((((MIN(os_mem_used)) / 1024 ) / 1024) / 1024)
min_os_mem_used, ((((AVG(os_mem_used)) / 1024 ) / 1024) / 1024) avg_os_mem_used,
((((STDDEV_POP(os_mem_used)) / 1024 ) / 1024) / 1024) stdev_os_mem_used,
((((MAX(length_data + length_index)) / 1024 ) / 1024) / 1024) max_size,
((((MIN(length_data + length_index)) / 1024 ) / 1024) / 1024) min_size,
((((AVG(length_data + length_index)) / 1024 ) / 1024) / 1024) avg_size,
((((STDDEV_POP(length_data + length_index)) / 1024 ) / 1024) / 1024) stdev_size,
MAX(num_connections) max_connections,  MIN(num_connections) min_connections,
AVG(num_connections) avg_connections, STDDEV_POP(num_connections) 
stdev_connections,
MAX(queries_per_second) max_qps, MIN(queries_per_second) min_qps,
AVG(queries_per_second) avg_qps, STDDEV_POP(queries_per_second) stdev_qps from
server_statistics\G

Original comment by [email protected] on 17 Nov 2009 at 2:44

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

add per-schema/client/overall growth % report

Original comment by [email protected] on 20 Nov 2009 at 5:43

• Added labels: ****
• Removed labels: ****

[GoogleCodeExporter]

note: I'm trying to stay away from adding any more perl modules to the 
installation
requirement. We should be able to do this with queries as seen above in some 
form or
another. 

Original comment by [email protected] on 3 Dec 2009 at 10:41

• Added labels: ****
• Removed labels: ****