Two MySQL indexes — a filter 3500× faster in one hour

A heavy Bitrix catalog dies on schedule. The smart filter fires queries at a properties table holding hundreds of thousands of rows, no matching index exists, and the CPU pins to the ceiling. From outside it reads like an attack. Sometimes it is one, but you can’t see it while the access log is off. This case had both causes at once, and both closed inside one billed hour.

Snapshot

The problem

Midday on 30 October, a studio manager pings the support chat: “can you check the site, it’s loading forever or not at all.” It’s the Bitrix store of their end client in a neighboring country. The server is old, on CentOS 7, and not part of the studio’s fleet: the studio touches it only on a direct request.

The studio’s department lead went onto the server himself and hit a wall. The database was eating the processor, and there was nothing to look at: the nginx access log had stopped filling an hour earlier, and the httpd log showed the proxy address instead of real visitor addresses. The studio’s lead engineer for this account answered straight: “I’m on the road, only this evening.”

For a store, “this evening” is a lost sales day. At 13:07 the studio asked: “can anyone take a look?” At 13:13 the project manager pulled in a second engineer. By 13:20 that engineer was already requesting access: an hour and change after the manager’s first message.

Why this is hard

The nightmare for an agency answering to an end client looks exactly like this: the contractor’s one engineer is on a train, the store is down, the server belongs to someone else. No monitoring, no config history, not even a MySQL root password on hand. The password had to be reset along the way. Diagnosis starts blind: the logs are off, and heavy legitimate traffic is indistinguishable from an attack. A one-person contractor is powerless on a day like this, however strong that one person is. The studio covers this with continuity inside the team: one engineer runs the account, but on escalation a second one is in within the hour.

How we did it

1. Turned the nginx access log back on. The log was disabled, with a config comment claiming this was the standard Bitrix setup. Saved disk I/O? Maybe. Blind during any incident? Guaranteed. The engineer restored the line access_log /var/log/nginx/access.log common; in nginx.conf. That one line pays off twice in this case.

2. Found the culprit in the query plan. MySQL profiling showed the brand filter hammering b_iblock_element_property, the standard Bitrix properties table, which here held 530,000 rows. No suitable index existed. The optimizer put the query cost at 538,080, with a run time of 15–30 seconds. Run a few of those in parallel and the CPU sits at 90–100%.

3. Added two indexes.

-- lookup by brand property
ALTER TABLE b_iblock_element_property
  ADD INDEX idx_brand_opt (IBLOCK_PROPERTY_ID, VALUE(10));

-- section-to-element relation
ALTER TABLE b_iblock_section_element
  ADD INDEX idx_section_element_opt (IBLOCK_SECTION_ID, IBLOCK_ELEMENT_ID);

VALUE in the properties table is a text column, so the index is a prefix one: the first ten characters give enough selectivity on a brand while the index stays compact. The query cost dropped from 538,080 to 154, a factor of 3500. Run time: under a second.

4. Said plainly: the CPU is still high. The queries got fast, but the load didn’t leave. Traffic analysis showed a stream that looked legitimate. The engineer saw no signs of a DDoS and offered to add 2 cores to the server.

5. And the re-enabled log paid off a second time. The studio’s department lead, now holding a live access log plus the steer “all the noise is around brands,” found the anomaly himself: the bulk of traffic was hitting a /brands section that no link on the site points to. He blocked the section, and the processor came back to normal. From the chat: “Looks like an attack after all… Blocked it, CPU is back to normal. Thanks for the brands steer and for fixing the logs.” The extra cores were never needed.

The “no DDoS found” call turned out to be premature. That’s the honest part of the story. But a working log plus the brand vector handed the studio exactly the lead it needed to close the attack with its own hands, 26 minutes after our report.

From the manager’s first message to “CPU is back to normal”: 3 hours 10 minutes on the calendar. On the tracker, 1.0 hour: the time the engineer actually spent on the server, and exactly what the estimate said.

Results

In plain terms: the store opens again in under a second, the server can see its own traffic, and the studio holds a tool it used the same day to close an attack itself. The server upgrade already moving through approvals was no longer needed.

Team

• engineer (studio) — MySQL diagnosis, indexes, access log; joined on escalation
• sysadmin-engineer (studio) — lead engineer on the account; on the road that day, back by evening
• Anton Hersun, Xaver Pro — project manager; escalated to the second engineer within 10 minutes

If your Bitrix catalog stalls under filters, or CPU load doesn’t match the traffic, send us the symptoms. We’ll read the query plans, name the bottleneck, and come back with a fixed estimate in hours. The query-plan review is free.

Speed up your catalog →