How to log slow queries in MySQL

To trouble shoot which queries are running slow in your application you need to first check which queries are running slow. MySQL provides an easy way to log all the queries that are slow using some easy to use commands. I will describe them below

First, you need to login to mysql server using appropriate privileges to set some global variables, normally a root user would be required.

Once you login into the mysql console run the following command to see what the current status of logging of slow mysql queries. To do, that run the following command in mysql command line.

$mysql > show global variables like '%slow%';

This might show you an output like below

Look for the slow_query_log variable in the above output and see what is the status. If slow query log is enabled in your MySQL then this value will be ON otherwise it will be OFF, which means you need to turn it on.

To enable mysql slow query log run the below command in your mysql console

SET global slow_query_log=1;

This will turn enable the slow query logs in your mysql. To  confirm it is enabled run again the command as described in the first step. You should see the status to ON as shown in the above screenshot.

To check the path where mysql will log the slow queries check the slow_query_log_file variable in the above screenshot.

There are other important variables like `long_query_time` which will determine which queries MySQL will consider as slow and log them in the log file. So if you set this variable to 1 second it log all those queries that take longer than 1 second to execute and give the result. You may also set this value to 0 to log all queries that take less than 1 second to execute but are called large number of times which might make your system slow. However for this tutorial I will set this limit to 1 second.  To set the long_query_time variable to 1 second you need to run the following command in mysql console.

SET global long_query_time=1;

MySQL will now log all those queries take more than 1 second to execute.

There are many other such variables which help you log your slow queries which you can then later optimize to make your application faster

 

 

Be excellent at work

This little post on my LinkedIn feed cheered up my mood. Was feeling pretty down today. I hope it help someone else too!


Are you hurt? Bad Day at work? Read this

On his first day in office as president,
When Abraham Lincoln entered to give his Inaugural speech,

One man stood up and said,
“Mr. Lincoln, you should not forget that your father used to make shoes for my family”

And the whole senate laughed, They thought they had made a fool of Lincoln.

Lincoln looked at the man directly in the eye and said,
“Sir, I know that my father used to make shoes for your family, and there will be many others here. Because he made shoes the way nobody else can, he was a creator. His shoes were not just shoes; he poured his whole soul into them. I want to ask you, have you any complaint? Because I know how to make shoes myself. If you have any complaint I can make you another pair of shoes. But as far as I know, nobody has ever complained about my father’s shoes. He was a genius, a great creator and I am proud of my father”.

The whole Senate was struck dumb. They could not understand what kind of man Abraham Lincoln was. He was proud because his father did his job so well that not even a single complaint had ever been heard.

Remember:
“No one can hurt you without your consent.”

“It is not what happens to us that hurts us. It is our response that hurts us. Be excellent at your work


Source: LinkedIn Feed Post

Fall Down 77 times, Stand up 78. NEVER Give Up.

According to Sam Wyle, Ross Perot quickly became a top employee at IBM. In fact, one year he fulfilled his annual sales quota in a mere two weeks. Yet, when he tried to pitch his ideas to supervisors he was largely ignored. This led him to leave IBM in 1962 to found Electronic Data Systems (EDS). To get the business going, he attempted to sell the products to large corporations for his data processing services. “Perot was refused seventy-seven times before he was given his first contract.”

Never give up. In life, the ability to develop tough skin and pick yourself up after losses or disappointments is critical.

Source: Linked Post shared by Sanjoy Kumar Malik