Bug #59631 BIGINT UNSIGNED byte related columns in performance_schema
Submitted: 20 Jan 2011 13:03 Modified: 22 Jul 2011 18:30
Reporter: Mark Leith Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.5.8 OS:Any
Assigned to: Marc Alff
Triage: Needs Triage: R6 (Needs Assessment) / E6 (Needs Assessment)

[20 Jan 2011 13:03] Mark Leith
Description:
performance_schema currently uses BIGINT UNSIGNED columns for many of it's instrumentation points, including all of the columns that track read or write IO. 

This means that if you want to compare read vs write IO, you are limited by only calculating positive numbers by default. For instance:

mysql> SELECT SUBSTRING_INDEX(file_name, '/', -1),
   ->        count_read, sum_number_of_bytes_read,
   ->        count_write, sum_number_of_bytes_write,
   ->        sum_number_of_bytes_write - sum_number_of_bytes_read bytes_outstanding
   ->   FROM file_summary_by_instance
   ->  WHERE event_name like '%binlog%';
ERROR 1690 (22003): BIGINT UNSIGNED value is out of range in '(`performance_schema`.`file_summary_by_instance`.`SUM_NUMBER_OF_BYTES_WRITE` - `performance_schema`.`file_summary_by_instance`.`SUM_NUMBER_OF_BYTES_READ`)'

The only way to be able to do this would be to:

CAST(sum_number_of_bytes_write AS SIGNED) - CAST(sum_number_of_bytes_read AS SIGNED)

In bytes terms we are talking about being able to track 8 exabytes (BIGINT) vs 16 exabytes (BIGINT UNSIGNED) of IO (per file, or per IO wait). If we were to steadily do 1GB of IO per second to a single file, it would take us roughly 272 years to overflow a BIGINT, and double that for a BIGINT UNSIGNED. 

Therefore this is a request to modify all byte related columns in performance_schema to BIGINT, instead of BIGINT UNSIGNED. 

How to repeat:
o Enable log_bin and performance_schema

USE test
CREATE TABLE t1 (i INT);
INSERT INTO t1 VALUES (1);
SELECT file_name, sum_number_of_bytes_write - sum_number_of_bytes_read AS bytes_diff
  FROM performance_schema.file_summary_by_instance;

Suggested fix:
Make all byte related columns BIGINT instead of BIGINT UNSIGNED
[22 Jul 2011 18:30] Paul Dubois
Noted in 5.6.3 changelog.

Performance Schema table columns that held byte counts were BIGINT
UNSIGNED were changed to BIGINT (signed). This makes it easier to
perform calculations that compute differences between columns. 

CHANGESET - http://lists.mysql.com/commits/139110