MySQL DML stats per table

MySQL provides a level of statistics for your INSERT, UPDATE, DELETE, REPLACE Data Manipulation Language (DML) commands using the STATUS output of various Com_ variables, however it is per server stats. I would like per table stats.

You can achieve this with tools such as MySQL Proxy and mk-query-digest, however there is actually a very simple solution that requires no additional tools.
The following 1 line Linux command (reformatted for ease of reading) gave me exactly what I wanted, and it had ZERO impact on the database.

$ mysqlbinlog /path/to/mysql-bin.000999 |  \
   grep -i -e "^update" -e "^insert" -e "^delete" -e "^replace" -e "^alter"  | \
   cut -c1-100 | tr '[A-Z]' '[a-z]' |  \
   sed -e "s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//" | sed -e "s/ where .*$//" |  \
   sort | uniq -c | sort -nr  

  33389 update e_acc
  17680 insert into r_b
  17680 insert into e_rec
  14332 insert into rcv_c
  13543 update e_rec
  10805 update loc
   3339 insert into r_att
   2781 insert into o_att
...

Granted the syntax could do with some regex improvements, but in 2 minutes I was able to deduce some approximate load. The mysqlbinlog command also gives option to retrieve data for a given time period, so it is very easy to get these statistics on a per hour basis.

Sometimes the most simple options are right in front of you, just just need to strive to find the simplest solution.

Tags: , , , ,

7 Responses to “MySQL DML stats per table”

  1. Hi Ronald,

    Nice! You can also do this with mk-query-digest, for example,

    $ mysqlbinlog /path/to/mysql-bin.000999 | mk-query-digest –type binlog –report-format profile

    I am curious whether the output of that is useful to you. It should look Cary-Millsap-inspired :-)

  2. [...] alias, SQL from SQL offers some powerful examples. Ronald Bradford contributes another example in MySQL DML stats per table. It would be fun if someone would collect these and post them somewhere as a script to add at the [...]

  3. [...] and SQL from SQL offers some powerful examples. Ronald Bradford contributes another example in MySQL DML stats per table. It would be fun if someone would collect these and post them somewhere as a script to add at the [...]

  4. Ronald Bradford says:

    mysqlbinlog `pwd`/`ls -tr mysql-bin* | tail -1` | \
    grep -i -e “^update” -e “^insert” -e “^delete” -e “^replace” -e “^alter” | \
    cut -c1-100 | tr ‘[A-Z]‘ ‘[a-z]‘ | \
    sed -e “s/\t/ /g;s/\`//g;s/(.*$//;s/ set .*$//;s/ as .*$//” | sed -e “s/ where .*$//” | \
    sort | uniq -c | sort -nr

  5. [...] Blog – MySQL DML stats per table Filed Under: Article, MySQL Logs [...]

  6. [...] Une autre manière d’analyser les logs est en utilisant les commandes linux. Un gros merci à Ronald Bradford pour avoir publié la série de commandes [...]

  7. [...] the DML stats from MySQL at table level there is only one option : mysqlbinlog . Here is the best post I found that can help you to answer your question. Get the MySQL table stats now! This entry [...]