Aug 26, 2019, 03:44 PM

News:

GinGly.com - Used by 85,000 Members - SMS Backed up 7,35,000 - Contacts Stored  28,850 !!


Methods to Identifying slow MySQL queries

Started by Sudhakar, Nov 09, 2009, 06:59 AM

previous topic - next topic
Go Down

Sudhakar

MySQL can sometimes create big problems on a server when you have users abusing it.
This article will teach you how to correctly identify the queries that are creating a problem for your server.
MySQL can log those queries that are taking longer then X seconds but this future is not turned on by default.

Here's how you turn it on:

1. Login to your server as root

2. Open my.cnf with your favorite editor. Example:

pico /etc/my.cnf

3. Into the [mysqld] section add the fallowing lines

log-slow-queries = /var/log/mysql-slow.log
long_query_time = 3

This is just an example. You can use any file name that you want and you can modify the long_query_time to any value. In this example I will be logging to /var/log/mysql-slow.log any queries that are taking longer then 3 seconds.

4. Go ahead and save the configuration.

For pico: CTRL+X and YES

5. Now we have to actually create the log file.

touch /var/log/mysql-slow.log

6. Now we are changing the owner of the file so that mysql and actually write to it.

chown mysql.root /var/log/mysql-slow.log

7. Now we restart mysql

service mysql restart

It should restart successfully. If it doesn't check that you didn't brake my.cnf
8. Wait a few minutes and then examine the slow queries log
cat /var/log/mysql-slow.log
tail /var/log/mysql-slow.log
tail -50 /var/log/mysql-slow.log

After you have identified the offending query go ahead and optimize or remove it.
Again test the results by looking at your server load and the mysql slow queries log.

After you fixed all the problems go ahead and comment the slow queries logging as it will slow your server a bit if you let it on. my.cnf should now look similar to this:

#log-slow-queries = /var/log/mysql-slow.log
#long_query_time = 3

And don't forget to restart MySQL after this.  8)

service mysql restart

Hope this helps !
  :educated

Source : Unidentified Author   :confused

Go Up
 

Quick Reply

With Quick-Reply you can write a post when viewing a topic without loading a new page. You can still use bulletin board code and smileys as you would in a normal post.

Warning: this topic has not been posted in for at least 120 days.
Unless you're sure you want to reply, please consider starting a new topic.

Note: this post will not display until it's been approved by a moderator.
Name:
Email:
Verification:
Please leave this box empty:

Type the letters shown in the picture
Listen to the letters / Request another image

Type the letters shown in the picture:

shortcuts: alt+s submit/post or alt+p preview
IT Acumens Web Designing Chennai | GinGly :: SMS Backup | CineBuzz :: Latest Cinema News | My Kids Diary :: Kids magical moment
Copyright 2005 - 2019 :: IT Acumens :: All Rights Reserved.
ITAcumens Forum with 2 lakhs post running for 14 years - Powered by IT Acumens Dedicated Server