WHMCS database optimization deals with the MySQL or MariaDB side of an install that has grown for years: log tables bigger than the billing data, slow queries from addons and reports, mixed storage engines and collations, and server settings nobody changed from the defaults. One WHMCS database starts at $199. When the slowness is in PHP or the cron, performance tuning is the better match, and heavy pages in the browser belong to speed optimization.
Where WHMCS databases put on weight
- Activity Log. WHMCS trims system entries to the activity log limit you set, but client entries stay until someone prunes them. On older installs tblactivitylog is often the largest table.
- Email Message Log. A copy of every client email, stored in tblemails, for as long as the install has existed.
- Debug logging left on. Hooks Debug Mode, SQL Debug Mode or Module Logging, switched on for an old support case and never switched off. WHMCS warns that they make the database grow very quickly.
- Gateway, WHOIS and ticket import logs. Rows nobody reads after a few months.
- Addon tables. Third-party addons that log every API call, often with no index on the columns their own pages search.
What the job includes
1. A size and query report
We list every table by data size, index size and row count, then run MySQL's slow query log during a normal business day. The worst queries are checked with EXPLAIN to see whether they scan whole tables. Most turn out to come from a custom report, a hook or an addon rather than WHMCS core.
2. Indexes where they are safe
Missing indexes are added to addon and custom tables. We avoid changing the structure of core WHMCS tables, because the updater expects its own schema; where a core query is slow, we fix the hook or report that triggers it.
3. Pruning you agree to first
Before anything is deleted, you decide what stays for audits, disputes and tax records. Invoices, transactions and client accounts are never pruned. Where the Admin Area has a tool for it, we use it: Prune Client Activity Logs, Prune Saved Emails and Prune Old Attachments in the Advanced tab of Utilities > System > System Cleanup, and the Empty options for the gateway and WHOIS logs. Older rows can be exported to a compressed archive file first if you want to keep them outside the live database. The Prune Ticket Attachments automation setting keeps attachments from piling up again.
4. Engine, collation and strict mode
Leftover MyISAM tables from old installs lock the whole table on every write, so we convert them to InnoDB. Tables are brought to the utf8_unicode_ci collation WHMCS recommends, since mixed collations can break queries and leave the admin dashboard blank. Strict SQL modes, which WHMCS does not support, are taken out of sql_mode.
5. Server settings
In my.cnf we size innodb_buffer_pool_size to your data and RAM, set max_connections to what the server can actually serve, and meet the minimums System Health checks for: connect_timeout of 10, wait_timeout and interactive_timeout of 300, and a max_allowed_packet of at least 4 MB (WHMCS's performance notes suggest 16 MB). After large deletions we run OPTIMIZE TABLE, which is what the Optimise Tables button at Utilities > System > Database Status does, in a quiet window so locks do not affect customers.
How we keep your data safe
- A full dump before any change, checked by loading it into a scratch database
- Deletions in small batches, so no single statement holds a long lock
- The cron paused during the largest operations, then checked once it resumes
What we need from you
- Database access through SSH or phpMyAdmin, and a WHMCS admin login
- Temporary ALTER, CREATE, DROP and INDEX privileges if you removed them from the WHMCS database user, as WHMCS allows for daily operation
- Your retention rules for logs and saved emails
What you get back
- Before-and-after table sizes and query times.
- Every index added and every setting changed, including the my.cnf differences.
- A short schedule for keeping logs in check, including which debug settings must stay off.
Pricing
| Scope | Price |
|---|---|
| Size report, pruning, engine and collation fixes and my.cnf review for one database | From $199 |
| Slow queries inside custom addons or reports that need code changes | Fixed quote |
| A dedicated database server, replication or very large databases | Fixed quote |
| Later changes | $65/hour |
Your WHMCS version, 8.13 or 9.x, does not change this work.
Not covered
- Switching on Automatically Delete Inactive Clients, which permanently removes client records and is a decision for you and your accountant
- Reporting dashboards built on the data, which is reporting and analytics
- Backup design; if dumps are slow or untested, add backup and disaster recovery
A screenshot of the table list at Utilities > System > Database Status is usually enough for a first estimate, so include one when you write.