Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Speed Up Your Database 300 Times

Speed Up Your Database 300 Times

Are your queries slow? Learn how to speed them up through better SQL and use of meaningful indices. You will understand what works well and what doesn't, and will walk away with a checklist for faster databases. I expect that you will all be itching to analyze MySQL queries to see how much you can shave off.

Anna Filina
PRO

May 28, 2017
Tweet

More Decks by Anna Filina

Other Decks in Programming

Transcript

  1. @afilina
    Speed Up Your Database
    300 Times
    PHP Serbia - May 28, 2017
    (Beginner Talk)

    View Slide

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

    View Slide

  3. Anna Filina
    • Project rescue expert
    • Dev, trainer, speaker

    View Slide

  4. What is Slow?

    View Slide

  5. Multitude of Queries
    • Loops:



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

    foreach ($pictures as $id) {

    query_pic_details($id);

    }

    View Slide

  6. Lazy Loading
    • Data loaded on demand.

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

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

    View Slide

  7. Multitude of Queries
    • Deeply nested code.
    • Try get_first_thumb instead.


    View Slide

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


    View Slide

  9. Slow Query log
    • Open my.cnf



    • Queries taking 0.2s or longer will be
    logged.
    • Open the log file and analyze the queries.
    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. Simple Query
    • Get all pictures from user #1




    • 38 seconds.
    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. Explain
    • 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 |
    +----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+

    View Slide

  14. Explain
    • Details:




    • Because of the join,

    we’re scanning all albums for each
    picture.
    • 40 billion rows scanned.

    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. Add an Index
    • album.id

    • Same query down to 2.38 sec.
    • We just saved 36 sec on every request!

    ALTER TABLE album ADD PRIMARY KEY(id);

    View Slide

  18. Under the Hood
    • 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

    View Slide

  19. Add a Better Index
    • picture.album_id


    • Now down to 0.12 sec.
    • 317 times faster than original.
    ALTER TABLE picture ADD INDEX(album_id);

    View Slide

  20. Under the Hood
    • Details:




    • Total 200,000 rows scanned.

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

    View Slide

  21. Add a Better Index
    • album.user_id


    • Now down to 0.10 sec.
    • Gained another 17% in speed.

    ALTER TABLE album ADD INDEX(user_id);

    View Slide

  22. Under the Hood
    • 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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  26. Indexing Strings
    • Useful for LIKE searches.
    • InnoDB



    ALTER TABLE photo ADD FULLTEXT INDEX(description);

    View Slide

  27. Should We Optimize
    Further?

    View Slide

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

    View Slide

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

    View Slide

  30. Tips for Better
    Queries

    View Slide

  31. Functions
    • Avoid functions on an indexed column:
    • The index will be ignored here.

    SELECT id, title FROM picture

    WHERE YEAR(created_date) >= 2011;

    View Slide

  32. Limit
    • Now down to 0.0009 sec.
    • 100 times faster than the previous query.
    • 42,000 times faster than original.
    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. Other Constraints
    • 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);

    View Slide

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

    View Slide

  35. Story

    View Slide

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

    View Slide

  37. How Did I Find the Problem?
    • 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 |

    View Slide

  38. How Did I Find the Problem?
    • 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

    View Slide

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


    View Slide

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


    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

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

    View Slide

  45. @afilina afilina.com

    View Slide