A MODX Revolution site rarely slows down because of its template logic. The database is usually the first component to degrade, and it does so quietly. Session rows accumulate, the manager log swells with thousands of action entries, cache-related tables fragment, and the InnoDB storage engine holds onto deleted-row space long after content has been purged. On a shared LiteSpeed account you cannot tune the MySQL server itself, so maintenance has to happen entirely at the schema and data level through phpMyAdmin and the MODX manager. The good news is that MODX uses a predictable, well-documented table layout, which means you can identify exactly where bloat lives and clean it without guessing.
This article walks through the tables that grow fastest, how to spot slow queries and broken tables inside phpMyAdmin, how to rebuild indexes and reclaim space, and how to resolve the "could not connect to database" errors that appear when a shared account hits its connection or resource ceiling.
Finding where MODX database bloat actually lives
Open phpMyAdmin from cPanel (Databases > phpMyAdmin) or DirectAdmin (Account Manager > phpMyAdmin / MySQL Management), then select your MODX database. Click the database name so the table list appears, and sort by the Size column if your phpMyAdmin build shows it, or run this query from the SQL tab to get an exact ranking:
SELECT table_name,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
table_rows
FROM information_schema.tables
WHERE table_schema = DATABASE()
ORDER BY (data_length + index_length) DESC
LIMIT 15;In almost every neglected MODX install the same offenders rise to the top. The modx_session table is the most common, because MODX writes a session row for every visitor and crawler unless garbage collection runs cleanly. On shared hosting the PHP session garbage collector is often set to a low probability, so expired rows linger. The modx_manager_log table grows every time an admin saves a resource, clears cache, or logs in, and it is never trimmed automatically. If you run form-processing extras like FormIt with logging, or SEO extras that store redirects, those tables expand too. The modx_context_resource, modx_site_content, and modx_site_tmplvar_contentvalues tables grow legitimately with content, but orphaned TV values from deleted resources frequently remain behind.
To confirm stale session rows, run:
SELECT COUNT(*) FROM modx_session
WHERE access < UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY);If that returns tens of thousands of rows, session bloat is your primary issue. You can safely delete expired sessions without affecting logged-in users, since MODX recreates active sessions on the next request:
DELETE FROM modx_session
WHERE access < UNIX_TIMESTAMP(NOW() - INTERVAL 2 DAY);For the manager log, keep a short window of recent activity and discard the rest:
DELETE FROM modx_manager_log
WHERE occurred < NOW() - INTERVAL 30 DAY;Before deleting anything, take a backup. In cPanel use Files > Backup > Download a MySQL Database Backup, or in phpMyAdmin select the database, open Export, choose the Custom method, and save a gzipped SQL dump. This matters because a DELETE on a corrupted table can fail halfway and leave inconsistent state.
Repairing broken tables and reclaiming InnoDB space
Broken tables show up in a few ways: the MODX manager throws a blank page, resource trees fail to load, or phpMyAdmin reports a table as "in use" or "crashed". MyISAM tables, still used by some older extras, are the usual culprits because they do not survive mid-write interruptions the way InnoDB does. In phpMyAdmin, tick the checkbox beside the suspect table in the table list, then from the With selected dropdown choose Repair table. For MyISAM this runs REPAIR TABLE and usually fixes index corruption instantly. You can also run it directly:
REPAIR TABLE modx_session;
CHECK TABLE modx_site_content;InnoDB tables, which make up most of a modern MODX schema, do not support REPAIR TABLE. For those you reclaim space and rebuild internal structures with OPTIMIZE TABLE, which InnoDB silently translates into a table rebuild:
OPTIMIZE TABLE modx_session, modx_manager_log, modx_site_tmplvar_contentvalues;This is where you recover the disk space left behind after large deletes. InnoDB does not shrink the physical file automatically after you remove rows; the freed pages stay allocated until the table is rebuilt. Running OPTIMIZE after the cleanup queries above is what actually returns space to your account quota, which is directly relevant on shared plans where disk usage is metered. Watch the operation though: optimizing a very large table can lock it briefly and consume CPU, so run it during low-traffic hours and one table at a time if your account has tight resource limits (LVE on CloudLinux will throttle rather than crash, but the manager may feel sluggish during the rebuild).
If phpMyAdmin times out during an optimize on a large table, the cause is usually the max_execution_time enforced on the panel's PHP process rather than MySQL itself. Split the work: optimize one table per request instead of batching several, and the operations will complete within the timeout window.
Diagnosing slow queries and fixing missing indexes
Without server-level access you cannot enable the MySQL slow query log, but you can still find inefficient queries. MODX writes database and PHP errors to a log you control. Check core/cache/logs/error.log through cPanel File Manager or DirectAdmin's file manager, and also inspect the per-directory error_log file that LiteSpeed drops into your document root. Slow or failing queries from extras frequently surface there with the full SQL statement, which tells you exactly which table and condition is the bottleneck.
To test a suspect query's efficiency, prefix it with EXPLAIN in the phpMyAdmin SQL tab:
EXPLAIN SELECT * FROM modx_site_content
WHERE context_key = 'web' AND published = 1 AND deleted = 0;In the output, a type value of ALL and a high rows count mean a full table scan, which is slow once a site has thousands of resources. MODX ships sensible indexes on its core tables, but custom extras and large TV setups sometimes query columns that are not indexed. The modx_site_tmplvar_contentvalues table is a frequent pain point because queries filter on tmplvarid and contentid together. Confirm the index exists:
SHOW INDEX FROM modx_site_tmplvar_contentvalues;If a compound index on the columns your extra filters by is genuinely missing, you can add one through the Structure tab by selecting the columns and clicking Index, or with SQL:
ALTER TABLE modx_site_tmplvar_contentvalues
ADD INDEX idx_tmplvar_content (tmplvarid, contentid);Be conservative. Do not drop or rename the indexes MODX creates, since the ORM (xPDO) depends on the documented schema, and a future upgrade may recreate them and conflict with your changes. Add indexes only to resolve a demonstrated full scan, and document what you added so upgrades stay predictable.
After any schema change, clear the MODX cache so xPDO rebuilds its table metadata. Do this from the manager via Manage > Clear Cache, or delete the contents of core/cache/ through File Manager, leaving the directory itself in place.
Resolving connection errors and preventing recurrence
The "Could not connect to the database" or "xPDO: Could not load driver" error during normal operation almost always traces back to the shared MySQL connection limit rather than wrong credentials, especially if the site works intermittently. Shared accounts cap the number of concurrent MySQL connections, and a bloated database that forces slow queries holds connections open longer, which exhausts the pool under traffic. The fastest structural fix is everything above: trimming sessions and logs and rebuilding tables reduces query time and frees connections sooner.
Verify your credentials are still valid by opening core/config/config.inc.php in File Manager and confirming $database_server, $dbase, $database_user, and $database_password match the database and user shown in cPanel MySQL Databases or DirectAdmin MySQL Management. On most shared stacks $database_server should be localhost, not 127.0.0.1, because the panel configures a socket connection. A mismatch here produces an immediate, permanent connection failure rather than an intermittent one.
To reduce the pressure that causes intermittent failures, keep session growth under control going forward. Raise PHP's session garbage collection through a .user.ini file in your document root so expired sessions clear more aggressively:
session.gc_probability = 1
session.gc_divisor = 100
session.gc_maxlifetime = 86400The .user.ini is read by the LiteSpeed PHP handler selected in cPanel MultiPHP Manager or the DirectAdmin PHP Selector, and changes apply within a few minutes without a restart. Pair that with a recurring manual cleanup: once a month, log into phpMyAdmin, run the session and manager-log delete queries, then optimize those two tables. On a content-heavy MODX site this ten-minute routine keeps the database small enough that connections cycle quickly and slow queries never get the chance to pile up.
Finally, treat a backup as the first step of every maintenance session, not the last. Export the database through phpMyAdmin or cPanel before deleting rows, repairing tables, or altering indexes, so a failed operation on a shared server never leaves you without a clean restore point.