Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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
share|improve this question
    
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

3 Answers 3

up vote 2 down vote accepted
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.

share|improve this answer

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!

share|improve this answer

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.