Bug #115332 Abnormal increase in the Innodb_rows_inserted metric value

• Submitted: 14 Jun 2024 9:50
• Modified: 14 Jun 2024 10:11
• Reporter: wei lee
• Status: Unsupported
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 8.0.33
• OS: CentOS
• CPU Architecture: Any

[14 Jun 2024 9:50] wei lee

Description:
Innodb_rows_inserted : The number of rows inserted into `InnoDB` tables.

It has been found that querying the information_schema.processlist table causes an increase in the Innodb_rows_inserted value. When a large number of processlist table queries are performed, this can result in the metric not accurately reflecting the actual business write situation.

The function row_insert_for_mysql_using_cursor does not categorize the processlist table as a system table. It would be more reasonable to use n_system_rows_inserted (Innodb_system_rows_inserted) here.

    dict_table_n_rows_inc(node->table);

    if (node->table->is_system_table) {
      srv_stats.n_system_rows_inserted.inc();
    } else {
      srv_stats.n_rows_inserted.inc();
    }
  }

How to repeat:

mysql [localhost:8024] {msandbox} (sysdb) > show  status like 'Innodb_rows_inserted';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| Innodb_rows_inserted | 400005 |
+----------------------+--------+
1 row in set (0.00 sec)

mysql [localhost:8024] {msandbox} (sysdb) > select id from information_schema.processlist where USER='tdsqlsys_agent' and info like '%%_shard_%%';
Empty set (0.00 sec)

mysql [localhost:8024] {msandbox} (sysdb) > show  status like 'Innodb_rows_inserted';
+----------------------+--------+
| Variable_name        | Value  |
+----------------------+--------+
| Innodb_rows_inserted | 400007 |
+----------------------+--------+
1 row in set (0.00 sec)

[14 Jun 2024 9:52] wei lee

any os can repeat

[14 Jun 2024 10:11] MySQL Verification Team

Hi Mr. lee,

Thank you for your bug report.

However, this is not a bug . We have used our latest bug fix release which clearly says:

+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Level   | Code | Message                                                                                                                                   |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Warning | 1287 | 'INFORMATION_SCHEMA.PROCESSLIST' is deprecated and will be removed in a future release. Please use performance_schema.processlist instead |
+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------+

Hence, I_S.processlist is no longer supported.

When P_S is used, everything is just fine:

mysql>  show  status like 'Innodb_rows_inserted';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_inserted | 2     |
+----------------------+-------+
1 row in set (0.00 sec)

mysql>  select id from performance_schema.processlist where USER='tdsqlsys_agent' and info like '%%_shard_%%';
Empty set (0.03 sec)

mysql>  show  status like 'Innodb_rows_inserted';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| Innodb_rows_inserted | 2     |
+----------------------+-------+
1 row in set (0.00 sec)

Unsupported.