Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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
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
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.

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


# Script to process multiple mysql slow logs
# using mysqlsla

# Directory containing slow logs

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

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

# process each slow log file
for file in "$sl_dir"/*
                echo "Processing file: $file";
                filename=$(basename "$file")
                mysqlsla -lt slow "$file" > "reports/$filename.rpt";
                echo "Finished processing file: $file";
share|improve this answer

This script gives a more clear output than mysqldumpslow:

share|improve this answer

Your Answer


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.