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
