[MDEV-28539] Some InnoDB counters are duplicating generic SHOW STATUS Created: 2022-05-11  Updated: 2024-01-16  Resolved: 2022-06-06

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - InnoDB
Fix Version/s: 10.10.0

Type: Task Priority: Major
Reporter: Marko Mäkelä Assignee: Marko Mäkelä
Resolution: Fixed Votes: 0
Labels: performance

Issue Links:
Relates
relates to MDEV-21212 buf_page_get_gen -> buf_pool->stat.n_... Closed
relates to MDEV-31772 Innodb_rows_* missing on mariadb-serv... Stalled

 Description   

The InnoDB srv_stats counters n_rows_updated, n_rows_deleted, n_rows_inserted, and n_rows_read are duplicating Handler_update, Handler_delete, Handler_write, and various Handler_read_ counters.

Updating those counters is not free, especially because some counters are furthermore split to distinguish a rare case of modifying tables in the system schema:

		if (table->is_system_db) {
 
			srv_stats.n_system_rows_updated.inc(size_t(trx->id));
 
		} else {
 
			srv_stats.n_rows_updated.inc(size_t(trx->id));
 
		}

On a related note, handler::increment_statistics() which updates the common status variables, does not employ any caching or sharding whatsoever. It could be better to first update the counters directly in handler data members and only update global counts at the end of each statement.

 Comments   
Comment by Tingyao Nian [ 2023-08-10 ]

Add my comment here same as MDEV-31772:

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.

Comment by Marko Mäkelä [ 2024-01-16 ]

The Handler_update, Handler_delete, Handler_write status variables as well as the various Handler_read_ counters indeed cover access to any storage engine. In information_schema.rocksdb_trx you can apparently find a transaction-level write_count but nothing on reads.

For internal temporary tables that may be used by operations like JOIN, ORDER BY or GROUP BY, there are separate counters Handler_tmp_delete, Handler_tmp_update, Handler_tmp_write, and nothing on reads.

Generated at Thu Feb 08 10:01:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.