I just came across a very useful feature which is slow_query_log that checks queries that run slowly. I managed to execute the commands in the CMD following this link:
https://www.a2hosting.com/kb/developer-corner/mysql/enabling-the-slow-query-log-in-mysql
the log file is created inside a project folder, and no queries found in it, so there should be no slow queries.
However I'm trying to understand how this slow_query_log checks, per database, per project?
If I want to check slow queries for a specific project what command should I type? How will the slow_query thingy know where all the queries are listed in my myproject thus what are the queries run throughout?
Please explain this part to me.
This is how I executed the commands in CMD:
cd C:/wamp/bin/mysql/mysql5.7.14/bin
mysql -u myusername-p
enter password: mypassword
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 5;
SET GLOBAL slow_query_log_file = 'C:/wamp/www/myproject/slow_query/log.php';
Then I exit the CMD, and returning typed, mysql SELECT SLEEP(6);
I found the following in the slow_query/log.php file
wampmysqld, Version: 5.7.14 (MySQL Community Server (GPL)). started with: TCP Port: 3306, Named Pipe: /tmp/mysql.sock Time
Id Command Argument
The best way would be to use a slow-query analyzer.
The one that I find best it's Percona's pt-query-digest which enables you to filter and analyze the slow log in many different ways. This tool is included in Percona's Toolkit which has many useful tools for MySQL DBAs
Now I'm assuming you want to filter the logs per schema/database (Project it's an alien concept in MySQL), if so you would use pt-query-digest using the --filter '($event->{db} || "") =~ m/mydb/' option where mydb is the database you want to filter.
You can read more useful information on the slow query log in Percona's blogs like this one