When the first numbers came back from our new dedicated benchmark server, I asked a simple question: is there room left? The category page on MariaDB topped out at 69 requests a second, while a product page on the same box managed 145. A listing shouldn't cost twice as much as a product. So I put a profiler on it, and the answer turned into KahunaCart 1.3.6.
Same server, same store of 5,000 products and 250,000 orders. That category page now handles 189 requests a second. Search on MariaDB went from 75 to 186. Capacity went up on every route we measure, on all three database engines.
What the profiler found
The biggest single cost was hiding in the filter panel. Next to each tick box on a category or search page, the store shows how many products match. To decide which values to offer at all, it also counted every attribute value across the whole catalog, on every request. On MariaDB that was 67 to 70 ms of database work per page, most of the time the page spent in the database. Those counts only change when the catalog does, so now they're kept until it does. The category counts in the navigation got the same treatment.
Smaller things added up:
- Catalog listings pick the page of product ids first and then load only those products. Before, the database joined and sorted every product for each page. The first catalog page's query on MariaDB went from 39 ms to under 10.
- PostgreSQL search ranks from a stored search column instead of re-reading the text of every match. A search for a common word went from 471 ms to under 10.
- Cart recalculation writes only what changed. An unchanged cart used to cost 26 database statements, 9 of them writes. Now it's 14 statements and no writes at all.
- Prices format several times faster, and the plugin does less at the start of every request. The helper functions for PDFs load only when you actually make a PDF, and the notification registry is built the first time something needs it.
The search bug
The biggest jump in search wasn't a slow query. It was a bug, and it could hit a live store.
When the product search index looked out of date (after a deploy or a restore without the data folder, or a bulk import that wrote products directly), the next shopper to search rebuilt the whole index inside their own request. That takes about 13 seconds. Worse, every other search that arrived in those 13 seconds saw the same out-of-date index, started a rebuild of its own and deleted the one in progress. Under steady search traffic they just kept knocking each other over. Over two days of load tests, the benchmark store rebuilt its index 2,367 times.
And it had been running in every benchmark we'd taken. That's why search always looked like the weak spot. It was running during the first 1.3.6 run as well, so we withdrew that run and took a new one after the fix.
Now only one rebuild runs at a time, in the background, into a new file. The old index keeps answering searches until the new one is complete and switched in. If there's no usable index yet, searches fall back to a plain database search, so a shopper never waits on a rebuild. While I was in there, I found search was also asking the index for a highlighted snippet of every match (several hundred for a common word) and then reading only the ids. It asks for the ids alone now.
Search capacity roughly doubled on SQLite (125 to 258 requests a second) and PostgreSQL (73 to 140), and went from 75 to 186 on MariaDB. Search is now strong on every engine.
MySQL and MariaDB search that slowed with every sale
Every product save and every stock movement reindexes that product. On MySQL and MariaDB, the catalog index deleted and re-inserted all of the product's rows each time. But InnoDB keeps deleted rows in a full-text index until somebody optimizes the table, and nobody ever does. One upgrade and reindex left our benchmark store with 30,000 dead entries on 15,000 live rows, which made searches 2 to 10 times slower. A busy store would have got slower with every sale. The index now writes only the rows that changed, and a full rebuild finishes by optimizing the table.
The admin got faster too
Order search on MySQL and MariaDB went from about 260 ms to about 5 ms. It tries an exact order number or email address first, and it stops asking the full-text index about words that almost every order shares, like a common email domain or your order number prefix. Reports are about 30% faster on SQLite and PostgreSQL.
Grav 2.2.3 and faster templates
Grav 2.2.3 ships alongside, and it brings its own share. Grav 2 runs Twig in page content inside a sandbox, which checks every call so nothing dangerous can be done from a page. Theme and plugin templates were paying for those same checks, even though they're code you chose to install. Grav's Twig fork now compiles them without the sandbox checks, and a listing template renders about twice as fast, within about 5% of no sandbox at all. It also moves to Twig 3.30. In a test on PostgreSQL, the Grav update on its own was worth 4 to 9% of capacity, and every Grav site gets it, store or not.
The numbers
Capacity here is the most requests a second a page sustained with 95% of them answered in under 500 ms. Same 8 vCPU server and the same store, KahunaCart 1.3.5 with Grav 2.2.1 against KahunaCart 1.3.6 with Grav 2.2.3:
| Page | SQLite | MariaDB | PostgreSQL |
|---|---|---|---|
| Home | 224 → 352 | 112 → 209 | 127 → 189 |
| Catalog | 171 → 274 | 111 → 191 | 79 → 143 |
| Category | 115 → 252 | 69 → 189 | 74 → 135 |
| Product | 233 → 361 | 145 → 228 | 129 → 154 |
| Search | 125 → 258 | 75 → 186 | 73 → 140 |
Under the normal mixed workload of browsing, carts and checkouts, 95% of catalog pages came back within 64 ms on SQLite (down from 94), 90 ms on MariaDB (from 151) and 104 ms on PostgreSQL (from 175). One request failed out of 73,111 across all three engines. Every route, the full method and every earlier run are on the benchmarks page, and the performance page explains in plainer terms why the store is fast.
Optional: persistent database connections
This one is not in the published numbers. KahunaCart 1.3.6 adds a Persistent Connections setting for stores on MySQL, MariaDB or PostgreSQL, and it's off by default. Turn it on and each PHP worker keeps its database connection open between requests instead of opening a new one every time. A new PostgreSQL connection costs about 7 ms, which is a big slice of a page that otherwise takes a few dozen. In a separate test on the same server, it raised PostgreSQL capacity by 50 to 65%: the home page went from 187 to 306 requests a second and the catalog from 132 to 197.
The catch is that every worker holds a connection even when it's idle, so your database's max_connections has to cover every PHP worker you run, plus the scheduler. That's why it's off. If you run your own database server and know those numbers, I'd turn it on. The setting now lives in Grav's shared database kit, so Forum Pro and Mailroom get the same setting in their next releases.
Updating
Update Grav to 2.2.3 and KahunaCart to 1.3.6 or later (1.3.7 followed the same day with a checkout fix), from the admin or with bin/gpm self-upgrade and bin/gpm update kahunacart. The database migrations run as usual. That's it, nothing else to change.
If you want to see what your own hosting can do, the benchmarking guide shows how to seed a store this size and time it. And if you run the numbers, I'd love to see them on the KahunaCart Discord.
Andy