Bug #79658 performance_schema statements by thread limited instrumentation
Submitted: 16 Dec 2015 8:49 Modified: 24 Dec 2015 7:18
Reporter: Jervin R Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.10 OS:Any
Assigned to: MySQL Verification Team CPU Architecture:Any

[16 Dec 2015 8:49] Jervin R 
Description:
WHen using ROW binlog format on the master, only 'statement/sql/begin' seems to be instrumented.

slave1 [localhost] {msandbox} (performance_schema) > SELECT THREAD_ID, EVENT_NAME, COUNT_STAR,
    ->   SUM_TIMER_WAIT/1000000 AS SUM_TIMER_WAIT_MS,
    ->   (SUM_TIMER_WAIT/COUNT_STAR)/1000000 AS AVG_TIME_MS
    -> FROM events_statements_summary_by_thread_by_event_name
    -> WHERE THREAD_ID IN (
    ->     SELECT THREAD_ID FROM replication_applier_status_by_worker
    -> ) AND COUNT_STAR > 0;
    SELECT THREAD_ID FROM replication_applier_status_by_worker
) AND COUNT_STAR > 0;+-----------+---------------------+------------+-------------------+-------------+
| THREAD_ID | EVENT_NAME          | COUNT_STAR | SUM_TIMER_WAIT_MS | AVG_TIME_MS |
+-----------+---------------------+------------+-------------------+-------------+
|        84 | statement/sql/begin |         43 |          757.5610 | 17.61769767 |
|        85 | statement/sql/begin |        339 |         5828.5290 | 17.19330088 |
|        86 | statement/sql/begin |       1332 |        23372.3000 | 17.54677177 |
|        87 | statement/sql/begin |       9692 |       170775.2900 | 17.62023215 |
+-----------+---------------------+------------+-------------------+-------------+
4 rows in set (0.01 sec)

Compared with STATEMENT, this is broken down properly to more specific statement:

slave1 [localhost] {msandbox} (performance_schema) > SELECT THREAD_ID, EVENT_NAME, COUNT_STAR,
    ->   SUM_TIMER_WAIT/1000000 AS SUM_TIMER_WAIT_MS,
    ->   (SUM_TIMER_WAIT/COUNT_STAR)/1000000 AS AVG_TIME_MS
    -> FROM events_statements_summary_by_thread_by_event_name
    -> WHERE THREAD_ID IN (
    ->     SELECT THREAD_ID FROM replication_applier_status_by_worker
    -> ) AND COUNT_STAR > 0;
    SELECT THREAD_ID FROM replication_applier_status_by_worker
) AND COUNT_STAR > 
+-----------+----------------------+------------+-------------------+--------------+
| THREAD_ID | EVENT_NAME           | COUNT_STAR | SUM_TIMER_WAIT_MS | AVG_TIME_MS  |
+-----------+----------------------+------------+-------------------+--------------+
|        40 | statement/sql/update |          3 |          527.2640 | 175.75466667 |
|        40 | statement/sql/insert |          1 |          115.3970 | 115.39700000 |
|        40 | statement/sql/delete |          1 |          134.7430 | 134.74300000 |
|        40 | statement/sql/begin  |         17 |          272.2470 |  16.01452941 |
|        41 | statement/sql/update |        102 |        30968.4890 | 303.61263725 |
|        41 | statement/sql/insert |         34 |         3991.2110 | 117.38855882 |
|        41 | statement/sql/delete |         34 |         4161.8310 | 122.40679412 |
|        41 | statement/sql/begin  |        125 |         2107.1340 |  16.85707200 |
|        42 | statement/sql/update |       1809 |       627345.4180 | 346.79127584 |
|        42 | statement/sql/insert |        603 |        73814.9090 | 122.41278441 |
|        42 | statement/sql/delete |        603 |        75502.3550 | 125.21120232 |
|        42 | statement/sql/begin  |       1214 |        20815.6950 |  17.14637150 |
|        43 | statement/sql/update |      12435 |      3552280.8530 | 285.66794154 |
|        43 | statement/sql/insert |       4145 |       502627.9480 | 121.26126610 |
|        43 | statement/sql/delete |       4145 |       515232.4040 | 124.30214813 |
|        43 | statement/sql/begin  |       9334 |       157567.5740 |  16.88103428 |
+-----------+----------------------+------------+-------------------+--------------+

How to repeat:
Can repeat with standard MTS setup procedure and enabling statements instrumentaion on performance_schema.
[19 Dec 2015 16:37] MySQL Verification Team 
the documentation hints at a limitation:
https://dev.mysql.com/doc/refman/5.7/en/performance-schema-statement-tables.html

"The preceding description applies only for statement-based replication. For row-based replication, table I/O done on the slave as it processes row changes can be instrumented, but row events in the relay log do not appear as discrete statements."
[24 Dec 2015 7:18] MySQL Verification Team 
Hi,

as Shane already wrote, this is not a bug but designed behavior

all best
Bogdan Kecman