1

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?

  • 1
    In my opinion, "Profiling" is usually useless. That article is nearly a decade old. It was deprecated in 5.6.7, four years ago. The performance_schema is replacing it. I'll second Károly's comments about the general and slow logs. If you have performance issues, then use the slowlog and pt-query-digest. – Rick James Nov 1 '16 at 1:14
3

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.

  • Option 1 is super cool. Using it. Cheers. – Lakshman Pilaka Nov 1 '16 at 7:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.