MySQL – How to Display Inserts, Updates, Deletes, and Reads Per Second

MySQL

Within the following query we can see inserts/updates/deletes/reads per second under "ROW OPERATIONS":

mysql> SHOW ENGINE INNODB STATUS\G
*************************** 1. row ***************************
  Type: InnoDB
  Name: 
Status: 
=====================================
2018-04-03 17:46:05 7f85eadf8700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 5 seconds
-----------------
BACKGROUND THREAD
-----------------
...
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread process no. 22128, id 140215363086080, state: sleeping
Number of rows inserted 436126087, updated 135043544, deleted 45, read 72105720260
51.19 inserts/s, 10.00 updates/s, 0.00 deletes/s, 4545.09 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================

1 row in set (0.00 sec)

Is it possible to perform a query that can easily grab the following data so that it can displayed within an html table some how?

51.19 inserts/s, 10.00 updates/s, 0.00 deletes/s, 4545.09 reads/s

For example to get "reads per second" I've tried querying information_schema.global_status using com_select/uptime but that gives me an all time average. I was hoping to get a per second average if possible..

Maybe I need to store results every so often and compare to those? Not sure..

Best Answer

For example to get "reads per second" I've tried querying information_schema.global_status using com_select/uptime but that gives me an all time average.

Yes, "all time" since the MySQL instance was started.

Maybe I need to store results every so often and compare to those?

Yes, I think this could be the way to go as I don't think there are any stats in MySQL for historic status variables. 

SELECT VARIABLE_NAME, VARIABLE_VALUE 
FROM information_schema.global_status 
WHERE VARIABLE_NAME IN ('COM_SELECT', 'COM_INSERT', 'COM_UPDATE', 'COM_DELETE');

Then wait for an interval of your preferred length, and then re-run the query, and store the difference somewhere. (Obviously, running the query itself is a SELECT, so it will influence the result, as will an INSERT query be if you store the results in the same MySQL instance.)

For reference: Server Status Variables: Com_xxx

Related Question