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.

Immediate Mitigations

Action	Config Parameter	Effect
Enable disk-based query cache	query_cache = yes	Prevents OOM by writing queries to disk (qoq* files)
Increase DB write threads	mysqlstore_max_threads_cdr = 8	Parallelizes database writes

Apply in /etc/voipmonitor.conf and restart: systemctl restart voipmonitor

Root Cause Solutions

I/O Bottleneck (Most Common)

Symptoms: High iowait in top, HDD storage.

Solution: Upgrade to SSD/NVMe storage for MySQL data directory. This typically provides 10-50x improvement.

RAM Bottleneck

Symptoms: Buffer pool constantly full, swapping.

Solution: Increase MySQL buffer pool:

# /etc/mysql/mysql.conf.d/mysqld.cnf
innodb_buffer_pool_size = 14G  # 50-70% of total RAM
innodb_flush_log_at_trx_commit = 2  # Faster writes

CPU Bottleneck

Symptoms: mysqld process at 100% CPU.

Solution: Hardware upgrade required. As interim measure, schedule reports and alerts outside peak hours. First, try MySQL InnoDB thread tuning:

# /etc/mysql/mysql.conf.d/mysqld.cnf
# Adjust these values to match your CPU core count
innodb_read_io_threads = 4
innodb_write_io_threads = 4
innodb_purge_threads = 3
innodb_thread_concurrency = 4

Then restart MySQL: systemctl restart mysql

This allows InnoDB to use multiple CPU cores more effectively instead of being limited to a single thread.

ℹ️ Note: These values assume a 4-core system. Adjust proportionally for your CPU core count (check with nproc command).

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

See Also

• Database_troubleshooting - Comprehensive database troubleshooting guide
• Emergency_procedures - RRD graph diagnostics for bottleneck identification
• Scaling - Performance tuning and scaling guidelines

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?