FooLab http://foolab.ca @foolabca Speed up Your Database ConFoo - February 27, 2014

FooLab Objectives • What is slow? • Indexes. • Better queries. • Checklist. 2

FooLab Anna Filina 3 • I help projects ship on time. • I train and mentor developers. • I code and optimize.

FooLab What is slow?

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); }

FooLab Lazy loading • Data loaded on demand. • Use LEFT JOIN to get all related data in one go. 6 echo $picture->album->user->name;

FooLab Multitude of queries • Deeply nested code. • Try get_first_thumb instead. 7

FooLab Count queries • Manual: add custom code before queries. • Auto: ORMs (like ActiveRecord) have listeners. 8

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

FooLab Example schema 10 200 users 100 albums each 100 pictures each 2,000,000 total pics

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;

FooLab Why is it so slow?

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 | +----+-------------+---------+------+---------------+------+---------+------+---------+--------------------------------+

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

FooLab Indexes

FooLab Metaphor 16 Flip through all pages of a address book... ... or pull on the letter tab.

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);

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

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);

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

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);

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

FooLab Indexes used 23 • Primary and foreign keys make for great indexes. • Also whatever you use in JOINs and WHEREs.

FooLab Index criteria 24 • Change frequency • Selectivity: how many different values? • Good examples: • Bad examples: user.gender picture.views user.id picture.date

FooLab Shall we optimize further? We’re quite fast already.

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.

FooLab Smaller indexes 27 • BIGINT as index is slower than TINYINT. • Use UNSIGNED to double the maximum value.

FooLab Tips for better queries.

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;

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;

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);

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

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

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

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

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.

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

FooLab Even more fun stuff 38 • Setup read slaves • Denormalize where appropriate • SSD hard drive