TL;DR: Databricks engineers, by leveraging huge knowledge analytics instruments like Databricks merchandise, shifted from reactive monitoring to a proactive scoring mechanism to drive database finest practices. This considerably improved database utilization effectivity by figuring out and resolving problematic queries and schema definitions earlier than buyer impression. As an illustration, one database dealt with a 4X site visitors improve whereas consuming fewer sources (CPU, reminiscence, and disk) as a consequence of optimized database effectivity pushed by the scoring mechanism.
At Databricks, our merchandise depend on 1000’s of databases throughout a number of clouds, areas, and database engines, supporting numerous use instances like consumer account metadata, job scheduling, and knowledge governance. These databases energy dependable transactions (e.g., atomic updates to consumer permissions) and quick lookups (e.g., retrieving Genie conversations). Nonetheless, this scale and selection, mixed with a multi-tenant structure the place buyer workloads are effectively managed on shared infrastructure, creates important reliability challenges. Inefficient queries or suboptimal schemas could cause latency spikes or lock competition, impacting many customers.
On this weblog put up, we provide an in-depth exploration of how our engineering staff at Databricks embraced a data-driven mindset to remodel our method to realize database reliability. We’ll begin with the normal reactive monitoring strategies we used and their limitations. Then, we’ll focus on how we launched client-side question tracing the place the question logs are ingested into Delta Tables and permit us to do versatile aggregation to achieve insights for database utilization throughout incidents. From there, we’ll dive into the proactive Question Scorer constructed into our Steady Integration (CI) pipeline to catch points early. The question patterns recognized by CI are exported as JSON and processed in notebooks, and Spark jobs be part of all the things to compute metrics at scale (keep in mind, the metrics span 1000’s of databases and 10s of 1000’s of queries). Lastly, we’ll describe how all these items come collectively in a unified Database Utilization Scorecard in our AI/BI dashboards that guides groups towards finest practices. All through, the theme is a shift from reactive firefighting to proactive enforcement. Our journey not solely improves the reliability of our personal platform—it additionally showcases how different groups can use the analytics instruments to implement an identical “Scorecard” pipeline to watch and optimize their very own methods. Whereas we selected Databricks for its seamless integration with our infrastructure, this technique is adaptable to any strong analytics platform.
Authentic Reactive Method – Server-Aspect Metrics
Within the early days, our method to database points was largely reactive. When a database incident occurred, the first instruments we used had been Percona Monitoring and Administration and mysqld-exporter, each primarily based on MySQL Efficiency Schema. These offered insights contained in the database server: we might see issues just like the longest-running queries, the variety of rows scanned by totally different operations, locks held, and CPU utilization.
This server-centric monitoring was invaluable, nevertheless it had important limitations. Shopper context was missing: the database would inform us what question was problematic, however not a lot about who or what triggered it. A spike in load may present up as excessive CPU utilization and a rise in a sure SQL assertion’s execution rely. However with out more information, we solely knew the symptom (e.g., “Question Q has a 20% load improve”), not the basis trigger (“Which tenant or characteristic is immediately issuing Question Q extra ceaselessly?”). The investigation usually concerned guesswork and cross-checking logs from numerous providers to correlate timestamps and discover the origin of the offending question. This could possibly be time-consuming throughout an energetic incident.
Refined Reactive Method – Shopper Question Tracing
To deal with the blind spots of server-side monitoring, we launched client-side question tracing. The concept is easy however highly effective: every time an software (database consumer) in our platform sends a SQL question to the database, we tag and log it with extra context equivalent to tenant ID, service or API identify, and request ID. By propagating these customized dimensions alongside every question, we acquire a holistic view that connects the database’s perspective with the applying’s perspective.
How does this assist in follow? Think about we observe by way of the database metrics that “Question Z” is immediately sluggish or consuming a number of sources. With consumer tracing, we will instantly ask: Which consumer or tenant is chargeable for Question Z? As a result of our purposes connect identifiers, we would discover, for instance, that Tenant A’s workspace is issuing Question Z at X load. This turns a imprecise statement (“the database is beneath excessive load”) into an actionable perception (“Tenant A, by way of a particular API, is inflicting the load”). With this information, the on-call engineers can quickly triage—maybe by rate-limiting that tenant’s requests.
We discovered that consumer question tracing saved us from a number of difficult firefights the place beforehand we relied solely on international database metrics and needed to speculate concerning the root trigger. Now, the mix of server-side and client-side knowledge solutions essential questions in minutes: Which tenant or characteristic brought on the question QPS to spike? Who’s utilizing essentially the most database time? Is a selected API name chargeable for a disproportionate quantity of load or errors? By aggregating metrics on these customized dimensions, we might detect patterns like a single buyer monopolizing sources or a brand new characteristic issuing inherently costly queries.
This extra context doesn’t simply assist throughout incidents—it additionally feeds into utilization dashboards and capability planning. We are able to observe which tenants or workloads are the most popular on a database and proactively assign sources or isolation as wanted (e.g., migrating a very heavy consumer to their very own database occasion). Briefly, instrumentation on the software stage gave us a brand new dimension of observability that enhances conventional database metrics.
Nonetheless, even with quicker diagnostics, we had been nonetheless usually reacting to points after they occurred. The following logical step in our journey was to stop these points from ever making it to manufacturing within the first place.
Proactive Method: Question/Schema Scorer in CI
Simply as static evaluation instruments can catch code bugs or model violations earlier than code is merged, we realized we might additionally analyze SQL question and schema patterns proactively. This led to the event of a Question Scorer built-in into our pre-merge CI pipeline.
Develop Lifecycle for SQL Question; Scorer flag anti-patterns early within the improvement cycle, the place they’re best to repair.
Every time a developer opens a pull request that features updates to SQL queries or schema, the Question and Schema Scorer kicks in. It evaluates the proposed modifications towards a set of best-practice guidelines and identified anti-patterns. If anti-patterns are flagged, the CI system can fail the take a look at and supply actionable ideas for fixes.
What sorts of question and schema anti-patterns can we search for? Over time, we’ve constructed up a library of anti-patterns primarily based on previous incidents and basic SQL information. Some key examples embrace:
- Unpredictable execution plans: Queries that would use totally different indexes or plans relying on knowledge distribution or optimizer whims. These are time bombs—they may work positive in testing however behave pathologically beneath sure circumstances.
- Inefficient queries: Queries that scan much more knowledge than wanted, equivalent to full desk scans on massive tables, lacking indexes, or non-selective indexes. Or the overly complicated queries with deep nested sub-queries could stress the optimizer.
- Unconstrained DML: DELETE or UPDATE operations with no WHERE clause or ones that would lock whole tables.
- Poor Schema Design: Tables missing major keys, having extreme/duplicate indexes, or utilizing outsized BLOB/TEXT columns, which trigger duplicate knowledge, sluggish writes, or degraded efficiency.
An instance “Time Bomb” Sql Question
| SQL Question | Desk Definition |
DELETE FROM t WHERE t.B = ? AND t.C = ?; |
CREATE TABLE t ( A INT PRIMARY KEY, B INT, C INT, KEY idx_b (B), KEY idx_c (C) ); |
We name this question anti-pattern “A number of Index Candidates” sample. This arises when a question’s WHERE clause could be glad by multiple index (idx_b and idx_c), giving the question optimizer a number of legitimate paths for execution. For instance, from above , each idx_b and idx_c might probably be used to fulfill the WHERE clause. Which index will MySQL use? That will depend on the question optimizer’s estimate of which path is cheaper—a choice which will differ as the information distribution modifications or if the index statistics grow to be stale.
The hazard is that one index path could be considerably costlier than the opposite, however the optimizer may misestimate and select the improper one.
We truly skilled an incident the place the optimizer chosen a suboptimal index, which resulted in locking a whole desk of over 100 million rows throughout a delete.
Our Question Scorer would block queries that aren’t plan-stable. If a question can use a number of indexes and there isn’t any clear, constant plan, it is flagged as harmful. In these instances, we ask builders to explicitly implement a known-safe index utilizing a FORCE INDEX clause, or restructure the question for extra deterministic habits.
By imposing these guidelines early within the improvement cycle, we have considerably lowered the introduction of latest database pitfalls. Engineers obtain instant suggestions of their pull requests in the event that they introduce queries that would hurt database well being—and over time, they be taught and internalize these finest practices.
Unified Database Utilization Scorecard: A Holistic View
Catching static anti-patterns is highly effective, however database reliability is a holistic property. It isn’t simply particular person queries that matter—it’s additionally influenced by site visitors patterns, knowledge quantity, and schema evolution. To deal with this, we developed a unified Database Utilization Scorecard that quantifies a broader set of finest practices.
Our philosophy of filling the database effectivity jar – Rocks are Massive and Vital duties, Sand is small and fewer vital duties and pebbles are the duties in between.
How can we compute this rating? We combine knowledge from all phases of a question’s lifecycle:
- CI Stage (Pre-Merge): We ingest all of the queries/schema and their Anti Patterns into delta desk
- Manufacturing Stage: Utilizing client-side question tracing and server-side metrics, a Delta Reside Tables (DLT) pipeline collects real-time efficiency knowledge—equivalent to question latencies, rows scanned versus returned, and success/failure charges.
All of this data is consolidated into an AI/BI Dashboard in central logfood.
Instance DB Utilization Scorecard for one service. The contributing elements embrace Extreme Rows Examined, SLA, Timeout, Unit Take a look at Protection, Anti Patterns, Learn/Write Amplification.
Key Takeaways
Our journey to reinforce OLTP SQL database reliability at Databricks presents priceless classes for scaling high-performance merchandise:
- Shift from Reactive to Proactive: Shifting past incident pushed reactively, we’re transitioning into proactively bettering the database finest practices utilizing the Database Utilization Scorecard which made database finest practices measurable and actionable.
- Implement Finest Practices Earlier within the Devloop: By integrating the Question Scorer into the early improvement loop, we lowered the price and energy of fixing anti-patterns like full desk scans or unstable plans, enabling builders to deal with points effectively throughout coding.
- Leverage analytics to achieve insights: By leveraging Databricks merchandise like Delta Tables, DLT Pipelines and AI/BI Dashboards, database utilization scorecard empowers groups to optimize 1000’s of database cases and assist builders successfully. Databricks product helps us speed up our course of and the answer can also be adaptable to different data-driven platforms.
This text was tailored from a chat we offered at SREcon25 Americas 2025 (The slides and recording will probably be obtainable right here). We had been honored to share our expertise to the group, and we’re excited to convey these insights to a broader viewers right here.
In the event you’re enthusiastic about fixing database reliability challenges, discover profession alternatives at Databricks (https://www.databricks.com/firm/careers/open-positions).
