ReSQL.ru
     
powered by simpleCommunicator - 2.0.60     © 2026 Programmizd 02
Мобильная версия    Контакт    Правила    FAQ    Помощь
Целевая тема:
Создать новую тему:
Автор:
Закрыть
Цитировать
Доб. в избранное | Игнор. тему | Прикреп. тему | Пометить прочит. / непрочит. | Фильтр
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Агрегирование трафика
7 сообщений из 7, страница 1 из 1
Агрегирование трафика
    #34657895
postt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
postt
Участник
Каким образом лучше агрегировать трафик?
Есть таблица логов:
Код: plaintext
1.
2.
3.
4.
5.
6.
traffic
bytes_in double precision
bytes_out double precision
date timestamp
ip inet
id_client int
Нужно сделать три уровня агрегации: по минутам (пятиминутки) - это и есть лог (traffic), из нее нужно получить почасовую и посуточную агрегированную таблицу.
Сейчас сделал триггер AFTER INSERT FOR EACH ROW на таблицу traffic, который обновляет таблицы traffic_hours и traffic_days (или делает INSERT, когда вставляется первая запись за день или час).
Думаю, что когда таблицы будут большими, будет все жутко тормозить... Прав ли я?
Есть еще один вариант: делать агрегацию раз в час и раз в сутки по cron-у, но при этом возникают проблемы с целостностью и нужно склеивать данные из разных таблиц для вывода статистики пользователю.
Какой вариант предпочтительнее и есть ли другие способы? Можно ли как-то прикрутить триггер STATEMENT?
...
Рейтинг: 0 / 0
12.07.2007, 20:31
| Ответить | Цитировать | Написать  
Агрегирование трафика
    #34657937
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
posttКаким образом лучше агрегировать трафик?
Какой вариант предпочтительнее и есть ли другие способы?
Ну триггерно - это действительно не самый лучший, но самый честный вариант.
Вопросы такие:
1. Скока данных прогнозируется в этой таблице?
2. На сколько честные данные пользователь должен иметь? Т.е. задержки в информации о траффике на 5-10-15 минут допустимы?
3. Какая ему нужна детализация данных?
...
Рейтинг: 0 / 0
12.07.2007, 20:56
| Ответить | Цитировать | Написать  
Агрегирование трафика
    #34659885
postt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
postt
Участник
1. 30-60 млн записей в таблице traffic;
2. Так как данные используются в тарификации, то нужны достаточно точные;
3. Данные, агрегированные за час.
...
Рейтинг: 0 / 0
16.07.2007, 06:43
| Ответить | Цитировать | Написать  
Агрегирование трафика
    #34660005
postt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
postt
Участник
Помогите оптимизировать запрос. План и структура таблицы ниже. Записей в таблице чуть больше миллиона.
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
EXPLAIN ANALYZE SELECT sum(bytes_in_m), sum(bytes_out_m), date_trunc('hour', '2007-07-16 12:45:00'::timestamp), ip_m, id_m, type_m FROM traffic_m WHERE date_trunc('hour', date_e_m)=date_trunc('hour', '2007-07-16 12:45:00'::timestamp) GROUP BY date_trunc('hour', '2007-07-16 12:45:00'::timestamp) , ip_m, id_m, type_m;
                                                                                     QUERY PLAN                                              
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 GroupAggregate  (cost= 42739 . 83 .. 46043 . 77  rows= 36258  width= 56 ) (actual time= 26051 . 641 .. 29955 . 147  rows= 4508  loops= 1 )
   ->  Sort  (cost= 42739 . 83 .. 43134 . 13  rows= 157718  width= 56 ) (actual time= 26051 . 111 .. 27167 . 344  rows= 208284  loops= 1 )
         Sort Key: '2007-07-16 12:00:00'::timestamp without time zone, public.traffic_m.ip_m, public.traffic_m.id_m, public.traffic_m.type_m
         ->  Result  (cost= 2 . 02 .. 15943 . 26  rows= 157718  width= 56 ) (actual time= 192 . 145 .. 4329 . 454  rows= 208284  loops= 1 )
               ->  Append  (cost= 2 . 02 .. 15943 . 26  rows= 157718  width= 56 ) (actual time= 192 . 130 .. 2864 . 002  rows= 208284  loops= 1 )
                     ->  Bitmap Heap Scan on traffic_m  (cost= 2 . 02 .. 9 . 64  rows= 4  width= 35 ) (actual time= 0 . 054 .. 0 . 054  rows= 0  loops= 1 )
                           Recheck Cond: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                           ->  Bitmap Index Scan on idx_traffic_m  (cost= 0 . 00 .. 2 . 02  rows= 4  width= 0 ) (actual time= 0 . 043 .. 0 . 043  rows= 0  loops= 1 )
                                 Index Cond: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Bitmap Heap Scan on traffic_m_2007_07 traffic_m  (cost= 1258 . 83 .. 15685 . 82  rows= 157666  width= 35 ) (actual time= 192 . 068 .. 1547 . 232  rows= 208284  loops= 1 )
                           Recheck Cond: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                           ->  Bitmap Index Scan on idx_traffic_m_2007_07_date_trunc  (cost= 0 . 00 .. 1258 . 83  rows= 157666  width= 0 ) (actual time= 189 . 130 .. 189 . 130  rows= 208284  loops= 1 )
                                 Index Cond: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2007_08 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 006 .. 0 . 006  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2007_09 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2007_10 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2007_11 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2007_12 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2008_01 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 005 .. 0 . 005  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2008_02 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2008_03 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2008_04 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 003 .. 0 . 003  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2008_05 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2008_06 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
                     ->  Seq Scan on traffic_m_2008_07 traffic_m  (cost= 0 . 00 .. 20 . 65  rows= 4  width= 56 ) (actual time= 0 . 004 .. 0 . 004  rows= 0  loops= 1 )
                           Filter: (date_trunc('hour'::text, date_e_m) = '2007-07-16 12:00:00'::timestamp without time zone)
 Total runtime:  29980 . 706  ms
( 38  rows)

clients=# \d traffic_m_2007_07
                                       Table "public.traffic_m_2007_07"
     Column     |            Type             |                            Modifiers
----------------+-----------------------------+------------------------------------------------------------------
 id_traffic_m | bigint                      | not null default nextval(('id_traffic_m_seq'::text)::regclass)
 bytes_in_m   | double precision            | not null default  0 
 bytes_out_m  | double precision            | not null default  0 
 date_s_m     | timestamp without time zone | not null
 date_e_m     | timestamp without time zone | not null
 ip_m         | inet                        | not null
 id_m         | integer                     |
 type_m       | integer                     | not null
Indexes:
    "idx_traffic_m_2007_07" btree (date_s_m)
    "idx_traffic_m_2007_07__date_trunc_day" btree (date_trunc('day'::text, date_e_m))
    "idx_traffic_m_2007_07_all" btree (date_trunc('hour'::text, date_e_m), ip_m, id_m, type_m)
    "idx_traffic_m_2007_07_all2" btree (ip_m, id_m, type_m)
    "idx_traffic_m_2007_07_date_e_m" btree (date_e_m)
    "idx_traffic_m_2007_07_date_trunc" btree (date_trunc('hour'::text, date_e_m))
    "idx_traffic_m_2007_07_id_m" btree (id_m)
    "idx_traffic_m_2007_07_ip_m" btree (ip_m)
    "idx_traffic_m_2007_07_type_m" btree (type_m)
Check constraints:
    "traffic_m_2007_07_date_s_m_check" CHECK (date_s_m >= '2007-07-01 00:00:00'::timestamp without time zone AND date_s_m < '2007-08-01 00:00:00'::timestamp without time zone)
Inherits: traffic_m
Многие индексы лишнии, не могу найти нужный
...
Рейтинг: 0 / 0
16.07.2007, 09:29
| Ответить | Цитировать | Написать  
Агрегирование трафика
    #34660348
Andrey Daeron
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
Andrey Daeron
Участник
posttПомогите оптимизировать запрос. План и структура таблицы ниже. Записей в таблице чуть больше миллиона.

С пару месяцев назад было обсуждение счета сквидового трафика, равно как и с примерами так и оптимизацией. Тот запрос до внятного времени таки довели.
...
Рейтинг: 0 / 0
16.07.2007, 11:14
| Ответить | Цитировать | Написать  
Агрегирование трафика
    #34660997
LeXa NalBat
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
LeXa NalBat
Участник
posttПомогите оптимизировать запрос. План и структура таблицы ниже. Записей в таблице чуть больше миллиона.

EXPLAIN ANALYZE SELECT sum(bytes_in_m), sum(bytes_out_m), date_trunc('hour', '2007-07-16 12:45:00'::timestamp), ip_m, id_m, type_m FROM traffic_m WHERE date_trunc('hour', date_e_m)=date_trunc('hour', '2007-07-16 12:45:00'::timestamp) GROUP BY date_trunc('hour', '2007-07-16 12:45:00'::timestamp) , ip_m, id_m, type_m;

Многие индексы лишнии, не могу найти нужныйСамая тормозная часть запроса - сортировка.
Код: plaintext
1.
2.
3.
4.
5.
GroupAggregate (actual time= 26051 . 641 .. 29955 . 147 
Sort (actual time= 26051 . 111 .. 27167 . 344 
Result (actual time= 192 . 145 .. 4329 . 454 
Append (actual time= 192 . 130 .. 2864 . 002 
Bitmap Heap Scan (actual time= 192 . 068 .. 1547 . 232 
Bitmap Index Scan (actual time= 189 . 130 .. 189 . 130
При запросе к целой таблице наверное можно избавиться от сортировки с помощью индекса по "date_trunc('hour', date_e_m), ip_m, id_m, type_m" (этот индекс у вас есть - idx_traffic_m_2007_07_all) переформулировав запрос "... GROUP BY date_trunc('hour', date_e_m), ip_m, id_m, type_m;".

Для партиционированной таблицы вам скорее всего понадобится кроме этого избавиться от Append. Включить set constraint_exclusion to on, и сформулировать (или дополнить) check constraint на таблицы traffic_m_????_?? и traffic_m таким образом, чтобы по нему можно было установить отрицание утверждения "date_trunc('hour', date_e_m)=date_trunc('hour', '2007-07-16 12:45:00'::timestamp)" для всех таблиц кроме единственной traffic_m_2007_07.

Хотя у меня избавиться от Append не получилось :-(
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
create table t1 ( name text, constraint c1 check ( name='' ) );
create table t1_a ( constraint c1_a check ( name between 'a' and 'b' ) )
 inherits (t1);
create table t1_b ( constraint c1_b check ( name between 'b' and 'c' ) )
 inherits (t1);

alter table t1_a drop constraint c1;
alter table t1_b drop constraint c1;

create index i1 on t1 ( name );
create index i1_a on t1_a ( name );
create index i1_b on t1_b ( name );

create rule r1_a as on insert to t1 where name between 'a' and 'b'
 do instead insert into t1_a values ( new.name );
create rule r1_b as on insert to t1 where name between 'b' and 'c'
 do instead insert into t1_b values ( new.name );

insert into t1 values ( 'amam' );
insert into t1 values ( 'baba' );

explain select * from t1 where name between 'aa' and 'az' order by name;

drop table t1 cascade;
Код: plaintext
1.
2.
3.
4.
5.
6.
7.
8.
                                       QUERY PLAN
----------------------------------------------------------------------------------------
 Sort  (cost= 20 . 64 .. 20 . 65  rows= 6  width= 32 )
   Sort Key: public.t1.name
   ->  Result  (cost= 0 . 00 .. 20 . 56  rows= 6  width= 32 )
         ->  Append  (cost= 0 . 00 .. 20 . 56  rows= 6  width= 32 )
               ->  Index Scan using i1_a on t1_a t1  (cost= 0 . 00 .. 20 . 56  rows= 6  width= 32 )
                     Index Cond: ((name >= 'aa'::text) AND (name <= 'az'::text))
( 6  rows)
...
Рейтинг: 0 / 0
16.07.2007, 13:42
| Ответить | Цитировать | Написать  
Агрегирование трафика
    #34663113
postt
Скрыть профиль Поместить в игнор-лист Сообщения автора в теме
postt
Участник
Насколько сильно замедляет работу PRYMARY KEY и стоит ли его тут использовать?
...
Рейтинг: 0 / 0
17.07.2007, 10:20
| Ответить | Цитировать | Написать  
7 сообщений из 7, страница 1 из 1
Форумы / PostgreSQL [игнор отключен] [закрыт для гостей] / Агрегирование трафика
Найденые пользователи ...
Разблокировать пользователей ...
Читали тему (0):
Читали форум (0):
Пользователи онлайн (0):
x
x
Закрыть


Просмотр
0 / 0
Close
Debug Console [Select Text]