mysql insert monitoring

Please tell me how i开发者_JAVA百科s it possible to monitor insertion rate per second for a table with mysql ?

Thank you!

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

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.