A Vanilla Forums database accumulates problems quietly. The schema itself is lean, but the tables that record activity, sessions, logs, and search metadata grow without any built-in cleanup schedule. On shared hosting you cannot tune the MySQL server, raise global buffer sizes, or run maintenance crons as root, so the practical work happens inside phpMyAdmin, the Vanilla Dashboard, and the handful of account-level files you control. This guide walks through diagnosing bloat, repairing broken tables, pruning expendable rows, and restoring the indexes that keep category and discussion pages fast.
Spotting bloat and identifying the heavy tables
The first step is measuring what is actually taking up space, because assumptions about which tables are large are usually wrong. Open phpMyAdmin from cPanel (Databases section) or DirectAdmin (Account Manager → phpMyAdmin), select your Vanilla database, and click the Structure tab. The table list shows row counts and a Size column. Sort by size and you will almost always find the same culprits near the top: GDN_Log, GDN_Session, GDN_Search (when the built-in search is active), GDN_Activity, and GDN_UserAuthenticationToken.
These tables behave differently from content tables like GDN_Discussion and GDN_Comment. The content tables grow in proportion to real forum activity and should be left alone. The service tables grow from mechanics that never prune themselves. GDN_Session holds one row per visitor session, including every anonymous crawler hit, and Vanilla does not aggressively garbage-collect expired rows. GDN_Log stores moderation events, spam decisions, and edits; on a forum fighting spam it can reach hundreds of thousands of rows. GDN_Activity records the activity feed and wall posts, which few admins ever purge.
To see the problem numerically, run a query from the phpMyAdmin SQL tab against the information schema. Substitute your real database name:
SELECT table_name,
table_rows,
ROUND((data_length + index_length) / 1024 / 1024, 2) AS size_mb,
ROUND(data_free / 1024 / 1024, 2) AS free_mb
FROM information_schema.tables
WHERE table_schema = 'youruser_vanilla'
ORDER BY (data_length + index_length) DESC;The free_mb column is the telling one. A large data_free value means the table has reclaimable space from deleted or updated rows that was never returned to the filesystem. This is the fragmentation that slows full-table scans and inflates your disk quota. A forum with a 400 MB database where 120 MB sits in data_free is carrying dead weight that account-level tools can recover.
Repairing crashed tables and connection errors
When the forum throws a white screen, a Something went wrong page, or logs entries such as Table './youruser_vanilla/GDN_Session' is marked as crashed and should be repaired, you are looking at a table-level fault rather than a credentials problem. MyISAM tables crash when a write is interrupted mid-operation, which happens during an I/O limit hit or an abrupt process kill on a busy shared node. Because you lack shell access to myisamchk, the repair runs through phpMyAdmin.
In phpMyAdmin, open the Structure tab, tick the checkbox beside the affected table (or use Check all), and choose Repair table from the With selected dropdown at the bottom. For a first pass, choose Check table to confirm the fault, then Repair table. This issues a REPAIR TABLE statement that you are permitted to run as the database user who owns the table. InnoDB tables will report that the storage engine does not support repair; for those, run OPTIMIZE TABLE GDN_Comment; instead, which rebuilds the table and clears corruption-adjacent fragmentation.
Connection errors are a separate failure class. If the Dashboard and front end both fail with Error connecting to the database, the problem is almost always in the Vanilla config, not the server. Open File Manager and edit /conf/config.php in your document root. Confirm the credentials block matches a user listed under cPanel → MySQL Databases:
$Configuration['Database']['Host'] = 'localhost';
$Configuration['Database']['Name'] = 'youruser_vanilla';
$Configuration['Database']['User'] = 'youruser_vforum';
$Configuration['Database']['Password'] = 'your_strong_password';On most cPanel and DirectAdmin stacks localhost is correct; if a managed stack uses a socket path or a specific hostname, the account's phpMyAdmin login screen or the MySQL Databases page shows the right value. After a password reset in the panel, the config file still holds the old password until you update it here. If the database user was accidentally removed from the database, re-add it under MySQL Databases → Add User To Database and grant ALL PRIVILEGES.
Pruning expendable rows and rebuilding indexes
With the heavy tables identified, the safe deletions come first. Expired sessions are the easiest win. From the phpMyAdmin SQL tab, remove sessions older than a few days:
DELETE FROM GDN_Session WHERE DateUpdated < (NOW() - INTERVAL 7 DAY);Stale authentication tokens follow the same pattern against GDN_UserAuthenticationToken using its Timestamp column. For the activity feed, trim old entries while keeping recent context:
DELETE FROM GDN_Activity WHERE DateInserted < (NOW() - INTERVAL 180 DAY);The moderation log needs judgement. Keeping recent spam and edit history is useful, but years of resolved entries serve no purpose. The Vanilla Dashboard offers a cleaner path here: go to Dashboard → Moderation → Log (the Spam and Moderation queues), where you can clear resolved items through the interface instead of risking a raw delete. Before any bulk SQL deletion, use phpMyAdmin's Export tab to download a gzip SQL dump of the affected tables so you have a rollback point that respects your account quota.
Once rows are deleted, the freed space and the index statistics both need a refresh. Select the pruned tables under Structure and run Optimize table from the dropdown. This issues OPTIMIZE TABLE, reclaiming the data_free space you measured earlier and rebuilding the indexes so the query planner has accurate cardinality figures. On InnoDB this also defragments the clustered index.
Slow category and discussion listings often trace back to missing or degraded indexes rather than raw size. A stock install indexes the common lookup columns, but a migrated or heavily customized database can lose them. Verify the critical ones exist with:
SHOW INDEX FROM GDN_Discussion;
SHOW INDEX FROM GDN_Comment;Confirm GDN_Comment carries an index on DiscussionID and that GDN_Discussion has one on CategoryID, DateLastComment. If a composite index is absent, add it carefully, matching Vanilla's naming convention:
ALTER TABLE GDN_Comment ADD INDEX IX_Comment_DiscussionID (DiscussionID);Add indexes one at a time on a quiet period, since the ALTER locks the table during the rebuild and a large table can exceed your account's I/O or execution window. If the operation times out in phpMyAdmin, raise max_execution_time for the session by adding max_execution_time = 300 to a .user.ini file in your document root, which the PHP Selector honours for the phpMyAdmin process.
Keeping the database healthy over time
Maintenance that happens once decays quickly, so the goal is reducing how fast the service tables refill. In the Dashboard under Settings → Security and Settings → Statistics, lower session lifetimes where the option exists, and keep a spam plugin such as Akismet or StopForumSpam active so junk registrations never reach GDN_User and bloat the log in the first place. Fewer spam accounts means a smaller GDN_Log and GDN_Activity over the following months.
For scheduled upkeep without root, use cPanel's Cron Jobs (or DirectAdmin's Cron Jobs panel) to hit a lightweight cleanup URL or a small PHP script in your account that runs the session and activity deletes on a weekly schedule. A monthly manual pass through phpMyAdmin to re-run the information schema size query and optimize the top three tables keeps fragmentation from ever becoming visible as slowness. Treat the export step as mandatory before every pruning run; a quota-safe gzip dump stored under /backups in your home directory is cheap insurance against a mistyped WHERE clause.