MySQL slow queries

[mysqldumpslow]

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

[Guest]

[36.301]

Yahoo crawler, Googlebot