MySQL 101

Things you should know before coding in MYSQL

Transcript

1. MySQL 101 Things you SHOULD know when using MySQL Thursday, 17 May, 12
2. Semester 1: Index Thursday, 17 May, 12
3. Things worth noting MySQL can do index merge (use 2 index) some times, but usually much more slower than composite index, or when mysql choose not to use it; Index WILL NOT HELP if the index cover too large part of the table (sequential scan is faster) Thursday, 17 May, 12
4. Index covered too large... SELECT * FROM tbl WHERE ID=1 ORDER BY created_at LIMIT 25 OFFSET 0 *** If tbl is too large, LIMIT or ORDER BY won’t help, MySQL still need to examined all rows Solution: Make rows examined smaller! (with where conditions, for example, why use LIMIT instead of > on created_at?) Thursday, 17 May, 12
5. Composite Index CREATE TABLE test ( id INT NOT NULL, last_name CHAR(30) NOT NULL, first_name CHAR(30) NOT NULL, PRIMARY KEY (id), INDEX name (last_name,first_name) ); Leftmost prefix of index: last_name > first_name Thursday, 17 May, 12
6. Composite Index Works only on these query: SELECT * FROM test WHERE last_name='Widenius'; SELECT * FROM test WHERE last_name='Widenius' AND first_name='Michael'; SELECT * FROM test WHERE last_name='Widenius' AND (first_name='Michael' OR first_name='Monty'); SELECT * FROM test WHERE last_name='Widenius' AND first_name >='M' AND first_name < 'N'; Thursday, 17 May, 12
7. Composite Index But NOT... SELECT * FROM test WHERE first_name='Michael'; SELECT * FROM test WHERE last_name='Widenius' OR first_name='Michael'; Thursday, 17 May, 12
8. Order By only works when... SELECT * FROM t1 ORDER BY key_part1,key_part2,... ; SELECT * FROM t1 WHERE key_part1=constant ORDER BY key_part2; SELECT * FROM t1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM t1 WHERE key_part1=1 ORDER BY key_part1 DESC, key_part2 DESC; SELECT * FROM activities WHERE user_id=1234 ORDER BY created_at; Thursday, 17 May, 12
9. Not covered here Foreign Key FULLTEXT INDEX Hash Index on Memory Storage Column Index Group By Optimization Percona patch for index statistics Thursday, 17 May, 12
10. Sem.2 Use MySQL like NOSQL Thursday, 17 May, 12
11. Twitter? Thursday, 17 May, 12
12. So actually MySQL works!!!!! Thursday, 17 May, 12
13. Not this way Thursday, 17 May, 12
14. Use it like NoSQL CREATE TABLE entities ( added_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, id BINARY(16) NOT NULL, updated TIMESTAMP NOT NULL, body MEDIUMBLOB, UNIQUE KEY (id), KEY (updated) ) ENGINE=InnoDB; Thursday, 17 May, 12
15. Use it like NoSQL CREATE TABLE entities ( added_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, id BINARY(16) NOT NULL, updated TIMESTAMP NOT NULL, body MEDIUMBLOB, UNIQUE KEY (id), KEY (updated) ) ENGINE=InnoDB; InnoDB stores data rows physically in primary key order Ensures new entities are written sequentially on disk after old entities, which helps for both read and write locality (new entities tend to be read more frequently than old entities) Thursday, 17 May, 12
16. Use it like NoSQL Create Index on separate table (in Pandaform case, MongoDB) CREATE TABLE index_user_id ( user_id BINARY(16) NOT NULL, entity_id BINARY(16) NOT NULL UNIQUE, PRIMARY KEY (user_id, entity_id) ) ENGINE=InnoDB; Thursday, 17 May, 12
17. And... So instead of ORDER BY created_at, use updated > [last id]! MySQL is bad at generating ID (Twitter solution: https:/ /github.com/twitter/ snowflake) Thursday, 17 May, 12
18. Lab Session 1: Configuration Thursday, 17 May, 12
19. Configurative Parameters key_buffer sort_buffer join_buffer sort_buffer_size read_buffer_size read_rnd_buffer_size Thursday, 17 May, 12
20. Configurative Parameters table_cache query_cache_limit query_cache_size tmp_table_size max_heap_table_size Thursday, 17 May, 12
21. And... innodb_buffer_pool_size Thursday, 17 May, 12
22. Lab Session 2: Trouble Shooting Thursday, 17 May, 12
23. mysqltuner.pl -------- General Statistics -------------------------------------------------- [--] Skipped version check for MySQLTuner script [OK] Currently running supported MySQL version 5.1.58-1ubuntu1-log [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated +InnoDB -ISAM - NDBCluster [--] Data in InnoDB tables: 1G (Tables: 60) [!!] Total fragmented tables: 60 -------- Security Recommendations ------------------------------------------- [OK] All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- [--] Up for: 19d 12h 41m 29s (18M q [11.184 qps], 25K conn, TX: 32B, RX: 3B) [--] Reads / Writes: 73% / 27% [--] Total buffers: 2.2G global + 21.9M per thread (151 max threads) [OK] Maximum possible memory usage: 5.5G (74% of installed RAM) [OK] Slow queries: 0% (5K/18M) [OK] Highest usage of available connections: 22% (34/151) [OK] Key buffer size / total MyISAM indexes: 64.0M/90.0K [OK] Key buffer hit rate: 100.0% (2M cached / 0 reads) [OK] Query cache efficiency: 41.3% (6M cached / 14M selects) [OK] Query cache prunes per day: 0 [OK] Sorts requiring temporary tables: 2% (19K temp sorts / 830K sorts) [OK] Temporary tables created on disk: 13% (98K on disk / 725K total) [OK] Thread cache hit rate: 99% (34 created / 25K connections) [!!] Table cache hit rate: 4% (90 open / 2K opened) [OK] Open file limit used: 0% (4/7K) [OK] Table locks acquired immediately: 100% (15M immediate / 15M locks) [OK] InnoDB data size / buffer pool: 1.9G/2.0G -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Increase table_cache gradually to avoid file descriptor limits Variables to adjust: table_cache (> 3596) Thursday, 17 May, 12
24. MySQL Slow Log # cat /etc/mysql/my.cnf | grep slow log_slow_queries = /var/log/mysql/mysql-slow.log Thursday, 17 May, 12
25. MySQL Slow Log # mysqldumpslow /var/log/mysql/mysql-slow.log Count: 34 Time=249.59s (8486s) Lock=0.00s (0s) Rows=13.7 (466), artstack[artstack]@localhost SELECT `activities`.* FROM `activities` WHERE `activities`.`user_id` = N ORDER BY created_at DESC LIMIT N OFFSET N Count: 43 Time=54.65s (2350s) Lock=0.00s (0s) Rows=1.0 (43), artstack[artstack]@localhost SELECT COUNT(count_column) FROM (SELECT N AS count_column FROM `activities` WHERE `activities`.`user_id` = N LIMIT N OFFSET N) subquery_for_count Count: 1 Time=4.42s (4s) Lock=0.00s (0s) Rows=25.0 (25), artstack[artstack]@localhost SELECT `activities`.user_id FROM `activities` WHERE `activities`.`user_id` = N ORDER BY created_at DESC LIMIT N OFFSET N Count: 1 Time=2.02s (2s) Lock=0.00s (0s) Rows=6.0 (6), artstack[artstack]@localhost SELECT `users`.* FROM `users` INNER JOIN `followings` ON `users`.`id` = `followings`.`follower_id` WHERE `followings`.`followable_id` = N AND `followings`.`followable_type` = 'S' ORDER BY rand() LIMIT N Count: 1 Time=1.90s (1s) Lock=0.00s (0s) Rows=25.0 (25), artstack[artstack]@localhost SELECT `activities`.* FROM `activities` WHERE `activities`.`user_id` = N order by id desc LIMIT N OFFSET N Thursday, 17 May, 12
26. EXPLAIN EXTENDED Thursday, 17 May, 12
27. EXPLAIN EXTENDED Thursday, 17 May, 12
28. Holy Shit Thursday, 17 May, 12