Your VICIdial Database Will Melt Down in 6 Months. Here's How to Prevent It.

It starts the same way every time. Your VICIdial system runs fine for six months. Then one Monday morning, agents can't log in. Real-time reports take 30 seconds to load. The hopper empties faster than it fills. You check the server — CPU is pegged, but not by Asterisk. It's MySQL, grinding through a query that used to take milliseconds and now takes 14 seconds because your vicidial_log table has 47 million rows and nobody set up archiving. This is the MySQL optimization guide that saves your next Monday morning. The database is VICIdial's single most critical component. Every agent action, every call event, every disposition, every real-time report, every hopper query, every dial-level calculation — all of it flows through one MySQL (or MariaDB) instance. In a VICIdial cluster, you can add telephony servers for more call capacity and web servers for more agent connections. But you cannot cluster the database. It's a single instance, and when it slows down, everything slows down. We've optimized MySQL on hundreds of VICIdial deployments, from 10-agent single-server setups to 500+ agent clusters processing millions of calls per month. This guide covers the database internals you actually need to understand: table architecture, growth patterns, buffer tuning, slow query identification, MEMORY table conversions, archival strategies, index optimization, the TIMESTAMP schema migration, corruption recovery, and replication for reporting. We include actual my.cnf configurations calibrated for real-world deployment sizes. --- ## Understanding VICIdial's Database Architecture Before you can optimize VICIdial's MySQL instance, you need to understand how VICIdial uses its database — because it's not like most web applications. ### The Access Pattern Problem VICIdial's database access patterns are unusual and demanding: 1. High-frequency writes: Every second, multiple Perl daemons update agent states, call statuses, and dial metrics across dozens of tables. The AST_update.pl process alone performs hundreds of UPDATEs per second in a busy 50-agent campaign. 2. Constant polling: Real-time reports don't use WebSockets or push notifications. They poll the database every second. Ten managers with real-time reports open means 10 SELECT queries per second against vicidial_live_agents and vicidial_auto_calls — on top of everything else. 3. Mixed workload: Short, fast transactional queries (agent state updates) run simultaneously with long analytical queries (campaign reports spanning millions of call records). The transactional queries need low latency. The analytical queries need throughput. MySQL has to serve both from the same instance. 4. Table-level locking: VICIdial uses MyISAM exclusively. Matt Florell has been unambiguous: "We do not recommend using InnoDB under any circumstances." MyISAM's table-level locks mean that a single slow SELECT on vicidial_log blocks every INSERT, UPDATE, and DELETE on that same table until the SELECT completes. This is the fundamental scaling constraint. ### Key Tables and Their Growth Patterns Understanding which tables grow and how fast tells you where to focus optimization. Here are the tables that matter, with approximate growth rates for a 50-agent outbound operation running 8 hours per day: | Table | Purpose | Growth Rate | Typical Size at 6 Months | |-------|---------|-------------|--------------------------| | vicidial_log | Primary call log (one row per call attempt) | ~1M rows/month | 6M rows, ~2 GB | | vicidial_log_extended | Extended call data (carrier info, etc.) | ~1M rows/month | 6M rows, ~3 GB | | call_log | Raw Asterisk CDR data | ~1M rows/month | 6M rows, ~2 GB | | vicidial_carrier_log | Trunk-level call events | ~2-3M rows/month | 15M rows, ~4 GB | | vicidial_manager | AMI command queue/history | ~5-10M rows/month | 40M+ rows, ~8 GB | | recording_log | Call recording metadata | ~500K rows/month | 3M rows, ~1 GB | | vicidial_closer_log | Inbound/transfer call log | ~200K rows/month | 1.2M rows, ~500 MB | | server_performance | Server stats (1 row/6 sec/server) | ~15K rows/day/server | 2.7M rows/server | vicidial_manager is the fastest-growing table in most deployments and the one most often overlooked. It stores every AMI command VICIdial sends to Asterisk — Originate commands for dialing, Hangup commands, Monitor commands for recording. At 50 agents with a 5:1 dial ratio, that's 250 Originate commands per cycle, plus hangups, plus monitors. This table can hit 100 million rows in under a year if not archived. ### MEMORY Tables: VICIdial's Real-Time Engine Several critical VICIdial tables run in the MEMORY storage engine (also called HEAP). These tables live entirely in RAM — no disk I/O, no table-level lock contention with disk writes. They are how VICIdial achieves real-time performance: | MEMORY Table | Purpose | Typical Row Count | |--------------|---------|-------------------| | vicidial_live_agents | Current state of every logged-in agent | 1 row per agent | | vicidial_live_inbound_agents | Inbound-eligible agents | 1 row per closer agent | | vicidial_auto_calls | Every active auto-dial call | 1 row per active call | | vicidial_hopper | Leads queued for dialing | configurable, usually 50-500 | | vicidial_live_sip_channels | Active SIP channels | dynamic | Critical behavior: MEMORY tables lose all data when MySQL restarts. This is by design. When MySQL comes back up, these tables are empty. Agents will need to log out and log back in, and the hopper will refill automatically. This is normal — but it means unexpected MySQL restarts during production hours cause a brief disruption as the system repopulates. The MEMORY table ceiling is controlled by max_heap_table_size in my.cnf. If a MEMORY table tries to grow beyond this limit, the INSERT fails with an error that typically manifests as agents unable to log in or calls not being placed. For most deployments, 64 MB is sufficient. For 500+ agent operations, increase to 128-256 MB. > Your Database Is Your Bottleneck — Even If You Don't Know It Yet. > ViciStack deployments ship with MySQL pre-tuned, MEMORY tables optimized, and archival jobs running from day one. See What Optimized Looks Like → --- ## my.cnf Tuning: The Settings That Actually Matter Out of the box, MySQL/MariaDB is configured for a general-purpose workload on modest hardware. VICIdial's access patterns — high-frequency writes, constant polling, mixed transactional and analytical queries — need specific tuning. Here are the parameters that make a measurable difference, organized by deployment size. ### Universal Settings (Every VICIdial Deployment) These settings should be applied regardless of size: ini [mysqld] # THE most important single setting for VICIdial skip-name-resolve # Allow VICIdial's connection-heavy architecture max_connections = 2000 # Disable query cache (more harm than good with VICIdial's write patterns) query_cache_size = 0 query_cache_type = 0 # Allow concurrent inserts during SELECT on MyISAM tables concurrent_insert = 2 # Table cache — VICIdial opens hundreds of tables table_open_cache = 4096 # Temp table limits (affects complex report queries) tmp_table_size = 64M max_heap_table_size = 64M # Connection timeout tuning wait_timeout = 600 interactive_timeout = 600 connect_timeout = 30 # Thread cache — avoids thread creation overhead for frequent connections thread_cache_size = 128 # Slow query logging (enable always, analyze periodically) slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow-query.log long_query_time = 2 The skip-name-resolve story: Without this setting, MySQL performs a reverse DNS lookup on every single incoming connection. In a VICIdial cluster where telephony servers, web servers, and dozens of cron-spawned Perl scripts all maintain constant database connections, those DNS lookups pile up. The symptom is "Too many connections" errors even though max_connections is set high enough — because connections are stuck waiting for DNS resolution. This single line has saved more VICIdial installations than any other my.cnf change. We covered this in the cluster guide, and we're repeating it here because it's that important. When you enable skip-name-resolve, you must use IP addresses (not hostnames) in MySQL GRANT statements. GRANT ALL ON *.* TO 'cron'@'10.0.0.5' works. GRANT ALL ON *.* TO 'cron'@'dialer1.local' does not. ### 50-Agent Deployment (Single Server or Small Cluster) ini [mysqld] # MyISAM key buffer — primary cache for MyISAM index blocks key_buffer_size = 512M # Sort buffer for ORDER BY / GROUP BY in reports sort_buffer_size = 4M read_buffer_size = 2M read_rnd_buffer_size = 4M # MyISAM-specific settings myisam_sort_buffer_size = 64M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 # Binlog (enable if you plan to set up replication later) # log-bin = mysql-bin # binlog_format = MIXED # expire_logs_days = 7 # InnoDB settings — even though VICIdial uses MyISAM, # MySQL system tables use InnoDB internally innodb_buffer_pool_size = 256M innodb_log_file_size = 64M Total RAM budget: On a dedicated database server with 16 GB RAM, this configuration uses approximately 1-2 GB for MySQL buffers, leaving ample room for OS cache (which MyISAM leverages heavily for data file caching through the filesystem cache). ### 100-Agent Deployment (Dedicated Database Server) ini [mysqld] key_buffer_size = 1024M sort_buffer_size = 8M read_buffer_size = 4M read_rnd_buffer_size = 8M join_buffer_size = 4M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 20G max_connections = 3000 table_open_cache = 8192 max_heap_table_size = 128M tmp_table_size = 128M thread_cache_size = 256 innodb_buffer_pool_size = 512M innodb_log_file_size = 128M # Enable binary logging for replication log-bin = mysql-bin binlog_format = MIXED expire_logs_days = 7 sync_binlog = 0 # Async for performance Total RAM budget: On a 32 GB dedicated database server, this uses approximately 3-4 GB for MySQL buffers. The operating system will use the remaining ~28 GB for filesystem cache, which directly benefits MyISAM's data file reads. ### 500-Agent Enterprise Deployment ini [mysqld] key_buffer_size = 4096M sort_buffer_size = 16M read_buffer_size = 8M read_rnd_buffer_size = 16M join_buffer_size = 8M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 256M myisam_max_sort_file_size = 50G max_connections = 6000 table_open_cache = 16384 table_definition_cache = 4096 open_files_limit = 65535 max_heap_table_size = 256M tmp_table_size = 256M thread_cache_size = 512 innodb_buffer_pool_size = 2G innodb_log_file_size = 256M log-bin = mysql-bin binlog_format = MIXED expire_logs_days = 3 sync_binlog = 0 # Per-thread buffers — be careful here, these multiply by max_connections # 6000 connections × 16M sort_buffer = 96 GB if all allocated simultaneously # In practice, MySQL allocates these on-demand, not all at once Critical note on per-thread buffer sizing at scale: Settings like sort_buffer_size, read_buffer_size, and join_buffer_size are allocated per-thread when needed. With max_connections = 6000, the theoretical maximum memory for sort buffers alone would be 6000 × 16 MB = 96 GB. In reality, MySQL allocates these on-demand and most connections don't need large sort buffers simultaneously. But if you see unexpected memory pressure, these per-thread buffers are the first place to look. Total RAM budget: 64 GB dedicated database server. MySQL buffers use approximately 8-10 GB. The remaining 54+ GB acts as filesystem cache for MyISAM data files, which is where the real performance comes from at scale. ### The key_buffer_size vs innodb_buffer_pool_size Question VICIdial uses MyISAM, so key_buffer_size is your primary tuning lever — it caches MyISAM index blocks in memory. innodb_buffer_pool_size caches InnoDB data and indexes. Since VICIdial's tables are MyISAM, why set innodb_buffer_pool_size at all? Because MySQL's internal system tables (mysql.user, information_schema, performance_schema) use InnoDB. MariaDB also uses InnoDB for some system catalogs. A small InnoDB buffer pool prevents these internal operations from thrashing disk. Don't set it to zero — just keep it modest (256M-2G depending on total RAM). The real performance lever for MyISAM data access is the operating system filesystem cache. MyISAM stores data in .MYD files and indexes in .MYI files. key_buffer_size caches the .MYI index blocks. But the .MYD data files are read through the OS filesystem cache. This is why you don't want MySQL to consume all available RAM — the OS needs that memory for file caching. The rule of thumb: MySQL buffers should use no more than 40-50% of total RAM on a dedicated database server, leaving the rest for the OS cache. --- ## Slow Query Identification and Analysis The slow query log is your single best diagnostic tool for database performance issues. Enable it always — the overhead is negligible compared to the debugging time it saves. ### Enabling the Slow Query Log ini [mysqld] slow_query_log = 1 slow_query_log_file = /var/log/mysql/slow-query.log long_query_time = 2 # Log queries taking >2 seconds log_queries_not_using_indexes = 1 # Also log queries missing indexes Setting long_query_time = 2 catches the genuinely problematic queries without flooding the log. For initial diagnosis on

Read the full article

About

Built by ViciStack — enterprise VoIP and call center infrastructure.

• VICIdial Hosting & Optimization
• Call Center Performance Guides
• Full Article: Your VICIdial Database Will Melt Down in 6 Months. Here's How to Prevent It.

License

MIT