5 ms·

> At 00:08 UTC, our on-call engineer had been paged and had responded. At 00:10 UTC, we linked the API degradation to the removal of the index. I'm sure stripe

by vruiz 11y ago

> At 00:08 UTC, our on-call engineer had been paged and had responded. At 00:10 UTC, we linked the API degradation to the removal of the index. I'm sure stripe has very good metrics of all their systems, nonetheless that's some rockstar level debugging skills.

kzhahou 11y ago
Well the DB admin had just removed the index a few minutes before, and immediately the API times spiked. When you remove an index there is exactly one risk --slowdown-- and that's precisely what the admin noticed.
- vruiz 11y ago
  That might be it, but by "on-call engineer" I understood it was someone else.
  - kzhahou 11y ago
    Sure. I picture it as: "Hey {on-call-engineer}, we're seeing a huge slowdown all of the sudden." "Was there any change in the last few minutes?" "We just deleted a database index." "Yup, that'll do it. Gotta restore it."
thezilch 11y ago
I'd hope one of the first things for DB slow down is the running queries (eg. `show processlist` in flavors of MySQL) and seeing the number of rows examined. Maybe your DB doesn't have that, so you see hopefully see long running queries and they are mostly the same one, or you can see the same queries in your DB's engine status (eg. `show innodb status` in MySQL). If you don't have rows examined from earlier, you can run whatever equivalent you have of `explain` on these long running queries that are happening a lot, and now you should find the number of rows it is examining (and probably that it is running a scan query and not indexed). I mean, you only get these scars from actually working with your DB and hopefully in development / capacity planning stages, so maybe that makes you a rockstar. Not to take anything away from the Stripe engineer(s).
protomyth 11y ago
We had a utility I wrote at one of the places I worked where you ran it against the database and it showed you all of the query plans running. It was easy with 15 seconds to see some non-indexed query and what was executing it. We used to run it after new version deployments to see if we had query problems. Tooling is very important.
- shazow 11y ago
  That sounds really handy, any chance it's publicly available or you can make it so?
  - protomyth 11y ago
    No, I couldn't release it due to not having the source or being in a position to ask permission. It was for Sybase but the same thing can be achieved in MS SQL by reading some system tables. It really wasn't that hard of a thing to write.
- perlgeek 11y ago
  As a stopgap solution, enabling the slow query log can also be very helpful.
  - protomyth 11y ago
    Yep. A lot of databases have some form of system tables that can tell you a lot about a running system with just some queries. I know Sybase and MS SQL server have the tables to find all the current queries and what they are doing. Ingress had a neat graph (seeing FSM should illicit a panic in most DBAs) and a log monitor. Read up on what is part of the database and write some tools.

by vruiz 11y ago

kzhahou 11y ago

Well the DB admin had just removed the index a few minutes before, and immediately the API times spiked. When you remove an index there is exactly one risk --slowdown-- and that's precisely what the admin noticed.

vruiz 11y ago
That might be it, but by "on-call engineer" I understood it was someone else.
- kzhahou 11y ago
  Sure. I picture it as: "Hey {on-call-engineer}, we're seeing a huge slowdown all of the sudden." "Was there any change in the last few minutes?" "We just deleted a database index." "Yup, that'll do it. Gotta restore it."

thezilch 11y ago

I'd hope one of the first things for DB slow down is the running queries (eg. `show processlist` in flavors of MySQL) and seeing the number of rows examined. Maybe your DB doesn't have that, so you see hopefully see long running queries and they are mostly the same one, or you can see the same queries in your DB's engine status (eg. `show innodb status` in MySQL). If you don't have rows examined from earlier, you can run whatever equivalent you have of `explain` on these long running queries that are happening a lot, and now you should find the number of rows it is examining (and probably that it is running a scan query and not indexed). I mean, you only get these scars from actually working with your DB and hopefully in development / capacity planning stages, so maybe that makes you a rockstar. Not to take anything away from the Stripe engineer(s).

protomyth 11y ago

We had a utility I wrote at one of the places I worked where you ran it against the database and it showed you all of the query plans running. It was easy with 15 seconds to see some non-indexed query and what was executing it. We used to run it after new version deployments to see if we had query problems. Tooling is very important.

shazow 11y ago
That sounds really handy, any chance it's publicly available or you can make it so?
- protomyth 11y ago
  No, I couldn't release it due to not having the source or being in a position to ask permission. It was for Sybase but the same thing can be achieved in MS SQL by reading some system tables. It really wasn't that hard of a thing to write.
perlgeek 11y ago
As a stopgap solution, enabling the slow query log can also be very helpful.
- protomyth 11y ago
  Yep. A lot of databases have some form of system tables that can tell you a lot about a running system with just some queries. I know Sybase and MS SQL server have the tables to find all the current queries and what they are doing. Ingress had a neat graph (seeing FSM should illicit a panic in most DBAs) and a log monitor. Read up on what is part of the database and write some tools.