[clickhouse] Benchmark Performance of ClickHouse Backend Storage

[mahadzaryab1]

Evaluate the performance of the ClickHouse Backend Storage implementation through comprehensive benchmarking. This initiative aims to document baseline metrics and proactively identify/resolve architectural bottlenecks to maximize throughput and efficiency.

[jixiuf]

how to reproduce the slowness that you're seeing?

when the count in my spans table is about 10 billions

then just click the Find Traces button from Jaeger UI

I tried this patch, then I can see the returned result in the Jaeger UI

diff --git a/internal/storage/v2/clickhouse/sql/queries.go b/internal/storage/v2/clickhouse/sql/queries.go
index a9827359..a02ad132 100644
--- a/internal/storage/v2/clickhouse/sql/queries.go
+++ b/internal/storage/v2/clickhouse/sql/queries.go
@@ -216,12 +216,12 @@ const SelectSpansByTraceID = SelectSpansQuery + " WHERE s.trace_id = ?"
 // for each trace ID.
 const SearchTraceIDs = `
 SELECT DISTINCT
-    s.trace_id,
+    l.trace_id,
     t.start,
     t.end
-FROM spans s
-LEFT JOIN trace_id_timestamps t ON s.trace_id = t.trace_id
-WHERE 1=1`
+FROM (SELECT DISTINCT    s.trace_id FROM spans s WHERE 1=1`

 const SelectServices = `
 SELECT
diff --git a/internal/storage/v2/clickhouse/tracestore/reader.go b/internal/storage/v2/clickhouse/tracestore/reader.go
index 15b03f3e..e1a9b974 100644
--- a/internal/storage/v2/clickhouse/tracestore/reader.go
+++ b/internal/storage/v2/clickhouse/tracestore/reader.go
@@ -253,6 +253,8 @@ func (r *Reader) buildFindTraceIDsQuery(query tracestore.TraceQueryParams) (stri
 	}

 	var q strings.Builder
+	var rightQ strings.Builder
+	var rightArgs []any
 	q.WriteString(sql.SearchTraceIDs)
 	args := []any{}

@@ -275,10 +278,15 @@ func (r *Reader) buildFindTraceIDsQuery(query tracestore.TraceQueryParams) (stri
 	if !query.StartTimeMin.IsZero() {
 		q.WriteString(" AND s.start_time >= ?")
 		args = append(args, query.StartTimeMin)
+
+		rightQ.WriteString(" AND start >= ?")
+		rightArgs = append(rightArgs, query.StartTimeMin)
 	}
 	if !query.StartTimeMax.IsZero() {
 		q.WriteString(" AND s.start_time <= ?")
 		args = append(args, query.StartTimeMax)
+		rightQ.WriteString(" AND end <= ?")
+		rightArgs = append(rightArgs, query.StartTimeMax)
 	}

 	for key, attr := range query.Attributes.All() {
@@ -332,6 +340,11 @@ func (r *Reader) buildFindTraceIDsQuery(query tracestore.TraceQueryParams) (stri

 	q.WriteString(" LIMIT ?")
 	args = append(args, limit)
+	right := fmt.Sprintf(" ) l JOIN (SELECT trace_id, start, end FROM trace_id_timestamps WHERE 1=1 %s) t on l.trace_id = t.trace_id",
+		rightQ.String())
+	q.WriteString(right)
+	args = append(args, rightArgs...)

 	return q.String(), args, nil
 }