Your VICIdial Database Has 50 Million Rows. Here's How to Fix It.

Every VICIdial deployment eventually hits the same wall: the database becomes the bottleneck. When your vicidial_log table has 50 million rows, your recording_log has grown to 30 GB, and your real-time reports take 15 seconds to load, you're past the point where adding more CPU or RAM to the dialer solves the problem.

A 25-agent center making 500 calls per agent per day generates 12,500 rows in vicidial_log alone. Add vicidial_agent_log, call_log, vicidial_closer_log, recording_log, and vicidial_did_log, and you're easily creating 50,000+ rows per day. After a year: 18 million rows. After two years: approaching 40 million. The table doesn't partition itself.

Symptoms You're Already Past the Tipping Point

Watch for these:

• Real-time reports lag — the agent performance screen takes 5+ seconds to load
• Outbound dialing hesitates — the hopper refill query takes longer, causing gaps between calls
• Recording page is slow — searching recordings by date range takes 30+ seconds
• Server load spikes during reports — campaign summary pins a CPU core
• SHOW PROCESSLIST shows long-running queries against vicidial_log or call_log running for 10+ seconds

Check your current table sizes:

SELECT table_name,
       ROUND(data_length / 1024 / 1024, 2) AS data_mb,
       ROUND(index_length / 1024 / 1024, 2) AS index_mb,
       table_rows
FROM information_schema.tables
WHERE table_schema = 'asterisk'
ORDER BY data_length DESC
LIMIT 20;

If vicidial_log, call_log, or recording_log are over 1 GB or over 10 million rows, you need partitioning and archiving.

The Tables That Grow Unbounded

Not every VICIdial table needs attention. These are the ones that grow continuously and cause issues:

Table	Grows By	Impact
vicidial_log	Every outbound call attempt	Report queries scan millions of rows
call_log	Every call (Asterisk CDR)	Joins against this table slow everything
vicidial_closer_log	Every inbound/transfer	Inbound reporting bogs down
vicidial_agent_log	Every agent state change	Agent performance queries drag
recording_log	Every recorded call	Recording search takes forever
vicidial_carrier_log	Every carrier event	Carrier analytics slow
vicidial_did_log	Every inbound DID call	DID routing reports slow
server_performance	Every update interval	Monitoring queries lag
vicidial_dial_log	Every dial attempt	Raw dial event analysis slow

Partitioning vicidial_log by Month

MySQL partition pruning allows queries that filter by date to scan only the relevant partition instead of the entire table. Since vicidial_log is almost always queried with a date filter, this is a massive performance win.

Step 1: Back Up First

This is non-negotiable. A failed ALTER TABLE on a multi-million row table can leave you with no data and no dialer.

mysqldump --single-transaction --routines --triggers \
  asterisk vicidial_log > /backup/vicidial_log_$(date +%Y%m%d).sql

# Verify the backup
mysql -e "SELECT COUNT(*) FROM vicidial_log;" asterisk
wc -l /backup/vicidial_log_$(date +%Y%m%d).sql

Step 2: Modify the Primary Key

MySQL requires the partition column to be part of every unique index. VICIdial typically uses uniqueid as the primary key, so you need to include call_date. Do this during off-hours — ALTER TABLE locks the table on older MySQL versions.

ALTER TABLE vicidial_log
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (uniqueid, call_date);

Step 3: Add Monthly Partitions

ALTER TABLE vicidial_log PARTITION BY RANGE (TO_DAYS(call_date)) (
  PARTITION p2025_01 VALUES LESS THAN (TO_DAYS('2025-02-01')),
  PARTITION p2025_02 VALUES LESS THAN (TO_DAYS('2025-03-01')),
  PARTITION p2025_03 VALUES LESS THAN (TO_DAYS('2025-04-01')),
  PARTITION p2025_04 VALUES LESS THAN (TO_DAYS('2025-05-01')),
  PARTITION p2025_05 VALUES LESS THAN (TO_DAYS('2025-06-01')),
  PARTITION p2025_06 VALUES LESS THAN (TO_DAYS('2025-07-01')),
  PARTITION p2025_07 VALUES LESS THAN (TO_DAYS('2025-08-01')),
  PARTITION p2025_08 VALUES LESS THAN (TO_DAYS('2025-09-01')),
  PARTITION p2025_09 VALUES LESS THAN (TO_DAYS('2025-10-01')),
  PARTITION p2025_10 VALUES LESS THAN (TO_DAYS('2025-11-01')),
  PARTITION p2025_11 VALUES LESS THAN (TO_DAYS('2025-12-01')),
  PARTITION p2025_12 VALUES LESS THAN (TO_DAYS('2026-01-01')),
  PARTITION p2026_01 VALUES LESS THAN (TO_DAYS('2026-02-01')),
  PARTITION p2026_02 VALUES LESS THAN (TO_DAYS('2026-03-01')),
  PARTITION p2026_03 VALUES LESS THAN (TO_DAYS('2026-04-01')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);

Step 4: Verify Partition Pruning

EXPLAIN PARTITIONS
SELECT * FROM vicidial_log
WHERE call_date BETWEEN '2026-03-01' AND '2026-03-19';

You should see partitions: p2026_03 in the output — not every partition in the table. If you see the full list, your query isn't including call_date in the WHERE clause, and MySQL can't prune.

Step 5: Automate Future Partitions

Create a cron job that adds new partitions monthly before the p_future catchall is needed:

#!/bin/bash
NEXT_MONTH=$(date -d "next month" +%Y_%m)
NEXT_MONTH_START=$(date -d "next month" +%Y-%m-01)
FOLLOWING_MONTH_START=$(date -d "2 months" +%Y-%m-01)

mysql asterisk -e "
ALTER TABLE vicidial_log REORGANIZE PARTITION p_future INTO (
  PARTITION p${NEXT_MONTH} VALUES LESS THAN (TO_DAYS('${FOLLOWING_MONTH_START}')),
  PARTITION p_future VALUES LESS THAN MAXVALUE
);"

Run this on the 15th of each month via cron.

Archiving: Drop Partitions Instead of DELETE

Once partitioned, archiving old data is trivial and instant. Instead of running a slow DELETE FROM vicidial_log WHERE call_date < '2025-01-01' that locks the table and generates massive binary log entries, you drop the entire partition:

#!/bin/bash
# Archive and drop partitions older than 6 months
ARCHIVE_DIR="/backup/vicidial_archive"
CUTOFF=$(date -d "6 months ago" +%Y_%m)

# Export first
mysqldump --single-transaction asterisk vicidial_log \
  --where="call_date < '$(date -d '6 months ago' +%Y-%m-01)'" \
  > ${ARCHIVE_DIR}/vicidial_log_archive_${CUTOFF}.sql

gzip ${ARCHIVE_DIR}/vicidial_log_archive_${CUTOFF}.sql

# Drop the partition (instant, no table lock)
mysql asterisk -e "ALTER TABLE vicidial_log DROP PARTITION p${CUTOFF};"

Dropping a partition is an O(1) operation — it removes the data file instantly regardless of how many rows are in it. Compare this to DELETE which would need to scan every row, update indexes, and write to the binary log.

Apply the same pattern to call_log, vicidial_closer_log, vicidial_agent_log, and recording_log. The process is identical — just change the table name and date column.

InnoDB Tuning That Actually Matters

Beyond partitioning, MySQL configuration has an outsized impact on VICIdial performance. Most installs run near-default settings, which aren't tuned for VICIdial's write-heavy, real-time pattern.

Buffer Pool

The single most impactful setting. Set to 60-70% of total RAM on a dedicated DB server, 40-50% if sharing with Asterisk.

[mysqld]
innodb_buffer_pool_size = 8G
innodb_buffer_pool_instances = 8
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

Check your hit rate — should be above 99%:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';

Write Optimization

VICIdial writes constantly. Optimize for that workload:

[mysqld]
innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT
innodb_io_capacity = 2000
innodb_io_capacity_max = 4000
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_log_file_size = 2G
innodb_log_buffer_size = 64M

Setting innodb_flush_log_at_trx_commit = 2 flushes to OS cache every commit and to disk every second. The data being written (call logs, agent events) can be reconstructed from Asterisk logs in a worst-case scenario.

Connection Tuning

[mysqld]
max_connections = 500
thread_cache_size = 50
table_open_cache = 4000
tmp_table_size = 256M
max_heap_table_size = 256M

Query Cache (MariaDB Only)

If you're on MariaDB (common for VICIdial), the query cache helps with repetitive real-time report queries:

query_cache_type = 1
query_cache_size = 256M
query_cache_limit = 4M

MySQL 8.0 removed the query cache entirely. If you're on MySQL 8.0+, skip this and rely on buffer pool caching.

One Table You Should Never Partition

Do not partition vicidial_list. It might be the largest table in your database, but VICIdial queries it by lead_id (not date), and the hopper refill uses complex multi-column filters. Partitioning by date would actually make these queries slower because MySQL cannot prune partitions when the query doesn't filter on the partition key.

Manage vicidial_list size by archiving completed lists to a separate table, keeping active campaign lists under 1 million leads each, and cleaning out fully-worked data during maintenance windows.

Enable the Slow Query Log

Catch problematic queries before they catch you:

[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow-query.log
long_query_time = 2
log_queries_not_using_indexes = ON

Review regularly with mysqldumpslow -s c -t 10 /var/log/mysql/slow-query.log to find the most common offenders.

The difference between a VICIdial database that runs smooth at 50 million rows and one that grinds to a halt is usually a weekend of partitioning work plus a properly tuned my.cnf. If that sounds like more than you want to tackle on your own, ViciStack handles the whole optimization stack — partitioning, archiving, InnoDB tuning, and ongoing monitoring — as part of our managed service.

Originally published at https://vicistack.com/blog/vicidial-database-partitioning/