Perfex CRM stores nearly everything it does inside MySQL/MariaDB: invoices, tickets, leads, activity trails, email tracking, session state, and background task schedules. On a busy install the database grows steadily, and because a shared hosting account has finite CPU, I/O, and connection limits, that growth eventually shows up as sluggish dashboards, timeouts when loading the Activity Log, and occasional Error establishing a database connection messages during peak hours. The database itself is rarely broken. What happens is that a handful of high-churn tables balloon with rows nobody reads, indexes stop matching real query patterns, and a table crash from an interrupted write leaves one table flagged as corrupt.

You do not have root, shell, or the ability to touch my.cnf on managed cloud shared hosting, and you cannot run SET GLOBAL or restart MariaDB. Everything below stays inside what a hosting user actually controls: phpMyAdmin (launched from cPanel Jupiter or DirectAdmin Evolution), the File Manager for editing Perfex config and reading logs, and the Perfex AdminCP itself. That is enough to identify the bloat, trim it safely, repair crashed tables, and align indexes so common queries stop doing full scans.

Diagnosing bloat and slow queries in phpMyAdmin

Start by measuring, not guessing. Open cPanel → Databases → phpMyAdmin (in DirectAdmin it is under Account Manager → phpMyAdmin), select your Perfex database from the left sidebar, then click the Structure tab. The column headers are sortable. Click Size to push the largest tables to the top. On almost every real Perfex install the offenders are the same set of tables, all prefixed with your table prefix (the default is tbl):

  • tblactivity_log — every admin action, forever, unless trimmed.
  • tbltracked_mails and email tracking rows — open/click pixels.
  • tblsessions — CodeIgniter session rows that never expire cleanly.
  • tblreminders and tblscheduled_emails — queued items that piled up.
  • tblviews_tracking — proposal/estimate view logging.

To see the raw numbers instead of the rounded display, run a query in the SQL tab. This lists every table by data and index size in megabytes so you know exactly where the weight is:

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 = DATABASE()
ORDER BY data_length + index_length DESC
LIMIT 25;

A table showing 400,000 rows in tblactivity_log against a few thousand invoices tells the story immediately. For slow queries, phpMyAdmin lets you prefix any SELECT with EXPLAIN to see how MySQL resolves it. If you notice the ticket list or lead kanban dragging, grab the query pattern and test it:

EXPLAIN SELECT * FROM tbltickets
WHERE status = 1 AND admin = 3
ORDER BY lastreply DESC;

In the output, a type of ALL and a large rows value with NULL under key means MySQL is reading the entire table because no useful index exists for that filter. That is your signal that an index is missing, which the last section addresses. Do not skip the measurement step; trimming the wrong table or adding a pointless index just wastes your account's I/O budget without helping.

Safely trimming unnecessary data

Before deleting a single row, take a backup. In phpMyAdmin, select the database, click Export, choose the Custom method, tick gzip compression, and download the .sql.gz file. If the export times out because the database is large, use cPanel → Files → Backup → Download a MySQL Database Backup instead, which streams to disk rather than through PHP. Keep that file until you have confirmed the site works after cleanup.

Perfex has built-in trimming that avoids raw SQL where possible. In the AdminCP go to Setup → Settings → General and look for the activity log and reminders retention options; setting Delete Activity Log Older Than (Months) to something like 3 lets the CRM prune itself on the next cron run. Under Utilities → Activity Log you can also review what is being recorded. For a one-time cleanup that the UI won't do fast enough, phpMyAdmin's SQL tab handles it in seconds. Always scope deletes with a date so you never wipe recent, relevant data:

DELETE FROM tblactivity_log
WHERE date < DATE_SUB(NOW(), INTERVAL 3 MONTH);

DELETE FROM tblsessions
WHERE timestamp < UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 7 DAY));

DELETE FROM tbltracked_mails
WHERE date < DATE_SUB(NOW(), INTERVAL 6 MONTH);

Deleting rows from an InnoDB table frees space internally but does not shrink the physical file, so the account's disk usage may not drop immediately. To reclaim it, select the trimmed tables on the Structure tab, choose Optimize table from the With selected dropdown, or run OPTIMIZE TABLE tblactivity_log, tbltracked_mails, tblsessions;. Run this during a quiet window because it locks the table briefly. Verify the Perfex cron job is actually firing, since a broken cron is the reason logs and reminders accumulate in the first place. The cron URL lives under Setup → Settings → Cron Job, and on Hostiso you schedule it in cPanel → Advanced → Cron Jobs pointing at php -q /home/USER/public_html/crons/cron.php every five minutes.

Repairing broken tables and connection errors

A crashed or marked-as-crashed table usually follows an interrupted write — a PHP timeout mid-transaction, or the account hitting its memory or process limit during a large import. Symptoms include a specific module throwing a 500 while the rest of the CRM loads, or Perfex writing Table './db/tblx' is marked as crashed and should be repaired. Confirm the exact message in the local error log at public_html/application/logs/ or via cPanel → Metrics → Errors, then open phpMyAdmin, select the affected table, and use With selected → Repair table on the Structure tab. This works cleanly for MyISAM. Modern Perfex tables are InnoDB, where REPAIR TABLE is a no-op; in that case the correct fix is to restore the single affected table from your export. Drop the broken table and re-import just its section from the backup .sql file.

Connection errors like Too many connections or intermittent Error establishing a database connection on shared hosting are almost never a corrupt database. They mean concurrent PHP workers are each holding a database link and exceeding the account's connection ceiling. You cannot raise the global limit, so reduce demand: confirm the database credentials in public_html/application/config/app-config.php are correct so the app isn't retrying failed logins, trim the bloated tables so each request finishes faster and releases its connection sooner, and lower the frequency of any custom API integrations hammering the CRM. If a specific report page opens dozens of queries, cache it by enabling Perfex's built-in caching under the same config directory rather than leaving connections open longer than needed.

Aligning indexes with real query patterns

Once the tables are lean, the remaining slowness comes from missing indexes on columns Perfex filters and sorts by heavily. The EXPLAIN checks from the first section point you at the columns. Perfex ships sensible primary keys, but on large installs the frequently filtered foreign-key columns benefit from added indexes. Check what already exists on a table with SHOW INDEX FROM tbltickets; before adding anything, so you don't create a duplicate that only wastes write performance and disk.

Add indexes conservatively, targeting the exact column combinations that appeared in slow WHERE and ORDER BY clauses:

ALTER TABLE tbltickets
  ADD INDEX idx_status_admin (status, admin);

ALTER TABLE tblactivity_log
  ADD INDEX idx_date (date);

ALTER TABLE tblsales_activity
  ADD INDEX idx_rel (rel_id, rel_type);

After each ALTER TABLE, re-run the same EXPLAIN and confirm the type column moved from ALL to ref or range and that key now names your new index. Every index speeds reads but slows inserts and consumes disk, so add only the ones that measurably change an EXPLAIN plan. Keep your gzip export from the trimming step as the rollback for any index that turns out unnecessary — dropping it is a one-line DROP INDEX on the Structure tab. Between scheduled retention settings, a working cron, periodic OPTIMIZE TABLE, and a small set of purposeful indexes, a Perfex database on shared hosting stays fast without ever needing root access.