MySQL Bugs: #92364: events_transactions_summary_global_by_event

Bug #92364	events_transactions_summary_global_by_event_name not working as expected
Submitted:	11 Sep 2018 12:06	Modified:	28 Feb 2019 18:07
Reporter:	Przemyslaw Malkowski	Email Updates:
Status:	Closed	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

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.

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.

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.

updating subject and category

Hello Przemyslaw,

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

regards,
Umesh

Posted by developer:
 
The following documentation was added. Changes should appear online soon.

Transaction Summary Tables
https://dev.mysql.com/doc/mysql-perfschema-excerpt/8.0/en/transaction-summary-tables.html

"The transaction access mode specifies whether transactions
operate in read/write or read-only mode."

"Transaction event collection occurs for all non-aborted
transactions initiated by the server, including empty
transactions."			
			
The INFORMATION_SCHEMA INNODB_METRICS Table
https://dev.mysql.com/doc/refman/8.0/en/innodb-metrics-table.html

"Transaction counter COUNT values may differ from the number of transaction
events reported in Performance Schema EVENTS_TRANSACTIONS_SUMMARY tables. 
InnoDB counts only those transactions that it executes, whereas
Performance Schema collects events for all non-aborted transactions
initiated by the server, including empty transactions."