TrafStat

Имеются следующие статистические данные:

За сутки работы системы база весит 48 Мбайт.
Число строк в таблице 'traffic' равен 304448.
За день к внешним ресурсам обращалось порядка 60 пользователей.
Плюс целую ночь раздавались торенты.

Может стоит сразу рассчитать минимальный необходимый интервал? Например, час. И либо на ходу суммировать, либо раз в сутки.

Собственно примерно думаю реализовать вот так.

У нас есть структура таблицы которая хранит информацию о трафике, назовем ее 'traffic':

create table `traffic`
(
  `date` datetime not null,
  `srcIp` int unsigned not null,
  `dstIp` int unsigned not null,
  `dstPort` int not null,
  `pId` tinyint not null,
  `inBytes` int unsigned not null,
  `outBytes` int unsigned not null,
  key `key1` (`date`),
  key `key2` (`srcIp`, `date`)
) engine = MyISAM;

Есть таблица trafficCurrent в которой будет храниться информация о трафике с разрешением в один час, т.е. детализация статистики по пользователю в один час. Эта таблица будет содержать данные о трафике только за текущий месяц, т.е. данные старше одного месяца будут удаляться из нее. Эта информация будет доступна например по ссылке "Текущая статистика" которая будет располагаться на главной странице "Статистики по месяцам".

Так же будут таблицы traffic2000, traffic2001, traffic2002 и т.д. в которых будут храниться данные о трафике за год. Но детализация о трафике в них будет в один месяц. Эта информация будет доступна через ссылки столбца "период" которые располагаются на главной странице "Статистика по месяцам". Эти таблицы, кроме таблицы в которую добавляется информация за текущий год, можно будет так же сжимать с помощью myisampack, что так же позволит уменьшить место занимаемое ими на диске.

Для работы с таблицами которые хранят данные за год создается служебная таблица 'periodTraffic':

create table `periodTraffic`
(
  `date` datetime not null,
  `tableName` char(10) not null,
  key `key1` (`date`)
) engine = MyISAM;

Соответственно WEB-интерфейс статистики считывает данные из таблицы periodTraffic, далее выводит информацию о трафике считывая данные из соответствующих таблиц.

А параметр указывающий сколько лет хранить информацию о трафике будет задаваться через настройки WEB-интерфейса.

Как то так. У кого есть какие замечания или мысли по этому поводу высказывайтесь.

Роман Чернышов писал(а):

Может стоит сразу рассчитать минимальный необходимый интервал? Например, час. И либо на ходу суммировать, либо раз в сутки.

Сейчас так и делается, данные на ходу суммируются и если только нет записи о этом клиенте то данная запись добавляется:

...

# получим текущую дату
my ($sec,$min,$hour,$day,$mon,$year) =
  localtime((@ARGV) ? time + ((shift @ARGV) * 60) : time);
$year += 1900; $mon++;
# Округлим значение времени до 30 минут. Это делается для того,
# что бы данные в базе были кратны одному часу. Это позволит
# базе данных быстрее работать с таблицей хранящей трафик.
$min = 30;
$sec = 0;
my $date = "$year-$mon-$day $hour:$min:$sec";

...

$query = 'insert into traffic ' .
  '(date, srcIp, dstIp, dstPort, pId, inBytes, outBytes) ' .
  'values(?, ?, ?, ?, ?, ?, ?)';
$sth = $dbh->prepare($query) or
  error("Failed prepare query '$query': " . $dbh->errstr);

...

  # добавим полученный трафик к уже имеющемуся в базе
  $rv = $dbh->do(qq{update traffic set inBytes = inBytes + $inBytes, outBytes = outBytes + $outBytes
    where date = '$date' && srcIp = '$srcIp' && dstIp = '$dstIp' && dstPort = '$dstPort' && pId = '$pId'}) or
      error("Failed execute 'do' query: " . $dbh->errstr);

  # если в базе еще нет записи о данном трафике, то добавим ее
  if ($rv eq "0E0") {
    $sth->execute($date, $srcIp, $dstIp, $dstPort, $pId, $inBytes, $outBytes) or
      error("Failed execute query '$query': " . $dbh->errstr);
  } elsif ($rv > 1) {
    warning("When add data to the database updated more than one record ($rv).");
  }

...