To assess the impact of purge lag (MDEV-29401), it could be useful to separately track the reads of pages that reside in the undo tablespaces, or in the system tablespace. That is, we could have a separate System_pages_read or System_pages_accessed. I do not currently have an idea how to do this without adding conditional branches (which destroy the performance of superscalar CPUs). For the Pages_read counter, which would cover reads from the file system to the buffer pool, we can afford to add a condition, because that code path is slow and hopefully rarely invoked.

Possibly, we could add another counter at the high level (say, trx_undo_prev_version_build(), row_vers_impl_x_locked_low()) for tracking undo log record access, and let the page counters cover all types of page reads.

Undo_rows_read added

The undo_records_read that monty added to trx_undo_prev_version_build() is covering also "semi-consistent reads" and implicit lock checks. So, the code is complete. I would like to see this being pushed to 10.6 after some performance and correctness testing.

psergei implemented an ANALYZE interface and helped me develop the following test case, which I think should be added so that we will have some validation that the counters actually work:

diff --git a/mysql-test/suite/innodb/t/mvcc_secondary.test b/mysql-test/suite/innodb/t/mvcc_secondary.test

index 93c91c40076..40ece3d33a7 100644

--- a/mysql-test/suite/innodb/t/mvcc_secondary.test

+++ b/mysql-test/suite/innodb/t/mvcc_secondary.test

@@ -17,10 +17,14 @@ UPDATE t1 SET c=REPEAT('d',170);

 connect (con1,localhost,root,,);

 SELECT pk FROM t1 FORCE INDEX (c);

+let $js=`ANALYZE FORMAT=json_ext SELECT pk FROM t1 FORCE INDEX (c)`;

+evalp select json_extract('$js', '\$**.undo_records_read') undo_records_read;

 connection default;

 COMMIT;

 connection con1;

 SELECT pk FROM t1 FORCE INDEX (c);

+let $js=`ANALYZE FORMAT=json_ext SELECT pk FROM t1 FORCE INDEX (c)`;

+evalp select json_extract('$js', '\$**.undo_records_read') undo_records_read;

 disconnect con1;

 connection default;

 DROP TABLE t1;

diff --git a/mysql-test/suite/innodb/r/mvcc_secondary.result b/mysql-test/suite/innodb/r/mvcc_secondary.result

index 2289742e830..ae30f96b5a2 100644

--- a/mysql-test/suite/innodb/r/mvcc_secondary.result

+++ b/mysql-test/suite/innodb/r/mvcc_secondary.result

@@ -13,12 +13,18 @@ connect  con1,localhost,root,,;

 SELECT pk FROM t1 FORCE INDEX (c);

 pk

 1

+select json_extract('$js', '\$**.undo_records_read') undo_records_read;

+undo_records_read

+[2]

 connection default;

 COMMIT;

 connection con1;

 SELECT pk FROM t1 FORCE INDEX (c);

 pk

 1

+select json_extract('$js', '\$**.undo_records_read') undo_records_read;

+undo_records_read

+NULL

 disconnect con1;

 connection default;

 DROP TABLE t1;

This test case actually revealed a performance bug. There is only 1 record in the table, with 2 versions, yet the previous version of the clustered index record will be fetched twice in Row_sel_get_clust_rec_for_mysql::operator(). I will have to check how the logic would be if I revert MDEV-20301.

I filed MDEV-31589 for the anomaly. MDEV-20301 is innocent for it.

Backporting code to 10.6 (original code was for 10.11)