What is running on the database server that keeps it busy? I ask this question to myself quite often, after yet another application code refactoring, to be exact The following couple of lines will help you to capture network traffic, extract SELECT (or any other) SQL statements from TCP packets, sort them by frequency of occurrence. In the most of the cases, this is all you need to begin optimization work: reducing number of round trips, removing unnecessary database calls by caching the results, etc. Output (example): -------- tcpdump: listening on eth0, link-type EN10MB (Ethernet), capture size 1500 bytes 12000 packets captured 12000 packets received by filter 0 packets dropped by kernel real 0m8.666s user 0m0.006s sys 0m0.016s -------- 000001 select last_insert_id() from system_parameter ... 000122 select count(1) from visit_tracking 000122 select visitor_id 000800 select web_page_id , web_page_type_id 000800 select web_page_type_id , name 003200 select count(1) from hit_count 006400 select pd.parameter_value, 006400 select rp.user_id , rp.update_time -------- Posted by Dmitri Mikhailov (TheLadders.com) on Apr 27 @ 21:05

---

# --- (1.1) To capture all traffic on the interface eth0, run:
time tcpdump -i eth0 -s 1500 -w 20060427-db-traffic-01.dmp 
 
# --- (1.2) To capture traffic on the interface eth0 coming from a specific IP address, run:
time tcpdump -i eth0 -s 1500 src host 192.168.2.10 -w 20060427-db-traffic-01.dmp 
 
# --- Press Ctrl+C --- do not leave tcpdump running infinitely on high traffic interfaces
 
# --- (2) To process the results, run:
strings 20060427-db-traffic-01.dmp | grep -i 'select' | awk '{printf("%s %s %s %s\n", $1,$2,$3, $4);}'| sort| uniq -c | awk '{printf("%06ld %s %s %s %s\n", $1,$2,$3,$4,$5);}'|sort

# --- (1.1) To capture all traffic on the interface eth0, run: time tcpdump -i eth0 -s 1500 -w 20060427-db-traffic-01.dmp # --- (1.2) To capture traffic on the interface eth0 coming from a specific IP address, run: time tcpdump -i eth0 -s 1500 src host 192.168.2.10 -w 20060427-db-traffic-01.dmp # --- Press Ctrl+C --- do not leave tcpdump running infinitely on high traffic interfaces # --- (2) To process the results, run: strings 20060427-db-traffic-01.dmp | grep -i 'select' | awk '{printf("%s %s %s %s\n", $1,$2,$3, $4);}'| sort| uniq -c | awk '{printf("%06ld %s %s %s %s\n", $1,$2,$3,$4,$5);}'|sort

Current Tags

You must be logged in to tag this tool

No Comments yet