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;
or
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;
+——-+——–+—————+———+———+—————-+——-+—————————-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+——-+——–+—————+———+———+—————-+——-+—————————-+
| msgs | ALL | NULL | NULL | NULL | NULL | 3886219 | where used; Using filesort |
| users | eq_ref | PRIMARY | PRIMARY | 8 | msgs.sender_id | 1 | |
+——-+——–+—————+———+———+—————-+——-+—————————-+
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;
+——-+————+—————–+————–+————-+———–+————-+———-+——–+———+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Comment |
+——-+————+—————–+————–+————-+———–+————-+———-+——–+———+
| 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;
+——-+——–+——————————-+—————+———+—————-+——+—————————-+
| table | type | possible_keys | key | key_len | ref | rows | Extra |
+——-+——–+——————————-+—————+———+—————-+——+—————————-+
| msgs | ref | user_id_index,sender_id_index | user_id_index | 8 | const | 635 | where used; Using filesort |
| users | eq_ref | PRIMARY | PRIMARY | 8 | msgs.sender_id | 1 | |
+——-+——–+——————————-+—————+———+—————-+——+—————————-+
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
Comments