SQL queue is growing in a peaktime: Difference between revisions

From VoIPmonitor.org
(Convert to redirect: content moved to Database_troubleshooting (race condition fix - original content was accidentally overwritten in rev 8275))
Tag: New redirect
(Rewrite: replaced redirect with focused troubleshooting page for SQL queue peak-time issues)
Tag: Removed redirect
Line 1: Line 1:
#REDIRECT [[Database_troubleshooting#SQL Queue and CDR Delays]]
{{DISPLAYTITLE:SQL Queue Growing During Peak Time}}
[[Category:Troubleshooting]]
[[Category:Database]]
[[Category:Performance]]
 
= 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 ==
 
<kroki lang="mermaid">
%%{init: {'flowchart': {'nodeSpacing': 15, 'rankSpacing': 30}}}%%
flowchart TD
    A[SQLq/SQLf growing] --> B{mysqld CPU 100%?}
    B -->|Yes| C[CPU bottleneck]
    B -->|No| D{High iowait?}
    D -->|Yes| E[I/O bottleneck]
    D -->|No| F[RAM/Buffer bottleneck]
 
    C --> G[Upgrade CPU or optimize queries]
    E --> H[Upgrade to SSD/NVMe]
    F --> I[Increase innodb_buffer_pool_size]
</kroki>
 
'''Monitor the queue:'''
<syntaxhighlight lang="bash">
journalctl -u voipmonitor -f | grep SQLf
</syntaxhighlight>
 
'''Check RRD graphs:''' Settings > Sensors > click graph icon > select peak time period. Look for growing "SQL Cache" or "SQL Cache Files".
 
== Immediate Mitigations ==
 
{| class="wikitable"
! Action !! Config Parameter !! Effect
|-
| Enable disk-based query cache || <code>query_cache = yes</code> || Prevents OOM by writing queries to disk (qoq* files)
|-
| Enable fast CDR save || <code>quick_save_cdr = yes</code> || Reduces GUI delay from 10s to 3s (increases I/O load)
|-
| Increase DB write threads || <code>mysqlstore_max_threads_cdr = 8</code> || Parallelizes database writes
|}
 
Apply in <code>/etc/voipmonitor.conf</code> and restart: <code>systemctl restart voipmonitor</code>
 
== Root Cause Solutions ==
 
=== I/O Bottleneck (Most Common) ===
 
Symptoms: High <code>iowait</code> in <code>top</code>, 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:
<syntaxhighlight lang="ini">
# /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
</syntaxhighlight>
 
=== CPU Bottleneck ===
 
Symptoms: <code>mysqld</code> 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 <code>query_cache=yes</code> to prevent OOM, <code>quick_save_cdr=yes</code> to reduce GUI delay, <code>mysqlstore_max_threads_cdr=8</code> for parallel writes. Root causes are typically I/O bottleneck (upgrade HDD to SSD/NVMe), RAM bottleneck (increase <code>innodb_buffer_pool_size</code> 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?

Revision as of 16:49, 8 January 2026


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

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?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=SQL_queue_is_growing_in_a_peaktime&oldid=10162"