Speed Up Your Database

Transcript

1. FooLab http://foolab.ca @foolabca Speed up Your Database Montreal.rb - June

   18, 2013

2. FooLab Speed matters • What is slow? • Indexes. •

   Better queries. • Checklist. 2

3. FooLab Anna Filina 3 User group Multi-technology conference Project rescue

4. FooLab What is slow?

5. FooLab Multitude of queries • Loops: • Reduce number of

   queries. • Check for lazy loading in your ORM. 5 picture_ids.each do |id| query_pic_details(id) end

6. FooLab Lazy loading • Data loaded on demand. • Use

   LEFT JOIN to get all related data in one go. 6 puts 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 Next Steps • I will post and tweet slides:

    @afilina • Send feedback: anna@foolab.ca 38