SQL queue is growing in a peaktime

SQL Queue Growing During Peak Time

When the SQL queue (SQLq/SQLf) grows during peak traffic, the database cannot keep up with the CDR insertion rate. This causes delayed CDR visibility in the GUI and can lead to memory exhaustion.

Quick Diagnosis

Monitor the queue:

journalctl -u voipmonitor -f | grep SQLf

Check RRD graphs: Settings > Sensors > click graph icon > select peak time period. Look for growing "SQL Cache" or "SQL Cache Files".

ℹ️ Note: SQLf value should normally stay below 10. If it consistently exceeds 10 or grows during peak time, apply mitigations.

Storage Speed is Critical

The most common cause of SQL queue growth is slow storage. MySQL write performance depends heavily on disk I/O speed.

Storage Type	Typical IOPS	CDR Write Capacity	Recommendation
HDD (7200 RPM)	~150	10-50 CDR/s	Not suitable for production
SATA SSD	~50,000	200-500 CDR/s	Suitable for small deployments
NVMe SSD	~500,000+	1000+ CDR/s	Recommended for production

Check your current I/O performance:

# Check disk utilization during peak time
iostat -x 1 5 | grep -E "Device|sda|nvme"

# If %util stays above 80%, storage is the bottleneck

VoIPmonitor Tuning

These parameters in /etc/voipmonitor.conf control how the sniffer writes to the database:

Parameter	Default	Recommended	Description
mysqlstore_max_threads_cdr	1	4-8	Number of parallel threads writing CDRs to database
mysqlstore_concat_limit	400	1000-2000	CDRs batched into single INSERT statement
query_cache	no	yes	Write queries to disk when queue grows (prevents OOM)

Example configuration:

# /etc/voipmonitor.conf
mysqlstore_max_threads_cdr = 8
mysqlstore_concat_limit = 2000
query_cache = yes

Restart after changes: systemctl restart voipmonitor

MySQL Tuning

Essential Settings

These settings are safe for most deployments and significantly improve write performance:

# /etc/mysql/mysql.conf.d/mysqld.cnf

# Buffer pool - use 50-70% of available RAM
innodb_buffer_pool_size = 8G

# Faster writes - flush log once per second instead of every transaction
# Small risk: up to 1 second of data loss on crash
innodb_flush_log_at_trx_commit = 2

# I/O threads - adjust to your CPU core count
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_purge_threads = 2

# Skip DNS lookups for faster connections
skip-name-resolve

# Larger log files = fewer checkpoints = better performance
innodb_log_file_size = 1G
innodb_log_buffer_size = 256M

SSD/NVMe Specific Settings

If using SSD or NVMe storage, add these optimizations:

# Disable HDD optimization (not needed for SSD)
innodb_flush_neighbors = 0

# Tell InnoDB about fast storage (adjust to your disk specs)
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000

# Bypass OS cache - better for dedicated DB servers
innodb_flush_method = O_DIRECT

Restart MySQL after changes: systemctl restart mysql

Verify Settings

# Check current buffer pool size
mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size';"

# Check if buffer pool is fully utilized
mysql -e "SHOW STATUS LIKE 'Innodb_buffer_pool_pages%';"

Prevention

• Schedule heavy operations off-peak: Configure alerts and reports to run at night
• Separate components: Use dedicated database server for high-traffic deployments
• Monitor trends: Set up alerts for SQLq growth before it becomes critical

Extreme High-Volume Deployments

For deployments processing hundreds or thousands of CDRs per second (10,000+ concurrent calls), standard tuning is not sufficient. These scenarios require:

• Disabling AUTO_INCREMENT and letting the sniffer generate IDs
• Hourly table partitioning instead of daily
• Batch sizes of 10,000+ CDRs
• Aggressive MySQL settings that trade durability for performance

See High-Performance_VoIPmonitor_and_MySQL_Setup_Manual for detailed configuration.

See Also

• Database_troubleshooting - Comprehensive database troubleshooting guide
• Emergency_procedures - RRD graph diagnostics for bottleneck identification
• Scaling - Performance tuning and scaling guidelines
• High-Performance_VoIPmonitor_and_MySQL_Setup_Manual - Configuration for 1000+ CDR/s deployments

AI Summary for RAG

Summary: Troubleshooting guide for SQL queue (SQLq/SQLf) growth during peak traffic periods. The queue grows when the database cannot keep up with CDR insertion rate. Diagnosis involves checking mysqld CPU usage, iowait, and RRD graphs in GUI (Settings > Sensors > graph icon). Immediate mitigations: enable query_cache=yes to prevent OOM, quick_save_cdr=yes to reduce GUI delay, mysqlstore_max_threads_cdr=8 for parallel writes. Root causes are typically I/O bottleneck (upgrade HDD to SSD/NVMe), RAM bottleneck (increase innodb_buffer_pool_size to 50-70% of RAM), or CPU bottleneck (hardware upgrade needed). Prevention includes scheduling heavy operations off-peak and using dedicated database servers.

Keywords: SQL queue, SQLq, SQLf, peak time, database bottleneck, query_cache, quick_save_cdr, mysqlstore_max_threads_cdr, innodb_buffer_pool_size, I/O bottleneck, SSD upgrade, OOM, CDR delay, database performance, peak traffic

Key Questions:

• Why is the SQL queue growing during peak hours?
• How do I monitor SQL queue size in VoIPmonitor?
• What causes CDR delays in the GUI?
• How do I prevent out-of-memory errors when SQL queue grows?
• Should I upgrade to SSD for VoIPmonitor database?
• What is the recommended innodb_buffer_pool_size setting?
• How do I speed up CDR insertion during high traffic?