How to log query and its execution time of each query which runs on a db?
Solution 1
Use mysql slow query log. This will help you to get only queries which are slow instead of log / analyze all queries. You just need to set param for long_query_time like 1 or 2 second.
Solution 2
Also you could set long_query_time = 0 and you will see all sql queries!
Related videos on Youtube
12 : 23
18 : 47
49 : 23
08 : 31
07 : 54
15 : 29
03 : 16
05 : 17
01 : 06
Techie
Remembering that I'll be dead soon, is the most important tool I've ever encountered to help me make the big choices in Life. Because almost everything - all external expectations, all pride, all fear of embarrassment or failure, these things just fall away in the face of death, leaving only what is truly important. Remembering that you are going to die, is the best way I know to avoid the trap of thinking you have something to lose. You are already naked, there is no reason not to follow your heart. -- Steve Jobs
Updated on July 14, 2022Comments
-
Techie almost 2 years
I have a huge database with more than 250 tables. Different type of queries are ran on the database. Since the database has grown over the years and now I need to optimise the database and queries. I have already followed optimisation concepts such as indexing and so on.
My problem is, How to log the query and its execution time of each query which runs on the database ? So I can analyse which query takes how many seconds and optimise them.
Given that I know that MYSQL Trigger would be ideal for this but I don't know how to write a trigger for the whole database, so that it logs each query to a table with query's execution time. I want the trigger to log all the CRUD operation which occurred in the database.
How can I get it done ?