Find top frequently using queries on MySQL

MySQL tip: การหา top 10 query บน MYSQL ที่ถูกใช้งานมากที่สุดในช่วงเวลาหนึ่งๆ โดยการหา top 10 query นี้สามารถทำได้ 2 วิธีด้วยกัน คือ ใช้ tcpdump หรือ mysqlbinlog ในการนำ query ต่างๆ มาเข้า process การจัดลำดับ

หา top 10 query ด้วย tcpdump

รันคำสั่ง tcpdump เพื่อทำการ capture ข้อมูลที่มีการรับส่งผ่าน port 3306
tcpdump -s 1500 -w tcp.out port 3306

หลังจากรันคำสั่ง tcpdump แล้วจะได้ไฟล์ tcp.out ซึ่งมีข้อมูลที่มีการรับส่งระหว่าง client และ server อยู่

รันคำสั่ง strings เพื่อกรองเอาเฉพาะ string ออกมาและใช้ grep และ sort เพื่อกรองและจัดเรียงข้อมูลอันดับ query ต่างๆ ดังตัวอย่าง
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

ผลลัพธ์

    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

หา top 10 query ด้วย mysqlbinlog

ถ้าหาก MySQL ได้เปิด binary log เราสามารถตรวจสอบและจัดลำดับ query ได้ด้วยคำสั่ง

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

โดยที่ /path/to/mysql-bin.000001 คือ ไฟล์ binary log ของ MySQL

ผลลัพธ์

   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