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] Shane Bester
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

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.