• Manual: add custom code before queries.
• Auto: ORMs have listeners or data collectors.
Count Queries
Slide 9
Slide 9 text
• Open my.cnf
• Queries taking 0.2s or longer will be logged.
• Open the log file and analyze the queries.
Slow Query Log
slow_query_log=ON
long_query_time=0.2
slow_query_log_file=/path/to/file
Slide 10
Slide 10 text
Example Schema
200
users
100
albums each
100
pictures each
2,000,000 pictures
Slide 11
Slide 11 text
• Get all pictures from user #1
• 38 seconds.
Simple Query
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album_id = album.id
WHERE album.user_id = 1;
Slide 12
Slide 12 text
Why is it so slow?
Slide 13
Slide 13 text
• Prefix with EXPLAIN:
• Each row = table scanned:
Explain
EXPLAIN SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album_id = album.id
WHERE album.user_id = 1;
+----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
| 1 | SIMPLE | album | ALL | NULL | NULL | NULL | NULL | 20000 | Using where |
| 1 | SIMPLE | picture | ALL | NULL | NULL | NULL | NULL | 2000000 | Using where; Using join buffer |
+----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
Slide 14
Slide 14 text
• Details:
• Because of the join,
we’re scanning all albums for each picture.
• 40 billion rows scanned.
Explain
table : album
key : NULL
rows : 20,000
table : picture
key : NULL
rows : 2,000,000
Slide 15
Slide 15 text
Indexes
Slide 16
Slide 16 text
• .
How Do They Work?
Slide 17
Slide 17 text
• album.id
• Same query down to 2.38 sec.
• We just saved 36 sec on every request!
Add an Index
ALTER TABLE album ADD PRIMARY KEY(id);
Slide 18
Slide 18 text
• Details:
• With the index,
we find our album right away.
• Total 2,000,000 rows scanned.
Under the Hood
table : album
key : PRIMARY
rows : 1
table : picture
key : NULL
rows : 2,000,000
Slide 19
Slide 19 text
• picture.album_id
• Now down to 0.12 sec.
• 317 times faster than original.
Add a Better Index
ALTER TABLE picture ADD INDEX(album_id);
Slide 20
Slide 20 text
• Details:
• Total 200,000 rows scanned.
Under the Hood
table : album
key : NULL
rows : 20,000
table : picture
key : album_id
rows : 100
Slide 21
Slide 21 text
• album.user_id
• Now down to 0.10 sec.
• Gained another 17% in speed.
Add a Better Index
ALTER TABLE album ADD INDEX(user_id);
Slide 22
Slide 22 text
• Details:
• With the second index,
we’re scanning 100 pics.
• Total 10,000 rows scanned.
Under the Hood
table : album
key : user_id
rows : 100
table : picture
key : album_id
rows : 100
Slide 23
Slide 23 text
There is an index on each field
And all selects take long to yield.
Try finding which ones you need,
Remove the rest and feel the speed.
-- Anna Filina
Don't Index Everything!
Slide 24
Slide 24 text
• Primary and foreign keys make for great indexes.
• Also whatever you use in JOIN, WHERE, ORDER
BY.
Indexes Used
Slide 25
Slide 25 text
• Change frequency
◦ Good: picture.date
◦ Bad: picture.views
• Selectivity: how many different values?
◦ Good: user.id
◦ Bad: user.is_active
Index Criteria
Slide 26
Slide 26 text
• Useful for LIKE searches.
• InnoDB
Indexing Strings
ALTER TABLE photo ADD FULLTEXT INDEX(description);
Slide 27
Slide 27 text
Should we optimize further?
Slide 28
Slide 28 text
• 5 customs officers, 1 min to process a traveller.
• 5 travellers / min = fluid.
• 6 travellers / min = queue slowly builds.
• 1000 travellers / min = 3h wait if you arrive on
2nd minute.
Airport Customs
Slide 29
Slide 29 text
• BIGINT as index is slower than TINYINT.
• Use UNSIGNED to double the maximum value.
Smaller Indexes
Slide 30
Slide 30 text
Tips for better queries.
Slide 31
Slide 31 text
• Some functions will prevent indexes
from being used:
• The index will be ignored here.
Functions
SELECT id, title FROM picture
WHERE YEAR(created_date) >= 2011;
Slide 32
Slide 32 text
• Now down to 0.0009 sec.
• 100 times faster than the previous query.
• 42,000 times faster than original.
Limit
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1
LIMIT 25;
Slide 33
Slide 33 text
• Range:
• Only makes sense if that column is indexed:
Other Constraints
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1
AND picture.id BETWEEN 26 AND 50; # 0.023 sec
ALTER TABLE picture ADD INDEX(id);
Slide 34
Slide 34 text
• IN clause:
Other Constraints
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1
AND picture.id IN (15,26,29,32,45); # 0.048 sec
Slide 35
Slide 35 text
Quote
Horror Story
Slide 36
Slide 36 text
• New features released before sale
• Index was not used
• Creating too many tmp tables on disk
• Slow queries
• Long queue
Problem
Slide 37
Slide 37 text
• Status:
How Did I Find the Problem?
SHOW GLOBAL STATUS;
+------------------------------------------+-------------+
| Variable_name | Value |
+------------------------------------------+-------------+
| Aborted_clients | 0 |
| Aborted_connects | 0 |
| Binlog_cache_disk_use | 0 |
| Binlog_cache_use | 0 |
| Binlog_stmt_cache_disk_use | 0 |
| Binlog_stmt_cache_use | 0 |
| Bytes_received | 23378012 |
| Bytes_sent | 2707328 |
Slide 38
Slide 38 text
• Created_tmp_disk_tables
◦ Temporary tables created on order by, group by, etc.
◦ Stored on disk when can't fit in RAM
◦ Maybe too big resultset.
◦ Are we using index?
• Handler_read_rnd_next
◦ Full or partial table scan
How Did I Find the Problem?
Slide 39
Slide 39 text
• MySQL can partition your tables transparently.
◦ Creates multiple files on disk.
◦ Shows data as a single table.
Split Tables
Slide 40
Slide 40 text
• MySQL ARCHIVE storage engine:
◦ Doesn’t support UPDATE or DELETE
◦ Very fast for SELECT
• You can archive old transactions, news, etc.
Archive
Slide 41
Slide 41 text
• Horizontal partitioning, sharding.
• Users 1-1000 and their data in database #1
• Users 1001-2000 and their data in database #2
Split Databases
Slide 42
Slide 42 text
✓Count queries.
✓Log the slow ones.
✓Use EXPLAIN and check
number of rows
scanned.
✓Try different indexes,
remove unused ones.
✓Use small indexes,
preferably numeric.
✓Don’t use functions on
indexed columns.
✓Make sure indexes are
used.
✓Limit number of results.
✓Different storage
engines.
✓Split tables and
databases.
Checklist