Bug #92364 events_transactions_summary_global_by_event_name not working as expected
Submitted: 11 Sep 12:06 Modified: 17 Sep 12:43
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Performance Schema Severity:S3 (Non-critical)
Version:5.7.22, 8.0.12 OS:Any
Assigned to: CPU Architecture:Any
Tags: events_transactions_summary_global_by_event_name

[11 Sep 12:06] Przemyslaw Malkowski
Description:
According to https://dev.mysql.com/doc/refman/8.0/en/innodb-performance-ro-txn.html, InnoDB should use read-only transactions optimization not only for explicit START TRANSACTION READ ONLY, but also when just a transaction is purely read one. However, looks like only explicit ones work as expected. Even void transaction increments read-write counter. Am I missing something?

How to repeat:
mysql [localhost] {msandbox} (db1) > select @@version,@@version_comment,@@autocommit;
+-----------+------------------------------+--------------+
| @@version | @@version_comment            | @@autocommit |
+-----------+------------------------------+--------------+
| 8.0.12    | MySQL Community Server - GPL |            1 |
+-----------+------------------------------+--------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (db1) > SELECT * FROM performance_schema.setup_consumers WHERE NAME LIKE '%transactions%';
+----------------------------------+---------+
| NAME                             | ENABLED |
+----------------------------------+---------+
| events_transactions_current      | YES     |
| events_transactions_history      | YES     |
| events_transactions_history_long | YES     |
+----------------------------------+---------+
3 rows in set (0.00 sec)

mysql [localhost] {msandbox} (db1) > SELECT * FROM performance_schema.setup_instruments WHERE NAME = 'transaction';
+-------------+---------+-------+------------+------------+---------------+
| NAME        | ENABLED | TIMED | PROPERTIES | VOLATILITY | DOCUMENTATION |
+-------------+---------+-------+------------+------------+---------------+
| transaction | YES     | YES   |            |          0 | NULL          |
+-------------+---------+-------+------------+------------+---------------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (db1) > SELECT COUNT_STAR,COUNT_READ_WRITE,COUNT_READ_ONLY FROM performance_schema.events_transactions_summary_global_by_event_name\G
*************************** 1. row ***************************
      COUNT_STAR: 17
COUNT_READ_WRITE: 17
 COUNT_READ_ONLY: 0
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (db1) > select a from t1 where id=1;                                                                                                                                                  +-------+
| a     |
+-------+
| foobr |
+-------+
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (db1) > SELECT COUNT_STAR,COUNT_READ_WRITE,COUNT_READ_ONLY FROM performance_schema.events_transactions_summary_global_by_event_name\G
*************************** 1. row ***************************
      COUNT_STAR: 18
COUNT_READ_WRITE: 18
 COUNT_READ_ONLY: 0
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (db1) > start transaction; commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (db1) > SELECT COUNT_STAR,COUNT_READ_WRITE,COUNT_READ_ONLY FROM performance_schema.events_transactions_summary_global_by_event_name\G
*************************** 1. row ***************************
      COUNT_STAR: 19
COUNT_READ_WRITE: 19
 COUNT_READ_ONLY: 0
1 row in set (0.00 sec)

mysql [localhost] {msandbox} (db1) > start transaction read only; commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {msandbox} (db1) > SELECT COUNT_STAR,COUNT_READ_WRITE,COUNT_READ_ONLY FROM performance_schema.events_transactions_summary_global_by_event_name\G
*************************** 1. row ***************************
      COUNT_STAR: 20
COUNT_READ_WRITE: 19
 COUNT_READ_ONLY: 1
1 row in set (0.00 sec)

Suggested fix:
Fix implicit read-only transactions or explain the above behavior.
[11 Sep 12:34] Przemyslaw Malkowski
OK, so the default transaction access mode is read-write then: https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_transaction_re...

When I change the global to transaction_read_only=ON, then new connections indeed follow the behavior as documented and create read-only transactions if no writes used. 
I guess this is documentation issue then, unless default transaction_read_only mode was meant to be enabled.
[13 Sep 11:50] Przemyslaw Malkowski
Correction to my previous comment. Using global transaction_read_only=ON virtually turns the whole instance read-only as transactions no longer can upgrade to read-write. And I find the variable description:
"The default transaction access mode. The value can be OFF (read/write, the default) or ON (read only)." as misleading. 

Now, the issue turned out to be not with transactions not using read-only optimization properly but rather the performance_schema.events_transactions_summary_global_by_event_name showing wrong results in this regard.

Another test with enabled related information_schema.innodb_metrics:

SET GLOBAL innodb_monitor_enable = 'trx%comm%';

proofs correct InnoDB behavior. For example, a void transaction does not increment any metrics counter (it does increment the P_S.events_transactions_summary_global_by_event_name for some reason!):

mysql > start transaction; commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql > SELECT name, comment, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%';
+---------------------------+--------------------------------------------------------------------+-------+
| name                      | comment                                                            | count |
+---------------------------+--------------------------------------------------------------------+-------+
| trx_rw_commits            | Number of read-write transactions  committed                       |     0 |
| trx_ro_commits            | Number of read-only transactions committed                         |     0 |
| trx_nl_ro_commits         | Number of non-locking auto-commit read-only transactions committed |     0 |
| trx_commits_insert_update | Number of transactions committed with inserts and updates          |     0 |
+---------------------------+--------------------------------------------------------------------+-------+
4 rows in set (0.00 sec)

And real transactions do increment these triggers as expected:

mysql > select a from db1.t1 where id=1;
+-------+
| a     |
+-------+
| foobr |
+-------+
1 row in set (0.00 sec)

mysql > SELECT name, comment, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%';
+---------------------------+--------------------------------------------------------------------+-------+
| name                      | comment                                                            | count |
+---------------------------+--------------------------------------------------------------------+-------+
| trx_rw_commits            | Number of read-write transactions  committed                       |     0 |
| trx_ro_commits            | Number of read-only transactions committed                         |     0 |
| trx_nl_ro_commits         | Number of non-locking auto-commit read-only transactions committed |     1 |
| trx_commits_insert_update | Number of transactions committed with inserts and updates          |     0 |
+---------------------------+--------------------------------------------------------------------+-------+
4 rows in set (0.00 sec)

mysql > start transaction; select a from db1.t1 where id=1; commit;
Query OK, 0 rows affected (0.00 sec)

+-------+
| a     |
+-------+
| foobr |
+-------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql > SELECT name, comment, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%';
+---------------------------+--------------------------------------------------------------------+-------+
| name                      | comment                                                            | count |
+---------------------------+--------------------------------------------------------------------+-------+
| trx_rw_commits            | Number of read-write transactions  committed                       |     0 |
| trx_ro_commits            | Number of read-only transactions committed                         |     1 |
| trx_nl_ro_commits         | Number of non-locking auto-commit read-only transactions committed |     1 |
| trx_commits_insert_update | Number of transactions committed with inserts and updates          |     0 |
+---------------------------+--------------------------------------------------------------------+-------+
4 rows in set (0.00 sec)

mysql > update db1.t1 set a="foo" where id=1;
Query OK, 1 row affected (0.10 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql > SELECT name, comment, count FROM information_schema.innodb_metrics WHERE name like 'trx%comm%';
+---------------------------+--------------------------------------------------------------------+-------+
| name                      | comment                                                            | count |
+---------------------------+--------------------------------------------------------------------+-------+
| trx_rw_commits            | Number of read-write transactions  committed                       |     1 |
| trx_ro_commits            | Number of read-only transactions committed                         |     1 |
| trx_nl_ro_commits         | Number of non-locking auto-commit read-only transactions committed |     1 |
| trx_commits_insert_update | Number of transactions committed with inserts and updates          |     1 |
+---------------------------+--------------------------------------------------------------------+-------+
4 rows in set (0.00 sec)

Summarizing, performance_schema.events_transactions_summary_global_by_event_name behavior for instrumenting transactions seems completely broken.
[13 Sep 11:53] Przemyslaw Malkowski
updating subject and category
[17 Sep 12:43] Umesh Shastry
Hello Przemyslaw,

Thank you for the report and feedback.
Observed this with 8.0.12 build.

regards,
Umesh