How to not gatekeep the database (safely)

​

Mali Akmanalp

​

mali+percona@akmanalp.com

Who am I?

• Mehmet Ali Akmanalp!
• “Mali”
• Hubspot for 4+ years
• Data infra engineer
• Working on our�“internal DBaaS”
• Meandering career before
• Originally from Istanbul, Turkey

HubSpot SQL infra today

• 10k+ MySQL instances (Percona!), grouped in Vitess clusters
• 1500+ product engineers running tens of thousands of microservices
• ~30 database migrations / day
• 1 million+ queries/sec midday mostly on primaries, 50+ billion daily

• … and 12 data infrastructure engineers :-)
• Working on our “internal DbaaS” for Vitess / MySQL
• Our “users” are the product engineers above

What this talk is about

• 1 million queries / sec is not that much
• Our challenge is less “Formula 1” and�more “bumper cars at scale” :-)
• Massive number of different workload types & microservices, variation in teams, best practices, levels of experience
• Cheapest possible hardware & storage, bin-packed & containerized
• Sort of like a DBaaS, but we care what goes on inside too
• Main topic: how do we tame that chaos?
• … but keep a non-restrictive developer experience

What it is NOT about

• The specific infra / software we use
• A declaration that ours is the only way and�that everyone should do the same
• A claim that we’re doing everything perfectly

What we’ll cover

1. Where we started
2. What didn’t work (or kinda worked)
3. What did work
4. What SQL @ HubSpot feels like

Prelude:

Where we started

The olden times

• Circa 2018, we had “legacy”:
• MySQL with puppet
• Deployed on mesos + singularity
• Config management and operations via ansible
• And then we moved to a new setup:
• Vitess + Kubernetes
• KubeCon 2018: “How We Moved Hundreds of MySQL Databases into Kubernetes”: https://youtu.be/ZjTraLkMjYM

​

First, getting the basics under control

• Full layer separation: Kube + vitess / mysql + app
• Without database users knowing or caring - zero downtime:
• Underlying infrastructure / kubernetes nodes change live
• Database software + configuration is upgraded live (Rolling restarts + canary deploys)
• Warming reads on replicas via vtgate (proxy)
• Automated failure mitigation
• Failover via orchestrator
• Chaos / acceptance tests
• Mitigate AZ outages
• Spread replicas across AZs
• Auto-balance primaries between AZs
• Chipped away at critsits and long tail issues related to:
• Low-level infrastructure breaking
• Bugs in orchestration / automation
• Deploying new database config + code

As old issues went away, new ones replaced them …

Have you ever noticed this:

Chillest oncall ever…

==�Company week off

​

(A curious connection?)

Observation:

Majority of critsits now were related to usage

Commonalities: not infra failures

A new class of problems relating to usage

“Bad query” that wreaks havoc

Unexplained timeouts / circuit breakers

Jobs / workers backlogged

Slow, trickling growth and then sudden disaster

Ungated feature takes database down

One way to react to this:�“I’m the captain now”

… but this was a no-go from the start

What didn’t work:

“not our problem”

Who owns this problem?

Database Team

Expertise in finding root-cause workload / query

Doesn’t have product context

Product Team

Expertise in the product they’re building

Trouble finding problematic needle in haystack of queries

Trouble monitoring lurking issues

Shared responsibility!

Unclear boundaries!

“Not our problem”: Postmortem

• Basically handing off full responsibility with 0 support structure
• More charitably: “Interesting, but we don’t have the resources”
• Pros:
• Easy to argue
• Isolates you from responsibility
• Cons:
• No support structure means users will make mistakes
• “It’s not my fault, but it is my problem.”
• Problems start small, but silently grow into larger problems
• When things finally blow up, it might not be an easy fix anymore
• Ultimately people will look to you, as an expert, for systemic solutions
• If you truly follow “5 whys” or “how to make sure the system prevents this from happening again”, this path is nonviable

What kinda worked:

“Best practices”

”RTFM”

“Best practices” / RTFM

• “Go read this whole book” is unfortunately not realistic
• People are generally too busy
• It can be intimidating
• Recent advancements on dev-focused work “use the index luke” and “efficient mysql performance”
• But ultimately the people who read these were already interested
• Internal docs are a bit better in this regard (more on this later)
• This can quickly turn into a form of “not my problem”

“Best practices” works best if …

• Awareness:
• Conceptual: “I should be doing X”
• Situational: “My app is currently not doing X”
• If I don’t know about something or have to be on the lookout myself, then the battle is already lost.
• Ability / Means:
• Lack of tooling or runbooks for observability or fixing �e.g. you know smoking is bad for you, but you can't quit by sheer will
• Understaffed / overburned with project work
• Code red / mainsail preempts project work
• Incentives / priorities:
• "No one gets promoted by / it’s not anyone’s job to improve bad code" is a toxic work environment
• Code red

Documentation: postmortem

• Pros:
• Prevent repeat questions of a factual nature
• Tailored to your internal infra
• Cons:
• Doesn’t work that well for conceptual questions
• People forget to link to it:
• We had a support bot that keyword matches
• It goes stale
• If you can dedicate resources to this - it could be great
• More:
• Thought of doing a “SQL 201” onboarding but never got around to it

Internal tech talks

Internal tech talks: postmortem

• Pros:
• These have much wider and more diverse reach:
• The people who don’t want to read a book about mysql are listening to these
• If you make it engaging, people do sit and listen to something they otherwise would not have
• Noticed improvement in quality of questions / support requests
• Cons:
• It’s work intensive
• It’s hard to consume piecemeal or use as a reference
• It’s harder to convince people to watch after conference time is over
• Though we’ve still racked up thousands of hours of watch time
• Overall:
• OK ROI if you do it once or twice a year to a large audience

Linting, but intentionally

• Intentionally: don’t throw the set of default rules at users
• Usually too restrictive and nitpicky about style but
• SQLCop
• Java build-time query linting via ANTLR parser
• Adding new rules is as easy as adding a class
• Live, centralized list of all DAOs, queries, schemas at HubSpot
• Test / run new rules against every query at HubSpot
• We add rules to direct behavior in specific ways:
• Ban new uses of feature X
• Find existing uses and create campaign to get rid of them

What didn’t work:

white glove service

White glove service

• Pick a database that has been having trouble in an especially recurring / problematic way
• By this point every party is motivated / anxious to collaborate
• Allocate a chunk of time / resources to work with the team
• Meet with the team to discuss issues, understand problems, collaborate on a plan for improvement.
• Form a new slack channel. Direct work, field questions, suggest improvements in one centralized place.
• Sort of like an SRE “embed”

White glove service: Postmortem

• Pros:
• Understanding teams’ largest problems
• Empathizing with their constraints
• Was able to effect larger / systematic improvements
• Cons:
• So …. much … work
• A brand new critical service is born every week: it just won’t scale
• Overall:
• This definitely has its place occasionally, for the truly critical stuff
• Cannot keep up with product / organizational growth

​

​

​

What did work:

Isolation

Isolation

• Full-ish database isolation between microservices
• Boycott database sharing!!! → Lower blast radius
• We actively encourage teams to split databases
• (on hold) Vitess MoveTables
• Truly micro microservices
• It’s as easy to make a new service as to add to an existing one
• Within-database isolation
• (in progress) QoS / load shedding patch for Vitess at the proxy layer
• Pipe in pre-set app priorities

​

​

​

What did work:

Tooling

A brief tour of developer experience today

Cluster Health Dashboard

Interlude: non-classic metrics I really like

• % of total query execution time across entire time period
• “How much CPU time am I spending on this overall”
• “chronic” optimization target if > e.g. 1-10%, don’t bother otherwise
• % total time when qps > 0
• “acute” optimization target - spikes!
• correlate with spikes in resources, e.g. CPU, CPU throttle, queueing
• Rows examined / rows sent ratio charted over time
• “Well-indexedness”
• If this changes over time, query plan changing?
• Avg time spent, charted over time
• If this changes often, figure out why
• If this changes for many fingerprints at once - server issue?

What did work:

Libraries & standardization

Libraries & standardization

• Why does it work? An equitable quid-pro-quo:
• For devs: we give them the lowest path of resistance
• For us: land grab on the area we need
• the most consistency
• the most data on
• the most changes / updates in
• Example incoming …

​

​

​

Libraries & standardization: Queries

​

Libraries & standardization: Migrations

​

migrations.sql:

Libraries & standardization: Demo

​

How does all this help infra?

• Improve usage patterns, performance & reliability
• Circuit breakers
• Guardrails
• warnings for result set size, IN (...) list size
• type checking
• SQL query linting …
• QoS swimlanes auto-marked
• Reasonable defaults (e.g. timeouts)
• Unit tests
• Improve debugging perf issues
• Live client metrics & instrumentation
• Tracing dimensions piped through via driver to logs
• Client side slow query + transaction tracking & logging
• Other bonuses
• Strong auth
• Multiregion discovery
• Upgrades

What did work:

Mass fixes

Mass fixes

• Old school of thought:
• Let teams do it: it splits the work up nicely
• What we found:
• Teams end up re-encountering the same issues over and over
• Even when you document, there is a support overhead
• There’s a burden to chasing down stragglers
• Everyone has some special situation
• Different school of thought:
• Efficient division of resources for a “designed process”
• We do the bits we can & we’re good at
• Teams do the bits they have specific information about that we don’t
• “last few changes” in a PR are hardest for us and easiest for them

Mass fixes: JDBI upgrade

• Huge list of breaking API changes all across our codebases
• Mothership: bulk branch-creation against all repositories at HubSpot
• Creating a branch kicks off build + tests, including database unit tests
• Track build errors via centralized UI
• When ready, mass-send PRs to everyone
• Refactory: java AST rewriting via openrewrite

Mass fixes: Vitess upgrade

• Was very far behind and kept falling behind
• So we jumped 9 versions in one go!
• “This is not supported” :-P
• Blue / green deployment
• Pre-detect incompatibilities
• Mothership + unit tests with upgraded vitess
• Live-ish query replay on an isolated snapshot
• Patched some protocol incompatibilities
• Teams mostly did … nothing!

Mass fixes: Multiregion / VTickets

• When we added EU to go multiregion, all our apps needed globally unique ids
• Instead of making every team at Hubspot change up their IDs, we built a globally unique id service
• Backwards compatible with existing IDs
• Patched into vitess - seamlessly replaces autoincrement
• https://product.hubspot.com/blog/our-journey-to-multiregion-vtickets

What did work:

Reaching out early and often

A qualitative scale of capacity trouble

Everything is great

OK, some slow queries

Mostly OK but growing fast

Early signs of trouble

Ongoing trouble

Either really small and simple db, or a team that’s really on top of things

Most keyspaces are like this: no need to chase after minor things

​

Nothing wrong for a while, and then suddenly … problems

Early signs of trouble: mild throttle + lag not enough to page, intermittent errors, support reqs asking why

Ongoing major trouble:

Pages, emergency support reqs, critsits, white glove service

Level 0

Level 1

Level 2

Level 3

Level 4

(i.e. how close is your “butt to the fire”)

A tale as old as time

• Team had a critsit related to a query missing indexes
• It wasn’t that bad until it started getting called a lot
• Result
• Lengthy degradation: index can’t be added quickly, scaling doesn’t help, traffic can’t be stopped entirely
• Multiple engineers involved
• How early I could detect small signs that something was off by looking at charts
• 2 weeks! - clearly something missing
• How early we should have intervened
• Certainly as early as I could see on the charts
• Or better: when the query was introduced (more on this at the end)

How does our tooling serve these use cases?

Everything is great

OK, some slow queries

Mostly OK but growing fast

Early signs of trouble

Ongoing trouble

Level 0

Level 1

Level 2

Level 3

Level 4

What the future might hold

• New challenges:
• “Slow” from a user perspective –> tail latencies hiding in aggregates?
• Making yet another invisible layer of issues visible, quantifiable, and tacklable
• Inducing (well-)indexedness from the start
• We already integrate into PRs
• We already parse schemas + queries
• “Well-indexed” is hard to to define fully but easy enough to start on
• Rick James tried: http://mysql.rjweb.org/doc.php/index_cookbook_mysql
• Awareness is half the battle
• We can detect when a query has been deployed
• Post deploy analytics in your weekly ops review
• Maybe pre-tag a query with the intended index and measure stats
• Actionable-only query notifications (next slide)

​

​

​

Conclusion?

TL;DR: How to get humans to adopt desirable behaviors?

​

• Libraries & tooling: Make it easy, pleasurable even, to do things the right way
• Guardrails: Make it hard to make a bad mistake
• Notifications: If you want them to consider something, you must keep it top of mind
• People can’t know to do something if you’re not telling them
• They’re busy with other things too
• Avoiding overload: focus on actionable + significant
• Finally, sometimes just do the work yourself if it makes sense
• Overall:
• Huge spillover benefits from helping devs help themselves
• … including more time to help yourself help yourself

​

​

​

​

The small print

• It’s … a lot of work - we did not get there overnight
• We lean constantly on myriad tools that other infra teams built: infra orchestration, monitoring + alerting, metrics, logs, libraries, auth + security, frameworks, dev tools, CI/CD …
• I want to acknowledge that a lot of this works because of:
• Extraordinary investment in infra of all kinds that cross-leverage
• Organizational buy-in on the importance of performance & reliability
• If this wasn’t the case, it’d be healthier to have far less ambitious goals

How to get started

• Take an expansive view of your domain & staff accordingly
• E.g. hire your most vocal users / engineers (not dbas)
• Start with core building blocks that have repeat value, e.g.
• E.g. who owns what database + programmable channel to contact
• Then try stuff that helps users help themselves
• It feels good: one less support ticket, one less critsit
• Then start carving out responsibilities to hand off explicitly
• Support framework: combo of docs + tooling + guardrails
• Start with the simplest useful solution that works, but don’t be afraid to discard and switch fast
• Runbooks -> Runbook generation / templating -> Automation
• Anemometer -> Slow query UI -> Query report -> ?? PMM + mods ??

Questions, comments, nice words:

mali+percona@akmanalp.com

Thank you