1

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..

1

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

  • That's what I figured might be the case.. Thank you very much for confirming! – Rick Apr 4 '18 at 17:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.