Posts Tagged innodb

MySQL 5.x – How to capture the number of inserts in a MySQL database

MySQL 5.x – How to capture the number of inserts in a MySQL database

I was asked to get the number of DML on a MySQL database recently. However, the performance schema was not installed so I couldn’t query from it. I found a status variable called innodb_rows_inserted.

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

+-----------+
| sleep(60) |
+-----------+
| 0 |
+-----------+
1 row in set (1 min 0.00 sec)

+----------------------+-----------+
| Variable_name | Value |
+----------------------+-----------+
| Innodb_rows_inserted | 576684821 |
+----------------------+-----------+
1 row in set (0.01 sec)

To get the value/second, simply perform the calculation below;

(576684821-576654182)/60 = ~510 inserts per second

Obviously, the number above is only for inserts. You can also modify the command above to include the following;

  • Innodb_rows_inserted
  • Innodb_rows_read
  • Innodb_rows_updated
  • Innodb_rows_deleted

Hope this helps!

Regards,
Wei Shan

,

Leave a comment