Analyzing the slow query log using mysqldumpslow.pl

 

Pre-prequest : Perl 

In Windows: You can download Strawberry Perl from here: https://strawberryperl.com

In Linux: yum -y install perl-DBD-MySQL

Once Installation is completed, execute perl --version command thought command prompt reconfirm whether Perl is installed properly or not.

Now go to the MySQL base directory and execute below commands to analyze the long queries.

   -- To find out the unique slow queries

 perl mysqldumpslow.pl "D:\Task\mysqldumpslow\mysql-slow--mysql-db01-2021091506.log"

 --  sort output by count (number of times query found in the slow query log) . Displays unique queries, if same arguments passed in the query or call script.

 perl mysqldumpslow.pl -a -s c "D:\Task\mysqldumpslow\mysql-slow--mysql-db01-2021091406.log" >> "D:\Task\mysqldumpslow\output_sort_by_count.sql"

 

 --  sort output by count (number of times query found in the slow query log) . Displays unique queries, if same arguments passed in the query or call script.

 perl mysqldumpslow.pl -a -s c "D:\Task\mysqldumpslow\mysql-slow--mysql-db01-2021091406.log" >> "D:\Task\mysqldumpslow\output_sort_by_count.sql"

  

 

  --  sorted by the average query time. Displays unique queries, if same arguments passed in the query or call script.

 perl mysqldumpslow.pl -s at "D:\Task\mysqldumpslow\mysql-slow--mysql-db01-2021091406.log" >> "D:\Task\mysqldumpslow\output_sort_by_avgtime.sql"