Why a Galera cluster was slower than staging for an online shop
By Krzysztof Książek Case study
Anonymised customer engagement. A real audit, written up with the client's name, people, hostnames, IP addresses, account names and personal data removed or generalised. Measurements are quoted as they were collected during the audit. The engagement ended with a written report.
- Client
- Online retailer, several country storefronts on one Magento 2 installation
- Database
- MariaDB 10.5, three-node Galera cluster on KVM virtual machines (32 vCPU, 128 GB RAM each)
- Question
- Configuration health of the OS, MariaDB and Galera, and why catalogue indexing runs much slower in production than on staging
- Evidence
- System and MariaDB summaries from all three nodes, global status and variables, configuration files, an 80-minute full query log from the active node, a slow log of long-running statements, and a general log of an indexing run on staging
- Deliverable
- Written audit report (about 50 pages) with 18 prioritised recommendations
An online retailer running Magento on a three-node MariaDB Galera cluster asked two questions: is the database configured sensibly, and why does the catalogue indexing process run so much slower in production than on staging? The short answers: the configuration was mostly sound, a handful of missing composite indexes accounted for a large share of query time, and the indexing process was written in a way that suits a standalone server and works against Galera.
The starting point
All three nodes were identical virtual machines. Only one of them was doing any real work: it held about 100 application connections, while the other two had none and simply applied replicated writesets. The team had tried load balancers earlier, had problems with them, and connected the application straight to one node instead.
The workload on that node was overwhelmingly reads:
- ~8,000 SELECT/s on average since restart, and ~12,500/s while data was collected.
- ~60 INSERT/s on average; UPDATE ~15/s; DELETE negligible.
- ~800 internal temporary tables/s, ~175/s of them on disk.
- ~1 InnoDB read from disk per second: the working set fitted in memory.
Two counters stood out. Rows read was far higher than rows sent (about 1.75 million rows read per second against 40,000 sent), which usually means queries examine many rows to return a few. And the server-side handler counters showed a lot of full table scans, index scans and sorting. Both point at query shape and indexing rather than hardware.
Finding 1: a few queries carried most of the load
The full query log was grouped by query fingerprint and ranked by total execution time. The top ten fingerprints fell into three groups.
Missing or imprecise indexes
Scroll horizontally to see all columns when needed.
| Query | Share of total time | What the evidence showed | Recommendation |
|---|---|---|---|
| Cookie-consent lookup by customer and type (third-party GDPR extension) | 13% | Every execution examined ~962,000 rows to return one; average 363 ms. No usable index. | Composite index on (customer_id, type) |
| JavaScript-bundle file lookup (third-party frontend extension) | 8% | ~1,100 executions/s, 13% of all queries. Used a single-column index on the file name; 156.5M rows examined to send 9.4M. | Composite index on (filename, locale), extended later if the ratio stays high |
| Layered-navigation URL key lookup (third-party extension) | 6% | ~230 executions/s. The optimiser combined two single-column indexes with an index-merge intersection. | Composite index on (category_id, store_id); an intersection is never better than one index on both columns |
| Category tree with URL rewrites | 2% | Mostly well indexed; the url_rewrite join could be covered fully. | Composite index on the four url_rewrite columns used by the join |
Admin catalogue search with LIKE '%…%' | 1% | Slowest statements in the log: 4–6 s each, ~168,000 rows examined. | Index on (attribute_id, store_id) in catalog_product_entity_varchar; about 10% faster when tried on staging, to be confirmed in production |
Together the three third-party extension queries accounted for over a quarter of the database’s query time. None of them is unusual — they are the kind of lookup an extension author writes against a small test catalogue and never revisits.
Well-written queries that should not reach the database
Product-page attribute loads, category name lookups and attribute option lists were correctly indexed and examined roughly the rows they returned. There was nothing to gain inside MariaDB; the only lever was caching, because this data changes rarely. The clearest example was a customer-group lookup executed about 1,200 times per second — 14% of all queries — against a table that was empty.
A long-running catalogue-rule refresh
In the slow log, an INSERT … SELECT DISTINCT … ON DUPLICATE KEY UPDATE that rebuilds catalogue price-rule assignments ran 50 times in an hour, 14–15 s each, examining about 493,000 rows per run. It was the largest reader of rows in the slow log. The recommendation was a controlled test: time the inner SELECT with and without forcing the existing index on the rule end-date column, and keep the hint only if it helps.
Finding 2: the indexing process fought Galera
The indexing process was the original complaint: noticeably slower in production than on staging. Staging was a single MariaDB instance; production was a Galera cluster. That difference alone explains part of the gap — every write transaction in Galera is replicated to all nodes and certified before it commits, so writes always cost more than on a standalone server.
The logs showed three things that made it worse:
- Very large transactions. Single statements populating index tables touched around 100,000 rows, and statements creating intermediate tables around 465,000. Galera has to ship, certify and apply each of those as one writeset on every node.
- One thread. The whole process ran sequentially, which is the worst case for Galera: it cannot hide certification latency behind parallel work.
- Repeated work. The slow log contained 54 near-identical intermediate tables created within an hour, differing only in their list of product IDs. Sampled product IDs appeared in up to 43 of the 54 tables.
The recommendation was to make the staging environment a Galera cluster first, so the difference could be measured instead of argued about, and then to restructure the process: read the source data, write it back in batches of a few thousand rows per transaction, and run those batches over several connections, with batch size and concurrency chosen by benchmark. Where repeated intermediate tables exist, maintaining one regular table incrementally should remove most of the duplicated writes.
The report also flagged the cost of triggers (executed per row, about 300 per second on average) and foreign keys on Galera. With the default total-order-isolation method, ALTER TABLE blocks the whole cluster, and foreign keys complicate the usual workaround of online schema-change tools.
Finding 3: configuration — mostly sound, with drift between nodes
A good deal was right and was left alone: swappiness at 1, the mq-deadline scheduler on virtual disks, no network errors, rare deadlocks and row-lock waits, per-session buffers at their defaults, the query cache off, and a sensible Galera base configuration with mariabackup for state transfers.
The changes recommended:
Scroll horizontally to see all columns when needed.
| Area | Observed | Recommendation |
|---|---|---|
| InnoDB buffer pool | 45 GB on 128 GB machines; the server process used 56–67 GB in total | Raise to 75–80 GB, re-check free memory, then consider more |
| I/O capacity | innodb_io_capacity 80,000 with a light write load; dirty pages never above 3% | Start at 2,000 / 4,000 (max) so InnoDB flushes less aggressively and merges repeated changes in memory |
| Flush method | fsync | O_DIRECT, after a test, to avoid double-buffering in the OS page cache |
| Adaptive hash index | Most lookups did not use it | Test with it disabled on a production-like copy with replayed traffic — not in production |
| Node consistency | One node differed in max_allowed_packet (1 GB vs 256 MB); another in optimizer_search_depth, optimizer_use_condition_selectivity, an optimizer_switch flag and the kernel dirty-page ratios | Make all nodes identical, so a failover does not also change query plans and limits |
Finding 4: operational risks
- Disk space. The data volume was 89–91% full on every node. Urgency depended on growth rate, which was not known; the report asked for that to be measured and for a plan to add space or reduce data.
- Version. The MariaDB release in use was behind its own release line and affected by published CVEs, including denial-of-service via crafted SQL. Recommended: the latest patch release of the same line, tested first.
- Accounts. One database account without a password and three with the obsolete password-hash format, on every node.
- No load balancer. With the application pinned to one node, a failure of that node means a manual reconnection. Recommended: revisit HAProxy or, better, ProxySQL for fast failover and, later, spreading reads across the cluster.
The recommendations, in order
- Add the five composite indexes above, starting with the cookie-consent lookup.
- Test the forced-index variant of the catalogue-rule refresh.
- Cache the read-only lookups that are already efficient, starting with the query against the empty table.
- Make the configuration identical on all nodes.
- Assess and address disk usage on the data volume.
- Remove or fix the password-less and old-hash accounts.
- Raise the buffer pool, lower I/O capacity, switch to
O_DIRECTafter testing. - Put ProxySQL or HAProxy in front of the cluster.
- Upgrade to the latest patch release after application testing.
- Turn staging into a Galera cluster and rework indexing into smaller, parallel transactions.
What this case shows
The hardware was not the problem, and neither was Galera as such. Most of the database time went to a small number of query fingerprints, several of them from third-party extensions, and the fix for most was an index or a cache. The slow indexing was a design mismatch: a sequential process issuing very large transactions, measured against a staging environment that could not reproduce the production behaviour. A useful audit names both the cheap fixes and the cases where the right next step is a test environment rather than a configuration change.