M_query_time_p99 Always Zero

Asuwini_P · February 14, 2023, 12:10pm

Hi Everyone,

Whenever I am seeing this m_query_time_p99 column in clickhouse-client, it is always showing 0, even in the production servers.
Can you please help me to understand what exactly this column means?

Michael_Coburn · February 14, 2023, 5:59pm

Hi @Asuwini_P thank you for posting to the Percona forums!

I just checked in my own instance and I too have no values for field m_query_time_p99. The field is described as ‘99 percentile of value of query_time in bucket’ but I suppose the QAN agent isn’t yet calculating this value.

Asuwini_P · February 15, 2023, 9:10am

Hi @Michael_Coburn ,

Thank you for your response

Is there any way to get this value? or could we calculate this value with the available columns in the metrics table?

Michael_Coburn · February 15, 2023, 3:05pm

Hi @Asuwini_P Can you tell me what the query source is for your MySQL queries? After taking a quick look at the code of the agent, It seems that if you are using the MySQL Slow Log as the source then the field should be populated

github.com

percona/pmm/blob/v2.34.0/agent/agents/mysql/slowlog/slowlog.go#L477


      
          		// BooleanMetrics: qc_hit, full_scan, full_join, tmp_table, tmp_table_on_disk, filesort, filesort_on_disk,
          		// select_full_range_join, select_range, select_range_check, sort_range, sort_rows, sort_scan,
          		// no_index_used, no_good_index_used.
          
          
		// query_time - Query_time
          		if m, ok := v.Metrics.TimeMetrics["Query_time"]; ok {
          			mb.Common.MQueryTimeCnt = float32(m.Cnt)
          			mb.Common.MQueryTimeSum = float32(m.Sum)
          			mb.Common.MQueryTimeMax = float32(*m.Max)
          			mb.Common.MQueryTimeMin = float32(*m.Min)
          			mb.Common.MQueryTimeP99 = float32(*m.P99)
          		}
          		// lock_time - Lock_time
          		if m, ok := v.Metrics.TimeMetrics["Lock_time"]; ok {
          			mb.Mysql.MLockTimeCnt = float32(m.Cnt)
          			mb.Mysql.MLockTimeSum = float32(m.Sum)
          			mb.Mysql.MLockTimeMax = float32(*m.Max)
          			mb.Mysql.MLockTimeMin = float32(*m.Min)
          			mb.Mysql.MLockTimeP99 = float32(*m.P99)
          		}
          		// rows_sent - Rows_sent

I don’t see the equivalent for perfschema, i think because perfschema is already aggregated data so we can’t calculate 99th percentile

github.com

percona/pmm/blob/v2.34.0/agent/agents/mysql/perfschema/perfschema.go#L429


      
          		}
          
          
		for _, p := range []struct {
          			value float32  // result value: currentESS.SumXXX-prevESS.SumXXX
          			sum   *float32 // MetricsBucket.XXXSum field to write value
          			cnt   *float32 // MetricsBucket.XXXCnt field to write count
          		}{
          			// in order of events_statements_summary_by_digest columns
          
          
			// convert picoseconds to seconds
          			{inc(currentESS.SumTimerWait, prevESS.SumTimerWait) / 1000000000000, &mb.Common.MQueryTimeSum, &mb.Common.MQueryTimeCnt},
          			{inc(currentESS.SumLockTime, prevESS.SumLockTime) / 1000000000000, &mb.Mysql.MLockTimeSum, &mb.Mysql.MLockTimeCnt},
          
          
			{inc(currentESS.SumRowsAffected, prevESS.SumRowsAffected), &mb.Mysql.MRowsAffectedSum, &mb.Mysql.MRowsAffectedCnt},
          			{inc(currentESS.SumRowsSent, prevESS.SumRowsSent), &mb.Mysql.MRowsSentSum, &mb.Mysql.MRowsSentCnt},
          			{inc(currentESS.SumRowsExamined, prevESS.SumRowsExamined), &mb.Mysql.MRowsExaminedSum, &mb.Mysql.MRowsExaminedCnt},
          
          
			{inc(currentESS.SumCreatedTmpDiskTables, prevESS.SumCreatedTmpDiskTables), &mb.Mysql.MTmpDiskTablesSum, &mb.Mysql.MTmpDiskTablesCnt},
          			{inc(currentESS.SumCreatedTmpTables, prevESS.SumCreatedTmpTables), &mb.Mysql.MTmpTablesSum, &mb.Mysql.MTmpTablesCnt},
          			{inc(currentESS.SumSelectFullJoin, prevESS.SumSelectFullJoin), &mb.Mysql.MFullJoinSum, &mb.Mysql.MFullJoinCnt},
          			{inc(currentESS.SumSelectFullRangeJoin, prevESS.SumSelectFullRangeJoin), &mb.Mysql.MSelectFullRangeJoinSum, &mb.Mysql.MSelectFullRangeJoinCnt},

Asuwini_P · February 15, 2023, 6:49pm

@Michael_Coburn ,
Thank you for the detailed explanation.
I am using performance schema as a query source. So that i couldn’t able to fetch the data

Michael_Coburn · February 21, 2023, 3:51pm

Hi @Asuwini_P
You may want to experiment with the slow log source and then you should start to see 99p calculations

Daniel_Guzman_Burgos · September 13, 2024, 3:00pm

Created this feature request: [PMM-13363] - Percona JIRA

Topic		Replies	Views
The QAN p99 calculation seems to be wrong? PMM 2.x	5	1222	September 15, 2022
Pmm-agent doesn't set database for some queries when analyzing mysql.slow-log PMM 2.x pmm , mysql , closed-no-reply	0	695	September 7, 2021
Unable to get query in Query Analytics (MYSQL) taking time more than 5min Other MySQL® Questions mysql , percona	4	969	January 6, 2023
mysql:queries and mysql:metrics not work .. PMM 1.x	7	1317	October 27, 2016
Percona Monitoring and Management 1.9.1 Is Now Available PMM 1.x	0	409	April 13, 2018

M_query_time_p99 Always Zero

Related topics