Clear Persistent Session and Cache Bloat

AbanteCart 1.4.4 introduced persistent shopping data, moving cart and session state into the database. Over time, abandoned checkouts and expired sessions bloat the ac_shopping_sessions table, inflating database size and degrading query performance. Similarly, if your store uses database caching, the ac_cache table accumulates tens of thousands of rows.

To clear this data safely from the control panel, navigate to System > Cache and click Clear Session Data and Clear All Cache. If your database is already too bloated and the control panel times out, execute the following SQL commands via phpMyAdmin or the MySQL CLI to instantly reclaim space:

TRUNCATE TABLE ac_shopping_sessions; TRUNCATE TABLE ac_cache;

Note: If you specified a custom table prefix during installation, replace ac_ with your exact prefix.

Move Cache Off the Database Engine

Using MySQL or MariaDB as a cache backend increases transaction overhead and rapidly exhausts CloudLinux LVE database connection limits during traffic spikes. Offloading cache to memory reduces database query volume significantly.

Log in to the AbanteCart admin panel and navigate to System > Settings > System. Locate the caching method setting and change it from Database to APCu or Memcached. If your hosting environment lacks memory-based caching extensions, select File-based Caching as a fallback. After applying this change, verify that the ac_cache table is no longer actively receiving new rows.

Optimize Fragmented Database Tables

Executing a TRUNCATE or DELETE command on massive tables leaves behind fragmented data files on the storage drive. Rebuilding the tables reclaims this unused disk space and reorganizes internal indexes, resulting in faster read operations.

You can optimize the entire AbanteCart database using SSH or the terminal feature in cPanel or DirectAdmin:

mysqlcheck -o -u your_db_user -p your_db_name

If you prefer running a direct SQL command for the most active tables, use:

OPTIMIZE TABLE ac_shopping_sessions, ac_cache, ac_products, ac_customers;

Diagnose Slow Queries from Third-Party Extensions

AbanteCart core queries are highly optimized, but third-party modules—especially those handling custom product filters, analytics, or complex pricing rules—often lack proper table indexes. When these unindexed queries scan the entire ac_products or ac_orders tables, database CPU utilization spikes.

If you have root access to a VPS or dedicated server, temporarily enable the MySQL slow query log to isolate the offending queries by setting the threshold to 2 seconds:

SET GLOBAL slow_query_log = 'ON'; SET GLOBAL long_query_time = 2;

For shared hosting accounts, review the CloudLinux MySQL Governor statistics in cPanel or DirectAdmin to identify persistent resource spikes. Once you identify the slow query, you can add missing compound indexes to the extension's database tables. Be aware that extensions relying heavily on AbanteCart's Dataset API abstract their data rather than using strict columns, meaning complex searches on dataset fields are inherently slower and harder to index.

Resolve Database Connection Errors and LVE Limits

If your store intermittently displays database connection errors during peak hours, it is likely hitting the Entry Processes (EP) or MySQL concurrent connection limits enforced by CloudLinux LVE. A high volume of slow queries or unoptimized database caching forces PHP workers to stay active longer, exhausting the available connection pool.

In cPanel (via Resource Usage) or DirectAdmin (via Resource Limits), check if the account is maxing out CPU or I/O limits. If I/O limits are being throttled, database read/write operations queue up and eventually drop connections. To mitigate this without upgrading your hosting plan, ensure OpCache is enabled for PHP 8.2 or 8.3, enforce strict pagination limits on storefront category pages, and configure AbanteCart to use APCu caching.