1

Is their any way to filter mysql slow queries database specifically from /var/log/mysql/mysql-slow.log file. I have a mysql server with 5 live databases. I am trying to optimize this server.

Also I want to know what are the best practices for optimizing mysql server.

My settings are:

log_slow_queries = /var/log/mysql/mysql-slow.log
long_query_time = 5
log-queries-not-using-indexes
user103373
  • 198
  • 6
  • 19
  • Hint: Use the {} button to format your configuration lines properly or they get all bunched up together. – Yanick Girouard Feb 28 '12 at 16:03
  • What kind of data? What kind of application is hitting the databases? How many queries per minute/hour? What kind of I/O, read heavy, write heavy? These are just a few of the questions you need to answer before anyone can provide any assistance. – Craig Feb 28 '12 at 16:05
  • Hi @user103373, please don't rollback my changes. I just tried to set the "MySQL" official capitalization. Thank you so much. – Valerio Bozzolan May 02 '22 at 07:15

4 Answers4

3
mysqldumpslow /var/log/mysql/mysql-slow.log

That will give you a list of distinct slow and non indexed queries sorted by how many times the query has ran, most first. Run that for each log, if the query execute time is less than your slow time (5), then it's a non indexed query.

Hope this helps someone.

Giravity
  • 46
  • 2
0

I don't have a slow query log to look at so I'm not able to test it myself, but you may want to look at something like mysql-log-filter

It's a PHP script that will parse MySQL Slow Query Logs and it offers a lot of different options. Maybe one that will allow you to get the information you need, or at least get closer. Worth a try!

Yanick Girouard
  • 2,385
  • 1
  • 18
  • 19
0

I have found a good utility mysqlsla to parse slow queries logs or filter them by database. http://hackmysql.com/mysqlsla

also we can use this bash script to customize slow queries & to generate report. The script uses the utility mysqlsla.

#!/bin/bash

# Script to process multiple mysql slow logs
# using mysqlsla http://hackmysql.com/mysqlsla

# Directory containing slow logs
sl_dir="/home/rhys/Desktop/slow_logs";

cd "$sl_dir";
#slow_logs=$(ls "$sl_dir");

# Folder for reports
if [ ! -d "$sl_dir"/reports ]; then
                mkdir "$sl_dir"/reports;
fi

# process each slow log file
for file in "$sl_dir"/*
do
                echo "Processing file: $file";
                filename=$(basename "$file")
                mysqlsla -lt slow "$file" > "reports/$filename.rpt";
                echo "Finished processing file: $file";
done
Mike
  • 22,310
  • 7
  • 56
  • 79
user103373
  • 198
  • 6
  • 19
0

This script gives a more clear output than mysqldumpslow:

https://github.com/LeeKemp/mysql-slow-query-log-parser/

jhvaras
  • 101
  • 2