Find top frequently using queries on MySQL

MySQL tip: find the top 10 most frequently used MySQL queries over a particular period. There are two methods: use tcpdump or mysqlbinlog to collect the queries and rank them.

Find the top 10 queries with tcpdump

Run tcpdump to capture traffic on port 3306:

tcpdump -s 1500 -w tcp.out port 3306

This creates tcp.out containing data transmitted between client and server. Run strings to extract strings, then grep and sort to filter and rank the queries, as shown:

strings tcp.out | 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 |head -n10

Output:

    312 update customer_product
    245 insert into stats
     81 update member_id
     57 insert into history
     53 delete from history
     49 update members_service_number
     41 delete from customer_history
     38 update shop_id

Find the top 10 queries with mysqlbinlog

If MySQL binary logging is enabled, inspect and rank the queries with:

mysqlbinlog /path/to/mysql-bin.000001 | \
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 |head -n10

Here, /path/to/mysql-bin.000001 is the MySQL binary log file. Output:

   2889 update items
    530 insert into history
    490 insert into history_uint
    133 update wp_options
     51 update item_discovery
     45 delete from trends_uint
     43 update wp_postmeta
     41 delete from history_uint
     40 delete from trends
     40 delete from history