4 ms·

The main thrust seems to be tooling in the db to give developers some idea of the performance impact of the code they write. But most programmers are using fra

The main thrust seems to be tooling in the db to give developers some idea of the performance impact of the code they write. But most programmers are using frameworks and ORMs and things that hide away what is actually happening with the database. A normal developer can look at a chunk of code on their side and have no real idea of what is happening on the database behind them. What webdevelopers need is backend profiling information e.g. actual SQL queries and timings, shown beside all the frontend profiling information (e.g. in webdeveloper tools in the browser). This profiling introspection can come from tooling in front of the database(s). I recall seeing a blog about how the Stackoverflow team have nice tooling that does this for them. (Impossible to google for words like "stackoverflow profiling" and things!)

avereveard 6y ago
what we need is to kill ORM stile framework (think hybernate) and move the world toward mapper style frameworks (think mybatis) sure, inserts are slightly more inconvenient. but the clarity and performance control on reads unparalleled.
tpetry 6y ago
You could easily instruct the orm to add a comment in front of the sql query so the database can profiling of a complete http request and show you all the bad things your orm is doing. Something like: /* dbxperience:request=9a7cd2a6 */ SELECT .... I have not tested it, but this is something google cloud sql recently promoted: https://cloud.google.com/blog/products/databases/get-ahead-of-database-performance-issues-with-cloud-sql-insights https://cloud.google.com/blog/products/databases/get-ahead-o...
- planxty 6y ago
  "Easily", not sure that is always easy to do. Is a clever approach to be sure, but to the author's point there could probably be better UX.
- theptip 6y ago
  You don’t need to go this path; modern(-ish) APM systems like Datadog will let you trace SQL commands in the context of the request being made. You can group by endpoint, or group by sql (merging IDs that differ), or chop and filter the data however you need. The DB is not in a position to give you all the stats you need; instead the DB should support e.g. exporting plan info to further enrich the whole-request metrics that are already being collected. I use this in prod; it’s great. Expensive though. https://www.datadoghq.com/blog/mysql-monitoring-with-datadog/ https://www.datadoghq.com/blog/mysql-monitoring-with-datadog...
  - sa46 6y ago
    I've used Datadog but if you need per query tracing from the database logs, I think the only way is to embed a trace ID in a SQL comment (or in an no-op column) like the grandparent showed. Datadog is tracing the request from the application akin to something like openTrace("Query Name", queryStr) db.Query(queryStr) closeTrace() The gap with that style of application-level tracing is that the database logs give no indication of where a query came from, hence the need for embedding a comment in the query with the trace ID. I would love for a better mechanism than SQL comments for distributed tracing all the way to the database.
    - theptip 6y ago
      I guess what I'm getting at is questioning why you need to look at the database logs (beyond what your APM system can pull from the DB's metrics). What are you getting from the DB logs that you can't get elsewhere?
      - sa46 6y ago
        Sure, off the top of my head: - Explain plans for slow queries, via auto_explain in Postgres. You could get really fancy here and convert the pieces of an explain plan (Aggregate, ModifyTable, FullTableScan) into proper distributed traces. Hard to tell, but it looks like GCP might offer that. - Various errors logs associated with queries that require more detail than a SQL state code. For example, lock timeouts. - Debugging, especially root cause analysis when you're trying to figure out why something broke. The trace IDs help build up context.
sbelskie 6y ago
Re: Stackoverflow and showing profiling information on the front end, you are likely talking about Miniprofiler. https://github.com/MiniProfiler/dotnet https://github.com/MiniProfiler/dotnet
dntrkv 6y ago
Apollo's GraphQL server has a VSCode plugin that shows you the average response time of a query. This type of tooling is difficult to implement but I think can be super useful. https://marketplace.visualstudio.com/items?itemName=apollographql.vscode-apollo#performance-insights https://marketplace.visualstudio.com/items?itemName=apollogr...

avereveard 6y ago

what we need is to kill ORM stile framework (think hybernate) and move the world toward mapper style frameworks (think mybatis) sure, inserts are slightly more inconvenient. but the clarity and performance control on reads unparalleled.

tpetry 6y ago

You could easily instruct the orm to add a comment in front of the sql query so the database can profiling of a complete http request and show you all the bad things your orm is doing. Something like: /* dbxperience:request=9a7cd2a6 */ SELECT .... I have not tested it, but this is something google cloud sql recently promoted: https://cloud.google.com/blog/products/databases/get-ahead-of-database-performance-issues-with-cloud-sql-insights https://cloud.google.com/blog/products/databases/get-ahead-o...

planxty 6y ago
"Easily", not sure that is always easy to do. Is a clever approach to be sure, but to the author's point there could probably be better UX.
theptip 6y ago
You don’t need to go this path; modern(-ish) APM systems like Datadog will let you trace SQL commands in the context of the request being made. You can group by endpoint, or group by sql (merging IDs that differ), or chop and filter the data however you need. The DB is not in a position to give you all the stats you need; instead the DB should support e.g. exporting plan info to further enrich the whole-request metrics that are already being collected. I use this in prod; it’s great. Expensive though. https://www.datadoghq.com/blog/mysql-monitoring-with-datadog/ https://www.datadoghq.com/blog/mysql-monitoring-with-datadog...
- sa46 6y ago
  I've used Datadog but if you need per query tracing from the database logs, I think the only way is to embed a trace ID in a SQL comment (or in an no-op column) like the grandparent showed. Datadog is tracing the request from the application akin to something like openTrace("Query Name", queryStr) db.Query(queryStr) closeTrace() The gap with that style of application-level tracing is that the database logs give no indication of where a query came from, hence the need for embedding a comment in the query with the trace ID. I would love for a better mechanism than SQL comments for distributed tracing all the way to the database.
  - theptip 6y ago
    I guess what I'm getting at is questioning why you need to look at the database logs (beyond what your APM system can pull from the DB's metrics). What are you getting from the DB logs that you can't get elsewhere?
    - sa46 6y ago
      Sure, off the top of my head: - Explain plans for slow queries, via auto_explain in Postgres. You could get really fancy here and convert the pieces of an explain plan (Aggregate, ModifyTable, FullTableScan) into proper distributed traces. Hard to tell, but it looks like GCP might offer that. - Various errors logs associated with queries that require more detail than a SQL state code. For example, lock timeouts. - Debugging, especially root cause analysis when you're trying to figure out why something broke. The trace IDs help build up context.

sbelskie 6y ago

Re: Stackoverflow and showing profiling information on the front end, you are likely talking about Miniprofiler. https://github.com/MiniProfiler/dotnet https://github.com/MiniProfiler/dotnet

dntrkv 6y ago

Apollo's GraphQL server has a VSCode plugin that shows you the average response time of a query. This type of tooling is difficult to implement but I think can be super useful. https://marketplace.visualstudio.com/items?itemName=apollographql.vscode-apollo#performance-insights https://marketplace.visualstudio.com/items?itemName=apollogr...