Speed Up Your Database

Are your queries slow? Learn how to speed them up through better SQL crafting and use of meaningful indexes. You will understand what works well and what doesn't, and will walk away with a checklist for faster databases. Through examples and benchmarks, I will demonstrate how to go from almost a minute of SQL execution to less than a millisecond. I expect that you will all be itching to analyze queries to see how much you can shave off.

Transcript

1. FooLab http://foolab.ca @foolabca Speed up Your Database ConFoo - February 27, 2014
2. FooLab Objectives • What is slow? • Indexes. • Better queries. • Checklist. 2
3. FooLab Anna Filina 3 • I help projects ship on time. • I train and mentor developers. • I code and optimize.
4. FooLab What is slow?
5. FooLab Multitude of queries • Loops: • Reduce number of queries. • Check for lazy loading in your ORM. 5 foreach (id in pictures) { query_pic_details(id); }
6. FooLab Lazy loading • Data loaded on demand. • Use LEFT JOIN to get all related data in one go. 6 echo $picture->album->user->name;
7. FooLab Multitude of queries • Deeply nested code. • Try get_first_thumb instead. 7
8. FooLab Count queries • Manual: add custom code before queries. • Auto: ORMs (like ActiveRecord) have listeners. 8
9. FooLab 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
10. FooLab Example schema 10 200 users 100 albums each 100 pictures each 2,000,000 total pics
11. FooLab 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;
12. FooLab Why is it so slow?
13. FooLab 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 | +----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
14. FooLab 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
15. FooLab Indexes
16. FooLab Metaphor 16 Flip through all pages of a address book... ... or pull on the letter tab.
17. FooLab 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);
18. FooLab 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
19. FooLab 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);
20. FooLab Under the hood 20 • Details: • Total 200,000 rows scanned. table : album key : NULL rows : 20,000 table : picture key : album_id rows : 100
21. FooLab 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);
22. FooLab 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
23. FooLab Indexes used 23 • Primary and foreign keys make for great indexes. • Also whatever you use in JOINs and WHEREs.
24. FooLab Index criteria 24 • Change frequency • Selectivity: how many different values? • Good examples: • Bad examples: user.gender picture.views user.id picture.date
25. FooLab Shall we optimize further? We’re quite fast already.
26. FooLab Airport customs 26 • 5 customs officers, 1 min to process a traveler. • 5 travelers / min = fluid. • 20 travelers / min = queue slowly builds. • 1000 travellers / min = 3h wait if you arrive on 2nd minute.
27. FooLab Smaller indexes 27 • BIGINT as index is slower than TINYINT. • Use UNSIGNED to double the maximum value.
28. FooLab Tips for better queries.
29. FooLab Functions 29 • Avoid functions on an indexed column: • The index will be ignored here. SELECT id, title FROM picture WHERE YEAR(create_date) >= 2011;
30. FooLab Limit 30 • 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;
31. FooLab Other constraints 31 • 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);
32. FooLab Other constraints 32 • 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
33. FooLab Split tables 33 • MySQL can partition your tables transparently. • Creates multiple files on disk. • Shows data as a single table.
34. FooLab Archives 34 • MySQL ARCHIVE storage engine: • Doesn’t support UPDATE or DELETE • Very fast for SELECT • You can archive old transactions, news, etc.
35. FooLab Split databases 35 • Horizontal partitionning, sharding. • Users 1-1000 and their data in database #1 • Users 1001-2000 and their data in database #2
36. FooLab Checklist 36 ✓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. ✓Limit number of results. ✓Split tables and databases.
37. FooLab Further reading • Storage engines: http://www.linux.org/article/view/an- introduction-to-mysql-storage-engines • Sharding: http://www.jurriaanpersyn.com/archives/ 2009/02/12/database-sharding-at-netlog-with-mysql- and-php/ 37
38. FooLab Even more fun stuff 38 • Setup read slaves • Denormalize where appropriate • SSD hard drive