Overview

It may be necessary to collect stream, client connections and CDN events data while managing a big number of WCS servers, to debug a streaming problems. In fact, the information that logged on every server, should be collected at one point. Note that logging itself is minimized in production use, to prevent server disk excessive load.

To collect such big amount of data, time series databases are good choice. Since build 5.2.774, the Remote Event Logging System (RELS) based on open source time series DB ClickHouse can be used to collect server logs.

Architecture

Every WCS server sends logging data to ClickHouse DB independently using JDBC-driver and HTTP connection. To optimize ClickHouse server load, the data are buffered and sent by time interval batch

Data table description

The logging data are collected to ClickHouse tables listed below. To speed up, an integer event identifiers are written to the tables. There is a textual string dictionary decribing all the events for each table to display human readable selection results.

Connections data (table ConnectionEvent)

Field	Type	Description
timestamp	UInt64	Time stamp
ip	IPv4	Server address
sessionId	String	Session Id
eventType	UInt64	Event type Id
eventPayload	String	Event payload

Streams data (table StreamEvent)

Field	Type	Description
timestamp	UInt64	Time stamp
ip	IPv4	Server address
sessionId	String	Session Id
mediaSessionId	String	Media session Id
streamName	String	Stream name
eventType	UInt64	Event type Id
eventPayload	String	Event payload

CDN data (table CDNEvent)

Field	Type	Description
timestamp	UInt64	Time stamp
ip	IPv4	Server address
nodeId	String	Node Id (IP address string)
eventType	UInt64	Event type Id
eventPayload	String	Event payload

Stream metrics data (table MediaSessionEvent)

Since build 5.2.1896 it is possible to collect a certain stream metrics data

Field	Type	Description
timestamp	UInt64	Time stamp
ip	IPv4	Server address
mediaSessionId	String	Media session Id
streamName	String	Stream name
videoProfileId	UInt32	Video profile Id
videoWidth	UInt32	Pictiure height
videoHeight	UInt32	Picture width
videoFrameRate	UInt32	Video frame rate per second
videoKframes	UInt64	Key frames number (I-frame)
videoPframes	UInt64	P-frames number
videoBframes	UInt64	B-frames number
videoRate	UInt64	Video bitrate, bps
audioRate	UInt64	Audio bitrate, bps
videoSyncTime	UInt64	Video synchronization value
audioSyncTime	UInt64	Audio synchronization value
videoTimestamp	UInt64	Video packet timestamp
audioTimestamp	UInt64	Audio packet timestamp
lastKeyFrameSyncTime	UInt64	Video synchronization value from the last key frame
sendNACK	UInt64	NACK sent count
recvNACK	UInt64	NACK received count
videoFramesLost	UInt64	Lost video frames count
audioPacketsLost	UInt64	Lost audio frames count
audioPlaybackSpeed	Float32	Audio publishing/playback speed
videoPlaybackSpeed	Float32	Video publishing/playback speed

HLS stream data (table HlsStreamEvent)

Since build 5.2.1917 it is possible to collect a certain HLS stream events data

Field	Type	Description
timestamp	UInt64	Time stamp
ip	IPv4	Server address
severity	UInt8	Event severity level: INFO, WARNING, ERROR
messageType	UInt16	Event type
streamId	String	HLS stream Id
variantName	String	Quality variant name
segmentId	String	HLS segment Id
message	String	Logged event description

HLS segments data (table HlsSegmenterEvent)

Since build 5.2.1917 it is possible to collect a certain HLS stream segments data

Field	Type	Description
timestamp	UInt64	Time stamp
ip	IPv4	Server address
streamId	String	HLS stream Id
variantName	String	Quality variant name
segmentId	String	Segment Id
segmentStartPts	UInt64	Segment start PTS
videoStartPts	UInt64	Video start PTS
audioStartPts	UInt64	Audio start PTS
videoWidth	UInt32	Picture width
videoHeight	UInt32	Picture height
videoFrameCount	UInt32	Video frames count
audioPacketCount	UInt32	Audio packets count
segmentDuration	UInt64	Segment duration, ms
independent	Bool	Independent segment
gap	Bool	GAP segment
discontinuity	Bool	DISCONTINUTY segment
segmentInterval	UInt64	Segments interval, ms
partial	Bool	Partial segment
playbackSpeed	Float32	Playback speed

HLS subscribers data (table HlsClientEvent)

Since build 5.2.1929 it is possible to collect a certain HLS stream subscribers data

Field	Type	Description
creationTime	UInt64	Subscriber creation time (a first request)
responseTime	UInt64	Subscriber response time
streamId	String	HLS stream Id
variantName	String	Quality variant name
uri	String	Playlist URI
localIp	IPv4	Server address
remoteIp	IPv4	Client address
remotePort	UInt32	Client port
userAgent	String	User-Agent data
httpStatus	UInt32	Response status
clientId	UInt64	Client session Id

Mixer metrics data (table MixerEvent)

Since build 5.2.1923 it is possible to collect a certain mixer metrics data

Field	Type	Description
timestamp	UInt64	Time stamp
mixerMediaSessionId	String	Mixer media session Id
mixerStreamName	String	Mixer output stream name
mediaSessionId	String	Incoming stream media session Id
streamName	String	Incoming stream name
mixerAverageTickTimeInMs	Int64	Mixer tick average duration, ms
audioMixerSync	Int64	Mixer output stream audio synchronization value
videoMixerSync	Int64	Mixer output stream audio synchronization value
nextAudioDataTime	Int64	Next audio packet time
nextVideoDataTime	Int64	Next video packet time
audioBuffered	Int64	Incoming stream audio packets buffered count
videoBuffered	Int64	Incoming stream video packets buffered count
audioDropsCounter	Int64	Incoming stream audio packets dropped count
audioDropsSizeInBytes	Int64	Incoming stream audio dropped data in bytes
videoDropsCounter	Int64	Incoming stream video packets dropped count
videoDropsSizeInBytes	Int64	Incoming stream video dropped data in bytes
videoFps	Int64	Mixer output stream framerate per second
audioRate	DOUBLE	Mixer output stream audio bitrate, bps
videoRate	DOUBLE	Mixer output stream audio bitrate, bps
eventType	UInt32	Mixer event type
eventPayload	String	Event description

Incoming audio recovery data (table AudioRecoveryEvent)

Since build 5.2.1969 it is possible to collect a certain stream audio recovery data

Field	Type	Description
timestamp	UInt64	Time stamp
mediaSessionId	String	Media session Id
type	UInt32	Event type
rtpTimestamp	UInt64	RTP stream timestamp

RTMP incoming streams buffer metrics data (table RtmpInBufferEvent)

Since build 5.2.1978 it is possible to collect a certain incoming RTMP stream buffer metrics data

Field	Type	Description
timestamp	UInt64	Time stamp
streamClockTime	UInt64	Stream clock time
mediaSessionId	String	Media session Id
streamName	String	Stream name
nextAudioDataTime	Int64	Next audio data packet time
nextVideoDataTime	Int64	Next video data packet time
audioBuffered	Int64	Audio packets buffered count
videoBuffered	Int64	Video packets buffered count
maximumAllowedBuffer	Int64	Maximum packet allowed in the buffer
bufferingCounter	Int64	Bufferings counter
lastAudioDataTime	Int64	Last audio data packet time
lastVideoDataTime	Int64	Last video data packet time
bufferState	UInt32	Buffer state

Configuration

ClickHouse installation and setup

Server requirement

CPU from 4 physical cores, frequency from 3 GHz, for example Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHz
RAM from 32 Gb
HDD from 2 Tb

ClickHouse installation from rpm package (CentOS, Red Hat etc)

1. Add the official repository

sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo

2. Install ClickHouse

sudo yum install -y clickhouse-server clickhouse-client

3. Launch ClickHouse

sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server

ClickHouse installation from deb package (Debian, Ubuntu etc)

1. Add the official repository

sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

2. Install ClickHouse

sudo apt-get install -y clickhouse-server clickhouse-client

3. Launch ClickHouse

sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server

ClickHouse configuration for WCS builds before 5.2.1999

1. Uncomment the following string in /etc/clickhouse-server/config.xml file to listen all the server network interfaces

    <listen_host>::</listen_host>

2. Set the following parameter for default user in /etc/clickhouse-server/users.xml file to temporary allow users management

            <access_management>1</access_management>

3. Restart ClickHouse

systemctl restart clickhouse-server

4. Create wcs database and tables

cat wcs_clickhouse.sql | clickhouse-client -mn

wcs_clickhouse.sql

CREATE DATABASE IF NOT EXISTS wcs;

DROP DICTIONARY IF EXISTS wcs.DictionaryStreamEvents;
 
DROP DICTIONARY IF EXISTS wcs.DictionaryConnectionEvents;
 
DROP DICTIONARY IF EXISTS wcs.DictionaryCDNEvents;

DROP DICTIONARY IF EXISTS wcs.DictionaryHlsStreamEventType;

DROP DICTIONARY IF EXISTS wcs.DictionaryHlsStreamEventSeverity;
 
DROP DICTIONARY IF EXISTS wcs.DictionaryMixerEvents;

DROP DICTIONARY IF EXISTS wcs.DictionaryBufferStateTypes;
 
DROP TABLE IF EXISTS wcs.StreamEvent;
 
DROP TABLE IF EXISTS wcs.ConnectionEvent;
 
DROP TABLE IF EXISTS wcs.CDNEvent;
 
DROP TABLE IF EXISTS wcs.StreamEventTypes;
 
DROP TABLE IF EXISTS wcs.ConnectionEventTypes;
 
DROP TABLE IF EXISTS wcs.CDNEventTypes;

DROP TABLE IF EXISTS wcs.MediaSessionEvents;

DROP TABLE IF EXISTS wcs.HlsStreamEvents;

DROP TABLE IF EXISTS wcs.HlsSegmenterEvents;

DROP TABLE IF EXISTS wcs.HlsStreamEventSeverity;

DROP TABLE IF EXISTS wcs.HlsStreamEventType;

DROP TABLE IF EXISTS wcs.HlsClientEvents;

DROP TABLE IF EXISTS wcs.MixerEvent;

DROP TABLE IF EXISTS wcs.MixerEventTypes;

DROP TABLE IF EXISTS wcs.RtmpInBufferEvent;

DROP TABLE IF EXISTS wcs.AudioRecoveryEvent;

DROP TABLE IF EXISTS wcs.BufferStateTypes;

CREATE TABLE wcs.ConnectionEventTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
 
INSERT INTO wcs.ConnectionEventTypes VALUES (0, 'CONNECTED'), (1, 'DISCONNECTED');
 
CREATE TABLE wcs.StreamEventTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
 
INSERT INTO wcs.StreamEventTypes VALUES (0,'CREATED'),(1,'LOCAL_SDP_CREATED'),(2,'REMOTE_SDP_RECEIVED'),(3,'ICE_STARTED'),(4,'ICE_COMPLETE'),(5,'DTLS_STARTED'),(6,'DTLS_COMPLETE'),(7,'INITIALIZED'),(8,'DISPOSING'),(9,'DISPOSED'),(10,'AUDIO_RECEIVED'),(11,'VIDEO_RECEIVED'),(12,'VIDEO_KFRAME_RECEIVED'),(13,'AUDIO_RTCP_RECEIVED'),(14,'VIDEO_RTCP_RECEIVED'),(15,'RESOLUTION_RECEIVED'),(16,'VIDEO_ENCODER_CREATED'),(17,'AUDIO_ENCODER_CREATED'),(18,'VIDEO_ENCODER_DISPOSED'),(19,'AUDIO_ENCODER_DISPOSED'),(20,'TERMINATED'),(21,'AUDIO_SENT'),(22,'VIDEO_SENT'),(23,'VIDEO_JITTER_BUFFER_STALL'),(24,'SENT_PLI'),(25,'RECEIVED_PLI'),(26,'SYNC_BUFFER_FULL'),(27,'SYNC_FORCE_FAILED'),(28,'SYNC_SHIFT'),(29,'SYNC_DEVIATION'),(30,'VIDEO_STATS'),(31,'RECORD');
 
CREATE TABLE wcs.CDNEventTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
 
INSERT INTO wcs.CDNEventTypes VALUES (0, 'STATE'), (1, 'CDN_STATE'), (2, 'VERSION'), (3, 'ACL_REFRESH'), (4, 'ACL_UPDATE');
 
CREATE DICTIONARY wcs.DictionaryStreamEvents (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'StreamEventTypes'
))   
LAYOUT(FLAT())
LIFETIME(300);
 
CREATE DICTIONARY wcs.DictionaryConnectionEvents (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'ConnectionEventTypes'
))   
LAYOUT(FLAT())
LIFETIME(300);
 
CREATE DICTIONARY wcs.DictionaryCDNEvents (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'CDNEventTypes'
))   
LAYOUT(FLAT())
LIFETIME(300);
 
CREATE TABLE wcs.StreamEvent
(
    `timestamp` UInt64,
    `ip` IPv4,
    `sessionId` String,
    `mediaSessionId` String,
    `streamName` String,
    `eventType` UInt64,
    `eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (sessionId, mediaSessionId, streamName)
SETTINGS index_granularity = 8192;
 
CREATE TABLE wcs.ConnectionEvent
(
    `timestamp` UInt64,
    `ip` IPv4,
    `sessionId` String,
    `eventType` UInt64,
    `eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (timestamp, sessionId)
SETTINGS index_granularity = 8192;
 
CREATE TABLE wcs.CDNEvent
(
    `timestamp` UInt64,
    `ip` IPv4,
    `nodeId` String,
    `eventType` UInt64,
    `eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (nodeId, eventType)
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.MediaSessionEvents
(
    `timestamp` UInt64,
    `ip` IPv4,
    `mediaSessionId` String,
    `streamName` String,
    `videoProfileId` UInt32,
    `videoWidth` UInt32,
    `videoHeight` UInt32,
    `videoFrameRate` UInt32,
    `videoKframes` UInt64,
    `videoPframes` UInt64,
    `videoBframes` UInt64,
    `videoRate` UInt64,
    `audioRate` UInt64,
    `videoSyncTime` UInt64,
    `audioSyncTime` UInt64,
    `videoTimestamp` UInt64,
    `audioTimestamp` UInt64,
    `lastKeyFrameSyncTime` UInt64,
    `sendNACK` UInt64,
    `recvNACK` UInt64,
    `videoFramesLost` UInt64,
    `audioPacketsLost` UInt64,
    `audioPlaybackSpeed` Float32,
    `videoPlaybackSpeed` Float32
)
ENGINE = MergeTree()
ORDER BY (mediaSessionId, streamName)
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.HlsSegmenterEvents
(
    `timestamp` UInt64,
    `ip` IPv4,
    `streamId` String,
    `variantName` String,
    `segmentId` String,
    `segmentStartPts` UInt64,
    `videoStartPts` UInt64,
    `audioStartPts` UInt64,
    `videoWidth` UInt32,
    `videoHeight` UInt32,
    `videoFrameCount` UInt32,
    `audioPacketCount` UInt32,
    `segmentDuration` UInt64,
    `independent` Bool,
    `gap` Bool,
    `discontinuity` Bool,
    `segmentInterval` UInt64,
    `partial` Bool,
    `playbackSpeed` Float32
)
ENGINE = MergeTree()
ORDER BY (streamId)
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.HlsStreamEventSeverity
(
    `id` UInt8,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO wcs.HlsStreamEventSeverity VALUES (0, 'INFO'), (1, 'WARNING'), (2, 'ERROR');

CREATE TABLE wcs.HlsStreamEventType
(
    `id` UInt16,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO wcs.HlsStreamEventType
VALUES (0, 'PLAYBACK_SPEED'), (1, 'FPS_CHANGED'), (2, 'GAP'), (3, 'RESOLUTION_CHANGED'), (4, 'DISCONTINUITY'), (5, 'TASK_SKIPPED'), (6, 'NO_KYE_FRAME'), (7, 'NO_VIDEO'), (8, 'NO_AUDIO'), (9, 'SEGMENT_INTERVAL'), (10, 'OTHER');

CREATE DICTIONARY wcs.DictionaryHlsStreamEventSeverity
(
   `id` UInt8,
   `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'HlsStreamEventSeverity'
))
LAYOUT(FLAT())
LIFETIME(300);

CREATE DICTIONARY wcs.DictionaryHlsStreamEventType
(
   `id` UInt16,
   `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'HlsStreamEventType'
))
LAYOUT(FLAT())
LIFETIME(300);

CREATE TABLE wcs.HlsStreamEvents
(
    `timestamp` UInt64,
    `ip` IPv4,
    `severity` UInt8,
    `messageType` UInt16,
    `streamId` String,
    `variantName` String,
    `segmentId` String,
    `message` String
)
ENGINE = MergeTree()
ORDER BY (streamId)
SETTINGS index_granularity = 8192; 

CREATE TABLE wcs.HlsClientEvents
(
    `creationTime` UInt64,
    `responseTime` UInt64,
    `streamId` String,
    `variantName` String,
    `uri` String,
    `localIp` IPv4,
    `remoteIp` IPv4,
    `remotePort` UInt32,
    `userAgent` String,
    `httpStatus` UInt32,
    `clientId` UInt64
)
ENGINE = MergeTree()
ORDER BY (creationTime)
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.MixerEvent
(
    `timestamp` UInt64,
    `mixerMediaSessionId` String,
    `mixerStreamName` String,
    `mediaSessionId` String,
    `streamName` String,
    `mixerAverageTickTimeInMs` Int64,
    `audioMixerSync` Int64,
    `videoMixerSync` Int64,
    `nextAudioDataTime` Int64,
    `nextVideoDataTime` Int64,
    `audioBuffered` Int64,
    `videoBuffered` Int64,
    `audioDropsCounter` Int64,
    `audioDropsSizeInBytes` Int64,
    `videoDropsCounter` Int64,
    `videoDropsSizeInBytes` Int64,
    `videoFps` Int64,
    `audioRate` DOUBLE,
    `videoRate` DOUBLE,
    `eventType` UInt32,
    `eventPayload` String
)
ENGINE = MergeTree()
ORDER BY timestamp
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.MixerEventTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO wcs.MixerEventTypes VALUES (0, 'nullEvent'), (1, 'dropBallastAudio'), (2, 'dropBallastVideo'), (3, 'audioNotBuffered'), (4, 'videoNotBuffered'), (5, 'audioBufferExhausted'), (6, 'videoBufferExhausted'), (7, 'alignStreamFailed'), (8, 'alignStreamDropAudio'), (9, 'alignStreamDropVideo'), (10, 'rateOutOfBoundsAudio'), (11, 'rateOutOfBoundsVideo');

CREATE DICTIONARY wcs.DictionaryMixerEvents (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'MixerEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);

CREATE TABLE wcs.RtmpInBufferEvent
(
    `timestamp` UInt64,
    `streamClockTime` UInt64,
    `mediaSessionId` String,
    `streamName` String,
    `nextAudioDataTime` Int64,
    `nextVideoDataTime` Int64,
    `audioBuffered` Int64,
    `videoBuffered` Int64,
    `maximumAllowedBuffer` Int64,
    `bufferingCounter` Int64,
    `lastAudioDataTime` Int64,
    `lastVideoDataTime` Int64,
    `bufferState` UInt32
)
ENGINE = MergeTree()
ORDER BY timestamp
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.BufferStateTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO wcs.BufferStateTypes VALUES (0, 'BUFFERING'), (1, 'HOLD'), (2, 'TERMINATED'), (3, 'OVERFLOW'), (4, 'PASSTHROUGH');

CREATE DICTIONARY wcs.DictionaryBufferStateTypes (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'BufferStateTypes'
))
LAYOUT(FLAT())
LIFETIME(300);

CREATE TABLE wcs.AudioRecoveryEvent
(
    `timestamp` UInt64,
    `mediaSessionId` String,
    `type` UInt32,
    `rtpTimestamp` UInt64
)
ENGINE = MergeTree()
ORDER BY rtpTimestamp
SETTINGS index_granularity = 8192;

5. Create wcs user and grant permissions to all the tables in wcs database

cat wcs_clickhouse_users.sql | clickhouse-client -mn

wcs_clickhouse_users.sql

CREATE USER IF NOT EXISTS wcs IDENTIFIED BY 'wcs';
GRANT ALL ON wcs.* TO wcs WITH GRANT OPTION;

6. Disable users management for default user by setting the following parameter in /etc/clickhouse-server/users.xml file

            <access_management>0</access_management>

7. Restart ClickHouse

systemctl restart clickhouse-server

ClickHouse configuration for WCS builds 5.2.1999 and newer

Since WCS build 5.2.1999 DB is created automatically when a certain WCS build is connection fron a certain IP address. All the datadases metadata are stored in the table WcsMetadata.metadata

SELECT *
FROM WcsMetadata.metadata

┌─dbName───────────────────────────────┬─ip────────────┬─hostname──────────────┬─versionNumber─┬─versionHash──────────────────────────────┐
│ test1flashphonercom_192168065_521999 │ 192.168.0.65  │ test1.flashphoner.com │ 5.2.1999      │ 93e39647113e0121dabc4283ef700814c355568f │
│ test2flashphonercom_192168039_521999 │ 192.168.0.39  │ test2.flashphoner.com │ 5.2.1999      │ 93e39647113e0121dabc4283ef700814c355568f │
└──────────────────────────────────────┴───────────────┴───────────────────────┴───────────────┴──────────────────────────────────────────┘

Therefore only the user should be created and privileges to create databases, tables and to insert data to the tables should be granted to set up ClickHouse.

1. Uncomment the following string in /etc/clickhouse-server/config.xml file to listen all the server network interfaces

    <listen_host>::</listen_host>

2. Set the following parameter for default user in /etc/clickhouse-server/users.xml file to temporary allow users management

            <access_management>1</access_management>
            <named_collection_control>1</named_collection_control>
            <show_named_collections>1</show_named_collections>
            <show_named_collections_secrets>1</show_named_collections_secrets>

3. Restart ClickHouse

systemctl restart clickhouse-server

4. Create wcs user and grant the privileges needed

cat wcs_clickhouse_users.sql | clickhouse-client -mn

wcs_clickhouse_users.sql

CREATE USER IF NOT EXISTS wcs IDENTIFIED BY 'wcs';
SET allow_introspection_functions = 1;
GRANT ALL ON *.* TO wcs WITH GRANT OPTION;

5. Disable users management for default user by setting the following parameter in /etc/clickhouse-server/users.xml file

            <access_management>0</access_management>

6. Restart ClickHouse

systemctl restart clickhouse-server

WCS configuration

Data logging to ClickHouse is enabled by the following list of the data to collect

rels_enabled=CONNECTION,STREAM,CDN,MEDIA_SESSION

The following data types are available:

CONNECTION, STREAM, CDN, MEDIA_SESSION, HLS_SEGMENTER, HLS_STREAM, HLS_CLIENT, MIXER, AUDIO_RECOVERY, RTMP_IN_BUFFER

Type	Description
CONNECTION	Client session events
STREAM	Stream events
CDN	CDN events
MEDIA_SESSION	Stream metrics
HLS_SEGMENTER	HLS stream segment metrics
HLS_STREAM	HLS stream events
HLS_CLIENT	HLS clients statistics
MIXER	Mixer events and metrics
AUDIO_RECOVERY	Audio packets recovery events
RTMP_IN_BUFFER	RTMP incoming streams buffer metrics

При управлении большим количеством WCS серверов, для отладки возможных проблем с вещанием потоков, возникает необходимость централизованного сбора данных о потоках, клиентских соединениях и событий CDN. Фактически, необходимо в одной точке собрать информацию, которая пишется в логи каждого сервера. При этом само по себе логирование в промышленной эксплуатации сведено к минимуму, чтобы не давать нагрузку на дисковую подсистему сервера.

Для сбора данных в реальном времени в больших объемах хорошо подходят базы данных временных рядов. На основе одной из таких БД c открытым исходным кодом ClickHouse, в сборке 5.2.774 добавлена система удаленного сбора логов RELS (Remote Event Logging System).

Архитектура

Каждый WCS сервер отправляет данные в ClickHouse независимо, используя JDBC-драйвер и HTTP-соединение. Для оптимизации работы с БД ClickHouse, данные буферизуются и отправляются пачками по времени

Описание таблиц данных

Данные собираются в таблицы ClickHouse, перечисленные ниже. При этом, в таблицу записывается числовой идентификатор события. Для того, чтобы отобразить события в выборках в удобном для чтения виде, каждой таблице сопоставлен словарь текстовых строк, описывающих события.

Данные соединений (таблица ConnectionEvent)

Поле	Тип	Описание
timestamp	UInt64	Метка времени
ip	IPv4	Адрес сервера
sessionId	String	Идентификатор сессии
eventType	UInt64	Идентификатор типа события
eventPayload	String	Содержимое события

Данные о событиях потоков (таблица StreamEvent)

Поле	Тип	Описание
timestamp	UInt64	Метка времени
ip	IPv4	Адрес сервера
sessionId	String	Идентификатор сессии
mediaSessionId	String	Идентификатор медиасессий
streamName	String	Имя потока
eventType	UInt64	Идентификатор типа события
eventPayload	String	Содержимое события

Данные CDN (таблица CDNEvent)

Поле	Тип	Описание
timestamp	UInt64	Метка времени
ip	IPv4	Адрес сервера
nodeId	String	Идентификатор узла (строка IP адреса)
eventType	UInt64	Идентификатор типа события
eventPayload	String	Содержимое события

Данные о метриках потоков (таблица MediaSessionEvent)

В сборке 5.2.1896 добавлен сбор данных о метриках определенных потоков

Поле	Тип	Описание
timestamp	UInt64	Метка времени
ip	IPv4	Адрес сервера
mediaSessionId	String	Идентификатор медиасессий
streamName	String	Имя потока
videoProfileId	UInt32	Идентификатор профиля видео
videoWidth	UInt32	Высота картинки
videoHeight	UInt32	Ширина картинки
videoFrameRate	UInt32	Частота кадров в секунду
videoKframes	UInt64	Количество ключевых кадров (I-frame)
videoPframes	UInt64	Количество промежуточных кадров (P-frame)
videoBframes	UInt64	Количество двунаправленных кадров (B-frames)
videoRate	UInt64	Битрейт видео, бит/с
audioRate	UInt64	Битрейт аудио, бит/с
videoSyncTime	UInt64	Значение синхронизации видео
audioSyncTime	UInt64	Значение синхронизации аудио
videoTimestamp	UInt64	Метка времени из видеопакета
audioTimestamp	UInt64	Метка времени из аудиопакета
lastKeyFrameSyncTime	UInt64	Значение синхронизации видео из последнего ключевого кадра
sendNACK	UInt64	Количество отправленных NACK
recvNACK	UInt64	Количество полученных NACK
videoFramesLost	UInt64	Количество потерянных кадров видео
audioPacketsLost	UInt64	Количество потерянных пакетов аудио
audioPlaybackSpeed	Float32	Скорость публикации/проигрывания аудио
videoPlaybackSpeed	Float32	Скорость публикации/проигрывания видео

Данные о нарезке HLS потока (таблица HlsStreamEvent)

В сборке 5.2.1917 добавлен сбор данных о событиях определенных HLS потоков

Поле	Тип	Описание
timestamp	UInt64	Метка времени
ip	IPv4	Адрес сервера
severity	UInt8	Уровень события: INFO, WARNING, ERROR
messageType	UInt16	Тип события
streamId	String	Идентификатор HLS потока (имя)
variantName	String	Имя варианта качества
segmentId	String	Идентификатор сегмента
message	String	Описание события в том виде, как залогировано

Данные о сегментах HLS потока (таблица HlsSegmenterEvent)

В сборке 5.2.1917 добавлен сбор данных о сегментах определенных HLS потоков

Поле	Тип	Описание
timestamp	UInt64	Метка времени
ip	IPv4	Адрес сервера
streamId	String	Идентификатор HLS потока (имя)
variantName	String	Имя варианта качества
segmentId	String	Идентификатор сегмента
segmentStartPts	UInt64	Начальный PTS сегмента
videoStartPts	UInt64	Начальный PTS видео дорожки
audioStartPts	UInt64	Начальный PTS аудио дорожки
videoWidth	UInt32	Ширина картинки
videoHeight	UInt32	Высота картинки
videoFrameCount	UInt32	Количество кадров видео
audioPacketCount	UInt32	Количество пакетов аудио
segmentDuration	UInt64	Длительность сегмента, мс
independent	Bool	Независимый сегмент
gap	Bool	GAP сегмент
discontinuity	Bool	DISCONTINUTY сегмент
segmentInterval	UInt64	Интервал между сегментами, мс
partial	Bool	Частичный сегмент
playbackSpeed	Float32	Скорость проигрывания

Данные о подписчиках HLS потока (таблица HlsClientEvent)

В сборке 5.2.1929 добавлен сбор данных о подписчиках определенных HLS потоков

Поле	Тип	Описание
creationTime	UInt64	Время подключения подписчика
responseTime	UInt64	Время ответа на запрос подписчика
streamId	String	Идентификатор HLS потока (имя)
variantName	String	Имя варианта качества
uri	String	URI плейлиста
localIp	IPv4	Адрес сервера
remoteIp	IPv4	Адрес клиента
remotePort	UInt32	TCP порт, с которого подключился клиента
userAgent	String	Значение заголовка User-Agent, полученное от клиента
httpStatus	UInt32	Статус ответа на запрос клиента
clientId	UInt64	Идентификатор клиентской сессии

Данные о метриках микшера (таблица MixerEvent)

В сборке 5.2.1923 добавлен сбор данных о метриках определенных микшеров

Поле	Тип	Описание
timestamp	UInt64	Метка времени
mixerMediaSessionId	String	Идентификатор медиасессии микшера
mixerStreamName	String	Имя выходного потока микшера
mediaSessionId	String	Идентификатор входящего потока микшера
streamName	String	Имя входящего потока
mixerAverageTickTimeInMs	Int64	Среднее время одного такта микширования, мс
audioMixerSync	Int64	Значение синхронизации аудио в выходном потоке микшера
videoMixerSync	Int64	Значение синхронизации видео в выходном потоке микшера
nextAudioDataTime	Int64	Время следующего аудио пакета
nextVideoDataTime	Int64	Время следующего видео пакета
audioBuffered	Int64	Количество аудио пакетов в буфере входящего потока
videoBuffered	Int64	Количество видео пакетов в буфере входящего потока
audioDropsCounter	Int64	Количество отброшенных аудио пакетов входящего потока
audioDropsSizeInBytes	Int64	Объем отброшенных данных аудио в байтах
videoDropsCounter	Int64	Количество отброшенных видео пакетов входящего потока
videoDropsSizeInBytes	Int64	Объем отброшенных данных видео в байтах
videoFps	Int64	Частота кадров выходного потока микшера в секунду
audioRate	DOUBLE	Битрейт аудио выходного потока микшера, бит/с
videoRate	DOUBLE	Битрейт видео выходного потока микшера, бит/с
eventType	UInt32	Тип события микшера
eventPayload	String	Описание события микшера

Данные о восстановлении потерянных аудио пакетов (таблица AudioRecoveryEvent)

В сборке 5.2.1969 добавлен сбор данных о восстановлении потерянных аудио пакетов

Поле	Тип	Описание
timestamp	UInt64	Метка времени
mediaSessionId	String	Идентификатор медиасессии
type	UInt32	Тип события
rtpTimestamp	UInt64	Метка времени из RTP потока

Данные о метриках буфера для входящих RTMP потоков (таблица RtmpInBufferEvent)

В сборке 5.2.1978 добавлен сбор данных о метриках буфера входящих RTMP потоков

Поле	Тип	Описание
timestamp	UInt64	Метка времени
streamClockTime	UInt64	Метка времени по часам потока
mediaSessionId	String	Идентификатор медиасессии
streamName	String	Имя потока
nextAudioDataTime	Int64	Метка времени следующего пакета аудио данных
nextVideoDataTime	Int64	Метка времени следующего пакета видео данных
audioBuffered	Int64	Количество данных аудио в буфере
videoBuffered	Int64	Количество данных видео в буфере
maximumAllowedBuffer	Int64	Максимальная емкость буфера
bufferingCounter	Int64	Счетчик буферизаций
lastAudioDataTime	Int64	Время последнего пакета аудио данных
lastVideoDataTime	Int64	Время последнего пакета видео данных
bufferState	UInt32	Состояние буфера

Настройка

Установка и настройка ClickHouse

Требования к серверу

CPU не менее 4 физических ядер, частотой не менее 3 ГГц, например Intel(R) Xeon(R) CPU E3-1246 v3 @ 3.50GHz
RAM не менее 32 Гб
HDD не менее 2 Тб

Установка ClickHouse из rpm пакетов (CentOS, Red Hat etc)

1. Подключите официальный репозиторий

sudo yum install -y yum-utils
sudo yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo

2. Установите ClickHouse

sudo yum install -y clickhouse-server clickhouse-client

3. Запустите ClickHouse

sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server

Установка ClickHouse из deb пакетов (Debian, Ubuntu etc)

1. Подключите официальный репозиторий

sudo apt-get install -y apt-transport-https ca-certificates dirmngr
sudo apt-key adv --keyserver hkp://keyserver.ubuntu.com:80 --recv 8919F6BD2B48D754
echo "deb https://packages.clickhouse.com/deb stable main" | sudo tee /etc/apt/sources.list.d/clickhouse.list
sudo apt-get update

2. Установите ClickHouse

sudo apt-get install -y clickhouse-server clickhouse-client

3. Запустите ClickHouse

sudo systemctl enable clickhouse-server
sudo systemctl start clickhouse-server

Настройка ClickHouse для сборок WCS до 5.2.1999

1. Для того, чтобы прослушивать входящие запросы на всех интерфесах сервера, раскомментируйте строку в файле /etc/clickhouse-server/config.xml

    <listen_host>::</listen_host>

2. Для того, чтобы создать пользователя, укажите для пользователя default в файле /etc/clickhouse-server/users.xml параметр

            <access_management>1</access_management>

3. Перезапустите ClickHouse

systemctl restart clickhouse-server

4. Создайте базу данных wcs и таблицы в ней

cat wcs_clickhouse.sql | clickhouse-client -mn

wcs_clickhouse.sql

CREATE DATABASE IF NOT EXISTS wcs;

DROP DICTIONARY IF EXISTS wcs.DictionaryStreamEvents;
 
DROP DICTIONARY IF EXISTS wcs.DictionaryConnectionEvents;
 
DROP DICTIONARY IF EXISTS wcs.DictionaryCDNEvents;

DROP DICTIONARY IF EXISTS wcs.DictionaryHlsStreamEventType;

DROP DICTIONARY IF EXISTS wcs.DictionaryHlsStreamEventSeverity;
 
DROP DICTIONARY IF EXISTS wcs.DictionaryMixerEvents;

DROP DICTIONARY IF EXISTS wcs.DictionaryBufferStateTypes;
 
DROP TABLE IF EXISTS wcs.StreamEvent;
 
DROP TABLE IF EXISTS wcs.ConnectionEvent;
 
DROP TABLE IF EXISTS wcs.CDNEvent;
 
DROP TABLE IF EXISTS wcs.StreamEventTypes;
 
DROP TABLE IF EXISTS wcs.ConnectionEventTypes;
 
DROP TABLE IF EXISTS wcs.CDNEventTypes;

DROP TABLE IF EXISTS wcs.MediaSessionEvents;

DROP TABLE IF EXISTS wcs.HlsStreamEvents;

DROP TABLE IF EXISTS wcs.HlsSegmenterEvents;

DROP TABLE IF EXISTS wcs.HlsStreamEventSeverity;

DROP TABLE IF EXISTS wcs.HlsStreamEventType;

DROP TABLE IF EXISTS wcs.HlsClientEvents;

DROP TABLE IF EXISTS wcs.MixerEvent;

DROP TABLE IF EXISTS wcs.MixerEventTypes;

DROP TABLE IF EXISTS wcs.RtmpInBufferEvent;

DROP TABLE IF EXISTS wcs.AudioRecoveryEvent;

DROP TABLE IF EXISTS wcs.BufferStateTypes;

CREATE TABLE wcs.ConnectionEventTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
 
INSERT INTO wcs.ConnectionEventTypes VALUES (0, 'CONNECTED'), (1, 'DISCONNECTED');
 
CREATE TABLE wcs.StreamEventTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
 
INSERT INTO wcs.StreamEventTypes VALUES (0,'CREATED'),(1,'LOCAL_SDP_CREATED'),(2,'REMOTE_SDP_RECEIVED'),(3,'ICE_STARTED'),(4,'ICE_COMPLETE'),(5,'DTLS_STARTED'),(6,'DTLS_COMPLETE'),(7,'INITIALIZED'),(8,'DISPOSING'),(9,'DISPOSED'),(10,'AUDIO_RECEIVED'),(11,'VIDEO_RECEIVED'),(12,'VIDEO_KFRAME_RECEIVED'),(13,'AUDIO_RTCP_RECEIVED'),(14,'VIDEO_RTCP_RECEIVED'),(15,'RESOLUTION_RECEIVED'),(16,'VIDEO_ENCODER_CREATED'),(17,'AUDIO_ENCODER_CREATED'),(18,'VIDEO_ENCODER_DISPOSED'),(19,'AUDIO_ENCODER_DISPOSED'),(20,'TERMINATED'),(21,'AUDIO_SENT'),(22,'VIDEO_SENT'),(23,'VIDEO_JITTER_BUFFER_STALL'),(24,'SENT_PLI'),(25,'RECEIVED_PLI'),(26,'SYNC_BUFFER_FULL'),(27,'SYNC_FORCE_FAILED'),(28,'SYNC_SHIFT'),(29,'SYNC_DEVIATION'),(30,'VIDEO_STATS'),(31,'RECORD');
 
CREATE TABLE wcs.CDNEventTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;
 
INSERT INTO wcs.CDNEventTypes VALUES (0, 'STATE'), (1, 'CDN_STATE'), (2, 'VERSION'), (3, 'ACL_REFRESH'), (4, 'ACL_UPDATE');
 
CREATE DICTIONARY wcs.DictionaryStreamEvents (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'StreamEventTypes'
))   
LAYOUT(FLAT())
LIFETIME(300);
 
CREATE DICTIONARY wcs.DictionaryConnectionEvents (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'ConnectionEventTypes'
))   
LAYOUT(FLAT())
LIFETIME(300);
 
CREATE DICTIONARY wcs.DictionaryCDNEvents (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'CDNEventTypes'
))   
LAYOUT(FLAT())
LIFETIME(300);
 
CREATE TABLE wcs.StreamEvent
(
    `timestamp` UInt64,
    `ip` IPv4,
    `sessionId` String,
    `mediaSessionId` String,
    `streamName` String,
    `eventType` UInt64,
    `eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (sessionId, mediaSessionId, streamName)
SETTINGS index_granularity = 8192;
 
CREATE TABLE wcs.ConnectionEvent
(
    `timestamp` UInt64,
    `ip` IPv4,
    `sessionId` String,
    `eventType` UInt64,
    `eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (timestamp, sessionId)
SETTINGS index_granularity = 8192;
 
CREATE TABLE wcs.CDNEvent
(
    `timestamp` UInt64,
    `ip` IPv4,
    `nodeId` String,
    `eventType` UInt64,
    `eventPayload` String
)
ENGINE = MergeTree()
ORDER BY (nodeId, eventType)
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.MediaSessionEvents
(
    `timestamp` UInt64,
    `ip` IPv4,
    `mediaSessionId` String,
    `streamName` String,
    `videoProfileId` UInt32,
    `videoWidth` UInt32,
    `videoHeight` UInt32,
    `videoFrameRate` UInt32,
    `videoKframes` UInt64,
    `videoPframes` UInt64,
    `videoBframes` UInt64,
    `videoRate` UInt64,
    `audioRate` UInt64,
    `videoSyncTime` UInt64,
    `audioSyncTime` UInt64,
    `videoTimestamp` UInt64,
    `audioTimestamp` UInt64,
    `lastKeyFrameSyncTime` UInt64,
    `sendNACK` UInt64,
    `recvNACK` UInt64,
    `videoFramesLost` UInt64,
    `audioPacketsLost` UInt64,
    `audioPlaybackSpeed` Float32,
    `videoPlaybackSpeed` Float32
)
ENGINE = MergeTree()
ORDER BY (mediaSessionId, streamName)
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.HlsSegmenterEvents
(
    `timestamp` UInt64,
    `ip` IPv4,
    `streamId` String,
    `variantName` String,
    `segmentId` String,
    `segmentStartPts` UInt64,
    `videoStartPts` UInt64,
    `audioStartPts` UInt64,
    `videoWidth` UInt32,
    `videoHeight` UInt32,
    `videoFrameCount` UInt32,
    `audioPacketCount` UInt32,
    `segmentDuration` UInt64,
    `independent` Bool,
    `gap` Bool,
    `discontinuity` Bool,
    `segmentInterval` UInt64,
    `partial` Bool,
    `playbackSpeed` Float32
)
ENGINE = MergeTree()
ORDER BY (streamId)
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.HlsStreamEventSeverity
(
    `id` UInt8,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO wcs.HlsStreamEventSeverity VALUES (0, 'INFO'), (1, 'WARNING'), (2, 'ERROR');

CREATE TABLE wcs.HlsStreamEventType
(
    `id` UInt16,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO wcs.HlsStreamEventType
VALUES (0, 'PLAYBACK_SPEED'), (1, 'FPS_CHANGED'), (2, 'GAP'), (3, 'RESOLUTION_CHANGED'), (4, 'DISCONTINUITY'), (5, 'TASK_SKIPPED'), (6, 'NO_KYE_FRAME'), (7, 'NO_VIDEO'), (8, 'NO_AUDIO'), (9, 'SEGMENT_INTERVAL'), (10, 'OTHER');

CREATE DICTIONARY wcs.DictionaryHlsStreamEventSeverity
(
   `id` UInt8,
   `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'HlsStreamEventSeverity'
))
LAYOUT(FLAT())
LIFETIME(300);

CREATE DICTIONARY wcs.DictionaryHlsStreamEventType
(
   `id` UInt16,
   `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'HlsStreamEventType'
))
LAYOUT(FLAT())
LIFETIME(300);

CREATE TABLE wcs.HlsStreamEvents
(
    `timestamp` UInt64,
    `ip` IPv4,
    `severity` UInt8,
    `messageType` UInt16,
    `streamId` String,
    `variantName` String,
    `segmentId` String,
    `message` String
)
ENGINE = MergeTree()
ORDER BY (streamId)
SETTINGS index_granularity = 8192; 

CREATE TABLE wcs.HlsClientEvents
(
    `creationTime` UInt64,
    `responseTime` UInt64,
    `streamId` String,
    `variantName` String,
    `uri` String,
    `localIp` IPv4,
    `remoteIp` IPv4,
    `remotePort` UInt32,
    `userAgent` String,
    `httpStatus` UInt32,
    `clientId` UInt64
)
ENGINE = MergeTree()
ORDER BY (creationTime)
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.MixerEvent
(
    `timestamp` UInt64,
    `mixerMediaSessionId` String,
    `mixerStreamName` String,
    `mediaSessionId` String,
    `streamName` String,
    `mixerAverageTickTimeInMs` Int64,
    `audioMixerSync` Int64,
    `videoMixerSync` Int64,
    `nextAudioDataTime` Int64,
    `nextVideoDataTime` Int64,
    `audioBuffered` Int64,
    `videoBuffered` Int64,
    `audioDropsCounter` Int64,
    `audioDropsSizeInBytes` Int64,
    `videoDropsCounter` Int64,
    `videoDropsSizeInBytes` Int64,
    `videoFps` Int64,
    `audioRate` DOUBLE,
    `videoRate` DOUBLE,
    `eventType` UInt32,
    `eventPayload` String
)
ENGINE = MergeTree()
ORDER BY timestamp
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.MixerEventTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO wcs.MixerEventTypes VALUES (0, 'nullEvent'), (1, 'dropBallastAudio'), (2, 'dropBallastVideo'), (3, 'audioNotBuffered'), (4, 'videoNotBuffered'), (5, 'audioBufferExhausted'), (6, 'videoBufferExhausted'), (7, 'alignStreamFailed'), (8, 'alignStreamDropAudio'), (9, 'alignStreamDropVideo'), (10, 'rateOutOfBoundsAudio'), (11, 'rateOutOfBoundsVideo');

CREATE DICTIONARY wcs.DictionaryMixerEvents (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'MixerEventTypes'
))
LAYOUT(FLAT())
LIFETIME(300);

CREATE TABLE wcs.RtmpInBufferEvent
(
    `timestamp` UInt64,
    `streamClockTime` UInt64,
    `mediaSessionId` String,
    `streamName` String,
    `nextAudioDataTime` Int64,
    `nextVideoDataTime` Int64,
    `audioBuffered` Int64,
    `videoBuffered` Int64,
    `maximumAllowedBuffer` Int64,
    `bufferingCounter` Int64,
    `lastAudioDataTime` Int64,
    `lastVideoDataTime` Int64,
    `bufferState` UInt32
)
ENGINE = MergeTree()
ORDER BY timestamp
SETTINGS index_granularity = 8192;

CREATE TABLE wcs.BufferStateTypes
(
    `id` UInt32,
    `type` String
)
ENGINE = MergeTree()
ORDER BY id
SETTINGS index_granularity = 8192;

INSERT INTO wcs.BufferStateTypes VALUES (0, 'BUFFERING'), (1, 'HOLD'), (2, 'TERMINATED'), (3, 'OVERFLOW'), (4, 'PASSTHROUGH');

CREATE DICTIONARY wcs.DictionaryBufferStateTypes (
    `id` UInt16,
    `type` String DEFAULT ''
)
PRIMARY KEY id
SOURCE(CLICKHOUSE(
 host 'localhost'
 port 9000
 user 'default'
 password ''
 db 'wcs'
 table 'BufferStateTypes'
))
LAYOUT(FLAT())
LIFETIME(300);

CREATE TABLE wcs.AudioRecoveryEvent
(
    `timestamp` UInt64,
    `mediaSessionId` String,
    `type` UInt32,
    `rtpTimestamp` UInt64
)
ENGINE = MergeTree()
ORDER BY rtpTimestamp
SETTINGS index_granularity = 8192;

5. Создайте пользователя wcs и дайте ему права на таблицы в базе данных wcs

cat wcs_clickhouse_users.sql | clickhouse-client -mn

wcs_clickhouse_users.sql

CREATE USER IF NOT EXISTS wcs IDENTIFIED BY 'wcs';
GRANT ALL ON wcs.* TO wcs WITH GRANT OPTION;

6. Отключите управление пользователями для пользователя default, указав в файле /etc/clickhouse-server/users.xml параметр

            <access_management>0</access_management>

7. Перезапустите ClickHouse

systemctl restart clickhouse-server

Настройка ClickHouse для сборок WCS 5.2.1999 и новее

В сборке 5.2.1999 добавлено автоматическое создание БД при подключении определенной версии WCS с определенного IP адреса. Данные о всех БД хранятся в таблице WcsMetadata.metadata

SELECT *
FROM WcsMetadata.metadata

┌─dbName───────────────────────────────┬─ip────────────┬─hostname──────────────┬─versionNumber─┬─versionHash──────────────────────────────┐
│ test1flashphonercom_192168065_521999 │ 192.168.0.65  │ test1.flashphoner.com │ 5.2.1999      │ 93e39647113e0121dabc4283ef700814c355568f │
│ test2flashphonercom_192168039_521999 │ 192.168.0.39  │ test2.flashphoner.com │ 5.2.1999      │ 93e39647113e0121dabc4283ef700814c355568f │
└──────────────────────────────────────┴───────────────┴───────────────────────┴───────────────┴──────────────────────────────────────────┘

Поэтому для настройки ClickHouse необходимо только создать пользователя и дать ему права на создание Баз данных, таблиц и вставку данных в них

1. Для того, чтобы прослушивать входящие запросы на всех интерфесах сервера, раскомментируйте строку в файле /etc/clickhouse-server/config.xml

    <listen_host>::</listen_host>

2. Для того, чтобы создать пользователя, укажите для пользователя default в файле /etc/clickhouse-server/users.xml параметры

            <access_management>1</access_management>
            <named_collection_control>1</named_collection_control>
            <show_named_collections>1</show_named_collections>
            <show_named_collections_secrets>1</show_named_collections_secrets>

3. Перезапустите ClickHouse

systemctl restart clickhouse-server

4. Создайте пользователя wcs и дайте ему необходимые права

cat wcs_clickhouse_users.sql | clickhouse-client -mn

wcs_clickhouse_users.sql

CREATE USER IF NOT EXISTS wcs IDENTIFIED BY 'wcs';
SET allow_introspection_functions = 1;
GRANT ALL ON *.* TO wcs WITH GRANT OPTION;

5. Отключите управление пользователями для пользователя default, указав в файле /etc/clickhouse-server/users.xml параметр

            <access_management>0</access_management>

6. Перезапустите ClickHouse

systemctl restart clickhouse-server

Настройка WCS

Сбор данных в БД ClickHouse включается настройкой, в которой перечисляются типы отправляемых данных

rels_enabled=CONNECTION,STREAM,CDN,MEDIA_SESSION

Доступны следующие типы данных:

CONNECTION, STREAM, CDN, MEDIA_SESSION, HLS_SEGMENTER, HLS_STREAM, HLS_CLIENT, MIXER, AUDIO_RECOVERY, RTMP_IN_BUFFER

Тип	Описание
CONNECTION	События клиенсткой сессии
STREAM	События потока
CDN	События CDN
MEDIA_SESSION	События медиа сессии
HLS_SEGMENTER	Данные о нарезке HLS потоков
HLS_STREAM	События HLS потоков
HLS_CLIENT	Статистика HLS клиентов
MIXER	События микшера
AUDIO_RECOVERY	Статистика потерь и восстановления аудиопакетов
RTMP_IN_BUFFER	Статистика буфера входящих RTMP потоков

ClickHouse connection setup in WCS builds before 5.2.1999

ClickHouse server, database address and protocol are set by the following parameters

rels_client_type=HTTP
rels_database_address=http://clickhouseserver:8123/wcs?user=wcs&password=wcs

HTTP protocol is recommended and used by default. But it can be switched to JDBC driver if needed

rels_client_type=JDBC
rels_database_address=jdbc:clickhouse://clickhouseserver:8123/wcs?user=wcs&password=wcs

ClickHouse connection setup in WCS builds 5.2.1999 and newer

ClickHouse server, user and protocol are set by the following parameters

rels_client_type=HTTP
rels_database_address=clickhouseserver:8123
rels_database_properties=user=wcs&password=wcs

HTTP protocol is recommended and used by default. But it can be switched to JDBC driver if needed

rels_client_type=JDBC

Stop data logging without WCS server restart

Data logging can be stopped without WCS restart if necessary. To do this:

1. Disable data logging in server settings

rels_enabled=false

2. Reload settings using CLI command

reload node-settings

ClickHouse server address changing without WCS server restart

ClickHouse server address can be changed without WCS restart. To do this:

1. Change address in server settings

rels_database_address=jdbc:clickhouse://newclickhouseserver:8123/wcs?user=wcs&password=wcs

2. Disable data logging in server settings

rels_enabled=false

3. Reload settings using CLI command

reload node-settings

4. Enable data logging in server settings

rels_enabled=true

5. Reload settings using CLI command

reload node-settings

Data collection management using REST API

The data of CONNECTION, STREAM, CDN, HLS_STREAM types are always collected, for all the client sessions and all the streams. All the other types are collected on demand only because a data amount may be too big.

The data collection for a certain stream is enabled by REST API.

A REST-query should be HTTP/HTTPS POST request as follows:

HTTP: http://streaming.flashphoner.com:8081/rest-api/rels/startup
HTTPS: https://streaming.flashphoner.com:8444/rest-api/rels/startup

Здесь:

streaming.flashphoner.com - WCS server address
8081 - the standard REST / HTTP port of the WCS server
8444 - the standard HTTPS port
rest-api - the required part of the URL
/rels/startup - REST-method to use

REST methods and responses

/rels/startup

Start data collectio of a certain type for the certain streams

Request example

POST /rest-api/rels/startup HTTP/1.1
Host: localhost:8081
Content-Type: application/json

{
    "mediaSession": {
        "frequency":100,
        "ids": [
            "d7d6b6e4-b137-461a-8a32-d6abf2b8666e",
            "39cbf770-128a-11ef-b839-d1a1f53f8bd2"
        ] 
    }
}

Response example

HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json

Return codes

Code	Reason
200	OK
400	Bad request
404	Not found
500	Internal server error

/rels/find_all

Get the sent data statistics

Request example

POST /rest-api/rels/find_all HTTP/1.1
Host: localhost:8081
Content-Type: application/json

Response example

HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json

[
  {
    "type": "CONNECTION",
    "sentBytes": 1989,
    "bitrateKbps": 0,
    "sentEvents": 6,
    "queueEvents": 0
  },
  {
    "type": "STREAM",
    "sentBytes": 70766,
    "bitrateKbps": 0,
    "sentEvents": 73,
    "queueEvents": 1
  },
  {
    "type": "CDN",
    "sentBytes": 0,
    "bitrateKbps": 0,
    "sentEvents": 0,
    "queueEvents": 0
  },
  {
    "type": "MEDIA_SESSION",
    "ids": [
      "d7d6b6e4-b137-461a-8a32-d6abf2b8666e",
      "39cbf770-128a-11ef-b839-d1a1f53f8bd2"
    ],
    "sentBytes": 205794,
    "bitrateKbps": 143,
    "sentEvents": 999,
    "queueEvents": 119
  }
]

Return codes

Code	Reason
200	OK
404	Not found
500	Internal server error

/rels/terminate

Stop data collection of the certain type for the certain streams

Request example

POST /rest-api/rels/terminate HTTP/1.1
Host: localhost:8081
Content-Type: application/json

{
    "mediaSession": {
        "ids": [
            "d7d6b6e4-b137-461a-8a32-d6abf2b8666e"
        ] 
    }
}

Response example

HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json

Return codes

Code	Reason
200	OK
400	Bad request
404	Not found
500	Internal server error

/rels/terminate_all

Stop the certain types data collection for all the streams

Request example

POST /rest-api/rels/terminate_all HTTP/1.1
Host: localhost:8081
Content-Type: application/json

{
    "types": [
        "MEDIA_SESSION"
    ]
}

Response example

HTTP/1.1 200 OK
Access-Control-Allow-Origin: *
Content-Type: application/json

{
  "MEDIA_SESSION": [
    "39cbf770-128a-11ef-b839-d1a1f53f8bd2"
  ]
}

Return codes

Code	Reason
200	OK
400	Bad request
404	Not found
500	Internal server error

Parameters

Parameter	Description	Example
mediaSession	MEDIA_SESSION data collection descriptor object	`"mediaSession": {"frequency":100, "ids":["12345678-0000-1111"]}`
frequency	data collection frequency in ms	100
ids	Medissesson identifiers list for the streams which data should be collected	`["12345678-0000-1111","12345678-3333-4444"]`
hlsSegmenter	HLS_SEGMENTER data collection descriptor object	"hlsSegmenter": {`"ids":["stream1"]}`
hlsClient	HLS_CLIENT data collection descriptor object	"hlsClient": {`"ids":["stream1"]}`
mixer	MIXER data collection descriptor object	"mixer": {`"ids":["12345678-5555-6666"]`}
audioRecovery	AUDIO_RECOVERY data collection descriptor object	"audioRecovery": {`"ids":["12345678-7777-8888"]`}
rtmpInBuffer	RTMP_IN_BUFFER data collection descriptor object	"rtmpInBuffer": {`"ids":["12345678-9999-AAAA"]`}

Data retrieving from DB

Logging data can be retrieved using SQL queries in CliskHouse client

SQL queries examples for WCS builds before 5.2.1999

select timestamp,ip,sessionId,mediaSessionId,streamName,dictGetString('wcs.DictionaryStreamEvents','type', eventType) as eventType from wcs.StreamEvent where streamName = 'test'

select timestamp,ip,sessionId,dictGetString('wcs.DictionaryConnectionEvents','type', eventType) as eventType from wcs.ConnectionEvent

select timestamp,ip,nodeId,dictGetString('wcs.DictionaryCDNEvents','type', eventType) as eventType,eventPayload from wcs.CDNEvent

SQL queries examples for WCS builds 5.2.1999 and newer

select timestamp,ip,sessionId,mediaSessionId,streamName,dictGetString('wcs.DictionaryStreamEvents','type', eventType) as eventType from test1flashphonercom_192168065_521999.StreamEvent where streamName = 'test'

select timestamp,ip,sessionId,dictGetString('wcs.DictionaryConnectionEvents','type', eventType) as eventType from test1flashphonercom_192168065_521999.ConnectionEvent

select timestamp,ip,nodeId,dictGetString('wcs.DictionaryCDNEvents','type', eventType) as eventType,eventPayload from test1flashphonercom_192168065_521999.CDNEvent

Space shortcuts

Page tree

Centralized server data logging to ClickHouse DB

Overview

Architecture

Data table description

Connections data (table ConnectionEvent)

Streams data (table StreamEvent)

CDN data (table CDNEvent)

Stream metrics data (table MediaSessionEvent)

HLS stream data (table HlsStreamEvent)

HLS segments data (table HlsSegmenterEvent)

HLS subscribers data (table HlsClientEvent)

Mixer metrics data (table MixerEvent)

Incoming audio recovery data (table AudioRecoveryEvent)

RTMP incoming streams buffer metrics data (table RtmpInBufferEvent)

Configuration

ClickHouse installation and setup

Server requirement

ClickHouse installation from rpm package (CentOS, Red Hat etc)

ClickHouse installation from deb package (Debian, Ubuntu etc)

ClickHouse configuration for WCS builds before 5.2.1999

ClickHouse configuration for WCS builds 5.2.1999 and newer

WCS configuration

Архитектура

Описание таблиц данных

Данные соединений (таблица ConnectionEvent)

Данные о событиях потоков (таблица StreamEvent)

Данные CDN (таблица CDNEvent)

Данные о метриках потоков (таблица MediaSessionEvent)

Данные о нарезке HLS потока (таблица HlsStreamEvent)

Данные о сегментах HLS потока (таблица HlsSegmenterEvent)

Данные о подписчиках HLS потока (таблица HlsClientEvent)

Данные о метриках микшера (таблица MixerEvent)

Данные о восстановлении потерянных аудио пакетов (таблица AudioRecoveryEvent)

Данные о метриках буфера для входящих RTMP потоков (таблица RtmpInBufferEvent)

Настройка

Установка и настройка ClickHouse

Требования к серверу

Установка ClickHouse из rpm пакетов (CentOS, Red Hat etc)

Установка ClickHouse из deb пакетов (Debian, Ubuntu etc)

Настройка ClickHouse для сборок WCS до 5.2.1999

Настройка ClickHouse для сборок WCS 5.2.1999 и новее

Настройка WCS

ClickHouse connection setup in WCS builds before 5.2.1999

ClickHouse connection setup in WCS builds 5.2.1999 and newer

Stop data logging without WCS server restart

ClickHouse server address changing without WCS server restart

Data collection management using REST API

REST methods and responses

/rels/startup

Request example

Response example

Return codes

/rels/find_all

Request example

Response example

Return codes

/rels/terminate

Request example

Response example

Return codes

/rels/terminate_all

Request example

Response example

Return codes

Parameters

Data retrieving from DB

SQL queries examples for WCS builds before 5.2.1999

SQL queries examples for WCS builds 5.2.1999 and newer