7, September 2008

MySQL slow queries - webmaster forum

 
Webdigity webmaster forums
This forum shares its ad revenue with its members!
[ Home | Help | Search | Forum's Shop | Archive | Login | Register | Webmaster Directory ]
Webdigity Webmaster Forums  >  Web Development  >  Databases  >  MySQL
Topic: MySQL slow queries
« previous next »
Pages: [1] Print

Author Topic: MySQL slow queries  (Read 1334 times)
I am a metal monkey!
Administrator
Community Supporter ?
Jedai Sword Master
*****
Gender: Male
Posts: 8037
41179 credits
Members referred : 3



« on: Dec 31, 2005, 02:34:14 AM »

These days I am occupied with optimizing the MySQL server, and I can say that I have learned a lot of stuff.

Here is a small tip for developers, about the slow queries.

There is a tool called mysqldumpslow, which can help you find out which queries are executed too slow, so you have to change them, or the indexed of the tables that are used by them.

To use this functionallity of MySQL, first you must be sure that slow queries are logged.

This can be done with two ways :

my.cnf
Open your my.cnf file (this is located at /etc/mysql/ in the most unix os) and add this line :

Code:
log_slow_queries=/var/log/slow-queries.log
long_query_time=10

Now the mySQL server will log all the queries that take more than 10 seconds to be executed in the file /var/log/slow-queries.log

At startup
Locate the script that starts and restarts the MySQL server ( /etc/init.d/mysql in most unix os ) and locate the line that starts the server. It should be something like :

Code:
/usr/bin/mysqld_safe > /dev/null 2>&1 &

Change this to :

Code:
/usr/bin/mysqld_safe > \
     --log-slow-queries=/var/log/slow-queries.log \
     --long_query_time=10 \
/dev/null 2>&1 &



Now that you have set the mySQL server to log the slow queries you can use this command to check them :

Code:
mysqldumpslow

This tool will provide you with all the queries that run slow to your server, so now you will be able to make all the necessery changes to your SQL statements or your tables structure.

Bellow is the list of parameters for mysqldumpslow :

  -v
    verbose

-d
    debug

-s=WORD
    what to sort by (t, at, l, al, r, ar etc)

-r 
    reverse the sort order (largest last instead of first)

-t=NUMBER
    just show the top n queries

-a 
    don't abstract all numbers to N and strings to 'S'

-n=NUMBER
    abstract numbers with at least n digits within names

-g=WORD
    grep: only consider stmts that include this string

-h=WORD
    hostname of db server for *-slow.log filename (can be wildcard)

-i=WORD
    name of server instance (if using mysql.server startup script)

-l 
    don't subtract lock time from total time

Trial and Error my two best teachers Cool
Join us @ facebook Visit through proxy

Last blog : MIA - Where Nick and Tim
Trackback URI for this entry : http://www.webdigity.com/trackback.php?topic=1108
Tags : mysql Bookmark this thread : Digg Del.icio.us Dzone more....

Topic sponsors:
Get a permanent link here for $1.99!


Pages: [1] Print 
Webdigity Webmaster Forums  >  Web Development  >  Databases  >  MySQL
Topic: MySQL slow queries
« previous next »
Jump to:
User Area
Welcome, Guest. Please login or register.
Did you miss your activation email?
Sep 07, 2008, 02:51:40 PM





Login with username, password and session length

Donate to our community, and get a permanent link back to your site!

Donate to our community, and get a permanent link back to your site!


Forum Statistics
Total Posts: 36.301
Total Topics: 7.479
Total Members: 3.905
Tutorials : 56
Resources : 143
Designs : 220
Latest Member: indiecorporate

17 Guests, 2 Users online :

11 users online today:



Readers

Web Design Gallery · Whois Lookup · Pagerank · Tag Browsing · Lo-fi version · Syndication · Webmaster forum history · Advertise
Developed by HumanWorks © 2005 - 2008 Webdigity webmaster community · sublime directory
Webdigity Webmaster Forums | Powered by SMF 1.0.12. © 2001-2005, Lewis Media. All Rights Reserved.