| Bug #79809 | "ROW OPERATIONS" print by `SHOW ENGINE INNODB STATUS` is not correct | ||
|---|---|---|---|
| Submitted: | 30 Dec 2015 8:09 | Modified: | 30 Dec 2015 18:22 |
| Reporter: | zhai weixiang (OCA) | Email Updates: | |
| Status: | Verified | Impact on me: | |
| Category: | MySQL Server: InnoDB storage engine | Severity: | S3 (Non-critical) |
| Version: | 5.7 5.6 | OS: | Any |
| Assigned to: | CPU Architecture: | Any | |
[30 Dec 2015 18:22]
MySQL Verification Team
I could verify this without checking as it's plagued us for years... A quick test proves it.. Number of rows inserted 45374, updated 9595, deleted 45288, read 144428 1972.03 inserts/s, 378.62 updates/s, 1973.03 deletes/s, 6437.56 reads/s ... Number of rows inserted 47087, updated 10082, deleted 46997, read 150446 1711.29 inserts/s, 486.51 updates/s, 1707.29 deletes/s, 6011.99 reads/s ... Number of rows inserted 48548, updated 10343, deleted 48460, read 155317 1461000.00 inserts/s, 261000.00 updates/s, 1463000.00 deletes/s, 4871000.00 reads/s
[30 Dec 2015 18:38]
MySQL Verification Team
people have to ignore the per/second values when top of innodb status says "Per second averages calculated from the last 0 seconds". fwiw, tools can rather use information_schema.innodb_metrics to get the data far easier.
Description: While executing `show engine innodb status`, it will calculate the average DML rates per second. But if I execute `show engine innodb status` twice in less than one second. It will print wrong number like this: -------------- ROW OPERATIONS -------------- 8 queries inside InnoDB, 0 queries in queue 8 read views open inside InnoDB Process ID=52440, Main thread ID=46949392733952, state: sleeping Number of rows inserted 1527921, updated 3025374, deleted 1512681, read 615480446 2380000.00 inserts/s, 4765000.00 updates/s, 2383000.00 deletes/s, 968512000.00 reads/s Check the code: fprintf(file, "%.2f inserts/s, %.2f updates/s," " %.2f deletes/s, %.2f reads/s\n", ((ulint) srv_stats.n_rows_inserted - srv_n_rows_inserted_old) / time_elapsed, ((ulint) srv_stats.n_rows_updated - srv_n_rows_updated_old) / time_elapsed, ((ulint) srv_stats.n_rows_deleted - srv_n_rows_deleted_old) / time_elapsed, ((ulint) srv_stats.n_rows_read - srv_n_rows_read_old) / time_elapsed); Here time_elapsed is calculated by current time minus srv_last_monitor_time and add 0.001. And if I execute the command in less than one second, time_elapsed equals to 0.001. How to repeat: Described above Suggested fix: Use a higher precision unit (for example, millisecond) to calculate the time interval.