5 ms·

Related to database indexes, but not the post: a busted database index brought down ticket sales of the 2008 Olympics Games. This was the first time regular p

by wdr1 5y ago

Related to database indexes, but not the post: a busted database index brought down ticket sales of the 2008 Olympics Games. This was the first time regular people could go buy tickets for events & they had been lining up overnight at Bank of China locations through the country. We were down for over a day before we called it off. Apparently this led to minor upheaval at several locations in Beijing & riot police were called in. We were pretty puzzled as we had an index and had load tested extensively. We had Oracle support working directly with us & couldn't figure out why queries had started to become table scans. The culprit? A point upgrade to DBD::Oracle (something like X.Y.3 to X.Y.4) introduced subtle but in character sets. So the index required using a particular Unicode character set, and we were specifying it, but when it was translated into the actually query, it wasn't exactly the right one, so the DB assumed it couldn't use the index. Then, when all the banks opened & a large portion of very populous country tried to buy tickets at the same time, things just melted. Not a fun day.

fatnoah 5y ago
I've never had to deal with a production-breaking issue resulting from this, but I have had to debug a collation-related issue in SQL Server that caused table scans to occur. Fortunately, the SQL profiler made it really obvious that a collation mismatch was the culprit.
mst 5y ago
I love DBD::Oracle so much I've always arranged to have other people test oracle related DBIx::Class features for me. Writing the pre-ANSI-JOIN-support SQL dialect code was pretty good fun though.
hinkley 5y ago
The one that always got us is much more mundane. Deleting a row requiring an index in every table with a FK to avoid tables scans. Near as I can tell, we assume there is some bit of magic built into the foreign key concept that handles this for us, but that is not the case.
- necovek 5y ago
  Foreign keys are naturally referencing primary keys, which have their own (unique) indexes by default. And there's some extra magic with CASCADE. But it seems like I am missing your point: care to expand on it so I can learn about the gotcha as well? What are you attempting to do, and what's making the performance slow?
  - nickkell 5y ago
    If I've understood correctly it's the foreign key column itself that isn't indexed by default. Deleting a record then requires a table scan to ensure that the constraint holds
    - dan-robertson 5y ago
      Example: if you delete a record from the customers table, you want an index on the foreign key in the orders table to delete the corresponding entries. This also means the performance of the delete can have the unfortunate property where the small work of deleting a single row cascades into the work of deleting many rows.
      - hinkley 5y ago
        Which is why many elect to just ban deletes.
        rjzzleep 5y ago
        That’s not the reason. The reason why people don’t delete is because nobody wants to be left with inconsistent data relations. Deleting a customer is more deleting their PII(our scrambling it) and leaving everything else in tact. Or in the case of Silicon Valley, leave everything in tact with a disabled flag and then spam you for the next decade or so.
        tremon 5y ago
taberiand 5y ago
The moral of this story, to me, is always lock in all dependencies to the exact specific versions tested for production release.
path411 5y ago
Can't imagine the pain to even discover this problem. It normally takes me a long time to be like, "maybe I didn't make a mistake, and I have a third party bug?". Third party bugs are always the most annoying to me, usually the longest to diagnose and then ultimately I have to just tell my boss I can't do anything to fix it, but hopefully I can find a way to avoid it.
- Aeolun 5y ago
  Fork and fix? I’ve personally been pleasantly surprised by how fast maintainers will merge a PR I submit. If it causes an issue with our own systems it also gives me a very good justification for doing it.
  - eatonphil 5y ago
    In Oracle's proprietary system?
    - throwanem 5y ago
      Oracle doesn't appear to maintain the DBD::Oracle Perl client module.
  - path411 5y ago
    Sometimes I've had a pr merged. Sometimes I've had stubborn maintainers that think that their project should not follow common domain standards for /shrug reasons. And then many other times I honestly don't have the knowledge to be able to contribute into large third party systems and it seems very rare to get an issue resolved without submitting a pr. And lastly sometimes it's hard to judge if unexpected behavior is really a 'bug'. Similar to the last point, it can honestly be hard to tell the difference sometimes.
- isolli 5y ago
  Imagine having to diagnose a bug in the processor! http://gallium.inria.fr/blog/intel-skylake-bug/ http://gallium.inria.fr/blog/intel-skylake-bug/ "More experienced programmers know very well that the bug is generally in their code: occasionally in third-party libraries; very rarely in system libraries; exceedingly rarely in the compiler; and never in the processor."
superjan 5y ago
At my previous job, a particular database was designed using ascii strings fields for a particular field rather than unicode. If you then query with a string in unicode format, the database decided that the comparison should be done in unicode. The only way was to table scan and convert all ascii fields to unicode on the fly. It was found only in production. Given that you mention China in your story, did GB 18030 have anything to do with your problems? https://en.wikipedia.org/wiki/GB_18030 https://en.wikipedia.org/wiki/GB_18030
- AdrianB1 5y ago
  That sounds like newbie developers wrote that and no DBAs were involved in checking the code, not a database design problem, you are throwing blame without knowing enough about the matter. If your string fit in a varchar, it makes absolutely no sense to change it to nvarchar because your query has unicode as input.
  - xupybd 5y ago
    What? The database made the call to compare the strings as Unicode. Are you accusing the DB developers of not doing their job?
    - AdrianB1 5y ago
      Yes, databases don't decide, developers do.
  - eyelidlessness 5y ago
    I reread the parent quote several times and cannot detect a hint of throwing blame at all.
  - superjan 5y ago
    Most, but not all of the team were young, and the DB schema was designed by themselves. I did not intend to throw blame. It is more the kind of situation where a sensible decision (at first glance) creates a footgun down the road.
wpietri 5y ago
Could you say more about why that was the culprit and not some problem with the load testing? For the Olympics, it seems like the most important test is "What happens when we launch?" That's the kind of thing I'd run on every commit.
- ppp3 5y ago
  an automated system level upgrade in the production env, exactaly 10 sections before launch. load tests usually only happens on staging env, not production, that is the policy.
  - wpietri 5y ago
    In that case, it sounds like a really good reason to question why the staging environment is not enough like prod that load tests are valid. Or why a not-fully-tested system upgrade would happen 10 seconds before launch.
Pamar 5y ago
Had a similar experience (albeit with much minor consequences): an Oracle Portal installation which worked fine in Dev/Test but with degrading performance on the actual Prod server (which was also immensely more powerful than what we used to develop). Oracle was called in, "en masse". Keep also in mind that Oracle Portal was basically running on top of lots of dedicated tables and stored procedures and you could not access the data directly, you interacted to the contents only through vistas, aliases etc. It was indeed due to a smallish version bump. But we had a pretty good Team Leader who had made Oracle declare that the two different version were absolutely compatible and allowing us to write code on the older version. He made them state this in written form, before starting the development so it was later impossible to blame the developers for the problem.

by wdr1 5y ago

fatnoah 5y ago

I've never had to deal with a production-breaking issue resulting from this, but I have had to debug a collation-related issue in SQL Server that caused table scans to occur. Fortunately, the SQL profiler made it really obvious that a collation mismatch was the culprit.

mst 5y ago

I love DBD::Oracle so much I've always arranged to have other people test oracle related DBIx::Class features for me. Writing the pre-ANSI-JOIN-support SQL dialect code was pretty good fun though.

hinkley 5y ago

The one that always got us is much more mundane. Deleting a row requiring an index in every table with a FK to avoid tables scans. Near as I can tell, we assume there is some bit of magic built into the foreign key concept that handles this for us, but that is not the case.

necovek 5y ago
Foreign keys are naturally referencing primary keys, which have their own (unique) indexes by default. And there's some extra magic with CASCADE. But it seems like I am missing your point: care to expand on it so I can learn about the gotcha as well? What are you attempting to do, and what's making the performance slow?
- nickkell 5y ago
  If I've understood correctly it's the foreign key column itself that isn't indexed by default. Deleting a record then requires a table scan to ensure that the constraint holds
  - dan-robertson 5y ago
    Example: if you delete a record from the customers table, you want an index on the foreign key in the orders table to delete the corresponding entries. This also means the performance of the delete can have the unfortunate property where the small work of deleting a single row cascades into the work of deleting many rows.
    - hinkley 5y ago
      Which is why many elect to just ban deletes.
      - rjzzleep 5y ago
        That’s not the reason. The reason why people don’t delete is because nobody wants to be left with inconsistent data relations. Deleting a customer is more deleting their PII(our scrambling it) and leaving everything else in tact. Or in the case of Silicon Valley, leave everything in tact with a disabled flag and then spam you for the next decade or so.
        tremon 5y ago

taberiand 5y ago

The moral of this story, to me, is always lock in all dependencies to the exact specific versions tested for production release.

path411 5y ago

Can't imagine the pain to even discover this problem. It normally takes me a long time to be like, "maybe I didn't make a mistake, and I have a third party bug?". Third party bugs are always the most annoying to me, usually the longest to diagnose and then ultimately I have to just tell my boss I can't do anything to fix it, but hopefully I can find a way to avoid it.

Aeolun 5y ago
Fork and fix? I’ve personally been pleasantly surprised by how fast maintainers will merge a PR I submit. If it causes an issue with our own systems it also gives me a very good justification for doing it.
- eatonphil 5y ago
  In Oracle's proprietary system?
  - throwanem 5y ago
    Oracle doesn't appear to maintain the DBD::Oracle Perl client module.
- path411 5y ago
  Sometimes I've had a pr merged. Sometimes I've had stubborn maintainers that think that their project should not follow common domain standards for /shrug reasons. And then many other times I honestly don't have the knowledge to be able to contribute into large third party systems and it seems very rare to get an issue resolved without submitting a pr. And lastly sometimes it's hard to judge if unexpected behavior is really a 'bug'. Similar to the last point, it can honestly be hard to tell the difference sometimes.
isolli 5y ago
Imagine having to diagnose a bug in the processor! http://gallium.inria.fr/blog/intel-skylake-bug/ http://gallium.inria.fr/blog/intel-skylake-bug/ "More experienced programmers know very well that the bug is generally in their code: occasionally in third-party libraries; very rarely in system libraries; exceedingly rarely in the compiler; and never in the processor."

superjan 5y ago

At my previous job, a particular database was designed using ascii strings fields for a particular field rather than unicode. If you then query with a string in unicode format, the database decided that the comparison should be done in unicode. The only way was to table scan and convert all ascii fields to unicode on the fly. It was found only in production. Given that you mention China in your story, did GB 18030 have anything to do with your problems? https://en.wikipedia.org/wiki/GB_18030 https://en.wikipedia.org/wiki/GB_18030

AdrianB1 5y ago
That sounds like newbie developers wrote that and no DBAs were involved in checking the code, not a database design problem, you are throwing blame without knowing enough about the matter. If your string fit in a varchar, it makes absolutely no sense to change it to nvarchar because your query has unicode as input.
- xupybd 5y ago
  What? The database made the call to compare the strings as Unicode. Are you accusing the DB developers of not doing their job?
  - AdrianB1 5y ago
    Yes, databases don't decide, developers do.
- eyelidlessness 5y ago
  I reread the parent quote several times and cannot detect a hint of throwing blame at all.
- superjan 5y ago
  Most, but not all of the team were young, and the DB schema was designed by themselves. I did not intend to throw blame. It is more the kind of situation where a sensible decision (at first glance) creates a footgun down the road.

wpietri 5y ago

Could you say more about why that was the culprit and not some problem with the load testing? For the Olympics, it seems like the most important test is "What happens when we launch?" That's the kind of thing I'd run on every commit.

ppp3 5y ago
an automated system level upgrade in the production env, exactaly 10 sections before launch. load tests usually only happens on staging env, not production, that is the policy.
- wpietri 5y ago
  In that case, it sounds like a really good reason to question why the staging environment is not enough like prod that load tests are valid. Or why a not-fully-tested system upgrade would happen 10 seconds before launch.

Pamar 5y ago

Had a similar experience (albeit with much minor consequences): an Oracle Portal installation which worked fine in Dev/Test but with degrading performance on the actual Prod server (which was also immensely more powerful than what we used to develop). Oracle was called in, "en masse". Keep also in mind that Oracle Portal was basically running on top of lots of dedicated tables and stored procedures and you could not access the data directly, you interacted to the contents only through vistas, aliases etc. It was indeed due to a smallish version bump. But we had a pretty good Team Leader who had made Oracle declare that the two different version were absolutely compatible and allowing us to write code on the older version. He made them state this in written form, before starting the development so it was later impossible to blame the developers for the problem.