OS: Debian 12 (Bookworm)
MariaDB Server: 10.11.3

This was done on purpose in MDEV-28539.

Thank you for confirming that this is WAI. Is it correct to assume that the Handler_ counters are an exact 1:1 replacement for the Innodb_rows_ counters, both before and after MDEV-28539?

Is there any chance that the docs could be updated to reflect this change?

Looking closer, the hander* counters include system tables while innodb had them split to InnoDB_system_rows_. Depending on updating of innodb_stats tables and transaction tables there could be a difference. I couldn't see a direct equivalent of Innodb_rows_inserted.

Docs updated:

https://mariadb.com/kb/en/innodb-status-variables/#innodb_rows_deleted

Hi danblack marko Are the handler* counters specifically for combination of all storage engines or just InnoDB? If the server uses more than 1 storage engine (ie. InnoDB and RockDB), then the handler* variables would not be a precise reflection for InnoDB at all? Please let me know if I'm misunderstand something.

Yes, handler* counters are for all storage engines.

You are aware that with this change you are breaking the interface for nearly all monitoring solutions?

Does anyone need the following counters that were removed in MDEV-28539?

  {"system_rows_deleted", &export_vars.innodb_system_rows_deleted,SHOW_SIZE_T},

  {"system_rows_inserted", &export_vars.innodb_system_rows_inserted,

   SHOW_SIZE_T},

  {"system_rows_read", &export_vars.innodb_system_rows_read, SHOW_SIZE_T},

  {"system_rows_updated", &export_vars.innodb_system_rows_updated,

   SHOW_SIZE_T},

Would it suffice to only add back one set of counters that are specific to all InnoDB tables? Should the counters also be updated for temporary tables?

I will have to think if these counters could be stored in ha_innobase objects, to reduce the overhead of updating them. This would require show_innodb_vars() to gather the counters from all open handles.

Percona PMM and FromDual Performance Monitor use these values:
https://docs.percona.com/percona-monitoring-and-management/details/dashboards/dashboard-mysql-innodb-details.html#innodb-row-reads
and possibly others...

oli, thank you. I have created PR#2959 for this. If possible, I would like to avoid adding the duplicated counters to INFORMATION_SCHEMA.INNODB_MONITOR and just have the global status variables. It is unclear which ones these monitoring solutions are actually using.

I did not conduct any performance tests yet. I would like to see some performance testing from the community. Based on MDEV-21212 we know that these counter updates do not come for free.

I fully agree with you, that duplicate counters should be avoided for various reasons...

But one needs from the software vendor a clear strategy and a clear statement where to go: GLOBAL STATUS, innodb_metrics or PERFORMANCE_SCHEMA, SHOW ENGINE INNODB STATUS, ... And we need some transition time to adapt the software...

Because you wrote in MDEV-15706: "INFORMATION_SCHEMA.INNODB_METRICS (to be removed in this task)". I was not so keen to make significant use of innodb_metrics. I noted in our guidelines: "Will be deprecated in MariaDB 10.5 (still exists in 10.6)"

Your documentation states: "The intention is to eventually remove the interface entirely" (https://mariadb.com/kb/en/information-schema-innodb_metrics-table/)

So we need clear decisions, where to go. You should also not break the interfaces. Our code looks for example as follows:

// MariaDB 10.5 has broken the interface!
if ( $aInstanceInfo['mr_version'] >= '100500')

// MariaDB 10.4 and older and MySQL
else

which breaks software compatibility, causes a lot of pain and work in development, testing, etc.

Percona did a benchmark in 2014: Overhead less than 1%
Source: https://www.percona.com/blog/2014/11/18/mysqls-innodb_metrics-table-how-much-is-the-overhead/

Prometheus seems also to rely on innodb_metrics: https://github.com/prometheus/mysqld_exporter/issues/494

oli, you make a good point about compatibility. I think that a step forward would be to make sure that all counters that are available via INFORMATION_SCHEMA.INNODB_METRICS or SHOW ENGINE INNODB STATUS is also available via global status variables.

Conditional branches can be very expensive in superscalar processors, especially when branches are mispredicted and pipelines have to be drained. We recently fixed one case of that in MDEV-30567. For PERFORMANCE_SCHEMA, I have been playing with the thought of using function pointers to all potentially instrumented operations, so that we can avoid frequent checks for performance_schema=ON. For INFORMATION_SCHEMA.INNODB_METRICS, it could be better to unconditionally enable all counters.

I recently found out about a peculiarity about information_schema.global_status.variable_value. It is internally a string data type (VARCHAR or something). We had a few occasional regression test failures such as MDEV-32874. The safe way to read numeric global status variables should be something like this:

SELECT CAST(VARIABLE_VALUE AS INTEGER) INTO @some_variable

FROM INFORMATION_SCHEMA.GLOBAL_STATUS

WHERE VARIABLE_NAME='some_variable';

I did not check if this would truncate values that do not fit in 31 bits. You might want BIGINT UNSIGNED instead. For evaluating the values externally, this does not matter; you would get the result set in string format anyway.

I measured a 0.6% loss of throughput for my initial attempt at reinstating the counters. I will try to implement some sharding.

In https://github.com/MariaDB/server/pull/2959 I tried a few ideas to reduce the performance impact of this, including two user-visible ones:

1. not updating the counters on the end of every statement, and
2. not updating (or reinstating) Innodb_rows_read.

Would such tweaks be acceptable?

1. for me acceptable as long as it is more or less accurate
2. do not understand it. Does it mean Innodb_rows_read is NOT more or less accurate? Then I do not like it.

A performance regression of roughly 15% was observed with larger numbers of concurrent connections for a variant that would first update the counters in the trx_t object and on commit update the global counters in trx_sys (in a race condition prone way, without acquiring a mutex). I think that this was on a 2-way NUMA system where the number of connections exceed the number of hardware threads on one NUMA node.

My last resort is a variant that would only add the INSERT, DELETE and UPDATE counters but not Innodb_rows_read. In this case, we can avoid updating the trx_sys on the commit of read-only or non-locking transactions. If there is a similar performance regression also for that, I don’t think we can bring back the counters in this form. What could be done is to make INFORMATION_SCHEMA.INNODB_MONITOR report the Handler_ counters, to be somewhat compatible with the existing monitoring solutions. This would not be exactly the same, because these would count operations also on other than InnoDB tables, possibly including some internal buffers that are used for computing JOIN or for sorting when no index is applicable to ORDER BY or GROUP BY.

I think that we need a product manager decision on this. While adding everything else than innodb_rows_read might be acceptable from the performance point of view, it could be better to modify the SQL layer counter code so that there are separate Handler_ counters for each storage engine (and the current Handler_ counters would be reported as a sum of them).