Bug #81699 ps_statement_avg_latency_histogram often fails to draw the point of max(avg_us)
Submitted: 3 Jun 2016 4:35 Modified: 14 Jun 2016 7:46
Reporter: keita nishizono Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: SYS Schema Severity:S3 (Non-critical)
Version:5.7.12, 5.7.13 OS:Any
Assigned to: CPU Architecture:Any

[3 Jun 2016 4:35] keita nishizono
Description:
ps_statement_avg_latency_histogram often fails to draw the point of max(avg_us) as follows.

mysql> SELECT * FROM sys.x$ps_digest_avg_latency_distribution;
+-----+--------+
| cnt | avg_us |
+-----+--------+
|   1 |    130 |
|   1 |   2545 |
|   1 |   6574 |
|   1 |  10336 |
|   1 |  21768 |
|   1 |  27656 |
|   1 |  43610 |
|   1 | 807309 |
+-----+--------+
8 rows in set (0.00 sec)

mysql> CALL ps_statement_avg_latency_histogram()\G
*************************** 1. row ***************************
Performance Schema Statement Digest Average Latency Histogram: 

  . = 1 unit
  * = 2 units
  # = 3 units

(0 - 50ms)     7 | .......
(50 - 101ms)   0 | 
(101 - 151ms)  0 | 
(151 - 202ms)  0 | 
(202 - 252ms)  0 | 
(252 - 303ms)  0 | 
(303 - 353ms)  0 | 
(353 - 404ms)  0 | 
(404 - 454ms)  0 | 
(454 - 504ms)  0 | 
(504 - 555ms)  0 | 
(555 - 605ms)  0 | 
(605 - 656ms)  0 | 
(656 - 706ms)  0 | 
(706 - 757ms)  0 | 
(757 - 807ms)  0 | 

  Total Statements: 8; Buckets: 16; Bucket Size: 50 ms;

1 row in set (0.01 sec)

How to repeat:
See description.

Suggested fix:
In this procedure, bucket size is defined as:

  SELECT ROUND((MAX(avg_us) - MIN(avg_us)) / (@buckets := 16)) AS size
    FROM sys.x$ps_digest_avg_latency_distribution

But the lowest range always begins with 0, it should be defined as:

  SELECT ROUND(MAX(avg_us) / (@buckets := 16)) AS size
    FROM sys.x$ps_digest_avg_latency_distribution

Also, count_in_bucket in the maximum range is defined as:

  IFNULL((SELECT SUM(cnt) FROM sys.x$ps_digest_avg_latency_distribution AS b1 
    WHERE b1.avg_us > @bucket_size * 15 AND b1.avg_us <= @bucket_size * 16), 0))

I think it should be defined as:

  IFNULL((SELECT SUM(cnt) FROM sys.x$ps_digest_avg_latency_distribution AS b1 
    WHERE b1.avg_us > @bucket_size * 15), 0))
[14 Jun 2016 7:46] Umesh Shastry
Hello keita nishizono,

Thank you for the report.

Thanks,
Umesh