Configuring MySQL query cache: where speed is lost
A catalog with filters, cart, and customer account on 1C-Bitrix hit the database on every request: selection by properties, stock, prices, discounts, order history.
With a live catalog, this means dozens of similar queries per page, and each one goes through query parsing, plan building, and disk reads again.
While traffic is low, response time stays normal—at peaks, the catalog response time grows by multiples, even though the data in the database has not changed.
Part of this load gives nothing: identical queries to reference lists, offers, and settings are executed repeatedly, and pages are rebuilt instead of serving a ready result.
MySQL query cache was created exactly for such repeats—a ready result set is served from memory without accessing tables.
Configuring MySQL query cache without analyzing the query picture does not remove the problem.
If everything gets into the cache, it becomes fragmented and gets in the way more than it helps, and any write to a table invalidates all its cached results—with an active catalog, this happens constantly.
This is also where slow Bitrix queries come from: missing indexes for specific filters, queries on unindexed properties, heavy table joins.
The growing load on a site's database almost always results from several causes at once, and they need to be addressed together.
We explain how this is done: we collect query statistics, separate repeated queries from heavy ones, and configure cache and indexes for actual query patterns.
What a configured query cache provides: response time and load
The query cache serves a ready result if a query with the same parameters arrives—the database does not parse the text again or walk the tables.
In 1C-Bitrix this works together with component and page cache, so the layers are configured consistently: if one invalidates the other, there will be no gain.
Page assembly time
The catalog, product cards, and sections are assembled from dozens of similar database calls.
When they are covered by a ready result, rebuilding the page speeds up many times over, and the response variance between the first and subsequent requests narrows—the site responds predictably, not "every other time."
Share of queries served from a ready result
The effect is measurable: we separately count how many requests were covered by cache and how many went to a table query.
For a working catalog, the bulk of repeated queries should not reach the database—this is what reduces the load on MySQL, freeing resources for what cannot be cached: the cart, checkout, and stock.
Peak hours and the class of failures
During promotions and sales, the number of concurrent visitors jumps, and an unconfigured cache turns into a connection queue. With cache, the database stops being a bottleneck: an entire class of failures is removed—connection exhaustion, timeouts, page-serving errors. The peak load of an online store no longer requires a multiple hardware reserve.
Configuration is not a one-time task: we verify invalidation rules, record lifetime, and cache warm-up after deploying an update with measurements before and after—otherwise stability under load lasts only until the first major catalog update.
Comparison of approaches to query caching in MySQL
Query caching breaks down into three layers: the database engine, managed cache with tag-based invalidation, and application-side cache.
The choice of layer determines not so much the serving speed as the accuracy and speed of invalidation when data changes, and this determines whether a visitor sees the current price and stock.
MySQL's built-in query cache serves the result without re-parsing the plan, but invalidates everything accumulated for the whole table: editing one row in the catalog zeroes out the entire set.
In current MySQL branches this mechanism has been removed, so for 1C-Bitrix projects it is not considered as the main caching mode—it remains only in legacy environments with mostly read load.
| Option | When it fits | Limitations |
|---|---|---|
| Built-in cache on the database side | Read queries, rare writes, a single server | Invalidation across the whole table on any write; absent in current MySQL branches |
| Managed cache with tag-based invalidation | Components and information blocks with explicit dependencies: catalog, sections, detail pages | Requires correct tagging; an incorrect tag causes a miss and an extra group invalidation |
| Application-side cache | Computed queries, aggregates, heavy joins, reports | No automatic invalidation—event-based reset; risk of serving stale data |
The working option is a combination: tags cover content components, the application handles aggregates, and the built-in database cache remains auxiliary. We select the set based on measurements for your load profile.
Cache configuration stages: diagnostics, design, verification
Changes to cache parameters without preliminary measurements give unpredictable results: the bottleneck is often one heavy catalog query, not the buffer size. That is why we follow a fixed order—from the query profile to the caching design and only then to configuration changes.
- Slow query audit. We enable the slow query log and capture a profile on real traffic: which queries hold the connection, how many times they repeat, and where an index is missing.
- Caching design. We map out what is covered by platform tools at the component and information block level, and what is covered by server cache; we define record lifetime and invalidation points.
- Configuration changes. We configure query cache parameters and missing indexes, and disable caching for queries that change on every page view.
- Catalog cache warm-up. After clearing, we crawl sections and product cards with a crawler—heavy queries are assembled in advance, not on the first visitor.
- Load verification. We repeat the profile and compare: heavy query time, number of database calls per page, and catalog response time under concurrent visitors.
- Cache maintenance procedure. We hand over a document: what we monitor, under which content changes we invalidate the cache, and how we roll back parameters if response time degrades.
Configurations, caching designs, and procedures
Documentation is not written "for a checkmark"—it must still work six months later, when the client's team has a new developer and it is necessary to understand why a block serves stale data.
That is why the result of the work is a package that lets a new engineer enter the project without digging through chat history, and an on-call engineer understands what to do when something deviates.
- Configuration files with actual values: what is enabled, which limits are set and why exactly so, which parameters were touched, and what effect that produced.
- Map of caching and invalidation zones: for each block—what is cached, which tags it is marked with, which event invalidates it, and with what delay.
- Diagram of relationships between components: where cache is shared, where it is isolated, and which blocks depend on the same data and are invalidated together.
- Cache warm-up procedure: the order of crawling pages after a release and clearing, so as not to send load to the database in one burst.
- Monitoring procedure: which metrics we look at, which values we consider normal, at which deviations we intervene, and what we check first.
- Developer memo: how to add a new block to the cache, which tags to set, which constructs break invalidation and why.
- Handover procedure: a walkthrough with your team, answers to questions for each zone, and checkpoints showing that the settings have taken hold.
All of this lives next to the code in version control, not in a separate folder on disk. Changes to the cache configuration go through normal code review—the procedure does not become outdated a month after project delivery.
Case study: the catalog tripled, response time stayed the same
The catalog grew from about ten to thirty thousand items, and section response time began to degrade on every navigation: some pages were rebuilt even though the data had not changed between requests.
The list and detail card components did not have correct cache tags, so any price or stock update invalidated the entire layer, and filter and sorting queries bypassed the MySQL query cache.
We started the analysis with the slow query log and execution plans—we found queries on unindexed sorting fields that ran on every request.
The product list was switched to a composite index and paginated output without recalculating the total count, and component cache was enabled with separate tags for the information block, section, and product card: editing one product now invalidates only related nodes, not the entire catalog.
| Metric | Before | After |
|---|---|---|
| Median catalog section response time | 2.4 s | 0.7 s |
| Database queries per catalog page | 310 | 40 |
| Share of queries served from cache | 21% | 88% |
| Pages served without assembly | 12% | 74% |
| Concurrent database connections at peak | 240 | 90 |
We separately configured layer lifetime and background warm-up: the tagged cache mechanism of 1C-Bitrix made it possible to invalidate only the affected nodes, and warm-up restored current pages before traffic arrived.
Peak load stopped hitting the database limit, and the catalog can now be expanded further without rebuilding the caching design—we verify this behavior on every release.
How query cache differs from page cache
A ready HTML page and the result of an individual query are two different caching levels in Bitrix. The page layer serves an already assembled response, and the visitor does not wait for components to run.
The query layer works lower: it holds the result of a specific database call, so identical queries are not executed repeatedly, and these mechanisms must not be confused during configuration.
Cache lifetime is set separately for each layer. Product descriptions, catalog sections, and reference lists change rarely—they are kept for hours.
Prices and stock depend on warehouses and are updated constantly, so their lifetime is short; otherwise a customer will place an order for something that is already unavailable.
The second lever is event-based cache invalidation. Tags are tied to a query: product, section, warehouse. When data changes, tagged records are invalidated immediately, without waiting for expiration.
This removes the main trade-off: there is no need to choose between freshness and speed—content is cached for a long time, while dependent blocks are updated in a targeted way.
MySQL's built-in query cache is considered deprecated in current branches, so the results of heavy queries are kept in external storage, and the composite layer serves ready pages to anonymous visitors.
This way, the currency of prices and stock is ensured by an event, not by a short lifetime for the entire cache.
Before configuration, we measure catalog behavior: how many queries are wasted and what happens when a product is updated. These numbers show where cache helps and where it only masks the bottleneck.
Technical questions asked before kickoff
Before work begins, clients ask the same technical questions—and these are the right questions. We answer them before the estimate so that the scope of work is clear in advance, rather than being clarified during the process.
Separately about the database: the built-in query cache in current MySQL branches is disabled by default, and there is no point in turning it back on—the gain comes from working with indexes and heavy queries. The bottleneck is determined by measurements, not guesses.
Cache is enabled, but the site is still slow—why?
An enabled cache does not mean a working one: some pages have a short lifetime, some are invalidated on almost every request. We calculate the hit rate and look at which queries go to the database—the bottleneck is often in heavy catalog queries, not in the cache itself.
How not to show stale prices after import?
We invalidate by events: editing a price or product clears only related branches, not the entire cache. We separate lifetimes—prices and stock update often, descriptions and photos rarely, so stale data simply is not shown.
Does component code need to be modified?
Changes to the core and standard components will be overwritten by the first update. We work through inherited components, templates, and event handlers: the caching logic is preserved, platform updates proceed smoothly, and nothing is lost when transferring to another site.
How to tell whether the database or templates are slow?
We measure three segments: database queries, template assembly, and external calls. A fast database with slow assembly means the issue is in templates and extra calls; the opposite picture means it is in queries and indexes. We fix what is actually slow.
What happens to cache during synchronization with 1C?
Exchange runs in batches; after each one, catalog data changes: without invalidation, some cards remain old. We invalidate cache when the exchange completes, but not entirely—otherwise the site is rebuilt after every pass and holds load constantly.
Let's discuss the task and come back with an estimate and plan
To make the conversation specific rather than "we'll take a look and tell you," we start with symptoms and access.
Describe what exactly is slow: catalog and filters, cart and checkout, the admin area, or exchange with 1C—and during which hours the degradation is most noticeable. This is already enough to suggest where query cache is not working at all or is invalidated too often.
What is needed to start:
- Access: hosting control panel, web server configuration, server and database access, project repository.
- Snapshot of the current state: caching parameters, configuration files, slow query log.
- Description of symptoms: specific pages, scenarios, and hours when the site responds slowly.
- Work window: agreed time for changes and service restarts without losing orders.
- Contact of the on-call engineer who will confirm actions in production.
In response, we send a caching work plan: what we change in the settings, which queries we rewrite, how we separate cache layers, and what we do with invalidation when the catalog and prices are updated.
The estimate comes together with the order of stages—from diagnostics to rollout, with a rollback point at each step.
Describe your site acceleration task—we will come back with an estimate, plan, and expected metric changes: response time, share of queries served from cache, and disk load.