Identifying slow MySQL queries
(32 votes, average: 3.75 out of 5)
Loading ...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:
- Login to your server as root
- Open my.cnf with your favorite editor. Example:
- Into the [mysqld] section add the fallowing lines
- Go ahead and save the configuration.
- Now we have to actually create the log file.
- Now we are changing the owner of the file so that mysql and actually write to it.
- Now we restart mysql
- Wait a few minutes and then examine the slow queries log
pico /etc/my.cnf
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.
For pico: CTRL+X and YES
touch /var/log/mysql-slow.log
chown mysql.root /var/log/mysql-slow.log
service mysql restart
It should restart successfully. If it doesn’t check that you didn’t brake my.cnf
A few examples on how to do it:
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.
service mysql restart
Hope this helps !
I suggest you also read:
If you have any problems or suggestions don’t hesitate to leave a comment.
If you enjoyed this post, make sure you subscribe to my RSS feed!
- November 9th
[...] teach you how to correctly identify the queries that are creating a problem for your server.Mas: http://www.cpanelconfig.com/optimize-a-cpanel-server/identifying-slow-mysql-queries/ [...]
try
tail -F /var/log/mysql-slow.log
if you want to keep an eye on the log file
[...] Blakovitch wrote an interesting post today onHere’s a quick excerpt [...]
‘chmod mysql.root /var/log/mysql-slow.log’ should be changed to ‘chown mysql.root /var/log/mysql-slow.log’
Wow… “pico” is still used?
Quick note: step 6 should be “chown”, not “chmod”.
Wow, interesting article
The web’s most interesting stories on Thu 13th Dec 2007
These are the web’s most talked about URLs on Thu 13th Dec 2007. The current winner is
[...] Identifying slow MySQL queries | cPanelConfig – cPanel server configuration guide [...]
After you’ve found a slow query, you need to either re-write it or speed it up. Re-writing queries is very much dependent on how your software works, but speeding queries you basically need to add indexes…
Look at the “where” clause of your query and try to find if there is a set of unique identifiers in there. For example, we can assume that a single user can only post one comment to a blog per second, therefore user ID and timestamp make a unique key.
If you can find a unique key, create an index. I’m no MySQL expert but the syntax will be something like:
CREATE INDEX myindex ON TABLE mytable (userid, timestamp);
The documentation is at http://dev.mysql.com/doc/refman/5.0/en/create-index.html
This is all very much beginners’ stuff but MySQL can scale pretty well so you should look to push it hard before shelling out on an Oracle license.
[...] when you have users abusing it. This article tells you how to speed up your server.. sailor moon sexread more | digg [...]
[...] Identifying slow MySQL queries | cPanelConfig – cPanel server configuration guide (tags: blog database find howto linux MySQL server sysadmin toread tutorial web webdesign webdev) [...]
[...] users abusing it. This article tells you how to speed up your server.. X Men evolution ascensionread more | digg story RSS feed for comments on this post. TrackBack URI Cartoons Fans Lounge [...]
thanks for the information and happy holidays!
Hi…Man i love reading your blog, interesting posts ! it was a great Thursday .
Hi there…Man i love reading your blog, interesting posts ! it was a great Thursday . Vanna White
Why not just use tuning scripts to check your server configuration?
[...] Source [C Panel Config] [...]
GNC-2007-12-14 #325
I give away three cool products more info in the show notes. Congrats to the winners more prizes to give away through the end of Sponsors: Special Promotion code 20% off on 1 Year Shared Hosting Plans use Godaddy Code…
@andy
Try the command
describe your-sql-command-here, for instance:
describe select * from stats where date(date)=’20071212′ order by server;
The result is an analysis of your query, giving some insight in the performance problem.
[...] users abusing it. This article tells you how to speed up your server.. wonder woman animation pornoread more | digg [...]
[...] abusing it. This article tells you how to speed up your server.. futurama scene frame grab clipread more | digg [...]
I would suggest us “VI”
“following” is spelled wrong.
That hurts the author’s credibility.
And nano replaced pico a while back.
I couldn’t understand some parts of this article ying slow MySQL queries | cPanelConfig – cPanel server configuration guide, but I guess I just need to check some more resources regarding this, because it sounds interesting.
[...] Link: cPanelConfig [...]
Hi there…Man i just love your blog, keep the cool posts comin..holy Wednesday
[...] Identifying slow MySQL queries | cPanelConfig – cPanel server …An article on how to identify MySQL slow queries and how to fix them. [...]
Hello…I Googled for log analysis, but found your page about viewing slow MySQL queries | cPanelConfig – cPanel server configuration guide…and have to say thanks. nice read.
Hello webmaster…Man i love reading your blog, interesting posts ! it was a great Wednesday
What do you mean ?
Very very useful, thank you very very much
Nice and useful, thank you
[...] Más información AQUI [...]
Interesting article, thanks.
[...] Identifying slow MySQL queries [...]
[...] Link [...]
[...] Identifying slow MySQL queries [...]
Very much to me has helped! Thanks you!
[...] Identifying slow MySQL queries [...]
[...] Identifying slow MySQL queries [...]
[...] Identifying slow MySQL queries [...]
[...] Identifying slow MySQL queries [...]
super useful
Very useful. Thanks!
Yeah!! It works fine for me. Thank You !
This is one of the things I’ve been meaning to do for a while now. I’ll give it a try shortly. Fingers crossed.
[...] Identifying slow MySQL queries [...]
very good post and useful. Thank you
Interesting Article. Thanks.