How to write SQL to calculate the total execution time of this SQL in the dashboard/#/statement?

translator_bot · June 22, 2024, 5:43pm

Note:
This topic has been translated from a Chinese forum by GPT and might contain errors.

Original topic: dashboard/#/statement 这个sql总耗时自己用sql怎么写

| username: tidb狂热爱好者

【TiDB Usage Environment】Production Environment
【TiDB Version】
6.5
【Reproduction Path】

dashboard/#/statement How to write this SQL for total elapsed time

Want to set up automatic alerts for SQL statements with a total elapsed time greater than 10 minutes
【Encountered Issues: Problem Phenomenon and Impact】
【Resource Configuration】
【Attachments: Screenshots/Logs/Monitoring】

translator_bot · June 22, 2024, 5:43pm

| username: Raymond | Original post link

Querying the “time” field in the processlist table should be possible.

translator_bot · June 22, 2024, 5:43pm

| username: 人如其名 | Original post link

github.com

pingcap/tidb-dashboard/blob/f318bea10b19bc95288d318ff2a18fd4d91ddd1f/pkg/apiserver/statement/queries.go#L60


      
          	selectStmt, err := s.genSelectStmt(tableColumns, reqFields)
          	if err != nil {
          		return nil, err
          	}
          
          	query := db.
          		Select(selectStmt).
          		Table(statementsTable).
          		// https://stackoverflow.com/questions/3269434/whats-the-most-efficient-way-to-test-if-two-ranges-overlap
          		Where("summary_begin_time <= FROM_UNIXTIME(?) AND summary_end_time >= FROM_UNIXTIME(?)", endTime, beginTime).
          		Group("schema_name, digest").
          		Order("agg_sum_latency DESC")
          
          	if len(schemas) > 0 {
          		regex := make([]string, 0, len(schemas))
          		for _, schema := range schemas {
          			regex = append(regex, fmt.Sprintf("\\b%s\\.", regexp.QuoteMeta(schema)))
          		}
          		regexAll := strings.Join(regex, "|")
          		query = query.Where("table_names REGEXP ?", regexAll)
          	}

translator_bot · June 22, 2024, 5:43pm

| username: wzf0072 | Original post link

select * from information_schema.processlist where info is not null and time>=600

translator_bot · June 22, 2024, 5:43pm

| username: Kongdom | Original post link

Pay attention to the time judgment. The original version judged two times. This is quite strange, usually judging one time is enough.

translator_bot · June 22, 2024, 5:43pm

| username: xingzhenxiang | Original post link

SELECT id, info, time, mem 
FROM information_schema.processlist 
WHERE info IS NOT NULL AND time >= 600;

translator_bot · June 22, 2024, 5:43pm

| username: system | Original post link

This topic was automatically closed 60 days after the last reply. New replies are no longer allowed.