Bug #46351 PERFORMANCE_SCHEMA: Inconsistency between SUMMARY tables

• Submitted: 23 Jul 2009 11:56
• Modified: 24 Jul 2009 18:45
• Reporter: Jørgen Austvik
• Status: Not a Bug
• Category: MySQL Server: Performance Schema
• Severity: S3 (Non-critical)
• OS: Any
• Assigned to: Marc ALFF
• CPU Architecture: Any

[23 Jul 2009 11:56] Jørgen Austvik

Description:
Adding as bug report, as I did not get any reply on my mails to Marc and Guilhem, and I do not want to forget this while being on vacation.

Marc Alff 2009-07-01:
--------8<---------------8<---------------8<---------------8<-------
Hi Guilhem & All

Guilhem Bichot wrote:
> How about creating some new mtr tests like this:
> mysql> select sum(count_read) from FILE_SUMMARY_BY_INSTANCE where
> event_name like "%frm%";
> +-----------------+
> | sum(count_read) |
> +-----------------+
> |             257 |
> +-----------------+
> 1 row in set (0.00 sec)
> mysql> select count_read from FILE_SUMMARY_BY_EVENT_NAME where
> event_name like "%frm%";
> +------------+
> | count_read |
> +------------+
> |        257 |
> +------------+
> (test says ok because 257==257)
> ?
>

The content of tests is a QA matter, I will defer this to Jorgen. 
--------8<---------------8<---------------8<---------------8<-------

When I add that test, it fails.

How to repeat:
----------8<----------------8<----------------8<----------------8<----------------8<------
# Generate some data:
UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'YES', timed =
'YES';
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (id INT PRIMARY KEY, b CHAR(100) DEFAULT 'initial value')
ENGINE=MyISAM;
INSERT INTO t1 (id) VALUES (1), (2), (3), (4), (5), (6), (7), (8);

# Turn off instrumentation to get stable data
UPDATE performance_schema.SETUP_INSTRUMENTS SET enabled = 'NO';

# FILE_SUMMARY

SELECT EVENT_NAME, e.COUNT_READ,  SUM(i.COUNT_READ),
e.COUNT_WRITE, SUM(i.COUNT_WRITE)
FROM performance_schema.FILE_SUMMARY_BY_EVENT_NAME AS e
JOIN performance_schema.FILE_SUMMARY_BY_INSTANCE AS i USING (EVENT_NAME)
GROUP BY EVENT_NAME
HAVING e.COUNT_READ <> SUM(i.COUNT_READ)
OR e.COUNT_WRITE <> SUM(i.COUNT_WRITE);

EVENT_NAME      COUNT_READ      SUM(i.COUNT_READ)       COUNT_WRITE
SUM(i.COUNT_WRITE)
wait/io/file/maria/key_file     8       8       33      2

SELECT EVENT_NAME, e.SUM_NUMBER_OF_BYTES_READ,
SUM(i.SUM_NUMBER_OF_BYTES_READ),
e.SUM_NUMBER_OF_BYTES_WRITE, SUM(i.SUM_NUMBER_OF_BYTES_WRITE)
FROM performance_schema.FILE_SUMMARY_BY_EVENT_NAME AS e
JOIN performance_schema.FILE_SUMMARY_BY_INSTANCE AS i USING (EVENT_NAME)
GROUP BY EVENT_NAME
HAVING e.SUM_NUMBER_OF_BYTES_READ  <> SUM(i.SUM_NUMBER_OF_BYTES_READ)
OR e.SUM_NUMBER_OF_BYTES_WRITE <> SUM(i.SUM_NUMBER_OF_BYTES_WRITE);

EVENT_NAME      SUM_NUMBER_OF_BYTES_READ
SUM(i.SUM_NUMBER_OF_BYTES_READ) SUM_NUMBER_OF_BYTES_WRITE
SUM(i.SUM_NUMBER_OF_BYTES_WRITE)
wait/io/file/maria/key_file     2596    2596    1576    326

SELECT * FROM performance_schema.FILE_SUMMARY_BY_EVENT_NAME
WHERE EVENT_NAME LIKE "%maria/key_file";

EVENT_NAME      COUNT_READ      COUNT_WRITE     SUM_NUMBER_OF_BYTES_READ
        SUM_NUMBER_OF_BYTES_WRITE
wait/io/file/maria/key_file     8       33      2596    1576

SELECT * FROM performance_schema.FILE_SUMMARY_BY_INSTANCE
WHERE EVENT_NAME LIKE "%maria/key_file";

FILE_NAME       EVENT_NAME      COUNT_READ      COUNT_WRITE
SUM_NUMBER_OF_BYTES_READ        SUM_NUMBER_OF_BYTES_WRITE
/tmp/var_666_9PkA/tmp/mysqld.1/#sql517b_1_0.MAI
wait/io/file/maria/key_file     4       1       880     163
/tmp/var_666_9PkA/tmp/mysqld.1/#sql517b_1_4.MAI
wait/io/file/maria/key_file     4       1       1716    163
----------8<----------------8<----------------8<----------------8<----------------8<------

[24 Jul 2009 18:45] Marc ALFF

This is not a bug, the test script does not account for deleted files.

A new test script has been checked in to cover this case,
which shows that the performance schema aggregates are consistent.

See aggregate.test in mysql-azalea-perfschema