Форум PHP

Добрый день.
Подскажите запутался с алгоритмом.

Как? Cделав 1ин запрос к mysql и выбрав все данные за текущий день, по полю `unix_secs`(timestamp)
Распределить на 5 минутные интервалы сложив значения из соседнего поля `doctets` в каждом полученном интервале.
Обратив внимание на поля. `srcaddr`, `dstaddr` (источники складываются отдельно, а назначения отдельно).
Вернуть 1ин массив, под return. Где бы было 576 ключей, начало интервала в timestamp и сумма doctets в этом интервале.

<?php  function report_statip($DATABASE){         $statip = $DATABASE -> query('SELECT `unix_secs`,`srcaddr`, `dstaddr`, `doctets`                    FROM `users_detail_traffic`                    WHERE `unix_secs` >= ? AND (`srcaddr`=? OR `dstaddr`=?)                    ORDER BY `users_detail_traffic`.`unix_secs` ASC',                    $this->get_date(), $this->ip, $this->ip); // $this->get_date() с начала дня. $this->ip IPшник источник/назначение в данном случае он 10.8.1.3         if(empty($statip)) { return false; }         foreach ($statip as $key => $stat){             if ($stat['srcaddr']==$this->ip){                     for ($i=0;$i<=288;$i++){                     $range=array();                     //$range[$i]= ПОЛНЫЙ СТУПОР                      }                 }else{                  //                 }             }         echo $stat['unix_secs']." *  ".$stat['doctets']." *  ".$stat['dstaddr']." *  ".$stat['srcaddr']."<br>";  } ?>

В базе
`unix_secs`, `doctets`, `srcaddr`, `dstaddr`

1428699619 * 141 * 10.8.1.3 * 149.154.167.51 1428699619 * 177 * 149.154.167.51 * 10.8.1.3 1428699669 * 133 * 10.8.1.3 * 64.233.162.188 1428699669 * 135 * 64.233.162.188 * 10.8.1.3 1428699694 * 8291 * 10.8.1.3 * 10.8.1.1 1428699774 * 25658 * 10.8.1.1 * 10.8.1.3 1428699804 * 141 * 10.8.1.3 * 149.154.167.51 1428699804 * 177 * 149.154.167.51 * 10.8.1.3 1428699849 * 8898 * 10.8.1.3 * 10.8.1.1 1428699989 * 141 * 10.8.1.3 * 149.154.167.51 1428699989 * 177 * 149.154.167.51 * 10.8.1.3 1428700004 * 8346 * 10.8.1.3 * 10.8.1.1

Можно реализовать одним группирующим запросом.

Отвечу сам =)

Поиск принадлежности числа ключу массива.
из числа unix_secs отнимаем mktime(0,0,0) полученную разность делим на 288(5 минутный интервал),
округляем до целого в сторону уменьшения round($x, 0, PHP_ROUND_HALF_DOWN);
Это и будет номер ключа массива. Остается только прочитать найденный ключ и если он уже есть добавить к нему octets.


>Можно реализовать одним группирующим запросом.
Интересно, интересно можно примерчик? как распределить на 288 ключей?

Функция мной написанная не полная. Выдернул только суть вопроса. В ней еще присутствует группировка по ip, группировка по протоколу (tcp, udp,icmp,...etc).все это заворачивается в многомерный массив и выплевывается дальше в обработку. Дергать базу лишними ненужными запросами ни к чему. Тем более что данные от нее уже были получены. Остается только их обработать.

Хотя.... возможно, что 3 группирующих запроса к базе будут работать быстрее, чем 1 обрабатывающий скрипт на php.

>делим на 288(5 минутный интервал)
почему на 288. в пяти минутах вроде как 300 секунд, не?
запись добавленная на 288 секунде с начала дня, попадет уже во второй интервал. Думаю, это не соответствует вашим требованиям.

>Интересно, интересно можно примерчик?
очень примерно:

--<? SELECT SUM(doctets),  srcaddr FROM users_detail_traffic GROUP BY FLOOR((unix_secs-start_time)/300), srcaddr

>почему на 288. в пяти минутах вроде как 300 секунд, не?
в 5 минутах 300 секунд, а в сутках 24*60/5=288 5минутных интервала.

Вот что пока вышло, если интересно.
время выполнения скрипта 0.085895776748657 сек Вместе с печатанием в браузер

<?php function report_statip($DATABASE){     $start = microtime(true);         $statip = $DATABASE -> query('SELECT `unix_secs`,`srcaddr`, `dstaddr`, `doctets`, `dpkts`,`srcport`,`dstport`,`prot`                    FROM `users_detail_traffic`.                    WHERE `unix_secs` >= ? AND (`srcaddr`=? OR `dstaddr`=?)                    ORDER BY `users_detail_traffic`.`unix_secs` ASC',                    $this->get_date(), $this->ip, $this->ip);         if(empty($statip)) { return false; }         $range[]=array();             for($i=0;$i<=287;$i++){                 $range[$i]['mktime_interval'] = mktime(0,0,0)+($i*300);                 $range[$i]['time_interval'] = date("Y-m-d H:i:s", $range[$i]['mktime_interval']);                 $range[$i]['ip']=$this->ip;                 $range[$i]['octet_in']=0;                 $range[$i]['octet_out']=0;             }         foreach ($statip as $key => $stat){             $interval=round( ( $statip[$key]['unix_secs'] - mktime(0,0,0) )/288, 0, PHP_ROUND_HALF_DOWN );             if ($stat['dstaddr'] == $this->ip){             $range[$interval]['octet_in'] = $range[$interval]['octet_in'] + $stat['doctets'];             }             if ($stat['srcaddr'] == $this->ip){             $range[$interval]['octet_out'] = $range[$interval]['octet_out'] + $stat['doctets'];             }         }                  foreach ($range as $row => $run){             unset($range[$row]['mktime_interval']);             $range[$row]['speed_in']=number_format(round($range[$row]['octet_in']/1024/1024*8,3),3);             $range[$row]['speed_out']=number_format(round($range[$row]['octet_out']/1024/1024*8,3),3);         }           print_r($range);     echo "\r".(microtime(true) - $start).' сек.';  } ?>

вывод

Array (     [0] => Array         (             [time_interval] => 2015-04-13 00:00:00             [ip] => 10.8.1.3             [octet_in] => 9755             [octet_out] => 13968             [speed_in] => 0.074             [speed_out] => 0.107         )     [1] => Array         (             [time_interval] => 2015-04-13 00:05:00             [ip] => 10.8.1.3             [octet_in] => 19823             [octet_out] => 18806             [speed_in] => 0.151             [speed_out] => 0.143         )     [2] => Array         (             [time_interval] => 2015-04-13 00:10:00             [ip] => 10.8.1.3             [octet_in] => 11767             [octet_out] => 12295             [speed_in] => 0.090             [speed_out] => 0.094         )

Если верить phpmyadmin запрос вида

SELECT SUM(doctets), srcaddr FROM users_detail_traffic GROUP BY FLOOR((1428872400)/300), srcaddr="10.8.1.3" and dstaddr="10.8.1.3"

занимает Отображение строк 0 - 0 (1 всего, Запрос занял 0.2272 сек.).
Немного медленнее.

>>почему на 288. в пяти минутах вроде как 300 секунд, не?
>в 5 минутах 300 секунд, а в сутках 24*60/5=288 5минутных интервала.
это я понимаю, но если вы будете делить на 288, то вы получите 300 интервалов по 288 секунд, а не 288 интервалов по 300 секунд.

>Если верить phpmyadmin запрос вида
>SELECT SUM(doctets), srcaddr FROM users_detail_traffic GROUP BY FLOOR((1428872400)/300), srcaddr="10.8.1.3" and dstaddr="10.8.1.3"

что за группировка такая: srcaddr="10.8.1.3" and dstaddr="10.8.1.3" ?

Я бы добавил поле grp в которое при записи в таблицу сразу записывал интервал, в который попадает текущая запись.
Тогда запрос можно изменить:

<? SELECT  SUM(CASE WHEN dstaddr='192.168.0.3' THEN doctets ELSE 0 END) AS octet_in,  SUM(CASE WHEN srcaddr='192.168.0.3' THEN doctets ELSE 0 END) AS octet_out, grp FROM users_detail_traffic WHERE `unix_secs` >= ? AND  '192.168.0.3' IN (srcaddr, dstaddr) GROUP BY grp

плюс надо поиграться с индексами: добавить индекс на поле grp или grp+unix_secs или unix_secs+grp. Неуверен поможет ли здесь составной индекс

0_o Какой интересный запрос.
В базу пишет отдельная софтина. fprobe + flow-tools. поле grp не добавить к сожалению.

>это я понимаю, но если вы будете делить на 288, то вы получите 300 интервалов по 288 секунд, а не 288 интервалов по 300 секунд.

Да, =) уже словил этот глюк.

>В базу пишет отдельная софтина. fprobe + flow-tools. поле grp не добавить к сожалению.
триггеры могут помочь в данной ситуации

Вообще, поле grp я предлагал добавить только в целях оптимизации, чтобы построить по этому полю индекс.
Хотя, учитывая специфику запроса, имея индекс на поле unix_secs запрос и так должен достаточно шустро отрабатывать (зависит конечно от кол-во записей в сутки).
Так, ради интереса, попробуйте тогда такой запрос

<? SELECT   SUM(CASE WHEN dstaddr='192.168.0.3' THEN doctets ELSE 0 END) AS octet_in,   SUM(CASE WHEN srcaddr='192.168.0.3' THEN doctets ELSE 0 END) AS octet_out, FLOOR((unix_secs - ?)/300) AS grp FROM users_detail_traffic  WHERE `unix_secs` >= ? AND  '192.168.0.3' IN (srcaddr, dstaddr)  GROUP BY grp

где вопросы - это timestamp начала дня. И, если можно, предоставьте explain этого запроса.

<? //за вчера mysql> explain SELECT       -> SUM(CASE WHEN dstaddr='10.8.1.3' THEN doctets ELSE 0 END) AS octet_in,       -> SUM(CASE WHEN srcaddr='10.8.1.3' THEN doctets ELSE 0 END) AS octet_out,     -> FLOOR((unix_secs - 1429045200)/300) AS grp     -> FROM users_detail_traffic      -> WHERE `unix_secs` >= 1429045200 AND  '10.8.1.3' IN (srcaddr, dstaddr)      -> GROUP BY grp; +----+-------------+----------------------+------+---------------+------+---------+------+--------+----------------------------------------------+ | id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        | +----+-------------+----------------------+------+---------------+------+---------+------+--------+----------------------------------------------+ |  1 | SIMPLE      | users_detail_traffic | ALL  | NULL          | NULL | NULL    | NULL | 139685 | Using where; Using temporary; Using filesort | +----+-------------+----------------------+------+---------------+------+---------+------+--------+----------------------------------------------+ 1 row in set (0,01 sec) mysql> //за сегодня mysql> explain SELECT       -> SUM(CASE WHEN dstaddr='10.8.1.3' THEN doctets ELSE 0 END) AS octet_in,       -> SUM(CASE WHEN srcaddr='10.8.1.3' THEN doctets ELSE 0 END) AS octet_out,     -> FLOOR((unix_secs - 1429131600)/300) AS grp     -> FROM users_detail_traffic      -> WHERE `unix_secs` >= 1429131600  AND  '10.8.1.3' IN (srcaddr, dstaddr)      -> GROUP BY grp; +----+-------------+----------------------+------+---------------+------+---------+------+--------+----------------------------------------------+ | id | select_type | table                | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        | +----+-------------+----------------------+------+---------------+------+---------+------+--------+----------------------------------------------+ |  1 | SIMPLE      | users_detail_traffic | ALL  | NULL          | NULL | NULL    | NULL | 139698 | Using where; Using temporary; Using filesort | +----+-------------+----------------------+------+---------------+------+---------+------+--------+----------------------------------------------+ 1 row in set (0,00 sec)

Быстро =)

только вот запрос за вчера

<? mysql> SELECT       -> SUM(CASE WHEN dstaddr='10.8.1.3' THEN doctets ELSE 0 END) AS octet_in,       -> SUM(CASE WHEN srcaddr='10.8.1.3' THEN doctets ELSE 0 END) AS octet_out,     -> FLOOR((unix_secs - 1429045200)/300) AS grp     -> FROM users_detail_traffic      -> WHERE `unix_secs` >= 1429045200 AND  '10.8.1.3' IN (srcaddr, dstaddr)      -> GROUP BY grp; +-----------+-----------+------+ | octet_in  | octet_out | grp  | +-----------+-----------+------+ |  30979620 |   1073662 |    0 | |  15205936 |    542428 |    1 | |  17952166 |    655311 |    2 | |      9248 |     15559 |    3 | |  21378399 |   1516559 |    4 | |  13536909 |    581915 |    5 | |  67910102 |   2204576 |    6 | ********* |     11803 |      5858 |  427 | |    108809 |     58492 |  428 | |    340907 |     40829 |  429 | |     18993 |     19071 |  430 | |     13203 |     16688 |  431 | +-----------+-----------+------+ 402 rows in set (0,07 sec) mysql>

431 grp

>Быстро =)
Вы, видимо, путаете - explain показывает план выполнения запроса, но самой выборки не происходит. Т.е. время 0,01с - это не время выполнения запроса.
Согласно эксплейну ни один индекс не используется. БД все записи перебирает. У вас есть индекс на поле unix_secs?

>только вот запрос за вчера
ну так правильно. "За вчера" захватил и "За сегодня". В таком случае надо unix_secs и "сверху" ограничивать.

В общем, добавьте условие WHERE unix_secs BETWEEN start AND end, + попробуйте добавить индекс на поле unix_secs и посмотрите explain, если опять индекс не будет использован (key = NULL в ответе эксплэйна), то еще добавьте после FROM users_detail_traffic FORCE INDEX(название_индекса).
И замерьте скорость выполнения запроса в разных условиях, с индексом и без.
Да, при замерах скорости отключайте кэширование запроса. Т.е. пишем SELECT SQL_NO_CACHE ...

Вот что получилось.

<? SELECT  SQL_NO_CACHE SUM(CASE WHEN dstaddr='10.8.1.3' THEN doctets ELSE 0 END) AS octet_in,   SUM(CASE WHEN srcaddr='10.8.1.3' THEN doctets ELSE 0 END) AS octet_out, FLOOR((unix_secs - 1429045200)/300) AS grp FROM users_detail_traffic  WHERE `unix_secs` BETWEEN '1429045200' AND '1429131599' AND '10.8.1.3' IN (srcaddr, dstaddr) GROUP BY grp; |  21137001 |    732921 |  285 | | 105570589 |   3571239 |  286 | |     34618 |     26471 |  287 | +-----------+-----------+------+ 270 rows in set (0,09 sec)

индекса на поле unix_secs не было, забыл поставить

<? ALTER TABLE `users_detail_traffic` ADD INDEX(`unix_secs`);

тот же запрос

|  21137001 |    732921 |  285 | | 105570589 |   3571239 |  286 | |     34618 |     26471 |  287 | +-----------+-----------+------+ 270 rows in set (0,05 sec)

explain/ key уже с ключем.

<? +----+-------------+----------------------+-------+---------------+-----------+---------+------+-------+---------------------------------------------------------------------+ | id | select_type | table                | type  | possible_keys | key       | key_len | ref  | rows  | Extra                                                               | +----+-------------+----------------------+-------+---------------+-----------+---------+------+-------+---------------------------------------------------------------------+ |  1 | SIMPLE      | users_detail_traffic | range | unix_secs     | unix_secs | 4       | NULL | 13565 | Using index condition; Using where; Using temporary; Using filesort | +----+-------------+----------------------+-------+---------------+-----------+---------+------+-------+---------------------------------------------------------------------+ 1 row in set (0,00 sec) mysql>

База еще небольшая всего 140145 записей.
Спасибо огромное, Куда пиво засылать? =)

Шлите мне :D)