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.

Join Our Newsletter.

Subscribe to CrowdforThink newsletter to get daily update directly deliver into your inbox.

CrowdforJobs is an advanced hiring platform based on artificial intelligence, enabling recruiters to hire top talent effortlessly.

CrowdforJobs

CrowdforApps brings to you the well researched list of the most successful and finest App development companies, Web software developers.

CrowdforApps

CrowdforGeeks is where lifelong learners come to learn the skills they need, to land the jobs they want, to build the lives they deserve.

CrowdforGeeks

CrowdforThink is a leading Indian media and information platform, known for its end-to-end coverage of the Indian startup ecosystem.

CrowdforThink
CFT

News & Blogs

d8427811a0a733d77e06de028b5f59ca.png

How to Implement MySQL Slow Query Log

ENABLING THE MYSQL SLOW QUERY LOG Slow queries can affect database performance and overall serve...

Top Authors

Hey, I am Suraj - a full-time blogger and a social media expert currently working on the Growth H...

Suraj Kumar

Zakariya has recently joined the PakWheels team as a Content Marketing Executive, shortly after g...

Zakariya Usman

Overall 3+ years of experience as a Full Stack Developer with a demonstrated history of working i...

Lokesh Gupta

With good communication and writing skiils, Astha Sharma is a full-time content writer working wi...

Astha Sharma
CFT

Our Client Says

WhatsApp Chat with Our Support Team