Bug #81099 INNODB_METRICS need better testing
Submitted: 15 Apr 2016 15:02 Modified: 18 Apr 2016 6:42
Reporter: Peter Zaitsev Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.7, 5.6.30, 5.7.12 OS:Any
Assigned to: CPU Architecture:Any

[15 Apr 2016 15:02] Peter Zaitsev
Description:
Right now enabling all Innodb Metrics and running basic sysbench with them I see many of them are  badly wrong:

mysql> select  name,count from innodb_metrics where name  like "trx_%";
+---------------------------+--------+
| name                      | count  |
+---------------------------+--------+
| trx_rw_commits            | 253526 |
| trx_ro_commits            |      5 |
| trx_nl_ro_commits         |      3 |
| trx_commits_insert_update | 251132 |
| trx_rollbacks             |      0 |
| trx_rollbacks_savepoint   |      0 |
| trx_rollback_active       |      0 |
| trx_active_transactions   |  -9939 |
| trx_rseg_history_len      |  53047 |
| trx_undo_slots_used       |   -133 |
| trx_undo_slots_cached     |  -1682 |
| trx_rseg_current_size     |   1306 |
+---------------------------+--------+
12 rows in set (0.00 sec)

Ie trx_active_transactions can't be negative.

Furthermore it looks like type is incorrect for number of counters. This one is named "counter" while it looks like it is gauge:

mysql> select  * from innodb_metrics where name='trx_active_transactions' \G
*************************** 1. row ***************************
           NAME: trx_active_transactions
      SUBSYSTEM: transaction
          COUNT: -110751
      MAX_COUNT: 64
      MIN_COUNT: -110751
      AVG_COUNT: -0.2537919497691259
    COUNT_RESET: -110751
MAX_COUNT_RESET: 64
MIN_COUNT_RESET: -110751
AVG_COUNT_RESET: NULL
   TIME_ENABLED: 2016-04-10 09:47:06
  TIME_DISABLED: NULL
   TIME_ELAPSED: 436385
     TIME_RESET: NULL
         STATUS: enabled
           TYPE: counter
        COMMENT: Number of active transactions
1 row in set (0.00 sec)

How to repeat:
sysbench --test=/usr/share/doc/sysbench/tests/db/oltp.lua   --oltp-table-size=500000 --max-time=0 --oltp-read-only=off --max-requests=0 --num-threads=64 --rand-type=pareto --db-driver=mysql --mysql-password=password --mysql-db=test_innodb  run

Suggested fix:
Fix the issue but also implement some basic test functionality for those metrics otherwise they will very likely to break with code updates.
[15 Apr 2016 17:06] MySQL Verification Team
also: https://bugs.mysql.com/bug.php?id=73114
[15 Apr 2016 18:48] Laurynas Biveinis
Also: http://bugs.mysql.com/bug.php?id=77399 "Deadlocks missed by INFORMATION_SCHEMA.INNODB_METRICS lock_deadlocks counter"
[18 Apr 2016 6:42] MySQL Verification Team
Hello Peter,

Thank you for the report.
Observed reported issue with 5.6.30, 5.7.12 GA builds(also, on 5.7/5.8 daily builds). Keeping this report independent of Shane's/Laurynas's for the fix + to implement test functionalities for the affected metrics.

Regards,
Umesh
[18 Apr 2016 6:43] MySQL Verification Team
test results

Attachment: 81099.results (application/octet-stream, text), 6.70 KiB.