The alert came in at night. The database behind a client’s website was working far harder than usual, and their hosting provider had found the reason: searches were taking 82 seconds on average to complete, while several bots fired them off one after another. By the next morning, the case was on our desk.
Less than an hour after the ticket was opened, we had replied with the real cause, a measurement that proved it and the exact list of what needed checking in production.
The fault itself is the least of it: two parts of the database that had not understood each other for years, and a flood of searches that made it obvious. What is worth telling is the work, because it is the part of support nobody sees when everything is running.
The first suspect is entitled to an alibi
Every case arrives with a first hypothesis. This one pointed at a specific part of the database, it was reasonable, and it came from the people who know the servers best. The easy thing would have been to tweak that part and wait and see.
We checked it first, and it was fine. We said so in the ticket, along with the reason: touching it was not going to fix anything. The client was spared a pointless change on a live website, with all its cost and risk. That never shows up in a report, and it is support work too.
Don’t touch the crime scene
We investigated in our development environment, with the same code and a copy of the real data. Meanwhile, the website kept serving its visitors without noticing a thing.
There we reproduced the problem, isolated it and tested the fix. The same searches became between 8 and 12 times faster and returned exactly the same results. The second part matters as much as the first, because a very fast search that finds something else is just another fault.
With that proof in hand, we were no longer asking anyone to trust our hunch.
One investigation, one thread
The hosting provider sees its servers and we see the website from the inside. Nobody sees everything from their own side, so a case like this only gets solved if both work together.
This was a good example. The provider had raised the alert in time and with data. In the ticket, we told them what to look at and what they should find if we were right. They checked it in both of their environments and confirmed it. They proposed a controlled test of the fix, and we added how to roll it back if anything went wrong. We also suggested stopping the bots before they reached the database, because the protection in place was not enough.
The client was in that thread from the start. They could see what was happening and who was handling it, and they never had to forward a single email from one provider to the other.
If the answer to all of them is "we restarted it and it works now", the case is still open.
To the end of the trail
Once the bots left, the load went back to normal. For practical purposes, there was no longer an incident, and that is exactly when many of them get closed. The cause was still there, waiting for the next bots.
There was also the question that separates fixing something from understanding it: why did it happen, and how do we stop it happening again? We answered it in the ticket the same day it was raised, after following the trail through the software’s history back to its origin. The culprit had been living in the house for years. It came from the version the website was built with, and we built that website, so we said so plainly.
Knowing the origin gave us what a patch never does: knowing exactly what needs fixing, confirming that a routine update would not have solved it, and being able to guarantee that whatever is created from now on will come out right.
Sentence pending enforcement
As we write this, the fix is going into a controlled test in pre-production. Before taking that step, we had already checked that the change was safe and put in writing what to do if anything went wrong. When it reaches production, it will be measured before and after, because that will be the figure that counts.
To gather all of the above, we have not had to touch production once.
Four questions for your support team
They apply to any incident on your website, whoever is handling it.
Have they explained what is failing, or only what they did to make it stop showing?
Is there a before and after measurement, or just a feeling that it is better now?
Do you know since when it has been happening and why? Without that answer, nobody can tell you whether it will come back.
When the hosting provider and the website team are both involved, do they talk to each other or are you the one forwarding the emails?
If the answer to all of them is “we restarted it and it works now”, the case is still open.
Sources
For anyone who wants to get into the technical detail.
- MySQL, 8.0 Reference Manual, 12.1 “Character Sets and Collations in General”: what a collation is, the set of rules the database uses to compare characters.
- Drupal.org, Search API, issue #3227268, “Search API full text queries do not use an index when used via views due to utf8mb4 / utf8 mismatch on PRIMARY”: the problem in the module and the decision to fix it first for new indexes.
- Search API, commit caff9cac, 2022: the patch, which takes effect when new tables are created.
- Drupal.org, search_api 8.x-1.24: the release published on 7 July 2022 that includes that fix.
- IETF, RFC 9309, “Robots Exclusion Protocol”, 2022: rules for robots are a request that crawlers are expected to honour on their own, not a form of access control.