Monitoring database load on a VICIdial server

VICIdial is a database-heavy application. Nearly every Agent action, every call disposition, every Campaign dial decision touches MariaDB. On a Single tenant box where the database and Asterisk share the same CPU and disk, a slow or expensive query during your busiest dialing hour can drag down call handling for everyone on the shift. Knowing how to spot database pressure early is a practical part of running a healthy dialer.

Why database load matters on a dialer

VICIdial's Keepalive processes hit the database constantly: checking Campaign status, pulling leads from the hopper, writing call outcomes to vicidial_log, and updating Agent session state. This baseline query traffic is low-latency and designed to run every few seconds. The problem arises when an ad-hoc report query, a bulk list upload, or an unindexed join runs at the same time. A query that takes 30 seconds to complete holds a lock or chews CPU for those 30 seconds. The keepalive scripts that need the same tables either wait, time out, or produce errors — and the dialer behaves erratically.

SHOW PROCESSLIST as a first look

The fastest way to see what the database is doing right now is to run SHOW PROCESSLIST inside MariaDB. It lists every active connection, the query it is running, and how long that query has been executing. On a healthy box during active dialing you will see dozens of short-lived queries from the VICIdial Perl and PHP processes, each completing in under a second. If you see one or two queries that have been running for 10, 30, or 60 seconds, those are your suspects. A long-running query on vicidial_log or vicidial_list that shows a state of Waiting for table lock means it is blocking later queries behind it.

Slow query log and connection count

The slow query log captures any query that exceeds a configured threshold, typically one second. Enabling it during a shift where you suspect database pressure will show you exactly which queries are taking longest, what indexes they are using, and whether the same query appears repeatedly. On a VICIdial box the most common offender is a report query that does a full table scan on vicidial_log without a campaign or date filter, because that table can hold tens of millions of rows on a busy system.

Connection count is a secondary signal. MariaDB has a max_connections limit; when the dialer's Perl and PHP processes together with any open reporting sessions approach that limit, new connections are refused. The dialer will log database connection errors and agent actions that require a database write will silently fail. On a VPS sized for your call volume this limit should be set comfortably above the expected concurrent connection count from all server processes.

How database pressure shows up on the system metrics

sequenceDiagram
  participant KA as Keepalive script
  participant DB as MariaDB
  participant RPT as Report query
  RPT->>DB: Long table scan (30s+)
  KA->>DB: Read vicidial_list
  DB-->>KA: Waiting for lock
  Note over KA,DB: Keepalive stalls
  KA->>DB: Retry after timeout
  DB-->>KA: OK (report finished)
  Note over KA: Dial decisions delayed ~30s

On the Server Performance Report, database pressure looks like rising SYSTEM CPU — the kernel is handling disk I/O for full table scans — combined with rising load even though Concurrent calls and Channel count have not changed. This is the key pattern that distinguishes a database bottleneck from a genuine call surge. If calls are flat but load is climbing, run SHOW PROCESSLIST before adjusting anything else.

Practical steps to reduce database load

• Schedule heavy reports for off-peak hours — never run a 30-day vicidial_log report during active dialing.
• Keep vicidial_log pruned — rows older than your retention window should be archived to a separate table or exported, so day-to-day queries stay fast.
• Upload lead lists between shifts — bulk inserts to vicidial_list compete with the same tables the dialer reads during hopper fills.
• Check SHOW STATUS for Threads_connected and Max_used_connections regularly — a trend toward your max_connections limit is a warning to either raise the limit or reduce the number of concurrent reporting sessions.

For the complete picture of server health metrics to track, see the server health and capacity guide. For an explanation of how load and CPU numbers appear when database pressure is the cause, read what high system load actually means on a dialer.

A right-sized managed box keeps the database and Asterisk from competing for the same thin CPU slice. See VICIfast plans to find a configuration matched to your agent count and call volume.