foolab.ca | @foolabca
Speed Up Your Database
300 Times
PHPBenelux - January 30, 2016
Slide 2
Slide 2 text
Objectives
• What is slow?
• Investigate performance
issues.
• Indexes.
• Better queries.
• Checklist.
2
Slide 3
Slide 3 text
Anna Filina
• Developer
• Problem solver
• Teacher
• Advisor
• FooLab + ConFoo
3
Slide 4
Slide 4 text
What is slow?
Slide 5
Slide 5 text
Multitude of queries
• Loops:
• Reduce number of queries.
• Check for lazy loading in your ORM.
5
foreach ($pictures as $id) {
query_pic_details($id);
}
Slide 6
Slide 6 text
Lazy loading
• Data loaded on demand.
• Use JOIN to get all related data in one go.
6
echo $picture->album->user->name;
Count queries
• Manual: add custom code before queries.
• Auto: ORMs (like Doctrine) have listeners or data
collectors.
8
Slide 9
Slide 9 text
Slow query log
• Open my.cnf
• Queries taking 0.5s or longer will be logged.
• Open the log file and analyze the queries.
9
slow_query_log=ON
long_query_time=0.5
slow_query_log_file=/path/to/file
Slide 10
Slide 10 text
Example schema
10
200
users
100
albums each
100
pictures each
2,000,000 pictures
Slide 11
Slide 11 text
Simple query
• Get all pictures from user #1
• 38 seconds.
11
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
Explain
13
• Prefix with EXPLAIN:
• Each row = table scanned:
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
Explain
14
• Details:
• Because of the join,
we’re scanning all albums for each picture.
• Total 40 billion rows scanned.
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
Metaphor
16
Flip through all pages of a address book...
... or pull on the letter tab.
Slide 17
Slide 17 text
Add an index
17
• album.id
• Same query down to 2.38 sec.
• We just saved 36 sec on every request!
ALTER TABLE album ADD PRIMARY KEY(id);
Slide 18
Slide 18 text
Under the hood
18
• Details:
• With the index,
we find our album right away.
• Total 2,000,000 rows scanned.
table : album
key : PRIMARY
rows : 1
table : picture
key : NULL
rows : 2,000,000
Slide 19
Slide 19 text
Add a better index
19
• picture.album_id
• Now down to 0.12 sec.
• 317 times faster than original.
ALTER TABLE picture ADD INDEX(album_id);
Slide 20
Slide 20 text
Under the hood
20
• Details:
• Total 200,000 rows scanned.
table : album
key : NULL
rows : 20,000
table : picture
key : album_id
rows : 100
Slide 21
Slide 21 text
Add a better index
21
• album.user_id
• Now down to 0.10 sec.
• Gained another 17% in speed.
ALTER TABLE album ADD INDEX(user_id);
Slide 22
Slide 22 text
Under the hood
22
• Details:
• With the second index,
we’re scanning 100 pics.
• Total 10,000 rows scanned.
table : album
key : user_id
rows : 100
table : picture
key : album_id
rows : 100
Slide 23
Slide 23 text
Don't index everything!
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
23
Slide 24
Slide 24 text
Indexes used
24
• Primary and foreign keys make for great indexes.
• Also whatever you use in JOIN, WHERE, ORDER BY.
Slide 25
Slide 25 text
Index criteria
25
• Change frequency
• Selectivity: how many different values?
• Good examples:
◦ user.id
◦ picture.date
• Bad examples:
◦ user.is_active
◦ picture.views
Slide 26
Slide 26 text
Shall we optimize
further?
We’re quite fast already.
Slide 27
Slide 27 text
Airport customs
27
• 5 customs officers, 1 min to process a traveller.
• 5 travellers / min = fluid.
• 20 travellers / min = queue slowly builds.
• 1000 travellers / min = 3h wait if you arrive on 2nd
minute.
Slide 28
Slide 28 text
Smaller indexes
28
• BIGINT as index is slower than TINYINT.
• Use UNSIGNED to double the maximum value.
Slide 29
Slide 29 text
Tips for Better
Queries
Slide 30
Slide 30 text
Functions
30
• Avoid functions on an indexed column:
• The index will be ignored here.
SELECT id, title FROM picture
WHERE YEAR(create_date) >= 2011;
Slide 31
Slide 31 text
Limit
31
• Now down to 0.0009 sec.
• 100 times faster than the last query.
SELECT picture.id, picture.title
FROM picture
LEFT JOIN album ON picture.album.id = album.id
WHERE album.user_id = 1
LIMIT 25;
Slide 32
Slide 32 text
Other constraints
32
• Range:
• Only makes sense if that column is indexed:
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 33
Slide 33 text
Other constraints
33
• IN clause:
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 34
Slide 34 text
Story
Slide 35
Slide 35 text
Problem
• New features released before sale
• Index was not used
• Creating too many tmp tables on disk
• Slow queries
• Long queue
35
Slide 36
Slide 36 text
How did I find the problem?
36
• Status:
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 37
Slide 37 text
How did I find the problem?
37
• 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
Slide 38
Slide 38 text
Split tables
38
• MySQL can partition your tables transparently.
◦ Creates multiple files on disk.
◦ Shows data as a single table.
Slide 39
Slide 39 text
Archives
39
• MySQL ARCHIVE storage engine:
◦ Doesn’t support UPDATE or DELETE
◦ Very fast for SELECT
• You can archive old transactions, news, etc.
Slide 40
Slide 40 text
Split databases
40
• Horizontal partitioning, sharding.
• Users 1-1000 and their data in database #1
• Users 1001-2000 and their data in database #2
Slide 41
Slide 41 text
Checklist
41
✓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.
✓Split tables and
databases.