Hi all!
Есть задача учёта статистики по логам RADIUS.
Что учитываем: просмотры людьми телеканалов.
На данный момент это ведётся в таком формате:
Начался просмотр - делается INSERT со сведениями - id сессии, id юзера, момент начала, id канала и прочее.
Закончился - UPDATE заполняет этой записи момент конца просмотра.
Как я понял, это практически дефолтные настройки sql-логгирования RADIUS.
Таблица выглядит так.
radius-# \d radacct TABLE "public.radacct" COLUMN | Type | Modifiers ----------------------+--------------------------+------------------------------------------------------------- radacctid | bigint | NOT NULL DEFAULT NEXTVAL('radacct_radacctid_seq'::regclass) acctsessionid | character varying(64) | NOT NULL acctuniqueid | character varying(32) | NOT NULL username | character varying(253) | groupname | character varying(253) | realm | character varying(64) | nasipaddress | inet | NOT NULL nasportid | character varying(15) | nasporttype | character varying(32) | acctstarttime | timestamp WITH time zone | acctstoptime | timestamp WITH time zone | acctsessiontime | bigint | acctauthentic | character varying(32) | connectinfo_start | character varying(50) | connectinfo_stop | character varying(50) | acctinputoctets | bigint | acctoutputoctets | bigint | calledstationid | character varying(50) | callingstationid | character varying(50) | acctterminatecause | character varying(32) | servicetype | character varying(32) | xascendsessionsvrkey | character varying(10) | framedprotocol | character varying(32) | framedipaddress | inet | acctstartdelay | integer | acctstopdelay | integer | channelid | integer | ....... Indexes: "radacct_pkey" PRIMARY KEY, btree (radacctid) "channel_id_index" btree (channelid) "radacct_active_user_idx" btree (username, nasipaddress, acctsessionid) WHERE acctstoptime IS NULL "radacct_start_user_idx" btree (acctstarttime, username) "start_stop_time_indexes" btree (acctstoptime, acctstarttime)
О системе известно, что
1. сессия длится не более 1 суток
2. в среднем открывается около 5 сессий в секунду
Нужно знать, сколько в текущий момент активных сессий, а также строить график кол-ва активных сессий для отрезка времени по имеющейся истории.
Есть ли готовые скрипты для такого учёта при таких нагрузках?
Оптимален ли формат хранения данных, или стоит его переработать?
В моём понимании, этот формат неудобный для работы в реальном времени, т. к. при UPDATE невозможно(прав ли я?) сузить поиск по таблице записей, обновлённых с момента последней обработки. SELECT по времени не покатит, т. к. таблица содержит очень много записей.
Поэтому я надумал хранить историю в таком формате:
event_id - авто-инкрементный id записи
event_ts - timestamp
event_type: 1 = open, 2 = close
channel - поле как пример инфы для анализа.
Получилось создать как-то так.
TABLE "public.log" COLUMN | Type | Modifiers ------------+-----------------------------+-------------------------------------------------------- event_id | bigint | NOT NULL DEFAULT NEXTVAL('log_event_id_seq'::regclass) event_ts | timestamp without time zone | event_type | smallint | channel | integer | session_id | character varying(30) | Indexes: "log_pkey" PRIMARY KEY, btree (event_id)
Добавил я в таблицу 1 млн записей, что есть примерно двойная суточная нагрузка.
Ввод сгенерил следующим скриптом.
Он генерит открытие 500тыс сессий, помечает их временем 1970-01-01 00:00:00 + i/EVENTS_PER_SECOND,
и затем их закрытие.
$ cat fill_table.sh #!/bin/bash SET -e NUM_OF_RECORDS=500000 EVENTS_PER_SECOND=100 NUM_OF_CHANNELS=10 echo "insert into log (event_ts, event_type, channel, session_id) values " I=0 while [[ $I -lt $NUM_OF_RECORDS ]] do IF [[ $I != 0 ]] then echo ',' fi echo "('1970-01-01 0:0:0'::timestamp+'"$(( $I / $EVENTS_PER_SECOND ))" sec'::interval, " \ "1, " \ $(( $I % $NUM_OF_CHANNELS )) ", " \ "'$I' )" I=$(( $I + 1 )) done echo ';' echo "insert into log (event_ts, event_type, channel, session_id) values " I=0 while [[ $I -lt $NUM_OF_RECORDS ]] do IF [[ $I != 0 ]] then echo ',' fi echo "('1970-01-01 0:0:0'::timestamp+'"$(( ( $NUM_OF_RECORDS + $I ) / $EVENTS_PER_SECOND ))" sec'::interval, " \ "2, " \ $(( $I % $NUM_OF_CHANNELS )) ", " \ "'$I' )" I=$(( $I + 1 )) done echo ';'
Теперь запрос. "Сколько активных (незакрытых) сессий?" В этом случае выглядит (в самом тупом виде, а другого я пока не придумал) так:
echo ' select count(*) from log as superquery where (event_type = 1) and (0 = (select count(*) from log as subquery where (subquery.event_id > superquery.event_id) and (event_type = 2) ) );' | psql -U postgres stats
Резюмируя: какой формат более оптимален? Описанный в начале, или второй описанный, или некий другой? Также прошу помочь советами и конкретными формулировками запросов.
Сумбурное впечатление
Вы написали целый трактат, но честно говоря, совершенно непонятно чего вы хотите от нас (читающих) ваш материал? Вопросы уж больно неконкретные:
> какой формат более оптимален?
Вы мне скажите какой оптимален ДЛЯ ВАС. Система ваша, разборки с данными тоже ваши.
С точки зрения теории БД оптимальным является НФБК, но есть вот такая оптимизация, а есть реалии жизни, когда приходится нормализовывать всё-таки до какого-то предела, а не до НФБК.
> SELECT по времени не покатит, т. к. таблица содержит очень много записей.
Если сделать индекс и применить разбиение (PARTITION) ещё как катит. А можно ещё и триггером воспользоваться при INSERT сохранять ID сессии в какой-либо таблице, при UPDATE убирать ID оттуда. Вот вам сходу два идеи. А вот создавать дублирующую данные вторую таблицу - точно не самая блестящая идея.
В свете вышеизложенного, рассматривать запросы не вижу смысла - сперва структуру БД создайте. Мне ничего сложного не видится, даже с учётом PARTITION. Кстати почитать об этом можно тут:
http://postgresql.ru.net/manual/ddl-partitioning.html