rrd_collect.pl (раз в минуту собирает статистику и распихивает по rrd базам)
Код: Выделить всё
#!/usr/bin/perl
use DBI;
$dbname = "";
$username = "";
$password = "";
$dbhost = "";
$dbport = "5432";
$dboptions = "-e";
$dbtty = "ansi";
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost;port=$dbport;options=$dboptions;tty=$dbtty","$username","$password",
{PrintError => 0});
if ($DBI::err != 0) {
print $DBI::errstr . "\n";
exit($DBI::err);
}
$rrdtool="/usr/local/bin/rrdtool";
$rrd_data_dir="/usr/home/rrd/rrd_data"; # Папка для хранения RRD баз
$rrd_graph_dir="/ftp/pub/server-stats"; # Папка для хранения графиков
###########################################################
my (@who_is_online) = ();
my (@all_user_list) = ();
my (@all_list) = ();
my (@who_offline) = ();
my (@off_list) = ();
my $time = time;
$before_time = $time - 90;
################ КТО В ОНЛАЙНЕ ############################
$who_online_query = "SELECT username FROM online";
$sth = $dbh->prepare($who_online_query);
$sth->execute();
while (my @who_online = $sth->fetchrow_array()) {
push(@who_is_online, [@who_online]);
}
################# СПИСОК ВСЕХ ЮЗЕРОВ ######################
$all_users_query = "SELECT username FROM users";
$sth = $dbh->prepare($all_users_query);
$sth->execute();
while (my @all_users = $sth->fetchrow_array()) {
push(@all_user_list, [@all_users]);
}
################# ОФФЛАЙН #################################
push(@all_list, @all_user_list, @who_is_online);
for $rsh ( @all_list ) {
$z = "@$rsh";
push (@who_offline, $z);
}
my $hash;
foreach $i (@who_offline){
$hash{$i}++;
}
foreach $key (keys %hash){
if($hash{$key}==1){push(@off_list, $key);}
}
######## ЕСЛИ ЮЗЕР ОНЛАЙН ДОБАВЛЯЕМ СТАТИСТИКУ В RRD БАЗУ ######
$online_update_query = "SELECT online.uid, online.last_change, users.username, users.uid AS usersuid, graph_data.id_user, graph_data.lin_bytes, graph_data.lout_bytes, graph_data.date_time FROM online, users, graph_data \
WHERE online.uid=users.uid AND graph_data.id_user=online.uid AND graph_data.date_time BETWEEN '$before_time' AND '$time'";
$sth = $dbh->prepare($online_update_query);
$sth->execute();
while (($uid,$last_change,$username,$uuid,$id_user,$in_bytes,$out_bytes,$date_time) = $sth->fetchrow_array()) {
#print "$username\n";
if (!(-e "$rrd_data_dir/$username.rrd"))
{
system ("$rrdtool create $rrd_data_dir/$username.rrd \\
DS:input:ABSOLUTE:600:U:U \\
DS:output:ABSOLUTE:600:U:U \\
RRA:AVERAGE:0.5:1:600 \\
RRA:AVERAGE:0.5:6:700 \\
RRA:AVERAGE:0.5:24:775 \\
RRA:AVERAGE:0.5:288:797 \\
RRA:MAX:0.5:1:600 \\
RRA:MAX:0.5:6:700 \\
RRA:MAX:0.5:24:775 \\
RRA:MAX:0.5:288:797");
}
#
system ($rrdtool." update $rrd_data_dir/$username.rrd N:$out_bytes:$in_bytes");
}
############# ЕСЛИ ЮЗЕР ОФФЛАЙН ЗАБИВАЕМ RRD БАЗУ НОЛЯМИ #####
for $off_name (@off_list) {
#print "$off_name.rrd";
if (!(-e "$rrd_data_dir/$off_name.rrd"))
{
system ("$rrdtool create $rrd_data_dir/$off_name.rrd \\
DS:input:ABSOLUTE:600:U:U \\
DS:output:ABSOLUTE:600:U:U \\
RRA:AVERAGE:0.5:1:600 \\
RRA:AVERAGE:0.5:6:700 \\
RRA:AVERAGE:0.5:24:775 \\
RRA:AVERAGE:0.5:288:797 \\
RRA:MAX:0.5:1:600 \\
RRA:MAX:0.5:6:700 \\
RRA:MAX:0.5:24:775 \\
RRA:MAX:0.5:288:797");
}
system ($rrdtool." update $rrd_data_dir/$off_name.rrd N:0:0");
}
##############################################################
$sth->finish();
$dbh->disconnect();
Код: Выделить всё
#!/usr/bin/perl
use DBI;
$dbname = "";
$username = "";
$password = "";
$dbhost = "";
$dbport = "5432";
$dboptions = "-e";
$dbtty = "ansi";
$dbh = DBI->connect("dbi:Pg:dbname=$dbname;host=$dbhost;port=$dbport;options=$dboptions;tty=$dbtty","$username","$password",
{PrintError => 0});
if ($DBI::err != 0) {
print $DBI::errstr . "\n";
exit($DBI::err);
}
$rrdtool="/usr/local/bin/rrdtool";
$rrd_data_dir="/usr/home/rrd/rrd_data"; # Папка для хранения RRD баз
$rrd_graph_dir="/ftp/pub/server-stats"; # Папка для хранения графиков
##############################################
$get_users_query = "SELECT username FROM users";
$sth = $dbh->prepare($get_users_query);
$sth->execute();
while (($username) = $sth->fetchrow_array()) {
if (!(-e "$rrd_data_dir/$username.rrd"))
{
system ("$rrdtool create $rrd_data_dir/$username.rrd \\
DS:input:ABSOLUTE:600:U:U \\
DS:output:ABSOLUTE:600:U:U \\
RRA:AVERAGE:0.5:1:600 \\
RRA:AVERAGE:0.5:6:700 \\
RRA:AVERAGE:0.5:24:775 \\
RRA:AVERAGE:0.5:288:797 \\
RRA:MAX:0.5:1:600 \\
RRA:MAX:0.5:6:700 \\
RRA:MAX:0.5:24:775 \\
RRA:MAX:0.5:288:797");
}
%date_data =(
'day' => '-86400:-300',
'week' => '-604800:-1800',
'month' => '-2678400:-7200',
'year' => '-33053184:-86400',
);
$inceil = 1024*1024;
$outceil = 1024*1024;
foreach $_ (keys %date_data)
{
($start, $end) = split (/:/, $date_data{$_});
system "$rrdtool graph $rrd_graph_dir/$username-$_.png \\
--start $start --end $end \\
--width 650 --height=150 --rigid --alt-autoscale-max --lower-limit=0 \\
--vertical-label=\"bits per second\" \\
--color CANVAS#F1F1F1 \\
DEF:in=$rrd_data_dir/$username.rrd:input:AVERAGE \\
DEF:out=$rrd_data_dir/$username.rrd:output:AVERAGE \\
CDEF:inbits=in,8,* \\
CDEF:outbits=out,8,* \\
CDEF:inperc=inbits,100,* \\
CDEF:outperc=outbits,100,* \\
CDEF:inperc_res=inperc,$inceil,/ \\
CDEF:outperc_res=outperc,$outceil,/ \\
AREA:inbits#00CC00:\"Input (Kbit/s)\" \\
LINE1:outbits#0000FF:\"Output (Kbit/s)\" \\
COMMENT:\"\\n\" \\
COMMENT:\"\\n\" \\
COMMENT:\"Max In\\:\" \\
GPRINT:inbits:MAX:\" %4.2lf %sb/s\\g\" \\
GPRINT:inperc_res:MAX:\" (%3.1lf\\g\" \\
COMMENT:\"%)\" \\
COMMENT:\" Avg In\\:\" \\
GPRINT:inbits:AVERAGE:\" %4.2lf %sb/s\\g\" \\
GPRINT:inperc_res:AVERAGE:\" (%3.1lf\\g\" \\
COMMENT:\"%)\" \\
COMMENT:\" Cur In\\:\" \\
GPRINT:inbits:LAST:\" %4.2lf %sb/s\\g\" \\
GPRINT:inperc_res:LAST:\" (%3.1lf\\g\" \\
COMMENT:\"%)\" \\
COMMENT:\"\\n\" \\
COMMENT:\"Max Out\\:\" \\
GPRINT:outbits:MAX:\" %4.2lf %sb/s\\g\" \\
GPRINT:outperc_res:MAX:\" (%3.1lf\\g\" \\
COMMENT:\"%)\" \\
COMMENT:\" Avg Out\\:\" \\
GPRINT:outbits:AVERAGE:\" %4.2lf %sb/s\\g\" \\
GPRINT:outperc_res:AVERAGE:\" (%3.1lf\\g\" \\
COMMENT:\"%)\" \\
COMMENT:\" Cur Out\\:\" \\
GPRINT:outbits:LAST:\" %4.2lf %sb/s\\g\" \\
GPRINT:outperc_res:LAST:\" (%3.1lf\\g\" \\
COMMENT:\"%)\" \\
COMMENT:\"\\n\" >/dev/null";
}
}
$sth->finish();
$dbh->disconnect();
Код: Выделить всё
CREATE TABLE "graph_data" (
"graph_id" BIGSERIAL NOT NULL,
"id_user" int8 NOT NULL DEFAULT (0)::bigint,
"lin_bytes" int8 NOT NULL DEFAULT (0)::bigint,
"lout_bytes" int8 NOT NULL DEFAULT (0)::bigint,
"date_time" int4 NOT NULL DEFAULT unix_timestamp(now()),
CONSTRAINT "graph_data_pkey" PRIMARY KEY("graph_id")
)
WITHOUT OIDS;
CREATE RULE "graph_data_act" AS
ON UPDATE TO actions DO ALSO
INSERT INTO graph_data (id_user, lin_bytes, lout_bytes) VALUES(OLD.uid, NEW.in_bytes-OLD.in_bytes, NEW.out_bytes-OLD.out_bytes);
