Data Cleaning: Difference between revisions

From VoIPmonitor.org
(Add troubleshooting section for disk full scenarios with du command and increasing maxpoolsize)
(Improved structure: added architecture diagram, fixed section numbering, improved syntax highlighting, streamlined AI RAG section)
Line 1: Line 1:
'''This guide explains how VoIPmonitor manages data retention for both captured packets (PCAP files) and Call Detail Records (CDRs) in the database. Proper configuration is essential for managing disk space and maintaining long-term database performance. '''
This guide explains how VoIPmonitor manages data retention for both captured packets (PCAP files) and Call Detail Records (CDRs) in the database. Proper configuration is essential for managing disk space and maintaining long-term database performance.
 
== Overview ==


== Overview of Data Cleaning ==
VoIPmonitor generates two primary types of data that require periodic cleaning:
VoIPmonitor generates two primary types of data that require periodic cleaning:
*'''PCAP Files:''' Raw packet captures of SIP/RTP/GRAPH data stored on the filesystem in the spool directory. These can consume significant disk space.
* '''PCAP Files:''' Raw packet captures of SIP/RTP/GRAPH data stored on the filesystem in the spool directory. These can consume significant disk space.
*'''CDR Data:''' Call metadata stored in the MySQL database. Large tables can slow down GUI performance if not managed properly.
* '''CDR Data:''' Call metadata stored in the MySQL database. Large tables can slow down GUI performance if not managed properly.


The system uses two separate, independent mechanisms to manage the retention of this data.
The system uses two separate, independent mechanisms to manage the retention of this data:
 
<kroki lang="plantuml">
@startuml
skinparam shadowing false
skinparam defaultFontName Arial
skinparam rectangle {
  BorderColor #4A90E2
  BackgroundColor #FFFFFF
}
 
rectangle "VoIPmonitor Sensor" as sensor
 
package "Filesystem Storage" {
  folder "/var/spool/voipmonitor" as spool {
    file "SIP PCAPs" as sip
    file "RTP PCAPs" as rtp
    file "GRAPH files" as graph
    file "AUDIO files" as audio
  }
}
 
database "MySQL Database" {
  collections "cdr" as cdr
  collections "cdr_next" as cdrnext
  collections "register_state" as reg
  collections "sip_msg" as sipmsg
}
 
sensor --> spool : writes
sensor --> cdr : writes
 
rectangle "Filesystem Cleaner\n(maxpoolsize/maxpooldays)" as fscleaner #E8F5E9
rectangle "Database Cleaner\n(cleandatabase)" as dbcleaner #E3F2FD
 
fscleaner --> spool : deletes old files
dbcleaner --> cdr : drops partitions
 
note bottom of fscleaner : Runs every 5 minutes\nDeletes oldest data first
note bottom of dbcleaner : Daily partition drop\nInstant operation
@enduml
</kroki>
 
== Filesystem Cleaning (PCAP Spool Directory) ==


== 1. Filesystem Cleaning (PCAP Spool Directory) ==
The sensor stores captured call data in a structured directory tree on the local filesystem.
The sensor stores captured call data in a structured directory tree on the local filesystem.


=== Spool Directory Location ===
=== Spool Directory Location ===
By default, all data is stored in `/var/spool/voipmonitor`. This location can be changed by setting the `spooldir` option in `voipmonitor.conf`.
 
By default, all data is stored in <code>/var/spool/voipmonitor</code>. This location can be changed by setting the <code>spooldir</code> option in <code>voipmonitor.conf</code>.


=== Retention Configuration ===
=== Retention Configuration ===
The cleaning process runs automatically every 5 minutes and removes the oldest data based on the rules you define in `voipmonitor.conf`. You can set limits based on total size (in Megabytes) or age (in days). If both a size and day limit are set for the same data type, the first limit that is reached will trigger the cleaning.


The following options are available:
The cleaning process runs automatically every 5 minutes and removes the oldest data based on the rules you define in <code>voipmonitor.conf</code>. You can set limits based on total size (in Megabytes) or age (in days). If both a size and day limit are set for the same data type, the first limit that is reached will trigger the cleaning.
 
{| class="wikitable"
{| class="wikitable"
|-
|-
! Parameter !! Default Value !! Description
! Parameter !! Default Value !! Description
|-
|-
| `maxpoolsize` || `102400` (100 GB) || The total maximum disk space for '''all''' captured data (SIP, RTP, GRAPH, AUDIO).
| <code>maxpoolsize</code> || 102400 (100 GB) || The total maximum disk space for '''all''' captured data (SIP, RTP, GRAPH, AUDIO).
|-
|-
| `maxpooldays` || (unset) || The maximum number of days to keep '''all''' captured data.
| <code>maxpooldays</code> || (unset) || The maximum number of days to keep '''all''' captured data.
|-
|-
| `maxpoolsipsize` || (unset) || A specific size limit for SIP PCAP files only.
| <code>maxpoolsipsize</code> || (unset) || A specific size limit for SIP PCAP files only.
|-
|-
| `maxpoolsipdays` || (unset) || A specific age limit for SIP PCAP files only.
| <code>maxpoolsipdays</code> || (unset) || A specific age limit for SIP PCAP files only.
|-
|-
| `maxpoolrtpsize` || (unset) || A specific size limit for RTP PCAP files only.
| <code>maxpoolrtpsize</code> || (unset) || A specific size limit for RTP PCAP files only.
|-
|-
| `maxpoolrtpdays` || (unset) || A specific age limit for RTP PCAP files only.
| <code>maxpoolrtpdays</code> || (unset) || A specific age limit for RTP PCAP files only.
|-
|-
| `maxpoolgraphsize` || (unset) || A specific size limit for GRAPH files only.
| <code>maxpoolgraphsize</code> || (unset) || A specific size limit for GRAPH files only.
|-
|-
| `maxpoolgraphdays` || (unset) || A specific age limit for GRAPH files only.
| <code>maxpoolgraphdays</code> || (unset) || A specific age limit for GRAPH files only.
|-
|-
| `maxpoolaudiosize` || (unset) || A specific size limit for converted audio files (WAV/OGG) only.
| <code>maxpoolaudiosize</code> || (unset) || A specific size limit for converted audio files (WAV/OGG) only.
|-
|-
| `maxpoolaudiodays` || (unset) || An age limit for converted audio files (WAV/OGG) only.
| <code>maxpoolaudiodays</code> || (unset) || An age limit for converted audio files (WAV/OGG) only.
|}
|}


=== Maintenance: Re-indexing the Spool Directory ===
=== Troubleshooting: Disk Full / Files Disappearing ===
VoIPmonitor maintains an index of all created PCAP files to perform cleaning efficiently without scanning the entire directory tree. If this index becomes corrupt, or if you manually move files into the spool, old data may not be deleted correctly.
 
In this case, you must trigger a manual re-index. This can be done via the sniffer's manager API.
# '''Open a manager API session:'''
#<syntaxhighlight lang="bash">echo 'manager_file start /tmp/vmsck' | nc 127.0.0.1 5029</syntaxhighlight>
# '''Send the re-index command:'''
#<syntaxhighlight lang="bash">echo reindexfiles | nc -U /tmp/vmsck</syntaxhighlight>
''Note: This command requires `netcat` with support for UNIX sockets (`-U`). For alternative methods, see the [[Encryption_in_manager_api_customer|Manager API documentation]].''


== 1.5. Troubleshooting: Disk Full Increasing Spooldir ==
If you see errors when attempting to extract older calls from the GUI, or if call files are disappearing too quickly, your spool directory may have reached its size limit.
If you see errors when attempting to extract older calls from the GUI, or if call files are disappearing too quickly, your spool directory may have reached its size limit. The solution is to check disk usage and increase the <code>maxpoolsize</code> parameter.


=== Diagnosis: Check Disk Usage ===
==== Diagnosis: Check Disk Usage ====


;1. Identify the sensor/probe responsible for the missing data.
# Identify the sensor/probe responsible for the missing data.
;2. SSH into the sensor/probe and navigate to the spooldir (e.g., <code>/var/spool/voipmonitor</code>).
# SSH into the sensor/probe and navigate to the spooldir.
;3. Check the disk usage to understand data growth:
# Check the disk usage:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Navigate to spooldir
cd /var/spool/voipmonitor
cd /var/spool/voipmonitor
# Check disk usage by subdirectory
du -h --max-depth=1 ./
du -h --max-depth=1 ./


# Common output example:
# Example output:
# 150G    ./2025-01
# 150G    ./2025-01
# 120G    ./2024-12
# 120G    ./2024-12
Line 75: Line 107:
</syntaxhighlight>
</syntaxhighlight>


;4. Identify the configured limit:
# Compare with the configured limit:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Check current maxpoolsize setting in voipmonitor.conf
grep maxpoolsize /etc/voipmonitor.conf
grep maxpoolsize /etc/voipmonitor.conf
# Example output: maxpoolsize = 102400  (100 GB in MB)
# Example output: maxpoolsize = 102400  (100 GB in MB)
</syntaxhighlight>
</syntaxhighlight>


=== Resolution: Increase Spooldir Size ===
==== Resolution: Increase Spooldir Size ====


If the actual usage (e.g., 360 GB) exceeds or approaches the configured limit (e.g., 100 GB), increase the <code>maxpoolsize</code> to a value large enough to hold your required retention period.
If the actual usage exceeds the configured limit, increase <code>maxpoolsize</code>:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
# Edit /etc/voipmonitor.conf
# Edit /etc/voipmonitor.conf
[general]
[general]
# Increase from 100GB to 700GB example
maxpoolsize = 716800   # 700 GB in MB
maxpoolsize = 716800 # 700 GB in MB
maxpooldays = 90      # Optional: Keep data for last 90 days
</syntaxhighlight>


# Optional: Also increase maxpooldays if you know the required retention in days
Apply changes:
maxpooldays = 90    # Keep data for last 90 days
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
</syntaxhighlight>
</syntaxhighlight>


After making changes:
=== Maintenance: Re-indexing the Spool Directory ===
 
VoIPmonitor maintains an index of all created PCAP files to perform cleaning efficiently without scanning the entire directory tree. If this index becomes corrupt, or if you manually move files into the spool, old data may not be deleted correctly.
 
To trigger a manual re-index via the manager API:
 
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Restart the sensor to apply changes
# Open a manager API session
systemctl restart voipmonitor
echo 'manager_file start /tmp/vmsck' | nc 127.0.0.1 5029


# Verify the configuration loaded
# Send the re-index command
grep maxpoolsize /etc/voipmonitor.conf
echo reindexfiles | nc -U /tmp/vmsck
</syntaxhighlight>
</syntaxhighlight>


== 2. Database Cleaning (CDR Retention) ==
Note: This command requires <code>netcat</code> with support for UNIX sockets (<code>-U</code>). For alternative methods, see the [[Encryption_in_manager_api_customer|Manager API documentation]].
Managing the size of the `cdr` table and other large tables is critical for GUI performance.
 
== Database Cleaning (CDR Retention) ==
 
Managing the size of the <code>cdr</code> table and other large tables is critical for GUI performance.
 
=== Partitioning Method (Recommended) ===
 
Since version 7, VoIPmonitor utilizes '''database partitioning''', which splits large tables into smaller, daily segments. This is the recommended method for managing database retention.


=== The Modern Method: Partitioning (Recommended) ===
{| class="wikitable" style="width:100%;"
Since version 7, VoIPmonitor utilizes **database partitioning**, which splits large tables into smaller, daily segments. This is the highly recommended method for managing database retention.
|-
* '''How it works:''' You set a single parameter, `cleandatabase`, in `voipmonitor.conf`. This defines the number of days to keep CDRs. For example, `cleandatabase = 30` will keep the last 30 days of data.
! Aspect !! Description
* '''Why it's better:''' The sniffer automatically drops old daily partitions, which is an instantaneous operation that takes milliseconds, regardless of how many millions of rows it contains. This puts zero load on the database.
|-
* '''Requirement:''' Partitioning is enabled by default on all new installations. If you are upgrading from a very old version, it may require starting with a fresh database.
| '''How it works''' || Set <code>cleandatabase = 30</code> in <code>voipmonitor.conf</code> to keep the last 30 days of data.
|-
| '''Why it's better''' || Dropping old partitions is instantaneous (milliseconds), regardless of row count. Zero database load.
|-
| '''Requirement''' || Partitioning is enabled by default on new installations.
|}


==== Quick Start: Global Retention ====
==== Quick Start: Global Retention ====
For most deployments, you only need to configure one parameter in `/etc/voipmonitor.conf`:


<syntaxhighlight lang="bash">
For most deployments, configure one parameter in <code>/etc/voipmonitor.conf</code>:
# Keep all records for 30 days (applies to CDR, sip_msg, register_state, etc.)
 
<syntaxhighlight lang="ini">
# Keep all records for 30 days
cleandatabase = 30
cleandatabase = 30
</syntaxhighlight>
</syntaxhighlight>


The `cleandatabase` parameter acts as a ''global default'' for ALL `cleandatabase_*` options. If you only set `cleandatabase`, it will automatically apply to these tables unless you explicitly override them:
The <code>cleandatabase</code> parameter acts as a global default for all <code>cleandatabase_*</code> options and applies to:
* `cdr` - Call Detail Records
* <code>cdr</code> - Call Detail Records
* `message` - SIP MESSAGE texts
* <code>message</code> - SIP MESSAGE texts
* `sip_msg` - SIP OPTIONS/SUBSCRIBE/NOTIFY messages
* <code>sip_msg</code> - SIP OPTIONS/SUBSCRIBE/NOTIFY messages
* `register_state` - SIP registration states
* <code>register_state</code> - SIP registration states
* `register_failed` - Failed registration attempts
* <code>register_failed</code> - Failed registration attempts
 
This is the simplest way to ensure consistent retention across all table types and prevents unexpected database growth from auxiliary SIP tables.
 
More details can be found in the [[Sniffer_configuration#cleandatabase|Sniffer Configuration guide]].


==== Database Retention Parameters ====
==== Retention Parameters ====


{| class="wikitable"
{| class="wikitable"
|-
|-
! Parameter !! Default Value !! Description
! Parameter !! Default !! Description
|-
|-
| `cleandatabase` || `0` (disabled) || Master retention setting in days for CDRs and several other tables.
| <code>cleandatabase</code> || 0 (disabled) || Master retention setting in days.
|-
|-
| `cleandatabase_cdr` || `0` (disabled, uses cleandatabase) || Specific retention period for `cdr` and `message` tables.
| <code>cleandatabase_cdr</code> || 0 || Specific retention for <code>cdr</code> and <code>message</code> tables.
|-
|-
| `cleandatabase_rtp_stat` || `2` days || Retention in days for detailed RTP statistics.
| <code>cleandatabase_rtp_stat</code> || 2 || Retention for detailed RTP statistics.
|-
|-
| `cleandatabase_sip_msg` || `0` (disabled) || Retention for SIP OPTIONS/SUBSCRIBE/NOTIFY messages.
| <code>cleandatabase_sip_msg</code> || 0 || Retention for OPTIONS/SUBSCRIBE/NOTIFY.
|-
|-
| `cleandatabase_size` || (unset) || Alternative cleaning method that removes old data to stay below a total database size limit (in Megabytes). Requires sniffer version 2024.05.1 or newer.
| <code>cleandatabase_size</code> || (unset) || Alternative: size-based limit in MB (requires version 2024.05.1+).
|-
|-
| `partition_operations_enable_fromto` || `1-5` || Restricts partition-dropping operations to a specific time window (e.g., 1 AM to 5 AM) to avoid impacting performance during peak hours.
| <code>partition_operations_enable_fromto</code> || 1-5 || Time window for partition operations (e.g., 1-5 AM).
|}
|}


=== The Legacy Method: Manual Deletion (Not Recommended) ===
More details: [[Sniffer_configuration#Database_Cleaning|Sniffer Configuration - Database Cleaning]].
If you are running a very old, non-partitioned database, you cannot use the `cleandatabase` option. You would need to create a custom script that runs a `DELETE FROM cdr WHERE calldate < ...` query.
* '''Warning:''' This method is extremely slow and resource-intensive on large tables. A single `DELETE` operation on millions of rows can take hours and generate significant I/O load on your database server, potentially impacting GUI performance.


== 3. Troubleshooting Disk Space Issues ==
=== Legacy Method: Manual Deletion (Not Recommended) ===
 
For very old, non-partitioned databases, you would need custom scripts with <code>DELETE FROM cdr WHERE calldate < ...</code> queries.
 
'''Warning:''' Manual DELETE on large tables is extremely slow and resource-intensive. A single operation on millions of rows can take hours and impact GUI performance.
 
== Troubleshooting Disk Space Issues ==


=== Disk Space Not Reclaimed After Cleanup ===
=== Disk Space Not Reclaimed After Cleanup ===


If you have configured automatic cleanup but disk space is not being freed from the MySQL data directory, the issue is likely related to the MySQL `innodb_file_per_table` setting. This setting controls whether each InnoDB table is stored in its own `.ibd` file or in the shared `ibdata1` file.
If automatic cleanup runs but disk space is not freed from the MySQL data directory, check the <code>innodb_file_per_table</code> setting:


'''Check the Current Setting:'''
<syntaxhighlight lang="sql">
<syntaxhighlight lang="bash">
SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name        | Value |
+-----------------------+-------+
| innodb_file_per_table | ON    |
+-----------------------+-------+
</syntaxhighlight>
</syntaxhighlight>


'''What This Means:'''
{| class="wikitable"
*'''If value is ON:''' Each table/partition is stored in its own file. When a partition is dropped, the file is deleted immediately, and disk space is reclaimed.
|-
*'''If value is OFF when database was created:''' All table data is stored in the shared `ibdata1` file. Dropping partitions or rows will not reduce this file size.
! Value !! Behavior
|-
| '''ON''' || Each table/partition has its own <code>.ibd</code> file. Dropping partitions reclaims space immediately.
|-
| '''OFF''' || All data in shared <code>ibdata1</code> file. Dropping partitions does '''not''' reduce file size.
|}


'''Solutions:'''
==== Solutions ====


''Option 1: Enable innodb_file_per_table (Preventative)''
;Option 1: Enable for Future Tables
Add to `/etc/my.cnf` or `/etc/mysql/my.cnf`:
Add to <code>/etc/my.cnf</code> or <code>/etc/mysql/my.cnf</code>:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="ini">
[mysqld]
[mysqld]
innodb_file_per_table = 1
innodb_file_per_table = 1
</syntaxhighlight>
</syntaxhighlight>
After changing this setting, restart MySQL:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
service mysql restart
systemctl restart mysql
</syntaxhighlight>
</syntaxhighlight>
''Note: This only affects NEW tables/partitions. Existing data in `ibdata1` will remain.''
Note: This only affects NEW tables/partitions. Existing data in <code>ibdata1</code> remains.


''Option 2: Reclaim Space from Existing Tables''
;Option 2: Reclaim Space from Existing Tables
If `innodb_file_per_table` is ON and you have partitioned tables, run:
<syntaxhighlight lang="sql">
<syntaxhighlight lang="bash">
OPTIMIZE TABLE cdr;
OPTIMIZE TABLE cdr;
</syntaxhighlight>
</syntaxhighlight>
'''Warning:''' This requires significant free disk space to duplicate the table data. If your disk is already nearly full, this command will fail or crash the database.
'''Warning:''' Requires significant free disk space to duplicate table data. May crash if disk is nearly full.


''Option 3: Export and Re-import (Complete Reclaim)''
;Option 3: Export and Re-import
If `innodb_file_per_table` was OFF when the database was created and you must reclaim space:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
mysqldump -u root -p voipmonitor > voipmonitor_backup.sql
mysqldump -u root -p voipmonitor > voipmonitor_backup.sql
Line 208: Line 254:
=== Monitoring Database Health ===
=== Monitoring Database Health ===


To proactively monitor database health and detect issues before they become critical, use the following metrics and tools.
==== SQL Queue Metrics ====
 
'''SQL Queue Metrics (SQLq/SQLf)'''
 
The VoIPmonitor sensor tracks database queue metrics that indicate if the database is falling behind processing CDRs. These are visible in:


* GUI: Settings → Sensors → Status (expanded status line)
The sensor tracks queue metrics visible in GUI → Settings → Sensors → Status:
* RRD files (if using collectd or similar)
* Sniffer's internal health monitoring logs


{| class="wikitable"
{| class="wikitable"
Line 222: Line 262:
! Metric !! Description !! Healthy Range
! Metric !! Description !! Healthy Range
|-
|-
| SQLq || SQL Queue count - number of CDRs waiting to be written to database || Near 0, sporadic spikes
| '''SQLq''' || CDRs waiting to be written to database || Near 0, sporadic spikes OK
|-
|-
| SQLf || SQL Failed count - number of failed database write attempts || Zero (or very low, not growing)
| '''SQLf''' || Failed database write attempts || Zero (not growing)
|}
|}


'''Interpreting SQLq/SQLf:'''
* Consistently high/growing SQLq → database cannot keep up
* Occasionally high SQLq during traffic spikes is normal
* Non-zero/growing SQLf → database errors or connectivity issues
* Consistently high or growing SQLq indicates the database cannot keep up
* Non-zero or growing SQLf indicates database errors or connectivity issues


For detailed troubleshooting of high SQL queue, see [[SQL_queue_is_growing_in_a_peaktime|SQL Queue Troubleshooting]].
See [[SQL_queue_is_growing_in_a_peaktime|SQL Queue Troubleshooting]] for details.


'''System Load Monitoring'''
==== System Monitoring ====


Check overall system load average:
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# Check system load
cat /proc/loadavg
cat /proc/loadavg
</syntaxhighlight>
The three numbers represent 1-minute, 5-minute, and 15-minute load averages. Consistently high load averages indicate CPU, I/O, or memory bottlenecks.
'''Disk I/O Monitoring'''


Use `iotop` to identify processes causing disk I/O bottlenecks:
# Monitor disk I/O (shows only active processes)
<syntaxhighlight lang="bash">
iotop -o
iotop -o
</syntaxhighlight>
</syntaxhighlight>
The `-o` flag shows only processes with active I/O. Look for high I/O from `mysql` or `mysqld` processes, which may indicate slow storage or poorly tuned MySQL settings.


== 4. Recommended MySQL Performance Settings ==
High I/O from <code>mysqld</code> processes may indicate slow storage or poorly tuned MySQL settings.
 
== MySQL Performance Settings ==


For high-performance database operations (especially with partitioning enabled), ensure your MySQL configuration includes these critical settings:
For high-performance operation with partitioning:


<syntaxhighlight lang="bash">
<syntaxhighlight lang="ini">
[mysqld]
[mysqld]
# Use 50-70% of available RAM for caching
# Use 50-70% of available RAM for caching
Line 266: Line 300:
</syntaxhighlight>
</syntaxhighlight>


For comprehensive performance tuning guidelines, see the [[Scaling|Scaling and Performance guide]].
For comprehensive tuning, see [[Scaling|Scaling and Performance Guide]].
 
== See Also ==
 
* [[Sniffer_configuration|Sniffer Configuration Reference]]
* [[Scaling|Scaling and Performance Guide]]
* [[SQL_queue_is_growing_in_a_peaktime|SQL Queue Troubleshooting]]
* [[Sniffer_troubleshooting|Sniffer Troubleshooting]]


== AI Summary for RAG ==
== AI Summary for RAG ==
'''Summary:''' This article explains the two distinct data retention mechanisms in VoIPmonitor: filesystem cleaning for PCAP files and database cleaning for CDRs. For filesystem storage in the `spooldir`, it details the various `maxpoolsize` and `maxpooldays` configuration options that control the retention of SIP, RTP, and other files based on size or age. It also describes the `reindexfiles` manager command for troubleshooting cases where old files are not being deleted. For database retention, it explains the modern partitioning approach with `cleandatabase` parameters (day-based and size-based), partitioning configuration, and scheduling via `partition_operations_enable_fromto`. It includes troubleshooting guidance for disk full scenarios where SIP trace extraction fails for older calls, providing step-by-step diagnosis using `du -h --max-depth=1` to check disk usage and resolution by increasing `maxpoolsize` in voipmonitor.conf. It includes troubleshooting guidance for disk space not being reclaimed due to `innodb_file_per_table` settings, with solutions including OPTIMIZE TABLE and database export/import. It also provides monitoring guidance using SQLq/SQLf metrics from health charts and system monitoring tools like `iotop` and `cat /proc/loadavg`, and recommends key MySQL performance settings.
'''Summary:''' VoIPmonitor has two independent data retention mechanisms: (1) Filesystem cleaning for PCAP files using <code>maxpoolsize</code>/<code>maxpooldays</code> parameters, running every 5 minutes to delete oldest data first; (2) Database cleaning using <code>cleandatabase</code> parameter with daily partitioning for instant partition drops. Troubleshooting covers disk full scenarios (check with <code>du -h --max-depth=1</code>, increase <code>maxpoolsize</code>), space not reclaimed issues (<code>innodb_file_per_table</code> setting), and database health monitoring (SQLq/SQLf metrics).
'''Keywords:''' data retention, cleaning, delete old calls, purge data, disk space, spooldir, maxpoolsize, maxpooldays, pcap, filesystem, database, cdr, cleandatabase, cleandatabase_size, partitioning, reindexfiles, manager api, innodb_file_per_table, SQLq, SQLf, monitoring, iotop, database health, mysql performance, disk full, increase maxpoolsize, du command, check disk usage, spooldir size, SIP trace extraction fails, old calls missing
 
'''Keywords:''' data retention, cleaning, delete old calls, disk space, spooldir, maxpoolsize, maxpooldays, cleandatabase, partitioning, reindexfiles, innodb_file_per_table, SQLq, SQLf
 
'''Key Questions:'''
'''Key Questions:'''
* How do I automatically delete old PCAP files to free up disk space?
* How do I automatically delete old PCAP files?
* What is the difference between `maxpoolsize` and `maxpooldays`?
* What is the difference between maxpoolsize and maxpooldays?
* My spool directory is full, but old files are not being deleted. How do I fix it?
* My spool directory is full but old files are not deleted - how to fix?
* How do I automatically delete old CDRs from the database?
* How do I configure database retention with cleandatabase?
* What is the `cleandatabase` option and how does it work?
* Why is disk space not reclaimed after MySQL cleanup?
* Why is database partitioning important for VoIPmonitor?
* What do SQLq and SQLf metrics mean?
* What is `cleandatabase_size` and how does it differ from `cleandatabase`?
* Disk space is not reclaimed after cleanup. What should i check?
* What do SQLq and SQLf metrics mean in the health status?
* How do I monitor database health and performance?
* Why does MySQL not free disk space after dropping partitions?
* SIP trace extraction fails for calls older than a specific date, how do I fix it?
* How do I check disk usage of the VoIPmonitor spooldir?
* What command shows disk usage by subdirectory in spooldir?
* How do I increase the spooldir size in VoIPmonitor?
* What should I do if PCAP files are disappearing too quickly?

Revision as of 19:04, 4 January 2026

This guide explains how VoIPmonitor manages data retention for both captured packets (PCAP files) and Call Detail Records (CDRs) in the database. Proper configuration is essential for managing disk space and maintaining long-term database performance.

Overview

VoIPmonitor generates two primary types of data that require periodic cleaning:

  • PCAP Files: Raw packet captures of SIP/RTP/GRAPH data stored on the filesystem in the spool directory. These can consume significant disk space.
  • CDR Data: Call metadata stored in the MySQL database. Large tables can slow down GUI performance if not managed properly.

The system uses two separate, independent mechanisms to manage the retention of this data:

Filesystem Cleaning (PCAP Spool Directory)

The sensor stores captured call data in a structured directory tree on the local filesystem.

Spool Directory Location

By default, all data is stored in /var/spool/voipmonitor. This location can be changed by setting the spooldir option in voipmonitor.conf.

Retention Configuration

The cleaning process runs automatically every 5 minutes and removes the oldest data based on the rules you define in voipmonitor.conf. You can set limits based on total size (in Megabytes) or age (in days). If both a size and day limit are set for the same data type, the first limit that is reached will trigger the cleaning.

Parameter Default Value Description
maxpoolsize 102400 (100 GB) The total maximum disk space for all captured data (SIP, RTP, GRAPH, AUDIO).
maxpooldays (unset) The maximum number of days to keep all captured data.
maxpoolsipsize (unset) A specific size limit for SIP PCAP files only.
maxpoolsipdays (unset) A specific age limit for SIP PCAP files only.
maxpoolrtpsize (unset) A specific size limit for RTP PCAP files only.
maxpoolrtpdays (unset) A specific age limit for RTP PCAP files only.
maxpoolgraphsize (unset) A specific size limit for GRAPH files only.
maxpoolgraphdays (unset) A specific age limit for GRAPH files only.
maxpoolaudiosize (unset) A specific size limit for converted audio files (WAV/OGG) only.
maxpoolaudiodays (unset) An age limit for converted audio files (WAV/OGG) only.

Troubleshooting: Disk Full / Files Disappearing

If you see errors when attempting to extract older calls from the GUI, or if call files are disappearing too quickly, your spool directory may have reached its size limit.

Diagnosis: Check Disk Usage

  1. Identify the sensor/probe responsible for the missing data.
  2. SSH into the sensor/probe and navigate to the spooldir.
  3. Check the disk usage:
cd /var/spool/voipmonitor
du -h --max-depth=1 ./

# Example output:
# 150G    ./2025-01
# 120G    ./2024-12
# 90G     ./2024-11
# 360G    .
  1. Compare with the configured limit:
grep maxpoolsize /etc/voipmonitor.conf
# Example output: maxpoolsize = 102400  (100 GB in MB)

Resolution: Increase Spooldir Size

If the actual usage exceeds the configured limit, increase maxpoolsize: 

# Edit /etc/voipmonitor.conf
[general]
maxpoolsize = 716800   # 700 GB in MB
maxpooldays = 90       # Optional: Keep data for last 90 days

Apply changes: 

systemctl restart voipmonitor

Maintenance: Re-indexing the Spool Directory

VoIPmonitor maintains an index of all created PCAP files to perform cleaning efficiently without scanning the entire directory tree. If this index becomes corrupt, or if you manually move files into the spool, old data may not be deleted correctly.

To trigger a manual re-index via the manager API: 

# Open a manager API session
echo 'manager_file start /tmp/vmsck' | nc 127.0.0.1 5029

# Send the re-index command
echo reindexfiles | nc -U /tmp/vmsck

Note: This command requires netcat with support for UNIX sockets (-U). For alternative methods, see the Manager API documentation.

Database Cleaning (CDR Retention)

Managing the size of the cdr table and other large tables is critical for GUI performance.

Partitioning Method (Recommended)

Since version 7, VoIPmonitor utilizes database partitioning, which splits large tables into smaller, daily segments. This is the recommended method for managing database retention.

Aspect Description
How it works Set cleandatabase = 30 in voipmonitor.conf to keep the last 30 days of data.
Why it's better Dropping old partitions is instantaneous (milliseconds), regardless of row count. Zero database load.
Requirement Partitioning is enabled by default on new installations.

Quick Start: Global Retention

For most deployments, configure one parameter in /etc/voipmonitor.conf: 

# Keep all records for 30 days
cleandatabase = 30

The cleandatabase parameter acts as a global default for all cleandatabase_* options and applies to:

  • cdr - Call Detail Records
  • message - SIP MESSAGE texts
  • sip_msg - SIP OPTIONS/SUBSCRIBE/NOTIFY messages
  • register_state - SIP registration states
  • register_failed - Failed registration attempts

Retention Parameters

Parameter Default Description
cleandatabase 0 (disabled) Master retention setting in days.
cleandatabase_cdr 0 Specific retention for cdr and message tables.
cleandatabase_rtp_stat 2 Retention for detailed RTP statistics.
cleandatabase_sip_msg 0 Retention for OPTIONS/SUBSCRIBE/NOTIFY.
cleandatabase_size (unset) Alternative: size-based limit in MB (requires version 2024.05.1+).
partition_operations_enable_fromto 1-5 Time window for partition operations (e.g., 1-5 AM).

More details: Sniffer Configuration - Database Cleaning.

Legacy Method: Manual Deletion (Not Recommended)

For very old, non-partitioned databases, you would need custom scripts with DELETE FROM cdr WHERE calldate < ... queries.

Warning: Manual DELETE on large tables is extremely slow and resource-intensive. A single operation on millions of rows can take hours and impact GUI performance.

Troubleshooting Disk Space Issues

Disk Space Not Reclaimed After Cleanup

If automatic cleanup runs but disk space is not freed from the MySQL data directory, check the innodb_file_per_table setting: 

SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';
Value Behavior
ON Each table/partition has its own .ibd file. Dropping partitions reclaims space immediately.
OFF All data in shared ibdata1 file. Dropping partitions does not reduce file size.

Solutions

Option 1
Enable for Future Tables

Add to /etc/my.cnf or /etc/mysql/my.cnf: 

[mysqld]
innodb_file_per_table = 1

systemctl restart mysql

Note: This only affects NEW tables/partitions. Existing data in ibdata1 remains.

Option 2
Reclaim Space from Existing Tables
OPTIMIZE TABLE cdr;

Warning: Requires significant free disk space to duplicate table data. May crash if disk is nearly full.

Option 3
Export and Re-import
mysqldump -u root -p voipmonitor > voipmonitor_backup.sql
mysql -u root -p -e "DROP DATABASE voipmonitor; CREATE DATABASE voipmonitor;"
mysql -u root -p voipmonitor < voipmonitor_backup.sql

Monitoring Database Health

SQL Queue Metrics

The sensor tracks queue metrics visible in GUI → Settings → Sensors → Status:

Metric Description Healthy Range
SQLq CDRs waiting to be written to database Near 0, sporadic spikes OK
SQLf Failed database write attempts Zero (not growing)
  • Consistently high/growing SQLq → database cannot keep up
  • Non-zero/growing SQLf → database errors or connectivity issues

See SQL Queue Troubleshooting for details.

System Monitoring

# Check system load
cat /proc/loadavg

# Monitor disk I/O (shows only active processes)
iotop -o

High I/O from mysqld processes may indicate slow storage or poorly tuned MySQL settings.

MySQL Performance Settings

For high-performance operation with partitioning: 

[mysqld]
# Use 50-70% of available RAM for caching
innodb_buffer_pool_size = 4G

# Flush logs to OS every second (faster, safe for VoIPmonitor)
innodb_flush_log_at_trx_commit = 2

# Enable per-table filespace for easy space reclamation
innodb_file_per_table = 1

For comprehensive tuning, see Scaling and Performance Guide.

See Also

AI Summary for RAG

Summary: VoIPmonitor has two independent data retention mechanisms: (1) Filesystem cleaning for PCAP files using maxpoolsize/maxpooldays parameters, running every 5 minutes to delete oldest data first; (2) Database cleaning using cleandatabase parameter with daily partitioning for instant partition drops. Troubleshooting covers disk full scenarios (check with du -h --max-depth=1, increase maxpoolsize), space not reclaimed issues (innodb_file_per_table setting), and database health monitoring (SQLq/SQLf metrics).

Keywords: data retention, cleaning, delete old calls, disk space, spooldir, maxpoolsize, maxpooldays, cleandatabase, partitioning, reindexfiles, innodb_file_per_table, SQLq, SQLf

Key Questions:

  • How do I automatically delete old PCAP files?
  • What is the difference between maxpoolsize and maxpooldays?
  • My spool directory is full but old files are not deleted - how to fix?
  • How do I configure database retention with cleandatabase?
  • Why is disk space not reclaimed after MySQL cleanup?
  • What do SQLq and SQLf metrics mean?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Data_Cleaning&oldid=6562"