How to optimize mysql server/queries

On a mysql server there are a lot of queries to optimize and a lot of load generated by them. I’ll try to present the most usual optimization issues and how to identify them.

First you’ll have to check mysql service settings . You can check them manually by following commands in mysql command line:

mysql>show variables;

mysql>show variables like ‘%cache%’;

and

mysql> show status;

you can check the counters and increase or decrease them according to their usage and limits.

The more easy way is to use some scripts to check mysql settings like : MySQL performance tuning primer script (tuning-primer.sh).

First and most important optimization is to activate and set query_cache and query_cache_size to lower disk IO usage.

After that you will have to check the running queries . For that you’ll need to enable first :

log-slow-queries= /var/lib/mysql/mysql-slow.log

long-query-time=3

in my.cnf then touch /var/lib/mysql/mysql-slow.log and set owner as mysql user/group. You can optionally add “log-queries-not-using-indexes”

Open then /var/lib/mysql/mysql-slow.log and check slow queries one by one.

Not every query in the slow query logs is necessary a bad one. Look for queries where some of the following criteria are met:

A) “Rows_examined” is more than 2000

B) “Rows_examined” is less than 2000 but that query is being run 20 times a second.

C) “Rows_examined” is three times bigger than “Rows_sent”

(Note that these are rough criteria. Your mileage may vary depending upon your situation.)

Focus on the two or three worst queries at first. Once you’ve found a few, utilize the EXPLAIN statement to find a better way to restructure your query. See this link for an explanation and walkthrough of the EXPLAIN statement.

Now lets grep in the log file:

grep Rows_examined /var/lib/mysql/mysql-slow.log.old | sort -g -k9 -r |head -5

a sample result would be:

# Query_time: 19 Lock_time: 0 Rows_sent: 31 Rows_examined: 6424367

# Query_time: 58 Lock_time: 0 Rows_sent: 3886219 Rows_examined: 3886219

# Query_time: 47 Lock_time: 0 Rows_sent: 3886219 Rows_examined: 3886219

# Query_time: 40 Lock_time: 0 Rows_sent: 3886219 Rows_examined: 3886219

# Query_time: 39 Lock_time: 0 Rows_sent: 3886219 Rows_examined: 3886219

# search for the worst offender, here’s one way to do that:

grep -A 2 -B 2 3886219 /var/lib/mysql/mysql-slow.log

# Time: 030611 18:49:05

# User@Host: dbusername[dbusername] @ composer.com [166.233.115.222]

# Query_time: 1 Lock_time: 0 Rows_sent: 3886219 Rows_examined: 3886219

SELECT msgs.*, username AS sender_username FROM msgs INNER JOIN users ON (users.id = msgs.sender_id) WHERE user_id=939 AND msgs.status != 1 AND del != 2 ORDER BY date DESC;

# Open up a mysql shell to fix the problem:

[localhost]$ mysql -h mysql.exampledomain.com -u dbusername -pYOURPASSWORDHERENOSPACES dbname

# EXPLAIN statement to show you how bad the query is.

# Notice that 42000 rows rows of data are examined.

mysql> EXPLAIN SELECT msgs.*, username AS sender_username FROM msgs INNER JOIN users ON (users.id = msgs.sender_id) WHERE user_id=939 AND msgs.status != 1 AND del != 2 ORDER BY date DESC;

+——-+——–+—————+———+———+—————-+——-+—————————-+

+——-+——–+—————+———+———+—————-+——-+—————————-+

+——-+——–+—————+———+———+—————-+——-+—————————-+

2 rows in set (0.00 sec)

# How long does the query take before fixing? About 1 second.

# (slightly modified for demostration purposes, but same result still).

mysql> SELECT count(*) FROM msgs INNER JOIN users ON (users.id = msgs.sender_id) WHERE user_id=939 AND msgs.status != 1 AND del != 2 ORDER BY date DESC;

+———-+

| count(*) |

+———-+

| 631 |

+———-+

1 row in set (1.03 sec)

# You want to be indexing on stuff in your WHERE and JOIN statements.

# specifically, where there is lots of uniqueness or “cardinality”.

# user_id from above is really good, because there are lots of unique values

# for user_id. Same thing goes for users.id and msgs.sender_id

# msgs.status won’t help that much (but won’t hurt) because mostly its values are

# 0 and 1. same thing goes for “del”.

# Add an index on the user_id, and msgs.sender_id columns.

# users.id is already indexed

# Note: always try to add a key of length 10 first, it’s better (if possible).

mysql> create index user_id_index on msgs(user_id(10));

ERROR 1089: Incorrect sub part key. The used key part isn’t a string, the used length is longer than the key part or the table handler doesn’t support unique sub keys

mysql> create index user_id_index on msgs(user_id);

Query OK, 42857 rows affected (1.59 sec)

Records: 42857 Duplicates: 0 Warnings: 0

mysql> create index sender_id_index on msgs(sender_id(10));ERROR 1089: Incorrect sub part key. The used key part isn’t a string, the used length is longer than the key part or the table handler doesn’t support unique sub keys

mysql> create index sender_id_index on msgs(sender_id);

Query OK, 42858 rows affected (1.16 sec)

Records: 42858 Duplicates: 0 Warnings: 0

# Check the indices, see if they look good.

# They do look good. Notice the high cardinatlity (uniqueness) of all three keys.

mysql> show index from msgs;

+——-+————+—————–+————–+————-+———–+————-+———-+——–+———+

+——-+————+—————–+————–+————-+———–+————-+———-+——–+———+

| msgs | 0 | PRIMARY | 1 | id | A | 42855 | NULL | NULL | |

| msgs | 1 | user_id_index | 1 | user_id | A | 1224 | NULL | NULL | |

| msgs | 1 | sender_id_index | 1 | sender_id | A | 1071 | NULL | NULL | |

+——-+————+—————–+————–+————-+———–+————-+———-+——–+———+

3 rows in set (0.00 sec)

# Very good.

# Now, check to see if your index actually improved anything.

# First, check with the EXPLAIN statement. Much better!

mysql> EXPLAIN SELECT msgs.*, username AS sender_username FROM msgs INNER JOIN users ON (users.id = msgs.sender_id) WHERE user_id=939 AND msgs.status != 1 AND del != 2 ORDER BY date DESC;

+——-+——–+——————————-+—————+———+—————-+——+—————————-+

+——-+——–+——————————-+—————+———+—————-+——+—————————-+

+——-+——–+——————————-+—————+———+—————-+——+—————————-+

2 rows in set (0.00 sec)

mysql>

# Now check the time it takes the query to complete.

# Only 0.01 seconds to complete. Much faster.

mysql> SELECT count(*) FROM msgs INNER JOIN users ON (users.id = msgs.sender_id) WHERE user_id=939 AND msgs.status != 1 AND del != 2 ORDER BY date DESC;

+———-+

| count(*) |

+———-+

| 631 |

+———-+

1 row in set (0.01 sec)

# Now start watching tail -f /var/lib/mysql/mysql-slow.log

# to find out more tables that should be indexed

tail -f /var/lib/mysql/mysql-slow.log