0

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

Alex Blex
  • 34,704
  • 7
  • 48
  • 75
112233
  • 2,406
  • 3
  • 38
  • 88
  • 1
    The log is global, but you can filter it with something like `grep`. Out of curiosity, why log. **php** ? – Alex Blex Jun 29 '17 at 16:45
  • @AlexBlex I simply named in php extension. No idea if I should save in any other extension.please advise. – 112233 Jun 29 '17 at 16:46
  • https://en.wikipedia.org/wiki/Filename_extension – Alex Blex Jun 29 '17 at 16:48
  • @AlexBlex, how can I use grep for a particular project? – 112233 Jun 29 '17 at 16:51
  • by a pattern specific to a project – Alex Blex Jun 29 '17 at 16:52
  • Try something a little longer like SLEEP(10). I followed your steps and got this # Time: 170629 16:43:24 # User@Host: root[root] @ localhost [] # Query_time: 10.019652 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0 SET timestamp=1498754604; SELECT SLEEP(10); – Dan Hawkins Jun 29 '17 at 16:53
  • @DanHawkins, appreciated if you could explain how my code above checks sql? Is it checking for all databases? How can I check for a particular project. – 112233 Jun 29 '17 at 16:55
  • AH. I see. Well, like @AlexBlex mentioned, this is a global log. So, every slow query, regardless of project, will be reported here. I do not believe there is configurable way to generate multiple slow query logs. I would suggest creating a database per project. Then, you can parse the slow query log for each database. – Dan Hawkins Jun 29 '17 at 17:06
  • For 5.6 and newer, you also need log_output = FILE -- or FILE,TABLE – Rick James Jul 07 '17 at 03:06

1 Answers1

1

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

Jimmy
  • 1,115
  • 1
  • 9
  • 21
  • thanks for explaining in detail. Kindly help to enlighten me a bit more on it. So to check which query slows run, I need to run one query after another? – 112233 Jun 29 '17 at 17:51
  • The slow-query-logs writes to it's file any query that takes more than long_query_time seconds along with useful information, it doesn't care about databases or projects. You can only filter it afterwards when you are analyzing the written file. If you want to understand why a query runs slow then you need to look at the EXPLAIN command. – Jimmy Jun 29 '17 at 18:18