Marko Mäkelä added a comment - 2023-06-27 13:48 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.

Michael Widenius added a comment - 2023-06-28 06:17 Undo_rows_read added

Marko Mäkelä added a comment - 2023-06-30 09:22 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 .

Marko Mäkelä added a comment - 2023-06-30 10:10 I filed MDEV-31589 for the anomaly. MDEV-20301 is innocent for it.

Michael Widenius added a comment - 2023-07-07 11:45 Backporting code to 10.6 (original code was for 10.11)