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".
Immediate Mitigations
| Action | Config Parameter | Effect |
|---|---|---|
| Enable disk-based query cache | query_cache = yes |
Prevents OOM by writing queries to disk (qoq* files) |
| Enable fast CDR save | quick_save_cdr = yes |
Reduces GUI delay from 10s to 3s (increases I/O load) |
| 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.
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?