High-Performance VoIPmonitor and MySQL Setup Manual

High-Performance VoIPmonitor and MySQL Setup Manual

This manual explains the configuration for a high-call-per-second (CPS) VoIPmonitor deployment. The configuration is split into two parts: the MySQL database and the VoIPmonitor sniffer.

Overall Concept

This system is designed for maximum data ingestion performance based on a centralized writer architecture. It trades certain data safety and consistency guarantees for an extremely high write throughput, suitable for large-scale deployments. The architecture is composed of three distinct stages:

1. Centralized CDR Ingestion (Sniffer Role): The primary sniffer instance is configured to act as a central data collector. Its main purpose is to receive Call Detail Records (CDRs) from all other sniffer instances in the network. In this mode, the central sniffer may not process all network packets itself; its primary function is to prepare and queue CDRs for database insertion. It is critical that in this architecture, only the central sniffer writes to the database. All other sniffers must be configured to send their data to this central instance.

1. Optimized Batch Writing (Sniffer to MySQL): To achieve maximum insert throughput, the central sniffer writes CDRs to MySQL in large, consolidated batches. A key optimization is that the database's automatic ID generators (AUTO_INCREMENT) are disabled. The sniffer pre-assigns unique IDs to the records, which allows for massive parallel inserts without the locking and serialization bottlenecks that normally occur at the database level.

1. High-Speed Database Storage (MySQL): The MySQL database is heavily optimized to ingest these large data batches at extremely high speeds. For environments with exceptionally high CDR-per-second rates, this configuration supports hourly table partitioning. While hourly partitioning dramatically accelerates write performance by keeping the active table small, it introduces a trade-off for data retrieval. When performing a search across an entire day, the database engine must query 24 separate partitions instead of a single daily one. This can result in slower query performance for reports or searches covering broad time ranges.

MySQL Configuration (my.cnf)

The goal of this configuration is to maximize the write performance of the InnoDB engine, often at the expense of strict ACID durability.

Directive	Description
skip-log-bin	Disables binary logging. This significantly increases write performance because the database doesn't have to record every data modification. The trade-off is the inability to perform point-in-time recovery or use replication.
innodb_flush_log_at_trx_commit=0	The most critical setting for write performance. Instead of flushing the transaction log to disk after every single transaction (default: 1), the database flushes it only once per second. This dramatically speeds up writes but can lead to the loss of the last second's worth of transactions if the server crashes.
innodb_flush_log_at_timeout = 1800	Defines the timeout in seconds for the log flushing. However, it is effectively overridden by the more frequent one-second flush from the setting above.
max_heap_table_size = 24G	Allows for the creation of very large temporary tables in memory. This is important for complex analytical queries that the VoIPmonitor GUI might run.
table_open_cache=9999	Keeps a large number of table definitions cached. Since VoIPmonitor uses hourly partitioning, it creates a vast number of tables, and this setting prevents the overhead of constantly opening and closing table files.
innodb_log_file_size = 5G	Defines a very large size for InnoDB's redo log files. Larger files reduce the frequency of I/O-intensive checkpoint operations, improving overall throughput.
innodb_log_buffer_size = 2G	Allocates a large buffer in memory (2 GB) for storing transaction logs before they are flushed to disk. This allows the system to handle large bursts of write activity without immediate disk I/O.
open_files_limit = 200000	Increases the file handle limit for the MySQL process, which is necessary to support the high table_open_cache value.
key_buffer_size = 2G	This setting is for the MyISAM storage engine. While most of VoIPmonitor uses InnoDB, some system or temporary tables might still use MyISAM. A 2GB buffer is allocated for its indexes.
sort_buffer_size = 65M	A large sort buffer for handling sorting operations (like ORDER BY or GROUP BY) for complex queries.
max_connections = 100000	An extremely high limit for connections. It acts as a safeguard against "too many connections" errors, though the real number of active connections will be much lower.
skip-name-resolve	Prevents MySQL from resolving hostnames for incoming connections. This speeds up the connection process by bypassing DNS lookups.
innodb_read_io_threads = 40	Sets a high number of threads for handling read I/O operations, suitable for systems with fast multi-channel storage (like NVMe arrays).
innodb_write_io_threads = 40	Sets a high number of threads for handling write I/O operations, matching the read threads for balanced I/O.
innodb_purge_threads = 20	A high number of threads dedicated to purging old, unneeded data versions, which is crucial in a high-volume write environment to prevent performance degradation.
innodb_flush_neighbors = 0	An optimization for SSD/NVMe drives. It disables a feature useful for rotational HDDs where adjacent blocks on disk are flushed at the same time. This is counterproductive for SSDs.
innodb_io_capacity = 1000000	Informs InnoDB that it's running on a very high-performance storage system capable of 1,000,000 IOPS. This allows InnoDB to be much more aggressive with background I/O tasks.
innodb_io_capacity_max = 10000000	Sets the absolute maximum IOPS the system can burst to, allowing InnoDB to use the full potential of the storage during intensive operations.
innodb_doublewrite = 0	Disables the doublewrite buffer. This provides a significant performance boost but increases the risk of data corruption (a "torn page") if a power failure occurs during a write operation. It's a performance-vs-safety trade-off.
innodb_buffer_pool_size = 150G	The most important parameter for InnoDB performance. A massive buffer pool size of 150 GB keeps a large portion of the database (data and indexes) in RAM, minimizing slow disk access.
innodb_flush_method = O_DIRECT	Bypasses the operating system's file cache. This prevents double-caching data (once in the OS, once in InnoDB) and gives the database direct control over I/O operations.
transaction-isolation = READ-UNCOMMITTED	The lowest level of transaction isolation. It allows transactions to read data that has not yet been committed. This offers maximum performance by minimizing locking.
performance_schema=0	Disables the Performance Schema, a detailed performance monitoring tool. Disabling it slightly reduces overhead and can increase performance.

VoIPmonitor Sniffer Configuration (voipmonitor.conf)

This configuration is tuned to enable the sniffer to process packets as quickly as possible, queue data efficiently, and avoid becoming a bottleneck.

Directive	Description
mysqlstore_concat_limit = 10000	Batches up to 10,000 CDRs into a single INSERT statement, dramatically reducing database overhead.
mysqlstore_max_threads_cdr = 40	Sets 40 threads for writing CDRs to the database, essential for high-volume parallel processing.
mysql_enable_new_store = per_query	Activates an alternative internal queueing mechanism for SQL queries, which can improve parallelization.
mysql_enable_set_id = yes	The sniffer generates the primary key (ID) for records itself, rather than relying on AUTO_INCREMENT in the database. This reduces database locking.
server_sql_queue_limit = 1000000	Sets a massive in-memory queue (1,000,000) for SQL commands to buffer against database slowdowns.
partition_operations_enable_fromto = 4-6	Schedules database partition management to run only between 4:00 and 6:00 AM, avoiding impact during peak hours.
cleandatabase = 3	Reduces the data retention period for cleaning the database to 3 days.
abort_if_rss_gt_gb = 20	Automatically restarts the sniffer if its memory usage exceeds 20 GB, acting as a safeguard against memory leaks.
mysql_redirect_cdr_queue = yes	Enables a separate, dedicated queue for CDRs being redirected from other sniffers.
partition_operations_drop_first = yes	Drops the oldest partition first before creating a new one during cleanup, which can be faster.
cdr_partition_by_hours = yes	Partitions CDR tables hourly instead of daily. This keeps the active write table extremely small, which is critical for high performance.
disable_cdr_indexes_rtp = yes	Disables some indexes on the cdr_rtp table to speed up writes, at the cost of slower searches on those fields.
sql_errors_skip = 1136,1054	Instructs the sniffer to ignore specific, non-critical database errors to prevent it from stopping during database maintenance.