refactor: switch group by instead of triggering aggregation

Using FINAL in clickhouse query will trigger the aggregation
merge of data while using group by will be more efficient.
It's recommended id docs as well - https://clickhouse.com/
docs/engines/table-engines/mergetree-family/
aggregatingmergetree#select-and-insert

frontend/src/api/infraMonitoring/getHostLists.ts

@@ -39,7 +39,6 @@ export interface HostData {
 	waitTimeSeries: TimeSeries;
 	load15: number;
 	load15TimeSeries: TimeSeries;
-	filesystem: number;
 }
 
 export interface HostListResponse {

frontend/src/components/HostMetricsDetail/HostMetricsDetail.styles.scss

@@ -48,7 +48,7 @@
 			.labels-row,
 			.values-row {
 				display: grid;
-				grid-template-columns: 1fr 1.5fr 1.5fr 1.5fr 1.5fr;
+				grid-template-columns: 1fr 1.5fr 1.5fr 1.5fr;
 				gap: 30px;
 				align-items: center;
 			}

frontend/src/components/HostMetricsDetail/HostMetricsDetails.tsx

@@ -426,12 +426,6 @@ function HostMetricsDetails({
 								>
 									MEMORY USAGE
 								</Typography.Text>
-								<Typography.Text
-									type="secondary"
-									className="host-details-metadata-label"
-								>
-									DISK USAGE
-								</Typography.Text>
 							</div>
 
 							<div className="values-row">
@@ -484,23 +478,6 @@ function HostMetricsDetails({
 										className="progress-bar"
 									/>
 								</div>
-								<div className="progress-container">
-									<Progress
-										percent={Number((host.filesystem * 100).toFixed(1))}
-										size="small"
-										strokeColor={((): string => {
-											const filesystemPercent = Number((host.filesystem * 100).toFixed(1));
-											if (filesystemPercent >= 90) {
-												return Color.BG_CHERRY_500;
-											}
-											if (filesystemPercent >= 60) {
-												return Color.BG_AMBER_500;
-											}
-											return Color.BG_FOREST_500;
-										})()}
-										className="progress-bar"
-									/>
-								</div>
 							</div>
 						</div>
 					</div>

frontend/src/container/InfraMonitoringHosts/utils.tsx

@@ -27,7 +27,6 @@ export interface HostRowData {
 	hostName: string;
 	cpu: React.ReactNode;
 	memory: React.ReactNode;
-	filesystem: React.ReactNode;
 	wait: string;
 	load15: number;
 	active: React.ReactNode;
@@ -109,14 +108,6 @@ export const getHostsListColumns = (): ColumnType<HostRowData>[] => [
 		sorter: true,
 		align: 'right',
 	},
-	{
-		title: <div className="column-header-right">Disk Usage</div>,
-		dataIndex: 'filesystem',
-		key: 'filesystem',
-		width: 100,
-		sorter: true,
-		align: 'right',
-	},
 	{
 		title: <div className="column-header-right">IOWait</div>,
 		dataIndex: 'wait',
@@ -187,26 +178,6 @@ export const formatDataForTable = (data: HostData[]): HostRowData[] =>
 				/>
 			</div>
 		),
-		filesystem: (
-			<div className="progress-container">
-				<Progress
-					percent={Number((host.filesystem * 100).toFixed(1))}
-					strokeLinecap="butt"
-					size="small"
-					strokeColor={((): string => {
-						const filesystemPercent = Number((host.filesystem * 100).toFixed(1));
-						if (filesystemPercent >= 90) {
-							return Color.BG_CHERRY_500;
-						}
-						if (filesystemPercent >= 60) {
-							return Color.BG_AMBER_500;
-						}
-						return Color.BG_FOREST_500;
-					})()}
-					className="progress-bar"
-				/>
-			</div>
-		),
 		wait: `${Number((host.wait * 100).toFixed(1))}%`,
 		load15: host.load15,
 	}));

frontend/src/container/LogDetailedView/InfraMetrics/constants.ts

@@ -1623,9 +1623,6 @@ export const getHostQueryPayload = (
 	const diskPendingKey = dotMetricsEnabled
 		? 'system.disk.pending_operations'
 		: 'system_disk_pending_operations';
-	const fsUsageKey = dotMetricsEnabled
-		? 'system.filesystem.usage'
-		: 'system_filesystem_usage';
 
 	return [
 		{
@@ -2486,143 +2483,6 @@ export const getHostQueryPayload = (
 			start,
 			end,
 		},
-		{
-			selectedTime: 'GLOBAL_TIME',
-			graphType: PANEL_TYPES.TIME_SERIES,
-			query: {
-				builder: {
-					queryData: [
-						{
-							aggregateAttribute: {
-								dataType: DataTypes.Float64,
-								id: 'system_filesystem_usage--float64--Gauge--true',
-
-								key: fsUsageKey,
-								type: 'Gauge',
-							},
-							aggregateOperator: 'avg',
-							dataSource: DataSource.METRICS,
-							disabled: true,
-							expression: 'A',
-							filters: {
-								items: [
-									{
-										id: 'fs_f1',
-										key: {
-											dataType: DataTypes.String,
-											id: 'host_name--string--tag--false',
-
-											key: hostNameKey,
-											type: 'tag',
-										},
-										op: '=',
-										value: hostName,
-									},
-									{
-										id: 'fs_f2',
-										key: {
-											dataType: DataTypes.String,
-											id: 'state--string--tag--false',
-
-											key: 'state',
-											type: 'tag',
-										},
-										op: '=',
-										value: 'used',
-									},
-								],
-								op: 'AND',
-							},
-							functions: [],
-							groupBy: [
-								{
-									dataType: DataTypes.String,
-									id: 'mountpoint--string--tag--false',
-
-									key: 'mountpoint',
-									type: 'tag',
-								},
-							],
-							having: [],
-							legend: '{{mountpoint}}',
-							limit: null,
-							orderBy: [],
-							queryName: 'A',
-							reduceTo: ReduceOperators.AVG,
-							spaceAggregation: 'sum',
-							stepInterval: 60,
-							timeAggregation: 'avg',
-						},
-						{
-							aggregateAttribute: {
-								dataType: DataTypes.Float64,
-								id: 'system_filesystem_usage--float64--Gauge--true',
-
-								key: fsUsageKey,
-								type: 'Gauge',
-							},
-							aggregateOperator: 'avg',
-							dataSource: DataSource.METRICS,
-							disabled: true,
-							expression: 'B',
-							filters: {
-								items: [
-									{
-										id: 'fs_f3',
-										key: {
-											dataType: DataTypes.String,
-											id: 'host_name--string--tag--false',
-
-											key: hostNameKey,
-											type: 'tag',
-										},
-										op: '=',
-										value: hostName,
-									},
-								],
-								op: 'AND',
-							},
-							functions: [],
-							groupBy: [
-								{
-									dataType: DataTypes.String,
-									id: 'mountpoint--string--tag--false',
-
-									key: 'mountpoint',
-									type: 'tag',
-								},
-							],
-							having: [],
-							legend: '{{mountpoint}}',
-							limit: null,
-							orderBy: [],
-							queryName: 'B',
-							reduceTo: ReduceOperators.AVG,
-							spaceAggregation: 'sum',
-							stepInterval: 60,
-							timeAggregation: 'avg',
-						},
-					],
-					queryFormulas: [
-						{
-							disabled: false,
-							expression: 'A/B',
-							legend: '{{mountpoint}}',
-							queryName: 'F1',
-						},
-					],
-					queryTraceOperator: [],
-				},
-				clickhouse_sql: [{ disabled: false, legend: '', name: 'A', query: '' }],
-				id: 'a1b2c3d4-e5f6-7890-abcd-ef1234567890',
-				promql: [{ disabled: false, legend: '', name: 'A', query: '' }],
-				queryType: EQueryType.QUERY_BUILDER,
-			},
-			variables: {},
-			formatForWeb: false,
-			start,
-			end,
-		},
 		{
 			selectedTime: 'GLOBAL_TIME',
 			graphType: PANEL_TYPES.TIME_SERIES,
@@ -2771,6 +2631,6 @@ export const hostWidgetInfo = [
 	{ title: 'Network connections', yAxisUnit: 'short' },
 	{ title: 'System disk io (bytes transferred)', yAxisUnit: 'bytes' },
 	{ title: 'System disk operations/s', yAxisUnit: 'short' },
-	{ title: 'Disk Usage (%) by mountpoint', yAxisUnit: 'percentunit' },
 	{ title: 'Queue size', yAxisUnit: 'short' },
+	{ title: 'Disk operations time', yAxisUnit: 's' },
 ];

pkg/query-service/app/clickhouseReader/reader.go

@@ -830,7 +830,7 @@ func (r *ClickHouseReader) GetUsage(ctx context.Context, queryParams *model.GetU
 
 func (r *ClickHouseReader) GetSpansForTrace(ctx context.Context, traceID string, traceDetailsQuery string) ([]model.SpanItemV2, *model.ApiError) {
 	var traceSummary model.TraceSummary
-	summaryQuery := fmt.Sprintf("SELECT * from %s.%s FINAL WHERE trace_id=$1", r.TraceDB, r.traceSummaryTable)
+	summaryQuery := fmt.Sprintf("SELECT trace_id, min(start) AS start, max(end) AS end, sum(num_spans) AS num_spans FROM %s.%s WHERE trace_id=$1 GROUP BY trace_id", r.TraceDB, r.traceSummaryTable)
 	err := r.db.QueryRow(ctx, summaryQuery, traceID).Scan(&traceSummary.TraceID, &traceSummary.Start, &traceSummary.End, &traceSummary.NumSpans)
 	if err != nil {
 		if err == sql.ErrNoRows {
@@ -6458,7 +6458,7 @@ func (r *ClickHouseReader) SearchTraces(ctx context.Context, params *model.Searc
 	}
 
 	var traceSummary model.TraceSummary
-	summaryQuery := fmt.Sprintf("SELECT * from %s.%s FINAL WHERE trace_id=$1", r.TraceDB, r.traceSummaryTable)
+	summaryQuery := fmt.Sprintf("SELECT trace_id, min(start) AS start, max(end) AS end, sum(num_spans) AS num_spans FROM %s.%s WHERE trace_id=$1 GROUP BY trace_id", r.TraceDB, r.traceSummaryTable)
 	err := r.db.QueryRow(ctx, summaryQuery, params.TraceID).Scan(&traceSummary.TraceID, &traceSummary.Start, &traceSummary.End, &traceSummary.NumSpans)
 	if err != nil {
 		if err == sql.ErrNoRows {

pkg/query-service/app/inframetrics/common.go

@@ -10,7 +10,6 @@ import (
 )
 
 var dotMetricMap = map[string]string{
-	"system_filesystem_usage":               "system.filesystem.usage",
 	"system_cpu_time":                       "system.cpu.time",
 	"system_memory_usage":                   "system.memory.usage",
 	"system_cpu_load_average_15m":           "system.cpu.load_average.15m",

pkg/query-service/app/inframetrics/hosts.go

@@ -53,11 +53,10 @@ var (
 	}
 
 	queryNamesForTopHosts = map[string][]string{
-		"cpu":        {"A", "B", "F1"},
-		"memory":     {"C", "D", "F2"},
-		"wait":       {"E", "F", "F3"},
-		"load15":     {"G"},
-		"filesystem": {"H", "I", "F4"},
+		"cpu":    {"A", "B", "F1"},
+		"memory": {"C", "D", "F2"},
+		"wait":   {"E", "F", "F3"},
+		"load15": {"G"},
 	}
 
 	// TODO(srikanthccv): remove hardcoded metric name and support keys from any system metric
@@ -68,11 +67,10 @@ var (
 		GetDotMetrics("os_type"),
 	}
 	metricNamesForHosts = map[string]string{
-		"cpu":        GetDotMetrics("system_cpu_time"),
-		"memory":     GetDotMetrics("system_memory_usage"),
-		"load15":     GetDotMetrics("system_cpu_load_average_15m"),
-		"wait":       GetDotMetrics("system_cpu_time"),
-		"filesystem": GetDotMetrics("system_filesystem_usage"),
+		"cpu":    GetDotMetrics("system_cpu_time"),
+		"memory": GetDotMetrics("system_memory_usage"),
+		"load15": GetDotMetrics("system_cpu_load_average_15m"),
+		"wait":   GetDotMetrics("system_cpu_time"),
 	}
 )
 
@@ -496,11 +494,10 @@ func (h *HostsRepo) GetHostList(ctx context.Context, orgID valuer.UUID, req mode
 	for _, result := range formattedResponse {
 		for _, row := range result.Table.Rows {
 			record := model.HostListRecord{
-				CPU:        -1,
-				Memory:     -1,
-				Wait:       -1,
-				Load15:     -1,
-				Filesystem: -1,
+				CPU:    -1,
+				Memory: -1,
+				Wait:   -1,
+				Load15: -1,
 			}
 
 			if hostName, ok := row.Data[hostNameAttrKey].(string); ok {
@@ -519,9 +516,6 @@ func (h *HostsRepo) GetHostList(ctx context.Context, orgID valuer.UUID, req mode
 			if load15, ok := row.Data["G"].(float64); ok {
 				record.Load15 = load15
 			}
-			if filesystem, ok := row.Data["F4"].(float64); ok {
-				record.Filesystem = filesystem
-			}
 			record.Meta = map[string]string{}
 			if _, ok := hostAttrs[record.HostName]; ok {
 				record.Meta = hostAttrs[record.HostName]

pkg/query-service/app/inframetrics/hosts_query.go

@@ -269,130 +269,42 @@ var HostsTableListQuery = v3.QueryRangeParamsV3{
 					Items:    []v3.FilterItem{},
 				},
 			},
-		"G": {
-			QueryName:  "G",
-			DataSource: v3.DataSourceMetrics,
-			AggregateAttribute: v3.AttributeKey{
-				Key:      metricNamesForHosts["load15"],
-				DataType: v3.AttributeKeyDataTypeFloat64,
-			},
-			Temporality: v3.Unspecified,
-			Filters: &v3.FilterSet{
-				Operator: "AND",
-				Items: []v3.FilterItem{
-					{
-						Key: v3.AttributeKey{
-							Key:      hostNameAttrKey,
-							DataType: v3.AttributeKeyDataTypeString,
-							Type:     v3.AttributeKeyTypeResource,
+			"G": {
+				QueryName:  "G",
+				DataSource: v3.DataSourceMetrics,
+				AggregateAttribute: v3.AttributeKey{
+					Key:      metricNamesForHosts["load15"],
+					DataType: v3.AttributeKeyDataTypeFloat64,
+				},
+				Temporality: v3.Unspecified,
+				Filters: &v3.FilterSet{
+					Operator: "AND",
+					Items: []v3.FilterItem{
+						{
+							Key: v3.AttributeKey{
+								Key:      hostNameAttrKey,
+								DataType: v3.AttributeKeyDataTypeString,
+								Type:     v3.AttributeKeyTypeResource,
+							},
+							Operator: v3.FilterOperatorNotContains,
+							Value:    agentNameToIgnore,
 						},
-						Operator: v3.FilterOperatorNotContains,
-						Value:    agentNameToIgnore,
 					},
 				},
-			},
-			GroupBy: []v3.AttributeKey{
-				{
-					Key:      hostNameAttrKey,
-					DataType: v3.AttributeKeyDataTypeString,
-					Type:     v3.AttributeKeyTypeResource,
-				},
-			},
-			Expression:       "G",
-			ReduceTo:         v3.ReduceToOperatorAvg,
-			TimeAggregation:  v3.TimeAggregationAvg,
-			SpaceAggregation: v3.SpaceAggregationSum,
-			Disabled:         false,
-			Legend:           "CPU Load Average (15m)",
-		},
-		"H": {
-			QueryName:  "H",
-			DataSource: v3.DataSourceMetrics,
-			AggregateAttribute: v3.AttributeKey{
-				Key:      metricNamesForHosts["filesystem"],
-				DataType: v3.AttributeKeyDataTypeFloat64,
-			},
-			Temporality: v3.Cumulative,
-			Filters: &v3.FilterSet{
-				Operator: "AND",
-				Items: []v3.FilterItem{
+				GroupBy: []v3.AttributeKey{
 					{
-						Key: v3.AttributeKey{
-							Key:      "state",
-							DataType: v3.AttributeKeyDataTypeString,
-							Type:     v3.AttributeKeyTypeTag,
-						},
-						Operator: v3.FilterOperatorEqual,
-						Value:    "used",
-					},
-					{
-						Key: v3.AttributeKey{
-							Key:      hostNameAttrKey,
-							DataType: v3.AttributeKeyDataTypeString,
-							Type:     v3.AttributeKeyTypeResource,
-						},
-						Operator: v3.FilterOperatorNotContains,
-						Value:    agentNameToIgnore,
+						Key:      hostNameAttrKey,
+						DataType: v3.AttributeKeyDataTypeString,
+						Type:     v3.AttributeKeyTypeResource,
 					},
 				},
+				Expression:       "G",
+				ReduceTo:         v3.ReduceToOperatorAvg,
+				TimeAggregation:  v3.TimeAggregationAvg,
+				SpaceAggregation: v3.SpaceAggregationSum,
+				Disabled:         false,
+				Legend:           "CPU Load Average (15m)",
 			},
-			GroupBy: []v3.AttributeKey{
-				{
-					Key:      hostNameAttrKey,
-					DataType: v3.AttributeKeyDataTypeString,
-					Type:     v3.AttributeKeyTypeResource,
-				},
-			},
-			Expression:       "H",
-			ReduceTo:         v3.ReduceToOperatorAvg,
-			TimeAggregation:  v3.TimeAggregationAvg,
-			SpaceAggregation: v3.SpaceAggregationSum,
-			Disabled:         true,
-		},
-		"I": {
-			QueryName:  "I",
-			DataSource: v3.DataSourceMetrics,
-			AggregateAttribute: v3.AttributeKey{
-				Key:      metricNamesForHosts["filesystem"],
-				DataType: v3.AttributeKeyDataTypeFloat64,
-			},
-			Temporality: v3.Cumulative,
-			Filters: &v3.FilterSet{
-				Operator: "AND",
-				Items: []v3.FilterItem{
-					{
-						Key: v3.AttributeKey{
-							Key:      hostNameAttrKey,
-							DataType: v3.AttributeKeyDataTypeString,
-							Type:     v3.AttributeKeyTypeResource,
-						},
-						Operator: v3.FilterOperatorNotContains,
-						Value:    agentNameToIgnore,
-					},
-				},
-			},
-			GroupBy: []v3.AttributeKey{
-				{
-					Key:      hostNameAttrKey,
-					DataType: v3.AttributeKeyDataTypeString,
-					Type:     v3.AttributeKeyTypeResource,
-				},
-			},
-			Expression:       "I",
-			ReduceTo:         v3.ReduceToOperatorAvg,
-			TimeAggregation:  v3.TimeAggregationAvg,
-			SpaceAggregation: v3.SpaceAggregationSum,
-			Disabled:         true,
-		},
-		"F4": {
-			QueryName:  "F4",
-			Expression: "H/I",
-			Legend:     "Disk Usage (%)",
-			Filters: &v3.FilterSet{
-				Operator: "AND",
-				Items:    []v3.FilterItem{},
-			},
-		},
 		},
 		PanelType: v3.PanelTypeTable,
 		QueryType: v3.QueryTypeBuilder,

pkg/query-service/model/infra.go

@@ -26,15 +26,14 @@ type HostListRequest struct {
 }
 
 type HostListRecord struct {
-	HostName   string            `json:"hostName"`
-	Active     bool              `json:"active"`
-	OS         string            `json:"os"`
-	CPU        float64           `json:"cpu"`
-	Memory     float64           `json:"memory"`
-	Wait       float64           `json:"wait"`
-	Load15     float64           `json:"load15"`
-	Filesystem float64           `json:"filesystem"`
-	Meta       map[string]string `json:"meta"`
+	HostName string            `json:"hostName"`
+	Active   bool              `json:"active"`
+	OS       string            `json:"os"`
+	CPU      float64           `json:"cpu"`
+	Memory   float64           `json:"memory"`
+	Wait     float64           `json:"wait"`
+	Load15   float64           `json:"load15"`
+	Meta     map[string]string `json:"meta"`
 }
 
 type HostListResponse struct {
@@ -65,10 +64,6 @@ func (r *HostListResponse) SortBy(orderBy *v3.OrderBy) {
 		sort.Slice(r.Records, func(i, j int) bool {
 			return r.Records[i].Wait > r.Records[j].Wait
 		})
-	case "filesystem":
-		sort.Slice(r.Records, func(i, j int) bool {
-			return r.Records[i].Filesystem > r.Records[j].Filesystem
-		})
 	}
 	// the default is descending
 	if orderBy.Order == v3.DirectionAsc {