[mrtg] Re: CSH or Perl script for 95% tile

[Paul Chvostek]

On Mon, Sep 09, 2002 at 05:31:35PM -0500, Kevin S. Dome wrote:
> 
>    My network administrator is looking for a script written to take the 
> ninety-fifth percentile of each co-location each hour, either written in 
> the c shell language or in perl. I would really appreciate it, also a 
> little howto might help as well.
> 
> p.s. i search the web and got one from seanadams.com his appears to have 
> an error in it,

Sean's 95th percentile hack is innovative, but the patches supplied are
not for current versions of MRTG.  The down side to the hack is that it
still only stores 30 days of data, so you can't re-create graphs for
previous months.

I'm doing 95th percentile calculations based on MRTG data, but not this
way.  I too have an hourly cron job that collects "new" 5-minute-samples
but it stores them in a MySQL table (2 million records and counting),
and I use SQL SELECTs to pull out the data.  Dates and bytecounts are
stored as ints for the best use of disk space, so to get the last 30
days' 95th percentile for a customer (30 days is 2592000 seconds):

 mysql> desc mrtglog;
 +---------+----------------------+------+-----+---------+----------------+
 | Field   | Type                 | Null | Key | Default | Extra          |
 +---------+----------------------+------+-----+---------+----------------+
 | id      | int(10) unsigned     |      | PRI | NULL    | auto_increment |
 | item    | smallint(5) unsigned |      | MUL | 0       |                |
 | date    | int(10) unsigned     |      | MUL | 0       |                |
 | avgin   | int(10) unsigned     |      | MUL | 0       |                |
 | avgout  | int(10) unsigned     |      | MUL | 0       |                |
 +---------+----------------------+------+-----+---------+----------------+
 5 rows in set (0.00 sec)

 mysql> select floor(count(*)*0.95) from mrtglog where item=91 and date > UNIX_TIMESTAMP()-2592000;
 +----------------------+
 | floor(count(*)*0.95) |
 +----------------------+
 |                 9489 |
 +----------------------+
 1 row in set (0.41 sec)

 mysql> SELECT avgout FROM mrtglog WHERE item=91 AND date > UNIX_TIMESTAMP()-2592000 ORDER BY avgout LIMIT 9489,1;
 +--------+
 | avgout |
 +--------+
 | 364968 |
 +--------+
 1 row in set (0.65 sec)

So the 95th percentile for outbound traffic from the customer who is
item number 91 is 364968 bps.  I've also done stuff in PHP that creates
a temporary table and populates with *all* the numbers, so you can get a
95th percentile that combines inbound and outbound traffic.  I've also
written some PHP which builds graphs on the fly, so I can create 95th
percentile charts sorted either by date *or* by size.  It's pretty cool.

If people are interesting in seeing more details along with some code
examples, let me know and I may set up a web page.

-- 
  Paul Chvostek                                             <paul at it.ca>
  Operations / Abuse / Whatever                          +1 416 598-0000
  it.canada - hosting and development                  http://www.it.ca/

--
Unsubscribe mailto:mrtg-request at list.ee.ethz.ch?subject=unsubscribe
Archive     http://www.ee.ethz.ch/~slist/mrtg
FAQ         http://faq.mrtg.org    Homepage     http://www.mrtg.org
WebAdmin    http://www.ee.ethz.ch/~slist/lsg2.cgi