| 1 | // SPDX-License-Identifier: GPL-3.0-or-later |
| 2 | |
| 3 | package proxysql |
| 4 | |
| 5 | import ( |
| 6 | "context" |
| 7 | "database/sql" |
| 8 | "fmt" |
| 9 | "strings" |
| 10 | |
| 11 | "github.com/netdata/netdata/go/plugins/pkg/funcapi" |
| 12 | "github.com/netdata/netdata/go/plugins/plugin/go.d/pkg/strmutil" |
| 13 | ) |
| 14 | |
| 15 | const proxysqlMaxQueryTextLength = 4096 |
| 16 | |
| 17 | const ( |
| 18 | topQueriesMethodID = "top-queries" |
| 19 | paramSort = "__sort" |
| 20 | ) |
| 21 | |
| 22 | func topQueriesMethodConfig() funcapi.MethodConfig { |
| 23 | return funcapi.MethodConfig{ |
| 24 | ID: topQueriesMethodID, |
| 25 | Name: "Top Queries", |
| 26 | UpdateEvery: 10, |
| 27 | Help: "Top SQL queries from ProxySQL query digest stats", |
| 28 | RequireCloud: true, |
| 29 | RequiredParams: []funcapi.ParamConfig{funcapi.BuildSortParam(proxysqlAllColumns)}, |
| 30 | } |
| 31 | } |
| 32 | |
| 33 | // proxysqlColumn defines metadata for a ProxySQL query digest column. |
| 34 | // Embeds funcapi.ColumnMeta for UI rendering and adds ProxySQL-specific fields. |
| 35 | type proxysqlColumn struct { |
| 36 | funcapi.ColumnMeta |
| 37 | |
| 38 | // DBColumn is the database column name |
| 39 | DBColumn string |
| 40 | // IsMicroseconds indicates if the value is in microseconds (needs /1000 conversion) |
| 41 | IsMicroseconds bool |
| 42 | // sortOpt indicates whether this column appears in the sort dropdown |
| 43 | sortOpt bool |
| 44 | // sortLbl is the label shown in the sort dropdown |
| 45 | sortLbl string |
| 46 | // defaultSort indicates whether this is the default sort column |
| 47 | defaultSort bool |
| 48 | } |
| 49 | |
| 50 | // funcapi.SortableColumn interface implementation for proxysqlColumn. |
| 51 | func (c proxysqlColumn) IsSortOption() bool { return c.sortOpt } |
| 52 | func (c proxysqlColumn) SortLabel() string { return c.sortLbl } |
| 53 | func (c proxysqlColumn) IsDefaultSort() bool { return c.defaultSort } |
| 54 | func (c proxysqlColumn) ColumnName() string { return c.Name } |
| 55 | func (c proxysqlColumn) SortColumn() string { return "" } |
| 56 | |
| 57 | // proxysqlColumnSet creates a ColumnSet from a slice of proxysqlColumn. |
| 58 | func proxysqlColumnSet(cols []proxysqlColumn) funcapi.ColumnSet[proxysqlColumn] { |
| 59 | return funcapi.Columns(cols, func(c proxysqlColumn) funcapi.ColumnMeta { return c.ColumnMeta }) |
| 60 | } |
| 61 | |
| 62 | var proxysqlAllColumns = []proxysqlColumn{ |
| 63 | {ColumnMeta: funcapi.ColumnMeta{Name: "digest", Tooltip: "Digest", Type: funcapi.FieldTypeString, Visible: false, Transform: funcapi.FieldTransformNone, Sort: funcapi.FieldSortAscending, Summary: funcapi.FieldSummaryCount, Filter: funcapi.FieldFilterMultiselect, UniqueKey: true, Sortable: true}, DBColumn: "digest"}, |
| 64 | {ColumnMeta: funcapi.ColumnMeta{Name: "query", Tooltip: "Query", Type: funcapi.FieldTypeString, Visible: true, Transform: funcapi.FieldTransformNone, Sort: funcapi.FieldSortAscending, Summary: funcapi.FieldSummaryCount, Filter: funcapi.FieldFilterMultiselect, Sticky: true, FullWidth: true, Sortable: true}, DBColumn: "digest_text"}, |
| 65 | {ColumnMeta: funcapi.ColumnMeta{Name: "schema", Tooltip: "Schema", Type: funcapi.FieldTypeString, Visible: true, Transform: funcapi.FieldTransformNone, Sort: funcapi.FieldSortAscending, Summary: funcapi.FieldSummaryCount, Filter: funcapi.FieldFilterMultiselect, GroupBy: &funcapi.GroupByOptions{IsDefault: true}, Sortable: true}, DBColumn: "schemaname"}, |
| 66 | {ColumnMeta: funcapi.ColumnMeta{Name: "user", Tooltip: "User", Type: funcapi.FieldTypeString, Visible: false, Transform: funcapi.FieldTransformNone, Sort: funcapi.FieldSortAscending, Summary: funcapi.FieldSummaryCount, Filter: funcapi.FieldFilterMultiselect, GroupBy: &funcapi.GroupByOptions{}, Sortable: true}, DBColumn: "username"}, |
| 67 | {ColumnMeta: funcapi.ColumnMeta{Name: "hostgroup", Tooltip: "Hostgroup", Type: funcapi.FieldTypeInteger, Visible: false, Transform: funcapi.FieldTransformNumber, Sort: funcapi.FieldSortAscending, Summary: funcapi.FieldSummaryCount, Filter: funcapi.FieldFilterRange, GroupBy: &funcapi.GroupByOptions{}, Sortable: true}, DBColumn: "hostgroup"}, |
| 68 | |
| 69 | {ColumnMeta: funcapi.ColumnMeta{Name: "calls", Tooltip: "Calls", Type: funcapi.FieldTypeInteger, Visible: true, Transform: funcapi.FieldTransformNumber, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummarySum, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Calls", Title: "Number of Calls", IsDefault: true}, Sortable: true}, DBColumn: "count_star", sortOpt: true, sortLbl: "Top queries by Number of Calls"}, |
| 70 | |
| 71 | {ColumnMeta: funcapi.ColumnMeta{Name: "totalTime", Tooltip: "Total Time", Type: funcapi.FieldTypeDuration, Units: "milliseconds", Visible: true, Transform: funcapi.FieldTransformDuration, DecimalPoints: 2, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummarySum, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Time", Title: "Execution Time", IsDefault: true}, Sortable: true}, DBColumn: "sum_time", IsMicroseconds: true, sortOpt: true, sortLbl: "Top queries by Total Execution Time", defaultSort: true}, |
| 72 | {ColumnMeta: funcapi.ColumnMeta{Name: "avgTime", Tooltip: "Avg Time", Type: funcapi.FieldTypeDuration, Units: "milliseconds", Visible: true, Transform: funcapi.FieldTransformDuration, DecimalPoints: 2, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummaryMean, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Time", Title: "Execution Time"}, Sortable: true}, DBColumn: "avg_time", IsMicroseconds: true, sortOpt: true, sortLbl: "Top queries by Average Execution Time"}, |
| 73 | {ColumnMeta: funcapi.ColumnMeta{Name: "minTime", Tooltip: "Min Time", Type: funcapi.FieldTypeDuration, Units: "milliseconds", Visible: false, Transform: funcapi.FieldTransformDuration, DecimalPoints: 2, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummaryMin, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Time", Title: "Execution Time"}, Sortable: true}, DBColumn: "min_time", IsMicroseconds: true}, |
| 74 | {ColumnMeta: funcapi.ColumnMeta{Name: "maxTime", Tooltip: "Max Time", Type: funcapi.FieldTypeDuration, Units: "milliseconds", Visible: false, Transform: funcapi.FieldTransformDuration, DecimalPoints: 2, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummaryMax, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Time", Title: "Execution Time"}, Sortable: true}, DBColumn: "max_time", IsMicroseconds: true}, |
| 75 | |
| 76 | {ColumnMeta: funcapi.ColumnMeta{Name: "rowsAffected", Tooltip: "Rows Affected", Type: funcapi.FieldTypeInteger, Visible: true, Transform: funcapi.FieldTransformNumber, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummarySum, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Rows", Title: "Rows"}, Sortable: true}, DBColumn: "sum_rows_affected", sortOpt: true, sortLbl: "Top queries by Rows Affected"}, |
| 77 | {ColumnMeta: funcapi.ColumnMeta{Name: "rowsSent", Tooltip: "Rows Sent", Type: funcapi.FieldTypeInteger, Visible: true, Transform: funcapi.FieldTransformNumber, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummarySum, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Rows", Title: "Rows"}, Sortable: true}, DBColumn: "sum_rows_sent", sortOpt: true, sortLbl: "Top queries by Rows Sent"}, |
| 78 | {ColumnMeta: funcapi.ColumnMeta{Name: "errors", Tooltip: "Errors", Type: funcapi.FieldTypeInteger, Visible: true, Transform: funcapi.FieldTransformNumber, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummarySum, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Errors", Title: "Errors & Warnings"}, Sortable: true}, DBColumn: "sum_errors", sortOpt: true, sortLbl: "Top queries by Errors"}, |
| 79 | {ColumnMeta: funcapi.ColumnMeta{Name: "warnings", Tooltip: "Warnings", Type: funcapi.FieldTypeInteger, Visible: true, Transform: funcapi.FieldTransformNumber, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummarySum, Filter: funcapi.FieldFilterRange, Chart: &funcapi.ChartOptions{Group: "Errors", Title: "Errors & Warnings"}, Sortable: true}, DBColumn: "sum_warnings", sortOpt: true, sortLbl: "Top queries by Warnings"}, |
| 80 | |
| 81 | {ColumnMeta: funcapi.ColumnMeta{Name: "firstSeen", Tooltip: "First Seen", Type: funcapi.FieldTypeString, Visible: false, Transform: funcapi.FieldTransformNone, Sort: funcapi.FieldSortAscending, Summary: funcapi.FieldSummaryCount, Filter: funcapi.FieldFilterMultiselect, Sortable: true}, DBColumn: "first_seen"}, |
| 82 | {ColumnMeta: funcapi.ColumnMeta{Name: "lastSeen", Tooltip: "Last Seen", Type: funcapi.FieldTypeString, Visible: false, Transform: funcapi.FieldTransformNone, Sort: funcapi.FieldSortDescending, Summary: funcapi.FieldSummaryCount, Filter: funcapi.FieldFilterMultiselect, Sortable: true}, DBColumn: "last_seen"}, |
| 83 | } |
| 84 | |
| 85 | // Compile-time interface check. |
| 86 | var _ funcapi.MethodHandler = (*funcTopQueries)(nil) |
| 87 | |
| 88 | // funcTopQueries handles the "top-queries" function for ProxySQL. |
| 89 | type funcTopQueries struct { |
| 90 | router *funcRouter |
| 91 | } |
| 92 | |
| 93 | func newFuncTopQueries(r *funcRouter) *funcTopQueries { |
| 94 | return &funcTopQueries{router: r} |
| 95 | } |
| 96 | |
| 97 | // MethodParams implements funcapi.MethodHandler. |
| 98 | func (f *funcTopQueries) MethodParams(ctx context.Context, method string) ([]funcapi.ParamConfig, error) { |
| 99 | if method != topQueriesMethodID { |
| 100 | return nil, nil |
| 101 | } |
| 102 | |
| 103 | c := f.router.collector |
| 104 | if c.Functions.TopQueries.Disabled { |
| 105 | return nil, fmt.Errorf("top-queries function disabled in configuration") |
| 106 | } |
| 107 | if c.db == nil { |
| 108 | if err := c.openConnection(); err != nil { |
| 109 | return nil, err |
| 110 | } |
| 111 | } |
| 112 | |
| 113 | return c.topQueriesParams(ctx) |
| 114 | } |
| 115 | |
| 116 | // Handle implements funcapi.MethodHandler. |
| 117 | func (f *funcTopQueries) Handle(ctx context.Context, method string, params funcapi.ResolvedParams) *funcapi.FunctionResponse { |
| 118 | if method != topQueriesMethodID { |
| 119 | return funcapi.NotFoundResponse(method) |
| 120 | } |
| 121 | |
| 122 | c := f.router.collector |
| 123 | if c.Functions.TopQueries.Disabled { |
| 124 | return funcapi.UnavailableResponse("top-queries function has been disabled in configuration") |
| 125 | } |
| 126 | if c.db == nil { |
| 127 | if err := c.openConnection(); err != nil { |
| 128 | return funcapi.UnavailableResponse(fmt.Sprintf("failed to open connection: %v", err)) |
| 129 | } |
| 130 | } |
| 131 | |
| 132 | queryCtx, cancel := context.WithTimeout(ctx, c.topQueriesTimeout()) |
| 133 | defer cancel() |
| 134 | return c.collectTopQueries(queryCtx, params.Column(paramSort)) |
| 135 | } |
| 136 | |
| 137 | func (f *funcTopQueries) Cleanup(ctx context.Context) {} |
| 138 | |
| 139 | func buildProxySQLSortParam(cols []proxysqlColumn) funcapi.ParamConfig { |
| 140 | return funcapi.BuildSortParam(cols) |
| 141 | } |
| 142 | |
| 143 | func (c *Collector) detectProxySQLDigestColumns(ctx context.Context) (map[string]bool, error) { |
| 144 | c.queryDigestColsMu.RLock() |
| 145 | if c.queryDigestCols != nil { |
| 146 | cols := c.queryDigestCols |
| 147 | c.queryDigestColsMu.RUnlock() |
| 148 | return cols, nil |
| 149 | } |
| 150 | c.queryDigestColsMu.RUnlock() |
| 151 | |
| 152 | c.queryDigestColsMu.Lock() |
| 153 | defer c.queryDigestColsMu.Unlock() |
| 154 | if c.queryDigestCols != nil { |
| 155 | return c.queryDigestCols, nil |
| 156 | } |
| 157 | |
| 158 | rows, err := c.db.QueryContext(ctx, "SELECT * FROM stats_mysql_query_digest WHERE 1=0") |
| 159 | if err != nil { |
| 160 | return nil, fmt.Errorf("failed to query stats_mysql_query_digest columns: %w", err) |
| 161 | } |
| 162 | defer rows.Close() |
| 163 | |
| 164 | names, err := rows.Columns() |
| 165 | if err != nil { |
| 166 | return nil, fmt.Errorf("failed to read columns: %w", err) |
| 167 | } |
| 168 | |
| 169 | cols := make(map[string]bool, len(names)) |
| 170 | for _, name := range names { |
| 171 | cols[strings.ToLower(name)] = true |
| 172 | } |
| 173 | c.queryDigestCols = cols |
| 174 | return cols, nil |
| 175 | } |
| 176 | |
| 177 | func (c *Collector) buildAvailableProxySQLColumns(available map[string]bool) []proxysqlColumn { |
| 178 | var cols []proxysqlColumn |
| 179 | for _, col := range proxysqlAllColumns { |
| 180 | if col.DBColumn == "" || available[strings.ToLower(col.DBColumn)] { |
| 181 | cols = append(cols, col) |
| 182 | } |
| 183 | } |
| 184 | return cols |
| 185 | } |
| 186 | |
| 187 | func (c *Collector) topQueriesParams(ctx context.Context) ([]funcapi.ParamConfig, error) { |
| 188 | availableCols, err := c.detectProxySQLDigestColumns(ctx) |
| 189 | if err != nil { |
| 190 | return nil, err |
| 191 | } |
| 192 | cols := c.buildAvailableProxySQLColumns(availableCols) |
| 193 | if len(cols) == 0 { |
| 194 | return nil, fmt.Errorf("no columns available in stats_mysql_query_digest") |
| 195 | } |
| 196 | return []funcapi.ParamConfig{buildProxySQLSortParam(cols)}, nil |
| 197 | } |
| 198 | |
| 199 | func (c *Collector) mapAndValidateProxySQLSortColumn(input string, cs funcapi.ColumnSet[proxysqlColumn]) string { |
| 200 | if cs.ContainsColumn(input) { |
| 201 | return input |
| 202 | } |
| 203 | if cs.ContainsColumn("totalTime") { |
| 204 | return "totalTime" |
| 205 | } |
| 206 | if cs.ContainsColumn("calls") { |
| 207 | return "calls" |
| 208 | } |
| 209 | names := cs.Names() |
| 210 | if len(names) > 0 { |
| 211 | return names[0] |
| 212 | } |
| 213 | return "" |
| 214 | } |
| 215 | |
| 216 | func (c *Collector) buildProxySQLDynamicSQL(cols []proxysqlColumn, sortColumn string, limit int) string { |
| 217 | selectParts := make([]string, 0, len(cols)) |
| 218 | for _, col := range cols { |
| 219 | expr := col.DBColumn |
| 220 | if col.IsMicroseconds { |
| 221 | expr = fmt.Sprintf("%s/1000", col.DBColumn) |
| 222 | } |
| 223 | selectParts = append(selectParts, fmt.Sprintf("%s AS `%s`", expr, col.Name)) |
| 224 | } |
| 225 | |
| 226 | return fmt.Sprintf(` |
| 227 | SELECT %s |
| 228 | FROM stats_mysql_query_digest |
| 229 | ORDER BY `+"`%s`"+` DESC |
| 230 | LIMIT %d |
| 231 | `, strings.Join(selectParts, ", "), sortColumn, limit) |
| 232 | } |
| 233 | |
| 234 | func (c *Collector) scanProxySQLDynamicRows(rows *sql.Rows, cols []proxysqlColumn) ([][]any, error) { |
| 235 | data := make([][]any, 0, 500) |
| 236 | |
| 237 | valuePtrs := make([]any, len(cols)) |
| 238 | values := make([]any, len(cols)) |
| 239 | |
| 240 | for rows.Next() { |
| 241 | for i, col := range cols { |
| 242 | switch col.Type { |
| 243 | case funcapi.FieldTypeString: |
| 244 | var v sql.NullString |
| 245 | values[i] = &v |
| 246 | case funcapi.FieldTypeInteger: |
| 247 | var v sql.NullInt64 |
| 248 | values[i] = &v |
| 249 | case funcapi.FieldTypeDuration: |
| 250 | var v sql.NullFloat64 |
| 251 | values[i] = &v |
| 252 | default: |
| 253 | var v any |
| 254 | values[i] = &v |
| 255 | } |
| 256 | valuePtrs[i] = values[i] |
| 257 | } |
| 258 | |
| 259 | if err := rows.Scan(valuePtrs...); err != nil { |
| 260 | return nil, fmt.Errorf("row scan failed: %w", err) |
| 261 | } |
| 262 | |
| 263 | row := make([]any, len(cols)) |
| 264 | for i, col := range cols { |
| 265 | switch v := values[i].(type) { |
| 266 | case *sql.NullString: |
| 267 | if v.Valid { |
| 268 | s := v.String |
| 269 | if col.Name == "query" { |
| 270 | s = strmutil.TruncateText(s, proxysqlMaxQueryTextLength) |
| 271 | } |
| 272 | row[i] = s |
| 273 | } else { |
| 274 | row[i] = "" |
| 275 | } |
| 276 | case *sql.NullInt64: |
| 277 | if v.Valid { |
| 278 | row[i] = v.Int64 |
| 279 | } else { |
| 280 | row[i] = int64(0) |
| 281 | } |
| 282 | case *sql.NullFloat64: |
| 283 | if v.Valid { |
| 284 | row[i] = v.Float64 |
| 285 | } else { |
| 286 | row[i] = float64(0) |
| 287 | } |
| 288 | default: |
| 289 | row[i] = nil |
| 290 | } |
| 291 | } |
| 292 | data = append(data, row) |
| 293 | } |
| 294 | |
| 295 | if err := rows.Err(); err != nil { |
| 296 | return nil, fmt.Errorf("rows iteration error: %w", err) |
| 297 | } |
| 298 | |
| 299 | return data, nil |
| 300 | } |
| 301 | |
| 302 | func (c *Collector) collectTopQueries(ctx context.Context, sortColumn string) *funcapi.FunctionResponse { |
| 303 | availableCols, err := c.detectProxySQLDigestColumns(ctx) |
| 304 | if err != nil { |
| 305 | return &funcapi.FunctionResponse{Status: 500, Message: fmt.Sprintf("failed to detect available columns: %v", err)} |
| 306 | } |
| 307 | |
| 308 | cols := c.buildAvailableProxySQLColumns(availableCols) |
| 309 | if len(cols) == 0 { |
| 310 | return &funcapi.FunctionResponse{Status: 500, Message: "no columns available in stats_mysql_query_digest"} |
| 311 | } |
| 312 | |
| 313 | cs := proxysqlColumnSet(cols) |
| 314 | sortColumn = c.mapAndValidateProxySQLSortColumn(sortColumn, cs) |
| 315 | |
| 316 | limit := c.topQueriesLimit() |
| 317 | |
| 318 | query := c.buildProxySQLDynamicSQL(cols, sortColumn, limit) |
| 319 | rows, err := c.db.QueryContext(ctx, query) |
| 320 | if err != nil { |
| 321 | if ctx.Err() == context.DeadlineExceeded { |
| 322 | return &funcapi.FunctionResponse{Status: 504, Message: "query timed out"} |
| 323 | } |
| 324 | return &funcapi.FunctionResponse{Status: 500, Message: fmt.Sprintf("query failed: %v", err)} |
| 325 | } |
| 326 | defer rows.Close() |
| 327 | |
| 328 | data, err := c.scanProxySQLDynamicRows(rows, cols) |
| 329 | if err != nil { |
| 330 | return &funcapi.FunctionResponse{Status: 500, Message: err.Error()} |
| 331 | } |
| 332 | |
| 333 | defaultSort := "totalTime" |
| 334 | if !cs.ContainsColumn(defaultSort) { |
| 335 | defaultSort = "calls" |
| 336 | } |
| 337 | |
| 338 | return &funcapi.FunctionResponse{ |
| 339 | Status: 200, |
| 340 | Help: "Top SQL queries from ProxySQL stats_mysql_query_digest", |
| 341 | Columns: cs.BuildColumns(), |
| 342 | Data: data, |
| 343 | DefaultSortColumn: defaultSort, |
| 344 | RequiredParams: []funcapi.ParamConfig{buildProxySQLSortParam(cols)}, |
| 345 | ChartingConfig: cs.BuildCharting(), |
| 346 | } |
| 347 | } |