How to Read the MySQL Slow Query Log




How to Read the MySQL Slow Query Log

How to Read the MySQL Slow Query Log

Introduction

For applications you are building where you've designed your own database schema, it is common to need to debug performance issues when databases grow very large. Often due to a lack of indexes, queries that were extremely fast when a database table had only ten thousand rows will become quite slow when the table has millions of rows.

The MySQL slow query log is where the MySQL database server registers all queries that exceed a given threshold of execution time. This can often be a good starting place to see which queries are slowest and how often they are slow.

MySQL on your server is configured to log all queries taking longer than 0.1 seconds. Therefore, just because a query is in the log does not mean it is a source of slowdown for your server and application.

You will need to understand the details of your application to determine whether a logged query is of any concern. For example, a query that takes 20 seconds to run but is only run once a month by an administrative script is probably not a source of concern. A query that takes 2 seconds but runs thousands of times an hour as a result of users interacting with your application may be a concern.

Reading the MySQL Slow Query Log

The slow query log is located at:

/var/log/mysql/mysql-slow.log

For security reasons, you can only access this file as root using sudo. You will not be able to read this file when SSH in as an app's system user.

Entries in the slow log file look like this:

# Time: 140905  6:33:11
# User@Host: dbuser[dbname] @ hostname [1.2.3.4]
# Query_time: 0.116250  Lock_time: 0.000035 Rows_sent: 0  Rows_examined: 20878
use dbname;
SET timestamp=1409898791;
...SLOW QUERY HERE...

Using mysqldumpslow

Rather than reading the slow query log directly, it is often better to use the command mysqldumpslow to parse and summarize the slow query log.

You can run the command like this while SSH'd in as root:

sudo -i mysqldumpslow /var/log/mysql/mysql-slow.log

 


Author Biography.

Lokesh Gupta
Lokesh Gupta

Overall 3+ years of experience as a Full Stack Developer with a demonstrated history of working in the information technology and services industry. I enjoy solving complex problems within budget and deadlines putting my skills on PHP, MySQL, Python, Codeigniter, Yii2, Laravel, AngularJS, ReactJS, NodeJS to best use. Through Knowledge of UML & visual modeling, application architecture design & business process modeling. Successfully delivered various projects, based on different technologies across the globe.



News & Blogs

b155c4808d155c5ed54a2d70f917f6ea.png

An Introduction to Angular: The Remarkable JS F...

Javascript frameworks have nowadays become the most popular and most-used web app development sol...

0752431b16084e2bc68868f7908c94a1.png

Develop RESTful API using Node JS, Express JS

REST (Representational State Transfer) is web standards based architecture and uses HTTP Pro...

6f5026c52c20b8aa8e6452b8934aa46b.png

How to Build a Real-time Chat App With NodeJS, ...

In this tutorial, we’ll be building a real-time chat application with NodeJS, Express, Sock...