Speed Up Your Database

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.

B3b2139e4f2c0eca4efe2379fcebc1c5?s=128

Anna Filina

March 09, 2018
Tweet

Transcript

  1. @afilina Speed Up Your Database ConFoo | March 9, 2018

    | Montreal
  2. • What is slow? • Investigate performance issues. • Indexes.

    • Better queries. • Checklist. Objectives
  3. • Project rescue expert. • Dev, mentor, public speaker. •

    Consultant at Zenika. Anna Filina
  4. What is slow?

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

    Check for lazy loading in your ORM.
 Multitude of Queries foreach ($pictures as $id) { query_pic_details($id);
 }
  6. • Data loaded on demand.
 • Use JOIN to get

    all related data in one go.
 Lazy Loading echo $picture->album->user->name;
  7. • Deeply nested code. • Try get_first_thumb instead.
 Multitude of

    Queries
  8. • Manual: add custom code before queries. • Auto: ORMs

    have listeners or data collectors.
 Count Queries
  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
  10. Example Schema 200
 users 100
 albums each 100
 pictures each

    2,000,000 pictures
  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;
  12. Why is it so slow?

  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 | +----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+
  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
  15. Indexes

  16. • .
 How Do They Work?

  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);
  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
  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);
  20. • Details:
 
 
 
 • Total 200,000 rows scanned.


    Under the Hood table : album key : NULL rows : 20,000 table : picture key : album_id rows : 100
  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);
  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
  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!
  24. • Primary and foreign keys make for great indexes. •

    Also whatever you use in JOIN, WHERE, ORDER BY. Indexes Used
  25. • Change frequency ◦ Good: picture.date ◦ Bad: picture.views •

    Selectivity: how many different values? ◦ Good: user.id ◦ Bad: user.is_active Index Criteria
  26. • Useful for LIKE searches. • InnoDB
 
 
 Indexing

    Strings ALTER TABLE photo ADD FULLTEXT INDEX(description);
  27. Should we optimize further?

  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
  29. • BIGINT as index is slower than TINYINT. • Use

    UNSIGNED to double the maximum value. Smaller Indexes
  30. Tips for better queries.

  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;
  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;
  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);
  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
  35. Quote Horror Story

  36. • New features released before sale • Index was not

    used • Creating too many tmp tables on disk • Slow queries • Long queue Problem
  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 |
  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?
  39. • MySQL can partition your tables transparently. ◦ Creates multiple

    files on disk. ◦ Shows data as a single table.
 Split Tables
  40. • MySQL ARCHIVE storage engine: ◦ Doesn’t support UPDATE or

    DELETE ◦ Very fast for SELECT • You can archive old transactions, news, etc.
 Archive
  41. • Horizontal partitioning, sharding. • Users 1-1000 and their data

    in database #1 • Users 1001-2000 and their data in database #2 Split Databases
  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
  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
  44. • Setup read slaves • Denormalize where appropriate ◦ picture.views

    • SSD hard drive Even More Fun Stuff
  45. @afilina | afilina.com | youtube.com/c/AnnaFilina