Анализ статистики от RADIUS - Mailing list 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