SQLAlchemy: Allow only slow queries to be recorded

[arturbalabanov]

What problem do you want to solve?

After integrating the SQLAlchemy instrumentor, there are spans recorded for every single query being run which in some contexts generates a lot of noise and makes the spans difficult to follow.

For example, I'm working on a project where we group spans in the context of a job and some of these jobs run dozens of queries among other actions. As a result, when there are so many small events (we care only about slow queries) it's easy to miss an important one in between

Describe the solution you'd like

This can be solved by adding a configuration option such as exec_time_treshold which when set will only create spans for queries which take longer than that. For example, when exec_time_treshold=100 only queries which take longer than 100ms will create spans. For backwards compatibility (also a sane default) when not set all queries will create spans.

Describe alternatives you've considered

We've also investigated grouping queries done in the same transaction together (sample implementation bellow), so that we can at least collapse them. But in reality most of our transactions contain a single query and in these cases it doesn't help much.

Sample (and hacky) implementation of grouped queries by transaction

    engine_tracer: EngineTracer | None = SQLAlchemyInstrumentor().instrument(
        engine=dbengine.sync_engine, enable_commenter=True
    )

    if engine_tracer is None:
        return

    def on_begin(conn: Connection) -> None:
        transaction_span_ctx_mngr = engine_tracer.tracer.start_as_current_span(
            "SQLAlchemy Transaction",
            kind=trace.SpanKind.CLIENT,
            attributes={
                SpanAttributes.DB_NAME: dbengine.url.database,
                SpanAttributes.DB_STATEMENT: "BEGIN",
                SpanAttributes.DB_OPERATION: "BEGIN",
            },
        )

        transaction_span_ctx_mngr.__enter__()

        conn.info["otel_transaction_span_ctx_mngr"] = transaction_span_ctx_mngr

    def on_commit(conn: Connection) -> None:
        transaction_span_ctx_mngr = conn.info.pop("otel_transaction_span_ctx_mngr", None)

        if transaction_span_ctx_mngr is not None:
            transaction_span_ctx_mngr.__exit__(None, None, None)

    def on_rollback(conn: Connection) -> None:
        transaction_span_ctx_mngr = conn.info.pop("otel_transaction_span_ctx_mngr", None)

        if transaction_span_ctx_mngr is not None:
            transaction_span_ctx_mngr.__exit__(None, None, None)

    engine_tracer._register_event_listener(dbengine.sync_engine, "begin", on_begin)
    engine_tracer._register_event_listener(dbengine.sync_engine, "commit", on_commit)
    engine_tracer._register_event_listener(dbengine.sync_engine, "rollback", on_rollback)

Additional Context

No response

Would you like to implement a fix?

Yes

[rbagd]

Did you consider using a custom span processor to filter out the non-needed spans?