CODEWITHSUNDEEP
mysqlmonitoring
robinmag
robinmag

Reputation: 18110

mysql insert monitoring

Please tell me how is it possible to monitor insertion rate per second for a table with mysql ?

Thank you!

Upvotes: 3

Views: 5148

Answers (2)

Paul Dixon
Paul Dixon

Reputation: 300835

You can monitor it in real time by simply doing something like this

  • Count the number of rows
  • Wait x seconds
  • Count the number of rows, find delta y between two counts
  • rate = y/x

If you don't want to do it in real time, you can could parse the MySQL binary log to see how many insert statements were executed and obtain their timestamps.

Or you could give each row a timestamp, and then find the average insertion rate for any arbitrary period by counting the rows inserted in that period. For example, to get a count of rows inserted in last hour...

SELECT count(*) AS inserted 
FROM table 
WHERE unix_timestamp(created) BETWEEN unix_timestamp()-3600 AND unix_timestamp();

Finally, if you want some general graphing of insertions (and other operations) you could use something like munin (though by default this would be tracking all inserts, not just those in a particular table). Here's an example

munin graph example

Upvotes: 4

johannes
johannes

Reputation: 15969

You can periodically do a

SHOW STATUS LIKE 'Handler_write'

which will give you the amount of insert operations sent to a storage engine. Note that these writes might have failed. But depending on your purpose it might be good enough.

Upvotes: 1

Related Questions