Data Cleaning: Difference between revisions

From VoIPmonitor.org
(Add missing filesystem params: maxpoolaudiographsize, secondary storage (_2), cleanspool, cleanspool_enable_fromto, all_unlink_log)
(Fix incorrect association of _2 parameters with tar_move - they are for spooldir_2 used with capture rules (TP-38))
 
(3 intermediate revisions by the same user not shown)
Line 1: Line 1:
{{DISPLAYTITLE:Data Cleaning and Retention}}
[[Category:Configuration]]
[[Category:Administration]]
This guide explains how VoIPmonitor manages data retention for PCAP files and database records.
This guide explains how VoIPmonitor manages data retention for PCAP files and database records.


Line 9: Line 13:
! Data Type !! Storage !! Cleanup Mechanism !! Key Parameters
! Data Type !! Storage !! Cleanup Mechanism !! Key Parameters
|-
|-
| '''PCAP Files''' || Filesystem (spool directory) || Cleanspool process || <code>maxpoolsize</code>, <code>maxpooldays</code>
| '''PCAP Files''' || Filesystem (spool directory) || Cleanspool process (every 5 min) || <code>maxpoolsize</code>, <code>maxpooldays</code>
|-
|-
| '''CDR Records''' || MySQL database || Partition dropping || <code>cleandatabase</code>
| '''CDR Records''' || MySQL database || Partition dropping (instant) || <code>cleandatabase</code>
|}
|}


These are '''independent systems''' - filesystem cleanup does not affect database records and vice versa.
{{Note|These are '''independent systems''' - filesystem cleanup does not affect database records and vice versa.}}
 
=== Quick Reference ===
 
{| class="wikitable"
|-
! Purpose !! Parameter !! Example
|-
| Limit total PCAP size || <code>maxpoolsize</code> || <code>maxpoolsize = 512000</code> (500 GB)
|-
| Limit RTP specifically || <code>maxpoolrtpsize</code> || <code>maxpoolrtpsize = 102400</code> (100 GB)
|-
| Limit PCAP age || <code>maxpooldays</code> || <code>maxpooldays = 30</code>
|-
| CDR retention (days) || <code>cleandatabase</code> || <code>cleandatabase = 30</code>
|-
| CDR retention (size) || <code>cleandatabase_size</code> || <code>cleandatabase_size = 512000</code> (+ <code>cleandatabase_size_force = true</code>)
|-
| Save only RTP stats || <code>savertp</code> || <code>savertp = header</code> (saves ~90% space)
|}


== Filesystem Cleaning (PCAP Files) ==
== Filesystem Cleaning (PCAP Files) ==
Line 20: Line 43:
=== How Cleanspool Works ===
=== How Cleanspool Works ===


The sniffer maintains a complete file index '''in memory''' during operation. Every 5 minutes, the cleanspool thread checks retention limits and deletes the oldest files when limits are exceeded.
The sniffer maintains a file index '''in memory'''. Every 5 minutes, the cleanspool thread checks retention limits and deletes the oldest files when limits are exceeded.
 
The <code>.cleanspool_cache</code> files in hourly directories (e.g., <code>/var/spool/voipmonitor/2026-01-01/15/.cleanspool_cache</code>) serve as '''persistent storage for fast restart''' - they allow quick index reload without scanning the entire directory structure.


<kroki lang="plantuml">
<kroki lang="plantuml">
@startuml
@startuml
skinparam shadowing false
skinparam shadowing false
skinparam defaultFontSize 11
participant "Sniffer" as S
participant "Sniffer" as S
participant "Cleanspool\nThread" as C
participant "Cleanspool" as C
database "In-Memory\nFile Index" as MEM
database "In-Memory\nIndex" as MEM
collections "Filesystem" as FS
collections "Filesystem" as FS


S -> FS: Write PCAP file
S -> FS: Write PCAP
S -> MEM: Update index
S -> MEM: Update index
... Every 5 minutes ...
... Every 5 minutes ...
 
C -> MEM: Find oldest files\nexceeding limits
C -> MEM: Find oldest files exceeding limits
loop Delete old files
loop For each file to delete
     C -> FS: DELETE
     C -> FS: DELETE file
     C -> MEM: Remove entry
     C -> MEM: Remove from index
end
end
 
note over MEM : Persisted to\n.cleanspool_cache
note over MEM : Index persisted to\n.cleanspool_cache\nfor fast restart
@enduml
@enduml
</kroki>
</kroki>


{{Note|1='''Legacy Indexing Method:''' Older VoIPmonitor versions used a different indexing method where file metadata was stored in the MySQL <code>files</code> table instead of in-memory. This legacy behavior can be enabled with <code>cleanspool_use_files = yes</code> (the parameter name refers to the SQL <code>files</code> table, not the <code>.cleanspool_cache</code> files). The default is <code>cleanspool_use_files = no</code> which uses the current in-memory indexing. The legacy method is deprecated and should not be used.}}
The <code>.cleanspool_cache</code> files in hourly directories serve as '''persistent storage for fast restart''' - they allow quick index reload without scanning the entire filesystem.
 
{{Note|1='''Legacy Indexing:''' Old VoIPmonitor versions stored metadata in MySQL <code>files</code> table. This can be enabled with <code>cleanspool_use_files = yes</code> (deprecated, do not use).}}


=== Retention Configuration ===
=== Retention Configuration ===


Retention limits can be set by '''size''' (MB) or '''age''' (days). When both are configured, the first limit reached triggers cleanup.
Limits can be set by '''size''' (MB) or '''age''' (days). When both are configured, the first limit reached triggers cleanup.


==== Global Limits ====
==== Global Limits ====
Line 65: Line 86:


==== Per-Type Limits ====
==== Per-Type Limits ====
You can set different retention for each data type:


{| class="wikitable"
{| class="wikitable"
Line 79: Line 98:
|-
|-
| Converted audio (WAV/OGG) || <code>maxpoolaudiosize</code> || <code>maxpoolaudiodays</code>
| Converted audio (WAV/OGG) || <code>maxpoolaudiosize</code> || <code>maxpoolaudiodays</code>
|-
| Audio + graphs combined || <code>maxpoolaudiographsize</code> || <code>maxpoolaudiographdays</code>
|}
==== Secondary Storage Limits ====
When using <code>tar_move</code> for tiered storage, separate limits apply to the secondary storage:
{| class="wikitable"
|-
! Parameter !! Description
|-
| <code>maxpoolsize_2</code> || Total size limit for secondary storage (default: 102400 MB)
|-
| <code>maxpooldays_2</code> || Age limit for secondary storage
|-
| <code>maxpoolsipsize_2</code>, <code>maxpoolrtpsize_2</code>, etc. || Per-type limits for secondary storage
|}
|}


==== Recommended Configuration ====
==== Recommended Configuration ====


For most deployments, limit RTP (which consumes most space) while keeping SIP longer:
Limit RTP (largest files) while keeping SIP longer for troubleshooting:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
# Limit RTP to 100 GB (deleted when exceeded)
# /etc/voipmonitor.conf
maxpoolrtpsize = 102400
maxpoolrtpsize = 102400   # 100 GB limit for RTP
 
maxpoolsize = 512000     # 500 GB overall limit
# Overall limit for all data
maxpoolsize = 512000
</syntaxhighlight>
</syntaxhighlight>


This keeps SIP signaling (small files, useful for troubleshooting) as long as overall space allows, while limiting large RTP files.
==== Secondary Storage Limits (spooldir_2) ====


==== Verifying Active Cleanup Rules ====
When using <code>spooldir_2</code> for secondary storage (configured via [[Capture_rules|capture rules]] with "Store pcaps to second spooldir" enabled), use <code>_2</code> suffix parameters for cleaning:


Check which rule triggered cleanup:
<code>maxpoolsize_2</code>, <code>maxpooldays_2</code>, <code>maxpoolrtpsize_2</code>, etc.


<syntaxhighlight lang="bash">
{{Note|1=<code>spooldir_2</code> is '''independent''' from <code>tar_move</code>. The <code>_2</code> suffix parameters apply only to the secondary spooldir configured via capture rules, not to <code>tar_move</code> destination storage.}}
journalctl -u voipmonitor | grep -i clean
</syntaxhighlight>


Log messages indicate: <code>clean_maxpoolsize</code>, <code>clean_maxpooldays</code>, <code>clean_maxpoolrtpdays</code>, etc.
=== Emergency Cleanup ===


=== Cleanspool Control Parameters ===
{{Warning|1=Emergency cleanup activates when disk is nearly full and '''ignores all <code>maxpool*</code> settings'''.}}


{| class="wikitable"
{| class="wikitable"
|-
|-
! Parameter !! Default !! Description
! Parameter !! Default !! Triggers When
|-
|-
| <code>cleanspool</code> || yes || Enable/disable spool directory cleaning
| <code>autocleanspoolminpercent</code> || 1 || Disk usage reaches 99%
|-
|-
| <code>cleanspool_enable_fromto</code> || 0-24 || Restrict cleaning to specific hours (e.g., <code>1-5</code> for 1-5 AM)
| <code>autocleanmingb</code> || 5 || Free space below 5 GB
|-
| <code>maxpool_clean_obsolete</code> || no || Delete files not in the index (use with caution)
|-
| <code>all_unlink_log</code> || no || Log all file deletion operations
|}
|}


=== Emergency Cleanup ===
When triggered, oldest data is deleted aggressively until thresholds are cleared. The <code>cleanspool_enable_fromto</code> time window is ignored.


Emergency cleanup acts as a safety mechanism when disk is nearly full:
=== Control Parameters ===


{| class="wikitable"
{| class="wikitable"
|-
|-
! Parameter !! Default !! Triggers When
! Parameter !! Default !! Description
|-
| <code>cleanspool</code> || yes || Enable/disable spool cleaning
|-
| <code>cleanspool_enable_fromto</code> || 0-24 || Restrict cleaning to hours (e.g., <code>1-5</code> for 1-5 AM)
|-
|-
| <code>autocleanspoolminpercent</code> || 1 || Disk usage reaches 99%
| <code>maxpool_clean_obsolete</code> || no || Delete files not in index (use with caution)
|-
|-
| <code>autocleanmingb</code> || 5 || Free space below 5 GB
| <code>all_unlink_log</code> || no || Log all file deletions
|}
|}
When triggered, oldest data is deleted aggressively '''regardless of <code>maxpool*</code> settings''' until thresholds are cleared. The <code>cleanspool_enable_fromto</code> restriction is ignored during emergency cleanup.


=== Reducing Data at Source ===
=== Reducing Data at Source ===
Before tuning retention, consider reducing data volume:


==== Save RTP Headers Only ====
==== Save RTP Headers Only ====


If you only need call quality statistics (MOS, jitter, packet loss) without audio playback:
If you only need quality metrics (MOS, jitter, packet loss) without audio playback:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
Line 164: Line 158:
</syntaxhighlight>
</syntaxhighlight>


This reduces storage by up to 90% while preserving all quality metrics.
This reduces storage by up to '''90%''' while preserving all quality statistics.
 


==== Selective Audio Recording ====


To record full audio only for specific calls (legal holds, VIP customers):
==== Disable RTP Entirely ====


# Set global default to headers only: <code>savertp = header</code>
If SIP signaling retention is the priority and you do not need RTP data at all:
# Create capture rules in GUI (Control Panel > Capture Rules) with <code>recordRTP=ON</code> for exceptions


See [[Capture_rules]] for details.
<syntaxhighlight lang="ini">
savertp = no
</syntaxhighlight>
 
This disables RTP packet storage entirely, reducing PCAP size by approximately '''20x'''. Use this when:
* Storage limits are insufficient for even header-only RTP
* SIP troubleshooting is the primary need (no audio analysis required)
* You need to retain SIP signaling for much longer periods


=== The maxpool_clean_obsolete Parameter ===
{{Warning|Setting <code>savertp = no</code> removes all RTP data including quality statistics. Use <code>savertp = header</code> instead if you need MOS, jitter, or packet loss metrics.}}
==== Selective Recording ====


Controls handling of files not in the index:
To record full audio only for specific calls:
# Set <code>savertp = header</code> globally
# Create capture rules with <code>recordRTP=ON</code> for exceptions


{| class="wikitable"
See [[Capture_rules]] for details.
|-
! Setting !! Behavior
|-
| <code>maxpool_clean_obsolete = no</code> (default) || Only delete indexed files. Unknown files are preserved.
|-
| <code>maxpool_clean_obsolete = yes</code> || Delete ALL files in spool, including unindexed ones.
|}


== Database Cleaning (CDR Records) ==
== Database Cleaning (CDR Records) ==
Line 192: Line 188:
=== Partitioning Method ===
=== Partitioning Method ===


VoIPmonitor uses daily partitioning for database tables. Dropping old partitions is instant (milliseconds) regardless of row count.
VoIPmonitor uses daily partitioning. Dropping old partitions is instant (milliseconds) regardless of row count.


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
Line 198: Line 194:
cleandatabase = 30
cleandatabase = 30
</syntaxhighlight>
</syntaxhighlight>
=== Database Cleaning Parameters ===


{| class="wikitable"
{| class="wikitable"
Line 203: Line 201:
! Parameter !! Default !! Description
! Parameter !! Default !! Description
|-
|-
| <code>cleandatabase</code> || 0 (disabled) || Global retention in days for CDR, register_state, register_failed, sip_msg
| <code>cleandatabase</code> || 0 (disabled) || Global retention for CDR, register_state, register_failed, sip_msg
|-
| <code>cleandatabase_cdr</code> || 0 || CDR table retention (includes <code>message</code> table for SIP MESSAGE)
|-
|-
| <code>cleandatabase_rtp_stat</code> || 2 || RTP statistics table retention
| <code>cleandatabase_cdr</code> || 0 || CDR table (includes <code>message</code> table)
|-
|-
| <code>cleandatabase_register_state</code> || 0 || Registration state retention
| <code>cleandatabase_rtp_stat</code> || 2 || RTP statistics table
|-
|-
| <code>cleandatabase_register_failed</code> || 0 || Failed registration retention
| <code>cleandatabase_register_state</code> || 0 || Registration state
|-
|-
| <code>cleandatabase_register_time_info</code> || 0 || Registration timing (must be set explicitly - NOT covered by global)
| <code>cleandatabase_register_failed</code> || 0 || Failed registrations
|-
|-
| <code>cleandatabase_sip_msg</code> || 0 || SIP messages (OPTIONS/SUBSCRIBE/NOTIFY) retention
| <code>cleandatabase_register_time_info</code> || 0 || Registration timing ('''NOT''' covered by global)
|-
|-
| <code>cleandatabase_cdr_rtp_energylevels</code> || 0 || RTP energy levels table retention
| <code>cleandatabase_sip_msg</code> || 0 || SIP messages (OPTIONS/SUBSCRIBE/NOTIFY)
|-
|-
| <code>cleandatabase_ss7</code> || 0 || SS7 table retention
| <code>cleandatabase_ss7</code> || 0 || SS7 records
|-
|-
| <code>partition_operations_enable_fromto</code> || 1-5 || Time window for partition operations (24h format)
| <code>partition_operations_enable_fromto</code> || 1-5 || Time window for partition operations (24h format)
|-
| <code>partition_operations_in_thread</code> || no || Run partition operations in separate thread (expert option)
|}
|}


{{Warning|1=The <code>register_time_info</code> table is NOT covered by global <code>cleandatabase</code>. You must set <code>cleandatabase_register_time_info</code> explicitly to enable cleanup.}}
{{Warning|1=<code>register_time_info</code> is NOT covered by global <code>cleandatabase</code>. Set <code>cleandatabase_register_time_info</code> explicitly.}}


=== Size-Based Database Cleaning ===
=== Size-Based Cleaning ===


To limit database by size instead of time:
To limit database by size instead of time:
Line 236: Line 230:
cleandatabase_size_force = true    # Required to enable
cleandatabase_size_force = true    # Required to enable
</syntaxhighlight>
</syntaxhighlight>
=== Limits ===
* '''Partition limit:''' ~8000 partitions per table (~22 years with daily partitioning)
* '''CDR record limit:''' No practical limit - uses BIGINT (18 quintillion records). See [[Upgrade_to_bigint]].


=== Multi-Sensor Environments ===
=== Multi-Sensor Environments ===


When multiple sensors share a database, only ONE sensor should manage partitions:
When multiple sensors share a database, only '''ONE''' sensor should manage partitions:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
Line 253: Line 242:
partition_operations_enable_fromto = 4-6
partition_operations_enable_fromto = 4-6
</syntaxhighlight>
</syntaxhighlight>
=== Limits ===
* '''MySQL/MariaDB partition limit:''' ~8000 partitions per table (~22 years with daily partitioning)
* '''Aurora DB partition limit:''' ~800 partitions per table - much lower than standard MySQL
* '''CDR record limit:''' No practical limit - uses BIGINT
=== Aurora DB / Limited Partition Environments ===
For databases with low partition limits (like Amazon Aurora), you can pre-create partitions before starting the sniffer and prevent runtime partition creation:
<syntaxhighlight lang="ini">
# Pre-create partitions up to a specific date before starting
create_new_partitions_until = 2026-01-31
# Disable only partition creation (dropping still works for cleanup)
disable_partition_operations_create = yes
# Or disable only partition dropping (creation still works)
# disable_partition_operations_drop = yes
# Number of partitions to create ahead (default 2)
create_new_partitions = 2
</syntaxhighlight>
{{Note|1=For Aurora DB with ~800 partition limit: with daily partitions, this gives ~2 years of data. Plan retention accordingly using <code>cleandatabase</code>.}}


== Advanced Topics ==
== Advanced Topics ==
Line 258: Line 273:
=== Spool Directory Location ===
=== Spool Directory Location ===


Default location: <code>/var/spool/voipmonitor</code>
Default: <code>/var/spool/voipmonitor</code>
 
Directory structure: <code>YYYY-MM-DD/HH/MM/{SIP|RTP|GRAPH|AUDIO}/files...</code>


==== Relocating the Spool Directory ====
Structure: <code>YYYY-MM-DD/HH/MM/{SIP|RTP|GRAPH|AUDIO}/files...</code>


To move spool to a larger partition:
==== Relocating the Spool ====


;Step 1: Create new directory
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
# 1. Create new directory
mkdir -p /mnt/storage/voipmonitor
mkdir -p /mnt/storage/voipmonitor
chown voipmonitor:voipmonitor /mnt/storage/voipmonitor
chown voipmonitor:voipmonitor /mnt/storage/voipmonitor
</syntaxhighlight>


;Step 2: Update sniffer configuration
# 2. Update sniffer config
<syntaxhighlight lang="ini">
# /etc/voipmonitor.conf:
# /etc/voipmonitor.conf
# spooldir = /mnt/storage/voipmonitor
spooldir = /mnt/storage/voipmonitor
</syntaxhighlight>


;Step 3: Update GUI configuration
# 3. Update GUI config (config/configuration.php):
Edit GUI config file and set <code>SNIFFER_DATA_PATH</code> to match:
# define('SNIFFER_DATA_PATH', '/mnt/storage/voipmonitor');
<syntaxhighlight lang="php">
define('SNIFFER_DATA_PATH', '/mnt/storage/voipmonitor');
</syntaxhighlight>


;Step 4: Restart services
# 4. Restart
<syntaxhighlight lang="bash">
systemctl restart voipmonitor
systemctl restart voipmonitor
</syntaxhighlight>
</syntaxhighlight>
Line 291: Line 297:
=== Tiered Storage (tar_move) ===
=== Tiered Storage (tar_move) ===


To extend retention using secondary storage:
Extend retention using secondary storage:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
# Local fast storage for live capture
spooldir = /var/spool/voipmonitor
spooldir = /var/spool/voipmonitor
# Archive to secondary storage
tar_move = yes
tar_move = yes
tar_move_destination_path = /mnt/archive/voipmonitor
tar_move_destination_path = /mnt/archive/voipmonitor
</syntaxhighlight>
</syntaxhighlight>


Files in <code>tar_move_destination_path</code> remain accessible via GUI.
Files in secondary storage remain accessible via GUI.


==== S3 Cloud Storage ====
==== S3 Cloud Storage ====
Line 315: Line 318:
=== Custom Autocleaning (GUI) ===
=== Custom Autocleaning (GUI) ===


For one-time cleanup of specific recordings (e.g., by IP address):
For one-time cleanup of specific recordings (by IP, phone number, etc.):
 
# Navigate to Settings > Custom Autocleaning
# Navigate to Settings > Custom Autocleaning
# Create rule with filters (IP, phone number, etc.)
# Create rule with filters
# Apply and remove rule after completion
# Apply and remove rule after completion
Useful for cleaning old data after configuring capture rules to stop future recording.


== Troubleshooting ==
== Troubleshooting ==
Line 327: Line 327:
=== Files Disappearing Faster Than Expected ===
=== Files Disappearing Faster Than Expected ===


Check these causes in order:
;1. Check if emergency cleanup is active
 
;1. Emergency cleanup triggered
<syntaxhighlight lang="bash">
<syntaxhighlight lang="bash">
df -h /var/spool/voipmonitor
df -h /var/spool/voipmonitor
# If >95% full, emergency cleanup is active
# If >95% full, emergency cleanup is running
</syntaxhighlight>
</syntaxhighlight>


;2. GUI configuration override
;2. Check GUI configuration override
When <code>mysqlloadconfig = yes</code> (default), GUI settings override config file. Check: Settings > Sensors > wrench icon.
When <code>mysqlloadconfig = yes</code> (default), GUI settings override config file. Check: Settings > Sensors > wrench icon.


;3. Insufficient maxpoolsize
;3. Set appropriate limits
Set <code>maxpoolsize</code> to 90-95% of disk capacity to leave buffer for growth between cleanup cycles.
Set <code>maxpoolsize</code> to 90-95% of disk capacity to leave buffer for growth.


=== Disk Space Not Reclaimed After Database Cleanup ===
=== Disk Space Not Reclaimed After Database Cleanup ===
Line 349: Line 347:
</syntaxhighlight>
</syntaxhighlight>


If OFF, space is not reclaimed when partitions drop. Enable for future tables:
If OFF, enable in <code>my.cnf</code>:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
Line 358: Line 356:
=== MySQL Error 28: No Space Left ===
=== MySQL Error 28: No Space Left ===


Primary solution - enable size-based cleaning:
Enable size-based cleaning:


<syntaxhighlight lang="ini">
<syntaxhighlight lang="ini">
Line 365: Line 363:
</syntaxhighlight>
</syntaxhighlight>


Other causes:
Other causes: Inode exhaustion (<code>df -i</code>), MySQL tmpdir full.
* '''Inode exhaustion:''' Check with <code>df -i</code>
* '''MySQL tmpdir full:''' Check with <code>SHOW VARIABLES LIKE 'tmpdir'</code>


=== Database Not Cleaning ===
=== Verify Database Cleaning ===
 
Verify tables are partitioned:


<syntaxhighlight lang="sql">
<syntaxhighlight lang="sql">
SHOW CREATE TABLE cdr\G
SELECT PARTITION_NAME, TABLE_ROWS
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
FROM information_schema.PARTITIONS
Line 383: Line 375:
</syntaxhighlight>
</syntaxhighlight>


If only expected partitions exist (matching your <code>cleandatabase</code> setting), cleaning IS working - you may simply have high data volume.
If partition count matches your <code>cleandatabase</code> setting, cleaning IS working.


=== Spool Filling Due to Database Bottleneck ===
For SQL queue issues and database performance, see [[Database_troubleshooting]].


If spool fills rapidly after disk swap or MySQL upgrade:
== See Also ==
 
<syntaxhighlight lang="bash">
# Check SQL queue
tail -f /var/log/syslog | grep voipmonitor | grep SQLq
</syntaxhighlight>
 
Growing SQLq indicates database cannot keep up. Solution:


<syntaxhighlight lang="ini">
* [[Sniffer_configuration]] - Full parameter reference
[mysqld]
* [[Database_troubleshooting]] - SQL queue, performance issues
innodb_buffer_pool_size = 4G          # 50-70% of RAM
* [[Scaling]] - Performance tuning
innodb_flush_log_at_trx_commit = 2    # Faster writes
* [[Capture_rules]] - Selective recording
</syntaxhighlight>


== See Also ==


* [[Sniffer_configuration|Sniffer Configuration Reference]]
* [[Scaling|Scaling and Performance Guide]]
* [[SQL_queue_is_growing_in_a_peaktime|SQL Queue Troubleshooting]]


== AI Summary for RAG ==
== AI Summary for RAG ==


'''Summary:''' VoIPmonitor has two independent data retention systems: (1) Filesystem cleaning for PCAP files using <code>maxpoolsize</code>/<code>maxpooldays</code>, running every 5 minutes; (2) Database cleaning using <code>cleandatabase</code> with daily partitioning.
'''Summary:''' VoIPmonitor has two independent data retention systems: (1) Cleanspool for PCAP files using <code>maxpoolsize</code>/<code>maxpooldays</code> parameters, running every 5 minutes with in-memory file index; (2) Database cleaning using <code>cleandatabase</code> with instant partition dropping. Key space-saving option: <code>savertp = header</code> reduces storage by 90% while keeping quality metrics. Emergency cleanup (<code>autocleanspoolminpercent=1</code>, <code>autocleanmingb=5</code>) activates when disk nearly full and ignores normal limits. GUI settings override config file when <code>mysqlloadconfig=yes</code>. Size-based database cleaning requires BOTH <code>cleandatabase_size</code> AND <code>cleandatabase_size_force=true</code>. In multi-sensor environments, only one sensor should manage partitions (<code>disable_partition_operations=yes</code> on others).
 
'''Cleanspool:''' File index kept in memory for fast operations. <code>.cleanspool_cache</code> files in hourly directories (e.g., <code>/var/spool/voipmonitor/2026-01-01/15/.cleanspool_cache</code>) are for fast restart only. Legacy parameter <code>cleanspool_use_files = yes</code> enables deprecated MySQL <code>files</code> table indexing (the "files" in parameter name refers to SQL table, not cache files). Default is <code>cleanspool_use_files = no</code> (in-memory indexing).
 
'''Key parameters:''' <code>maxpoolsize</code> (default 100GB), <code>maxpoolrtpsize</code>/<code>maxpoolrtpdays</code> for RTP-specific limits, <code>cleandatabase</code> for CDR retention. Emergency cleanup via <code>autocleanspoolminpercent</code> (default 1%) and <code>autocleanmingb</code> (default 5GB) overrides settings when disk nearly full. GUI settings override config file when <code>mysqlloadconfig=yes</code>. Size-based database cleaning requires <code>cleandatabase_size</code> AND <code>cleandatabase_size_force=true</code>. Database partition limit ~8000 (~22 years); CDR uses BIGINT.


'''Keywords:''' data retention, maxpoolsize, maxpooldays, maxpoolrtpsize, cleandatabase, cleandatabase_size, .cleanspool_cache, cleanspool_use_files, files table, autocleanspoolminpercent, autocleanmingb, tar_move, innodb_file_per_table, savertp header, BIGINT
'''Keywords:''' data retention, maxpoolsize, maxpooldays, maxpoolrtpsize, cleandatabase, cleandatabase_size, cleandatabase_size_force, cleanspool, autocleanspoolminpercent, autocleanmingb, tar_move, tiered storage, savertp header, innodb_file_per_table, partition dropping, emergency cleanup, mysqlloadconfig, disable_partition_operations


'''Key Questions:'''
'''Key Questions:'''
* How does cleanspool work?
* How does VoIPmonitor cleanspool work?
* Where are .cleanspool_cache files?
* How to configure PCAP file retention?
* What is cleanspool_use_files?
* How to limit database size?
* Why are files deleted faster than expected?
* Why are files being deleted faster than expected?
* How to fix MySQL Error 28?
* How to fix MySQL Error 28 no space left?
* How to configure size-based database cleaning?
* How to reduce storage usage (savertp header)?
* How to extend retention with tiered storage?
* How to configure tiered storage?
* Why is disk space not reclaimed after cleanup?
* Why is disk space not reclaimed after cleanup?
* How to manage partitions in multi-sensor environment?

Latest revision as of 12:10, 22 January 2026


This guide explains how VoIPmonitor manages data retention for PCAP files and database records.

Overview

VoIPmonitor generates two types of data requiring periodic cleanup:

Data Type Storage Cleanup Mechanism Key Parameters
PCAP Files Filesystem (spool directory) Cleanspool process (every 5 min) maxpoolsize, maxpooldays
CDR Records MySQL database Partition dropping (instant) cleandatabase

ℹ️ Note: These are independent systems - filesystem cleanup does not affect database records and vice versa.

Quick Reference

Purpose Parameter Example
Limit total PCAP size maxpoolsize maxpoolsize = 512000 (500 GB)
Limit RTP specifically maxpoolrtpsize maxpoolrtpsize = 102400 (100 GB)
Limit PCAP age maxpooldays maxpooldays = 30
CDR retention (days) cleandatabase cleandatabase = 30
CDR retention (size) cleandatabase_size cleandatabase_size = 512000 (+ cleandatabase_size_force = true)
Save only RTP stats savertp savertp = header (saves ~90% space)

Filesystem Cleaning (PCAP Files)

How Cleanspool Works

The sniffer maintains a file index in memory. Every 5 minutes, the cleanspool thread checks retention limits and deletes the oldest files when limits are exceeded.

The .cleanspool_cache files in hourly directories serve as persistent storage for fast restart - they allow quick index reload without scanning the entire filesystem.

ℹ️ Note: Legacy Indexing: Old VoIPmonitor versions stored metadata in MySQL files table. This can be enabled with cleanspool_use_files = yes (deprecated, do not use).

Retention Configuration

Limits can be set by size (MB) or age (days). When both are configured, the first limit reached triggers cleanup.

Global Limits

Parameter Default Description
maxpoolsize 102400 (100 GB) Maximum total size for all PCAP data
maxpooldays (unset) Maximum age in days for all PCAP data

Per-Type Limits

Data Type Size Parameter Days Parameter
SIP signaling maxpoolsipsize maxpoolsipdays
RTP audio maxpoolrtpsize maxpoolrtpdays
Quality graphs maxpoolgraphsize maxpoolgraphdays
Converted audio (WAV/OGG) maxpoolaudiosize maxpoolaudiodays

Recommended Configuration

Limit RTP (largest files) while keeping SIP longer for troubleshooting: 

# /etc/voipmonitor.conf
maxpoolrtpsize = 102400   # 100 GB limit for RTP
maxpoolsize = 512000      # 500 GB overall limit

Secondary Storage Limits (spooldir_2)

When using spooldir_2 for secondary storage (configured via capture rules with "Store pcaps to second spooldir" enabled), use _2 suffix parameters for cleaning:

maxpoolsize_2, maxpooldays_2, maxpoolrtpsize_2, etc.

ℹ️ Note: spooldir_2 is independent from tar_move. The _2 suffix parameters apply only to the secondary spooldir configured via capture rules, not to tar_move destination storage.

Emergency Cleanup

⚠️ Warning: Emergency cleanup activates when disk is nearly full and ignores all maxpool* settings.

Parameter Default Triggers When
autocleanspoolminpercent 1 Disk usage reaches 99%
autocleanmingb 5 Free space below 5 GB

When triggered, oldest data is deleted aggressively until thresholds are cleared. The cleanspool_enable_fromto time window is ignored.

Control Parameters

Parameter Default Description
cleanspool yes Enable/disable spool cleaning
cleanspool_enable_fromto 0-24 Restrict cleaning to hours (e.g., 1-5 for 1-5 AM)
maxpool_clean_obsolete no Delete files not in index (use with caution)
all_unlink_log no Log all file deletions

Reducing Data at Source

Save RTP Headers Only

If you only need quality metrics (MOS, jitter, packet loss) without audio playback: 

savertp = header

This reduces storage by up to 90% while preserving all quality statistics.


Disable RTP Entirely

If SIP signaling retention is the priority and you do not need RTP data at all: 

savertp = no

This disables RTP packet storage entirely, reducing PCAP size by approximately 20x. Use this when:

  • Storage limits are insufficient for even header-only RTP
  • SIP troubleshooting is the primary need (no audio analysis required)
  • You need to retain SIP signaling for much longer periods

⚠️ Warning:

Selective Recording

To record full audio only for specific calls:

  1. Set savertp = header globally
  2. Create capture rules with recordRTP=ON for exceptions

See Capture_rules for details.

Database Cleaning (CDR Records)

Partitioning Method

VoIPmonitor uses daily partitioning. Dropping old partitions is instant (milliseconds) regardless of row count. 

# Keep CDR records for 30 days
cleandatabase = 30

Database Cleaning Parameters

Parameter Default Description
cleandatabase 0 (disabled) Global retention for CDR, register_state, register_failed, sip_msg
cleandatabase_cdr 0 CDR table (includes message table)
cleandatabase_rtp_stat 2 RTP statistics table
cleandatabase_register_state 0 Registration state
cleandatabase_register_failed 0 Failed registrations
cleandatabase_register_time_info 0 Registration timing (NOT covered by global)
cleandatabase_sip_msg 0 SIP messages (OPTIONS/SUBSCRIBE/NOTIFY)
cleandatabase_ss7 0 SS7 records
partition_operations_enable_fromto 1-5 Time window for partition operations (24h format)

⚠️ Warning: register_time_info is NOT covered by global cleandatabase. Set cleandatabase_register_time_info explicitly.

Size-Based Cleaning

To limit database by size instead of time: 

cleandatabase_size = 512000        # 500 GB limit in MB
cleandatabase_size_force = true    # Required to enable

Multi-Sensor Environments

When multiple sensors share a database, only ONE sensor should manage partitions: 

# On all sensors EXCEPT one:
disable_partition_operations = yes

# On the designated sensor:
partition_operations_enable_fromto = 4-6

Limits

  • MySQL/MariaDB partition limit: ~8000 partitions per table (~22 years with daily partitioning)
  • Aurora DB partition limit: ~800 partitions per table - much lower than standard MySQL
  • CDR record limit: No practical limit - uses BIGINT

Aurora DB / Limited Partition Environments

For databases with low partition limits (like Amazon Aurora), you can pre-create partitions before starting the sniffer and prevent runtime partition creation: 

# Pre-create partitions up to a specific date before starting
create_new_partitions_until = 2026-01-31

# Disable only partition creation (dropping still works for cleanup)
disable_partition_operations_create = yes

# Or disable only partition dropping (creation still works)
# disable_partition_operations_drop = yes

# Number of partitions to create ahead (default 2)
create_new_partitions = 2

ℹ️ Note: For Aurora DB with ~800 partition limit: with daily partitions, this gives ~2 years of data. Plan retention accordingly using cleandatabase.

Advanced Topics

Spool Directory Location

Default: /var/spool/voipmonitor

Structure: YYYY-MM-DD/HH/MM/{SIP|RTP|GRAPH|AUDIO}/files...

Relocating the Spool

# 1. Create new directory
mkdir -p /mnt/storage/voipmonitor
chown voipmonitor:voipmonitor /mnt/storage/voipmonitor

# 2. Update sniffer config
# /etc/voipmonitor.conf:
# spooldir = /mnt/storage/voipmonitor

# 3. Update GUI config (config/configuration.php):
# define('SNIFFER_DATA_PATH', '/mnt/storage/voipmonitor');

# 4. Restart
systemctl restart voipmonitor

Tiered Storage (tar_move)

Extend retention using secondary storage: 

spooldir = /var/spool/voipmonitor
tar_move = yes
tar_move_destination_path = /mnt/archive/voipmonitor

Files in secondary storage remain accessible via GUI.

S3 Cloud Storage

Use rclone instead of s3fs to avoid GUI unresponsiveness: 

rclone mount bucket-name /mnt/s3-archive \
  --allow-other --dir-cache-time 30s --vfs-cache-mode off

Custom Autocleaning (GUI)

For one-time cleanup of specific recordings (by IP, phone number, etc.):

  1. Navigate to Settings > Custom Autocleaning
  2. Create rule with filters
  3. Apply and remove rule after completion

Troubleshooting

Files Disappearing Faster Than Expected

1. Check if emergency cleanup is active
df -h /var/spool/voipmonitor
# If >95% full, emergency cleanup is running
2. Check GUI configuration override

When mysqlloadconfig = yes (default), GUI settings override config file. Check: Settings > Sensors > wrench icon.

3. Set appropriate limits

Set maxpoolsize to 90-95% of disk capacity to leave buffer for growth.

Disk Space Not Reclaimed After Database Cleanup

Check innodb_file_per_table: 

SHOW GLOBAL VARIABLES LIKE 'innodb_file_per_table';

If OFF, enable in my.cnf: 

[mysqld]
innodb_file_per_table = 1

MySQL Error 28: No Space Left

Enable size-based cleaning: 

cleandatabase_size = 512000
cleandatabase_size_force = true

Other causes: Inode exhaustion (df -i), MySQL tmpdir full.

Verify Database Cleaning

SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'cdr'
ORDER BY PARTITION_ORDINAL_POSITION DESC
LIMIT 10;

If partition count matches your cleandatabase setting, cleaning IS working.

For SQL queue issues and database performance, see Database_troubleshooting.

See Also


AI Summary for RAG

Summary: VoIPmonitor has two independent data retention systems: (1) Cleanspool for PCAP files using maxpoolsize/maxpooldays parameters, running every 5 minutes with in-memory file index; (2) Database cleaning using cleandatabase with instant partition dropping. Key space-saving option: savertp = header reduces storage by 90% while keeping quality metrics. Emergency cleanup (autocleanspoolminpercent=1, autocleanmingb=5) activates when disk nearly full and ignores normal limits. GUI settings override config file when mysqlloadconfig=yes. Size-based database cleaning requires BOTH cleandatabase_size AND cleandatabase_size_force=true. In multi-sensor environments, only one sensor should manage partitions (disable_partition_operations=yes on others).

Keywords: data retention, maxpoolsize, maxpooldays, maxpoolrtpsize, cleandatabase, cleandatabase_size, cleandatabase_size_force, cleanspool, autocleanspoolminpercent, autocleanmingb, tar_move, tiered storage, savertp header, innodb_file_per_table, partition dropping, emergency cleanup, mysqlloadconfig, disable_partition_operations

Key Questions:

  • How does VoIPmonitor cleanspool work?
  • How to configure PCAP file retention?
  • How to limit database size?
  • Why are files being deleted faster than expected?
  • How to fix MySQL Error 28 no space left?
  • How to reduce storage usage (savertp header)?
  • How to configure tiered storage?
  • Why is disk space not reclaimed after cleanup?
  • How to manage partitions in multi-sensor environment?
Retrieved from "https://www.voipmonitor.org/doc/index.php?title=Data_Cleaning&oldid=11005"