Diagnosing Database Bottlenecks Using Sensor RRD Charts

If your VoIPmonitor GUI becomes unresponsive or PHP processes are being terminated by the OOM (Out of Memory) killer, the root cause may be a database performance bottleneck, not a PHP configuration issue.

This guide explains how to use the sensor's RRD (Round-Robin Database) charts to identify whether the database server is the limiting factor.

Symptoms of Database Bottlenecks affecting the GUI

GUI becomes extremely slow or unresponsive during peak hours
PHP processes are killed by the OOM killer on the GUI server
Dashboard and CDR views take a long time to load
Alerts and reports fail during high traffic periods
System appears fine during off-peak hours but degrades during peak usage

ℹ️ Note: These symptoms often occur when the GUI server is waiting for database queries to complete, causing PHP processes to pile up and consume excessive memory.

Understanding Sensor RRD Charts

The VoIPmonitor sensor generates performance charts (RRD files) that track system metrics over time. These charts are accessible through the GUI and provide visual indicators of where bottlenecks are occurring.

To access sensor RRD charts:

Navigate to Settings > Sensors in the GUI
Click the graph icon next to the sensor
Select the time range covering the problematic peak hours

Diagnostic Flowchart

Diagnostic Step 1: Look for Growing SQL Cache

The most critical indicator of a database bottleneck is growing SQL cache or SQL cache files during peak hours.

Metric	What to Look For	Indicates
SQL Cache	Consistently increasing during peak hours, never decreasing	Database cannot keep up with insert rate
SQL Cache Files	Growing over time during peak usage	Database buffer pool too small or storage too slow
CPU Load (mysqld)	Near 100% during peak hours	CPU bottleneck on database server
Disk I/O (mysql)	High or saturated during peak hours	Storage bottleneck (magnetic disks instead of SSDs)

If you see SQL cache or SQL cache files growing consistently during peak traffic periods, the database server is the bottleneck.

Diagnostic Step 2: Determine the Bottleneck Type

After identifying that the database is the issue, determine which resource is the limiting factor:

CPU Bottleneck

Check database CPU usage during peak hours
If mysqld is at or near 100% CPU, you need more CPU cores or faster CPUs

Memory Bottleneck

Check if SQL cache grows because buffer pool is too small
Database runs out of RAM for caching, forcing disk reads
The SQL cache chart shows a pattern of filling up and staying full

Storage I/O Bottleneck (Most Common)

High disk I/O wait times for mysqld process
Disk latency (iowait) increases during peak hours
Database storage on magnetic disks (e.g., 10K SAS) instead of SSD/NVMe
SQL cache grows because data cannot be written/read fast enough

Solutions for Database Performance Bottlenecks

Solution 1: Add More RAM to the Database Server

This is often the most effective fix for memory-related bottlenecks.

Current RAM	Recommended Upgrade	Expected Impact
32GB	64GB or 128GB	Significantly reduces cache growth
64GB	128GB or 256GB	Handles much higher peak loads
128GB	256GB	Suitable for large deployments

After adding RAM, tune innodb_buffer_pool_size in your MySQL configuration:

# /etc/mysql/my.cnf
# Set to 50-70% of total RAM on dedicated database server
innodb_buffer_pool_size = 128G

For more tuning guidance, see Scaling - Database Performance.

⚠️ Warning: Do NOT reduce innodb_buffer_pool_size on the GUI server when the database is the bottleneck. This will make the problem worse.

Solution 2: Upgrade Database Storage to SSD/NVMe

If your database storage is on magnetic disks (e.g., 10K SATA or SAS), upgrading to SSDs is often the single most effective improvement.

Current Storage	Recommended Upgrade	Expected Speedup
10K RPM SATA HDD	NVMe SSD array	10-50x faster I/O
10K RPM SAS HDD	Enterprise SSD (SAS/SATA)	5-20x faster I/O
Older SSD	Modern NVMe (PCIe 4.0+)	2-5x faster I/O

For high-traffic deployments, NVMe storage is recommended for the database host.

See Hardware - Storage Selection for detailed recommendations.

Solution 3: Temporary Mitigation - Schedule Alerts/Reports Outside Peak Hours

If you cannot immediately upgrade the database server hardware, temporarily reduce the load by scheduling intensive tasks during off-peak hours.

1. Disable or reduce alert frequency during peak hours:

Navigate to GUI > Alerts
Temporarily disable high-frequency alerts
Set alerts to run during off-peak periods (e.g., 2am-4am)

2. Schedule reports outside peak usage:

Navigate to GUI > Reports
Configure scheduled reports for off-peak hours
Avoid generating reports during the busiest part of the day

3. Reduce dashboard complexity during peak hours:

Simplify dashboards that query large ranges of data
Avoid "All time" statistics during peak loads
Use cached dashboards or static displays when possible

Solution 4: Consider Component Separation

If the database server is a bottleneck and upgrading is not feasible, consider moving to a dedicated database architecture.

In a component separation deployment (see Scaling - Component Separation):

Host 1: Dedicated database server with maximum RAM and SSD/NVMe storage
Host 2: GUI web server
Host 3: Sensor(s)

This allows you to independently scale the database with more powerful hardware without affecting the GUI.

Common Pitfalls to Avoid

Incorrect Solutions When Database is the Bottleneck
Reducing PHP memory_limit	This does NOT fix the root cause. PHP waits for the database; less memory means processes crash sooner.
Tuning PHP-FPM worker counts	More workers will pile up waiting for slow database queries, consuming even more memory.
Reducing innodb_buffer_pool_size	This makes the database slower, not faster. It causes more disk I/O and longer query times.
Adding RAM to the GUI server	If the bottleneck is the database, adding RAM to the GUI won't help. The database is the limiting factor.

Verification Checklist

After implementing a database upgrade to fix the bottleneck:

1. Monitor SQL cache charts during the next peak traffic period 2. Check that SQL cache does not grow uncontrollably 3. Verify GUI responsiveness during peak hours 4. Confirm no OOM killer events 5. Check database query latency (slow queries should be minimal)

AI Summary for RAG

Summary: Guide for diagnosing database bottlenecks affecting VoIPmonitor GUI using sensor RRD charts. Symptoms: GUI unresponsive during peak hours, OOM killer terminating PHP processes, slow dashboard/CDR views. KEY DIAGNOSTIC: Check sensor RRD charts (Settings > Sensors > graph icon) for growing SQL cache during peak hours - primary indicator of database bottleneck. Bottleneck types: CPU (mysqld at 100%), Memory (buffer pool too small), Storage I/O (most common - high iowait, magnetic disks). Solutions: (1) Add RAM to database server and tune innodb_buffer_pool_size to 50-70% of RAM; (2) Upgrade storage from HDD to SSD/NVMe (10-50x speedup); (3) Schedule alerts/reports outside peak hours; (4) Component separation with dedicated database server. INCORRECT solutions: Do NOT reduce PHP memory_limit, do NOT tune PHP-FPM workers, do NOT reduce innodb_buffer_pool_size, do NOT add RAM to GUI server instead of database.

Keywords: database bottleneck, RRD charts, sensor performance, SQL cache, SQL cache files, peak hours, OOM killer, GUI unresponsive, dashboard slow, RAM upgrade, SSD upgrade, NVMe, iowait, innodb_buffer_pool_size, component separation, dedicated database

Key Questions:

How do I diagnose database bottlenecks in VoIPmonitor?
What do growing SQL cache files in RRD charts indicate?
Why is my VoIPmonitor GUI slow during peak hours?
How to fix OOM killer terminating PHP processes?
Should I upgrade RAM on GUI server or database server?
What storage is recommended for VoIPmonitor database?
How to access sensor RRD charts in VoIPmonitor GUI?
What are incorrect solutions for database bottlenecks?
How much RAM should innodb_buffer_pool_size be set to?
When should I consider component separation for VoIPmonitor?

Emergency procedures