Анализ статистики от RADIUS
От | Andrey Utkin |
---|---|
Тема | Анализ статистики от RADIUS |
Дата | |
Msg-id | CANZNk81kFjf70x4_OZS7x6Lc7A5pKdQD2az7K0HpPJSMSecMrQ@mail.gmail.com обсуждение исходный текст |
Список | pgsql-ru-general |
Hi all! Есть задача учёта статистики по логам RADIUS. Что учитываем: просмотры людьми телеканалов. На данный момент это ведётся в таком формате: Начался просмотр - делается INSERT со сведениями - id сессии, id юзера, момент начала, id канала и прочее. Закончился - UPDATE заполняет этой записи момент конца просмотра. Как я понял, это практически дефолтные настройки sql-логгирования RADIUS. Таблица выглядит так. =====CODE===== 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) =====CODE===== О системе известно, что 1. сессия длится не более 1 суток 2. в среднем открывается около 5 сессий в секунду Нужно знать, сколько в текущий момент активных сессий, а также строить график кол-ва активных сессий для отрезка времени по имеющейся истории. Есть ли готовые скрипты для такого учёта при таких нагрузках? Оптимален ли формат хранения данных, или стоит его переработать? В моём понимании, этот формат неудобный для работы в реальном времени, т. к. при UPDATE невозможно(прав ли я?) сузить поиск по таблице записей, обновлённых с момента последней обработки. SELECT по времени не покатит, т. к. таблица содержит очень много записей. Поэтому я надумал хранить историю в таком формате: event_id - авто-инкрементный id записи event_ts - timestamp event_type: 1 = open, 2 = close channel - поле как пример инфы для анализа. Получилось создать как-то так. =====CODE===== 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) =====CODE===== Добавил я в таблицу 1 млн записей, что есть примерно двойная суточная нагрузка. Ввод сгенерил следующим скриптом. Он генерит открытие 500тыс сессий, помечает их временем 1970-01-01 00:00:00 + i/EVENTS_PER_SECOND, и затем их закрытие. =====CODE===== $ 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 ';' =====CODE===== Теперь запрос. "Сколько активных (незакрытых) сессий?" В этом тестовом случае выглядит (в самом тупом виде, а другого я пока не придумал) так: =====CODE===== 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 =====CODE===== (Ессно, нужно будет добавить ограничение - просматривать только события за последние сутки) Запрос запустился... И фиг знает, сколько ему нужно будет времени. Понимаю, что нужно уйти от подзапроса, но неясно как. Резюмируя: какой формат более оптимален? Описанный в начале, или второй описанный, или некий другой? Также прошу помочь советами и конкретными формулировками запросов. -- Andrey Utkin
В списке pgsql-ru-general по дате отправления: