MySQL Profiling

Question

Our application uses a MySql database. I want to view the query that is getting executed on a user action in the application.

SQL Profiling is the first thing came to my mind and I followed this link. I am getting the query profiles that are executed in that particular session where i set profiling = 1; . How can i view the queries that are getting executed in my application?

Answer 1

There are several options to do so.

1# Probably the easiest is to turn on general query logging:

set global general_log=ON;
set global general_log_file='/tmp/query.log';

Then you can see all the queries being executed in the file.

2# Another option is to use slow logs with long_query_time=0. This you can manage on session level:

set long_query_time = 0;

Slow log also has a bit more information than general log.

3# You can use tcpdump and percona-toolkit to retrieve the queries:

tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt

pt-query-digest --type tcpdump mysql.tcp.txt

4# Profiling also work

set profiling=1
show profiles;
+----------+------------+--------------------------+
| Query_ID | Duration   | Query                    |
+----------+------------+--------------------------+
...
|        3 | 0.01397050 | select * from mysql.user |
+----------+------------+--------------------------+
3 rows in set, 1 warning (0.00 sec)

show profile for query 3;

This has the biggest overhead and also in my opinion the most cumbersome.