YouTube Icon

How to Implement MySQL Slow Query Log




How to Implement MySQL Slow Query Log

ENABLING THE MYSQL SLOW QUERY LOG

Slow queries can affect database performance and overall server performance. The slow query log feature in MySQL enables you to log queries that exceed a predefined time limit. This greatly simplifies the task of finding inefficient or time-consuming queries.

Debunking the Common Cloud Adoption Myths

To enable the slow query log in MySQL, follow these steps:

Log in to your server using SSH.

At the command line, type the following command:

mysql -u root -p

Learn MySQL

Type the MySQL root password.

To enable the slow query log, type the following command at the mysql> prompt:

SET GLOBAL slow_query_log = 'ON';

There are additional options that you can set for the slow query log:

  • By default, when the slow query log is enabled, it logs any query that takes longer than 10 seconds to run. To change this interval, type the following command, replacing X with the time in seconds:
SET GLOBAL long_query_time = X;
  • By default, the slow query log file is located at /var/lib/mysql/hostname-slow.log. To change the log path or filename, type the following command, replacing path with the path to the file, and filename with the name of the log filename:
SET GLOBAL slow_query_log_file = '/path/filename';

Also Read:- How to Read the MySQL Slow Query Log

To verify that the slow query log is working correctly, log out of the mysql program, and then log back in. (This reloads the session variables for the mysql program.) Type the following command, replacing X with a value that is greater than the long_query_time setting:

SELECT SLEEP(X);

The slow query log file should contain information about the query.

Continue to monitor the slow query log file to see which queries take a long time to run.

When you are done troubleshooting, disable the slow query log. To do this, run the mysql program again, and then type the following command:

SET GLOBAL slow_query_log = 'OFF';

Also Read:- Create a Simple CRUD Database App - Connecting to MySQL with PHP



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.

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

Our mission is "Har Koi Dekhe Video, Har Ghar Dekhe Video, Ghar Ghar Dekhe Video" so we Provide videos related to Tutorials, Travel, Technology, Wedding, Cooking, Dance, Festivals, Celebration.

Apna Video Wala
CFT

News & Blogs

b8e260f9e81b8a699e442e9ea09b9f75.png

How to Read the MySQL Slow Query Log

Introduction For applications you are building where you've designed your own database schem...

Top Authors

Lamia Rochdi is the Marketing Manager at Bell Flavors & Fragrances EMEA. A successful family-...

Lamia Rochdi

I’m Mertin Wilson a technician in a camera company and certified expert of different P...

Mertin Wilson

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

Zakariya Usman

Pankaj Singh is a Senior Digital Marketing Consultant with more than 2 years of experience in SEO...

Pankaj Singh
CFT

Our Client Says

WhatsApp Chat with Our Support Team