Category pages from 2,205 ms to 292 ms — and the HTTP 500s stopped.

symptom

About one request in seven came back as HTTP 500 — 825 of them in an hour and a half. The error log said the same thing every time: the store could not open a database connection. The count had been climbing for weeks, from 17 a day at the start of August to 915 on the day of the diagnosis.

diagnosis

The server was 40% loaded and the traffic was even — no attack. The hosting plan allows 30 database connections per user, and the store was running out of them. Every request, bots included, rewrote a row in the session table; at 2.3 GB each write held its connection long enough for the queue to overflow.

The table had grown because OpenCart 3.0.3’s session clean-up compares a number with a date, and that comparison has never matched a single row. Sessions from 2023 were still there.

what was done

1. The database backed up without the sessions, and the live sessions separately.
2. 22.8 million stale rows deleted in batches of 5,000 — 17 minutes, with the store open the whole time.
3. The session clean-up patched so that the database itself computes expiry. PHP and MySQL on this server run three hours apart, and the first version of the fix logged every visitor out on every request. It was caught by comparing session data between two requests — the basket test kept passing.
4. An index added on the category tree, and a modification that stops computing rating, discount and special price for every product of a category when the page is not sorted by them.
5. Static files cached for a month to a year; an SQL-injection scanner that was hammering the store blocked.

what it did not need

No new server or plan — the existing one was 40% loaded. No theme rewrite, no caching plugin.

what is still slow

The category that holds almost the whole catalogue, 24,209 products, still takes about 2.2 seconds. To show 24 books in alphabetical order the database has to sort all of them; the same query without the sort takes 12 ms. That page needs a page cache, not another index. PHP is still 7.2: an encrypted third-party module blocks the upgrade until the host provides its loader for a newer version.