Centralized stream statistics and CDN events collection to MySQL DB¶
Overview¶
It may be necessary to collect stream statistics while managing a big number of WCS servers, to check broadcasting quality of service. If servers are in CDN, signaling events collection may also be useful to debug a network issues.
Since build 5.2.749 it is possible to collect stream statistics and CDN events centrally.
Architecture¶
Statistics collector server role can be assigned to one of WCS servers, in this case it also performs a usual media server functions (streams publishing and playback). Another servers send statistics data to the collector using a special TCP-based FREP protocol. Collector server writes received data to MySQL DB.
Staistics collector server role can be assigned to any WCS server. In theory, all the WCS servers can be collectors and send data directly to common MySQL server, but this can dramatically increase network load. In CDN, it is recommended to assign collector role to Controller node.
Data tables description¶
All the data are written to the following tables
Client sessions statistics¶
| Field | Type | Description |
|---|---|---|
| serverIp | varchar(15) | WCS IP address |
| start | datetime | Connection establishing time |
| sessionId | varchar(300) | Session Id |
| status | varchar(30) | Session status |
| info | varchar(300) | Reason phrase for FAILED status |
| duration | int(11) | Session duration |
Client sessions statistics data are sent at session start and session end.
Stream statistics¶
| Field | Type | Description |
|---|---|---|
| serverIp | varchar(15) | WCS IP address |
| start | datetime | Mediasession start time |
| sessionId | varchar(300) | Client session Id |
| mediaProvider | varchar(30) | Mediaprovider |
| name | varchar(300) | Stream name |
| mediaSessionId | varchar(300) | Mediasession Id |
| duration | int(32) | Mediasession duration |
| status | varchar(30) | Mediasession status |
| info | varchar(300) | Reason phrase for FAILED status |
| type | varchar(100) | Mediasession type (PUBLISH or SUBSCRIBE) |
| subscribers | int(16) | Subscribers count |
| audioLost | int(2) | Lost audio packets count |
| audioRate | int(4) | Audio bitrate |
| audioCodec | varchar(13) | Audio codec |
| videoWidth | int(2) | Picture width |
| videoHeight | int(2) | Picture height |
| videoPLI | int(2) | PLI requests count |
| videoNack | int(2) | NACK count |
| videoRate | int(4) | Video bitrate |
| videoFPS | int(2) | Video FPS |
| videoBframes | int(2) | B-frames count |
| videoCodec | varchar(4) | Video codec |
| localSdp | varbinary(1500) | Server side SDP |
| remoteSdp | varbinary(1500) | Browser SDP |
Stream statistics data are sent at stream publishing ir playback start and at stream end.
CDN events statistics¶
| Field | Type | Description |
|---|---|---|
| serverIp | varchar(15) | WCS IP address |
| date | datetime | Event date and time |
| ts | varchar(30) | CDN signaling timestamp |
| srcNode | varchar(30) | CDN signaling source |
| dstNode | varchar(30) | CDN signaling destination |
| event | varchar(60) | CDN event type |
| payload | varbinary(1500) | CDN signaling payload |
CDN event statistics data are sent directly when event occurs. Therefore, CDN statisctics collection is disabled by default, and it is not recommended to collect the CDN statistics in production, this is for debug purposes only.
Configuration¶
MySQL server setup¶
Installation¶
Below, MySQL installation to Centos 7 example described:
1. Download the necessary repository
2. Install the repository
3. Install MySQL
4. Launch MySQL
Setup¶
1. Create database and user for statistics collection
CREATE DATABASE wcs;
CREATE USER 'wcs'@'localhost' IDENTIFIED BY 'wcs';
GRANT ALL PRIVILEGES ON wcs.* TO 'wcs'@'localhost';
FLUSH PRIVILEGES;
2. Import database schema
Database schema file wcs.sql
-- MySQL dump 10.13 Distrib 8.0.20, for Linux (x86_64)
--
-- Host: 172.17.0.2 Database: wcs
-- ------------------------------------------------------
-- Server version 5.7.31
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!50503 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;
--
-- Table structure for table `cdn`
--
DROP TABLE IF EXISTS `cdn`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `cdn` (
`serverIp` varchar(15) NOT NULL,
`date` datetime NOT NULL,
`ts` varchar(30) NOT NULL,
`srcNode` varchar(30) NOT NULL,
`dstNode` varchar(30) NOT NULL,
`event` varchar(60) NOT NULL,
`payload` varbinary(1500) NOT NULL,
PRIMARY KEY (`ts`,`srcNode`,`event`,`dstNode`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `conndr`
--
DROP TABLE IF EXISTS `conndr`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `conndr` (
`serverIp` varchar(15) NOT NULL,
`start` datetime NOT NULL,
`sessionId` varchar(300) NOT NULL,
`status` varchar(30) NOT NULL,
`info` varchar(300) DEFAULT NULL,
`duration` int(11) DEFAULT NULL,
PRIMARY KEY (`sessionId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `sdr`
--
DROP TABLE IF EXISTS `sdr`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `sdr` (
`serverIp` varchar(15) NOT NULL,
`start` datetime NOT NULL,
`sessionId` varchar(300) NOT NULL,
`mediaProvider` varchar(30) NOT NULL,
`name` varchar(300) NOT NULL,
`mediaSessionId` varchar(300) NOT NULL,
`duration` int(32) DEFAULT NULL,
`status` varchar(30) NOT NULL,
`info` varchar(300) DEFAULT NULL,
`type` varchar(100) DEFAULT NULL,
`subscribers` int(16) DEFAULT NULL,
`audioLost` int(2) DEFAULT NULL,
`audioRate` int(4) DEFAULT NULL,
`audioCodec` varchar(13) DEFAULT NULL,
`videoWidth` int(2) DEFAULT NULL,
`videoHeight` int(2) DEFAULT NULL,
`videoPLI` int(2) DEFAULT NULL,
`videoNack` int(2) DEFAULT NULL,
`videoRate` int(4) DEFAULT NULL,
`videoFPS` int(2) DEFAULT NULL,
`videoBframes` int(2) DEFAULT NULL,
`videoCodec` varchar(4) DEFAULT NULL,
`localSdp` varbinary(1500) DEFAULT NULL,
`remoteSdp` varbinary(1500) DEFAULT NULL,
PRIMARY KEY (`sessionId`,`mediaSessionId`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
/*!40101 SET character_set_client = @saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2020-08-26 11:30:24
WCS setup¶
Statistics collection can be enabled using the following parameter
Role (client or server) can be set using the following parameter (CLIENT by default)
Statistics collector server address can be set using the following parameter
This parameter should be set on statistics client.
Database URI can be set using the following parameter
This parameter should be set on statistics server.
The port for statistics data transmission can be set using the following parameter (8085 by default)
Statistics authetication key can be set using the following parameter
This parameter must be the same on statistics server and client.
Settings applying¶
All the settings can be applyed without server restart using CLI command
In this case, it is recommended to change the settings by the following way:
1. Disable statistics collection on the WCS server
2. Reload settings
3. Set the necessary parameters and enable statistics collection
4. Reload settings
Data collection filter¶
Statistics data can be filtered before sending by the following parameter
Possible filter values
| Filter | Description |
|---|---|
| CONNECT | Connection establishing data |
| CONNECTION_STATUS_EVENT | Session status data |
| STREAM | Stream publishing/playing start data |
| STREAM_STATUS_EVENT | Stream status data |
For debug purposes, all the CDN events collection can be enabled
or CDN events can be filtered
| Filter | Description |
|---|---|
| ROLE_REFRESH | CDN node role |
| NODE_REFRESH | CDN node information on entering to CDN |
| ROUTE_REFRESH | Stream playback route information |
| ROUTE_UPDATE | Stream playback route update information |
| EXTENDED_ROUTE_UPDATE | Extended stream playback route update information |
| EXTENDED_ROUTE_REFRESH | Extended stream playback route information |
| VERSION_REFRESH | CDN version information |
| STATE_REFRESH | CDN node state information |
| GROUP_REFRESH | CDN group information |
| PROFILE_UPDATE | Transcoding profile update information |
| STREAM_ACCESS_LIST_REFRESH | Stream access keys information |
| STREAM_ACCESS_LIST_NODE_ID_UPDATE | Node Id update for stream access keys information |
Data retrieving from DB¶
Statistics data can be retrieved from MySQL DB using SQL queries
+---------------+---------------------+-------------+------------+------------+------------+
| serverIP | start | name | status | audioCodec | videoCodec |
+---------------+---------------------+-------------+------------+------------+------------+
| 95.191.131.64 | 2020-08-10 13:44:00 | test | PUBLISHING | opus | H264 |
| 95.191.131.64 | 2020-08-10 13:44:10 | test | PLAYING | opus | H264 |
+---------------+---------------------+-------------+------------+------------+------------+
2 rows in set (0.00 sec)
SDP data retrieving¶
SDP data are compressed due to big size. To decompress data while retrieving, use xxd and zlib-flate utilities