This message was deleted.

I’ve also been the consultant in 3. Tools like New Relic can help pin down slow queries and bottle necks in production as well

Have worked at places that have done 1, 2 and 3. Found that service ownership (2) usually yields not only the quickest results, but helps train/skill-up the teams involved over time. Definitely scenarios where 1/3 are more effective though

Query is probably a part of business Logic - so this should belong to the application developers. But as Platform Engineer my duty is to provide them with all necessary troubleshooting capabilities

Thanks 🙂 Do you know a tool that would tell you how to fix the query? Not only show which query is slow.

GitHub Copilot 😂

Depends on your database engine and how much advice you're looking for? https://pganalyze.com/postgres-index-advisor for example provides some information

Yeah, I tried thinks like Copilot or ChatGPT. Not much of a success 😉

Yes, several application monitoring tools go beyond just identifying bugs; they also provide insights or suggestions on how to resolve them. These tools often incorporate aspects of artificial intelligence, machine learning, and sophisticated analysis techniques to offer actionable feedback for resolving issues. Here are a few notable examples: • Sentry: Sentry offers real-time error tracking and is known for its ability to not only detect errors but also provide context, including stack traces, parameters, and environment states that help developers understand and fix issues. While it primarily focuses on identifying the point of failure, its rich data allows developers to deduce potential fixes. • Dynatrace: Dynatrace uses artificial intelligence to automatically detect problems in real-time, pinpoint their root cause, and suggest remedies. Its AI capabilities are designed to reduce the time developers spend diagnosing issues, offering direct insights into the source of the problem and potential solutions. • New Relic: New Relic's APM tool provides detailed performance data and error tracking with actionable insights. It can highlight problematic code segments and, in some cases, suggest optimizations or fixes based on patterns it detects in the application's performance data. • Raygun: Raygun's Crash Reporting tool not only captures errors but also provides diagnostics information, including stack traces and affected users. It gives developers insights into how to approach fixing errors, although direct code fix suggestions are more about providing context and less about specific code edits. • Rollbar: Rollbar is known for its real-time error monitoring and automatic exception reporting. It provides detailed stack traces, request parameters, and environment data to help pinpoint where and why an error occurred. While direct "how-to-fix" advice may be limited, the detailed information aids in troubleshooting. • AppDynamics: AppDynamics provides deep performance diagnostics and transaction flow monitoring, offering insights into the root cause of issues. It can suggest areas of improvement based on performance metrics and error rates, although it might not always provide a direct code fix. These tools are designed to make the debugging process more efficient by not only identifying the presence of a bug but also offering insights into its nature and potential fixes. However, the depth of feedback on how to resolve issues can vary. In many cases, they provide enough context for developers to understand the problem and devise their own solutions, rather than offering a specific code change to resolve the issue directly. This approach balances the need for detailed diagnostic information with the flexibility for developers to apply their own expertise and understanding of the codebase to implement the most appropriate fix.

Usually an apm or profiler, logging can trace down the query. Then a plan or analyze can give you insight into why it is bad. Then back to dev team to modify unless it is the rare just tweak the index type of senario.

unless it is the rare just tweak the index type of senario

Yeah, exactly. If index doesn't help, then automated tools rarely suggest something useful.

4️⃣ look at the query plan

@Gerald Benischke And how do you do that? Manually?

As so many things in IT: it depends ;-) I like to double check my queries during development and an “explain plan” usually gives me the confidence that it won’t fail at scale - does it use a full table scan is usually a red flag. If it’s an existing system you usually have tooling to tell you which queries take the longest and then I’d manually replicate the query and check the plan.

@Gerald Benischke Do you have any tool that checks the execution plan automatically before you even commit the code to repository? Such a tool would tell you immediately if your query would work well in production.

at $previousJob we used a mongo setting to error on any queries without an index and used that in the integration tests - https://www.mongodb.com/docs/manual/reference/parameters/#mongodb-parameter-param.notablescan and we logged any queries that did COLLSCAN - which made it easy to spot before it got to production - but no, I don't know of a specific tool per-se