MariaDB tuning for PBX and dialer workloads

FreePBX® and Vicidial® both use MariaDB for their state — extensions, CDRs, lead lists, agent activity, recordings metadata. The default MariaDB config on a fresh Debian or AlmaLinux install is conservative; pinning a few key knobs for these workloads gives meaningful response-time improvement without much effort.

What's specific about PBX workloads

• Write-heavy. CDR inserts, vicidial_live_agents updates, vicidial_log inserts per call. Steady-state writes vastly exceed reads on a busy call center.
• Many small queries. Per-call dialplan lookups, per-second agent state polls. The MariaDB connection setup overhead matters when you're doing thousands of queries per minute.
• Tables grow fast. vicidial_log on a busy operation gains millions of rows per month. Indexing + partitioning matter.
• Concurrent connections. Each agent's session opens DB connections, the dialer opens connections, the web UI opens connections. Quickly hits the default max_connections ceiling.

The knobs that move the needle

Edit /etc/mysql/mariadb.conf.d/99-tuning.cnf (Debian) or /etc/my.cnf.d/99-tuning.cnf (AlmaLinux):

[mysqld]
# Memory — biggest single impact. Set this to ~50-70% of available RAM on a
# database-dominated VPS. For a 4 GB VPS dedicated to PBX + DB: 2 GB here.
innodb_buffer_pool_size = 2G

# Connection count — default 151. PBX workloads easily exceed this with
# many agents + the dialer + web UI.
max_connections = 300

# Per-thread buffers. Each connection allocates these. Keep small.
sort_buffer_size = 2M
read_buffer_size = 1M
read_rnd_buffer_size = 4M
join_buffer_size = 4M

# InnoDB log file size — bigger = better write throughput, but slower crash recovery
innodb_log_file_size = 256M

# Flush log to disk every N seconds rather than every transaction (faster, slightly less durable)
# 1 = ACID compliant (default)
# 2 = flush each tx to OS, OS flushes to disk every second (small durability loss, big perf win)
innodb_flush_log_at_trx_commit = 2

# Don't double-write the log file
innodb_flush_method = O_DIRECT

# Slow query log — log queries taking > 1 second so you can tune them later
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

# Per-thread tmp tables in memory before spilling to disk
tmp_table_size = 64M
max_heap_table_size = 64M

Restart MariaDB after editing: systemctl restart mariadb.

Verify the changes took effect

mysql -e "SHOW VARIABLES LIKE 'innodb_buffer_pool_size'"
mysql -e "SHOW VARIABLES LIKE 'max_connections'"
mysql -e "SHOW STATUS LIKE 'Threads_connected'"

Vicidial-specific table maintenance

vicidial_log, vicidial_closer_log, vicidial_drop_log grow unbounded. Best practices:

• Archive monthly. Move rows older than N months into vicidial_log_archive (a separate table). Reports targeting the recent window stay fast; historical queries hit the archive.
• Partition by date. For very busy operations, MariaDB partitioning on call_date keeps query performance flat even as the table grows to billions of rows. See the MariaDB docs for ALTER TABLE ... PARTITION BY syntax.
• Periodic OPTIMIZE. After archive deletions, run OPTIMIZE TABLE vicidial_log during low-traffic windows to reclaim space.

FreePBX-specific notes

• FreePBX uses TWO databases: asterisk (configuration) and asteriskcdrdb (CDRs). Tuning applies to both.
• The asteriskcdrdb grows steadily with call volume; archive old CDRs annually to a separate database, not just delete (compliance retention).
• Don't drop the cdr table — FreePBX needs the structure to exist even when empty.

When to add a read replica

If query load from reporting (Grafana dashboards, custom scripts, FreePBX's reports module) starts competing with the operational write workload from Asterisk/Vicidial, set up a read replica on a second VPS:

• Primary handles all writes + operational reads from the PBX engine.
• Replica handles all reporting / analytics queries.
• MariaDB replication: master-replica via binlog. Built into MariaDB; no third-party tooling needed.

Setup is non-trivial but well-documented. Worth doing once reporting queries are visibly impacting call quality.

Monitoring

Watch:

• SHOW STATUS LIKE 'Threads_connected' — should be well below max_connections.
• SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty' vs total — dirty pages should be a small percentage; if high, the disk write subsystem is the bottleneck.
• The slow query log — review weekly, add indexes for the slowest queries.
• Disk space — MariaDB doesn't run well at > 90% disk full; OPTIMIZE TABLE temporarily doubles the space need.

For dashboards, see the Vicidial Grafana monitoring article in the call-center category.