Speed Up Your Database

Are your queries slow? Learn how to speed them up through better SQL 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. @afilina
   Speed Up Your Database
   ConFoo | March 9, 2018 | Montreal
   

   View Slide

2. • What is slow?
   • Investigate performance issues.
   • Indexes.
   • Better queries.
   • Checklist.
   Objectives
   

   View Slide

3. • Project rescue expert.
   • Dev, mentor, public speaker.
   • Consultant at Zenika.
   Anna Filina
   

   View Slide

4. What is slow?
   

   View Slide

5. • Loops:

   

   

   • Reduce number of queries.
   • Check for lazy loading in your ORM.

   Multitude of Queries
   foreach ($pictures as $id) {
   query_pic_details($id);

   }
   

   View Slide

6. • Data loaded on demand.

   • Use JOIN to get all related data in one go.

   Lazy Loading
   echo $picture->album->user->name;
   

   View Slide

7. • Deeply nested code.
   • Try get_first_thumb instead.

   Multitude of Queries
   

   View Slide

8. • Manual: add custom code before queries.
   • Auto: ORMs have listeners or data collectors.

   Count Queries
   

   View Slide

9. • 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
   

   View Slide

10. Example Schema
    200

    users
    100

    albums each
    100

    pictures each
    2,000,000 pictures
    

    View Slide

11. • 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;
    

    View Slide

12. Why is it so slow?
    

    View Slide

13. • 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 |
    +----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
    

    View Slide

14. • 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
    

    View Slide

15. Indexes
    

    View Slide

16. • .

    How Do They Work?
    

    View Slide

17. • 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);
    

    View Slide

18. • 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
    

    View Slide

19. • 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);
    

    View Slide

20. • Details:

    

    

    

    • Total 200,000 rows scanned.

    Under the Hood
    table : album
    key : NULL
    rows : 20,000
    table : picture
    key : album_id
    rows : 100
    

    View Slide

21. • 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);
    

    View Slide

22. • 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
    

    View Slide

23. 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!
    

    View Slide

24. • Primary and foreign keys make for great indexes.
    • Also whatever you use in JOIN, WHERE, ORDER
    BY.
    Indexes Used
    

    View Slide

25. • Change frequency
    ◦ Good: picture.date
    ◦ Bad: picture.views
    • Selectivity: how many different values?
    ◦ Good: user.id
    ◦ Bad: user.is_active
    Index Criteria
    

    View Slide

26. • Useful for LIKE searches.
    • InnoDB

    

    

    Indexing Strings
    ALTER TABLE photo ADD FULLTEXT INDEX(description);
    

    View Slide

27. Should we optimize further?
    

    View Slide

28. • 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
    

    View Slide

29. • BIGINT as index is slower than TINYINT.
    • Use UNSIGNED to double the maximum value.
    Smaller Indexes
    

    View Slide

30. Tips for better queries.
    

    View Slide

31. • 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;
    

    View Slide

32. • 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;
    

    View Slide

33. • 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);
    

    View Slide

34. • 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
    

    View Slide

35. Quote
    Horror Story
    

    View Slide

36. • New features released before sale
    • Index was not used
    • Creating too many tmp tables on disk
    • Slow queries
    • Long queue
    Problem
    

    View Slide

37. • 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 |
    

    View Slide

38. • 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?
    

    View Slide

39. • MySQL can partition your tables transparently.
    ◦ Creates multiple files on disk.
    ◦ Shows data as a single table.

    Split Tables
    

    View Slide

40. • MySQL ARCHIVE storage engine:
    ◦ Doesn’t support UPDATE or DELETE
    ◦ Very fast for SELECT
    • You can archive old transactions, news, etc.

    Archive
    

    View Slide

41. • Horizontal partitioning, sharding.
    • Users 1-1000 and their data in database #1
    • Users 1001-2000 and their data in database #2
    Split Databases
    

    View Slide

42. ✓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
    

    View Slide

43. • 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/
    • Optimization manual: http://dev.mysql.com/doc/
    refman/5.7/en/optimization.html
    Further Reading
    

    View Slide

44. • Setup read slaves
    • Denormalize where appropriate
    ◦ picture.views
    • SSD hard drive
    Even More Fun Stuff
    

    View Slide

45. @afilina | afilina.com | youtube.com/c/AnnaFilina
    

    View Slide