15 pro-tips for MySQL users - PHPBenelux meetup

Transcript

1. 15 Pro-tips for MySQL Users PHPBenelux Meeting - March 2011 Enrise HQ - Amersfoort 1 http://joind.in/talk/view/2947 woensdag 25 april 12
2. Who am I? 2 woensdag 25 april 12
3. Who am I? Joshua Thijssen (32) 2 woensdag 25 april 12
4. Who am I? Joshua Thijssen (32) Senior Software Engineer @ Enrise 2 woensdag 25 april 12
5. Who am I? Joshua Thijssen (32) Senior Software Engineer @ Enrise Development in PHP, Python, Perl, C, Java.... 2 woensdag 25 april 12
6. Who am I? Joshua Thijssen (32) Senior Software Engineer @ Enrise Development in PHP, Python, Perl, C, Java.... Blogs: http://www.adayinthelifeof.nl http://www.enrise.com/blog 2 woensdag 25 april 12
7. Who am I? Joshua Thijssen (32) Senior Software Engineer @ Enrise Development in PHP, Python, Perl, C, Java.... Blogs: http://www.adayinthelifeof.nl http://www.enrise.com/blog Email: joshua@enrise.com 2 woensdag 25 april 12
8. Who am I? Joshua Thijssen (32) Senior Software Engineer @ Enrise Development in PHP, Python, Perl, C, Java.... Blogs: http://www.adayinthelifeof.nl http://www.enrise.com/blog Email: joshua@enrise.com Twitter: @jaytaph Identi.ca: jaytaph 2 woensdag 25 april 12
9. What are we going to discuss? ‣ QUESTIONS? RAISE YOUR HAND OR YELL LOUD 3 woensdag 25 april 12
10. What are we going to discuss? ‣ QUESTIONS? RAISE YOUR HAND OR YELL LOUD ‣ 15 MySQL Pro-tips 3 woensdag 25 april 12
11. What are we going to discuss? ‣ QUESTIONS? RAISE YOUR HAND OR YELL LOUD ‣ 15 MySQL Pro-tips ‣ No “theoretical tips”, all taken from the field. 3 woensdag 25 april 12
12. What are we going to discuss? ‣ QUESTIONS? RAISE YOUR HAND OR YELL LOUD ‣ 15 MySQL Pro-tips ‣ No “theoretical tips”, all taken from the field. ‣ Starting simple - ending “complex” 3 woensdag 25 april 12
13. Tip 1 ‣ EXPLAIN IS YOUR BESTEST FRIEND 1) Know how to use explain. 4 woensdag 25 april 12
14. Tip 1: Know your EXPLAIN (1) 5 woensdag 25 april 12
15. Tip 1: Know your EXPLAIN (1) ‣ I will not show you how to use EXPLAIN. 5 woensdag 25 april 12
16. Tip 1: Know your EXPLAIN (1) ‣ I will not show you how to use EXPLAIN. ‣ Use EXPLAIN and EXPLAIN EXTENDED/ SHOW WARNINGS; 5 woensdag 25 april 12
17. Tip 1: Know your EXPLAIN (1) 6 woensdag 25 april 12
18. Tip 1: Know your EXPLAIN (1) 6 woensdag 25 april 12
19. Tip 1: Know your EXPLAIN (1) 6 woensdag 25 april 12
20. Tip 2 ‣ THERE ARE ONLY A FEW “BASIC” ONES. 2) Know the most basic my.cnf settings 7 woensdag 25 april 12
21. Tip 2: My.cnf settings (1) Know the most important ones: key_buffer_size, innodb_buffer_pool_size, sort_buffer_size, max_connections 8 woensdag 25 april 12
22. Tip 2: My.cnf settings (2) 9 woensdag 25 april 12
23. Tip 2: My.cnf settings (2) ‣ Some settings work on global level, some per connection! 9 woensdag 25 april 12
24. Tip 2: My.cnf settings (2) ‣ Some settings work on global level, some per connection! ‣ Know some quirks: (max_heap_table_size vs tmp_table_size, binlog- do-db, replicate-ignore-db etc) 9 woensdag 25 april 12
25. Tip 2: My.cnf settings (3) ‣ http://www.omh.cc/mycnf/ ‣ http://rackerhacker.com/mysqltuner/ ‣ http://www.day32.com/MySQL/ ‣ phpmyadmin 10 woensdag 25 april 12
26. Tip 3 ‣ RESTORING JUST ONE TABLE CAN BE PAINFUL OTHERWISE 3) Backup on table level 11 woensdag 25 april 12
27. Tip 3: Backup on table level (1) ‣ COULD YOU RESTORE TABLE x? YES! YES I CAN! 12 woensdag 25 april 12
28. Tip 3: Backup on table level (1) ‣ COULD YOU RESTORE TABLE x? YES! YES I CAN! ‣ mysqldump can dump per database OR by table. 12 woensdag 25 april 12
29. Tip 3: Backup on table level (1) ‣ COULD YOU RESTORE TABLE x? YES! YES I CAN! ‣ mysqldump can dump per database OR by table. ‣ Simple scripts to scan/dump tables. 12 woensdag 25 april 12
30. Tip 3: Backup on table level (1) ‣ COULD YOU RESTORE TABLE x? YES! YES I CAN! ‣ mysqldump can dump per database OR by table. ‣ Simple scripts to scan/dump tables. ‣ Easy restore for single table (or part of table) 12 woensdag 25 april 12
31. Tip 4 ‣ DON’T ASK WHAT YOU DON’T NEED 4) Don’t use “SELECT *” when you only need 1 or 2 fields. 13 woensdag 25 april 12
32. Tip 4: Select * (1) ‣ DON’T ASK WHAT YOU DON’T NEED 14 woensdag 25 april 12
33. Tip 4: Select * (1) ‣ DON’T ASK WHAT YOU DON’T NEED ‣ Much more data to be read from disk 14 woensdag 25 april 12
34. Tip 4: Select * (1) ‣ DON’T ASK WHAT YOU DON’T NEED ‣ Much more data to be read from disk ‣ Much more data will be send over, thus slower (blobs/texts) 14 woensdag 25 april 12
35. Tip 4: Select * (1) ‣ DON’T ASK WHAT YOU DON’T NEED ‣ Much more data to be read from disk ‣ Much more data will be send over, thus slower (blobs/texts) ‣ Cannot use covering indices 14 woensdag 25 april 12
36. 15 Tip 4: Select * (2) woensdag 25 april 12
37. 15 Tip 4: Select * (2) woensdag 25 april 12
38. Tip 5 ‣ ENFORCE CONSISTENCY 5) Use triggers and stored procedures 16 woensdag 25 april 12
39. Tip 5: Triggers and stored procedures (1) ‣ ENFORCE CONSISTENCY 17 woensdag 25 april 12
40. Tip 5: Triggers and stored procedures (1) ‣ ENFORCE CONSISTENCY ‣ 6 triggers per table (insert, update, delete, before and after the mutation) 17 woensdag 25 april 12
41. Tip 5: Triggers and stored procedures (1) ‣ ENFORCE CONSISTENCY ‣ 6 triggers per table (insert, update, delete, before and after the mutation) ‣ 3rd party tools (phpmyadmin etc) can also use the database without loosing data consistency. 17 woensdag 25 april 12
42. Tip 5: Triggers and stored procedures (1) ‣ ENFORCE CONSISTENCY ‣ 6 triggers per table (insert, update, delete, before and after the mutation) ‣ 3rd party tools (phpmyadmin etc) can also use the database without loosing data consistency. ‣ Watch out with (phpmyadmin) table dumps! 17 woensdag 25 april 12
43. Tip 6 ‣ THERE ARE MUCH BETTER SOLUTIONS 6) Don’t use FULLTEXT searches 18 woensdag 25 april 12
44. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE MUCH BETTER SOLUTIONS 19 woensdag 25 april 12
45. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE MUCH BETTER SOLUTIONS ‣ They only work for MyISAM tables. 19 woensdag 25 april 12
46. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE MUCH BETTER SOLUTIONS ‣ They only work for MyISAM tables. ‣ Not compatible with other DB’s. 19 woensdag 25 april 12
47. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE MUCH BETTER SOLUTIONS ‣ They only work for MyISAM tables. ‣ Not compatible with other DB’s. ‣ Slow (especially compared to Solr, Sphinx). 19 woensdag 25 april 12
48. Tip 6: Don’t use FULLTEXT search (1) ‣ THERE ARE MUCH BETTER SOLUTIONS ‣ They only work for MyISAM tables. ‣ Not compatible with other DB’s. ‣ Slow (especially compared to Solr, Sphinx). ‣ No extra features (faceted search, spell checking etc). 19 woensdag 25 april 12
49. Tip 7 ‣ IT LOOKS LIKE YOU NEED MORE ADVANCED 7) Wildcard searches (%item%) are bad for performance 20 woensdag 25 april 12
50. Tip 7: Wildcard searches (1) ‣ THERE ARE MUCH BETTER SOLUTIONS 21 woensdag 25 april 12
51. Tip 7: Wildcard searches (1) ‣ THERE ARE MUCH BETTER SOLUTIONS ‣ MySQL cannot use indexes! 21 woensdag 25 april 12
52. Tip 7: Wildcard searches (1) ‣ THERE ARE MUCH BETTER SOLUTIONS ‣ MySQL cannot use indexes! ‣ Revert your data: search for ‘moc.esirne@%’ instead of ‘%@enrise.com’. 21 woensdag 25 april 12
53. Tip 7: Wildcard searches (1) ‣ THERE ARE MUCH BETTER SOLUTIONS ‣ MySQL cannot use indexes! ‣ Revert your data: search for ‘moc.esirne@%’ instead of ‘%@enrise.com’. ‣ Use a better solution (solr, sphinx). You probably want it. 21 woensdag 25 april 12
54. Tip 8 ‣ MAKE CACHING AND LOCKING HAPPY AGAIN 8) Shard your volatile and non- volatile data. 22 woensdag 25 april 12
55. Tip 8: Sharding (1) 23 woensdag 25 april 12
56. Tip 8: Sharding (2) 24 woensdag 25 april 12
57. ‣ Remember: an update on a table will invalidate ALL queries referring to that table. Tip 8: Sharding (2) 24 woensdag 25 april 12
58. ‣ Remember: an update on a table will invalidate ALL queries referring to that table. ‣ UPDATE pages SET hit_count = hit_count + 1; Tip 8: Sharding (2) 24 woensdag 25 april 12
59. ‣ Remember: an update on a table will invalidate ALL queries referring to that table. ‣ UPDATE pages SET hit_count = hit_count + 1; ‣ Thus: page table will NEVER be cached. Tip 8: Sharding (2) 24 woensdag 25 april 12
60. Tip 8: Sharding (3) 25 woensdag 25 april 12
61. ‣ Define hot data (volatile, changes often) and cold data (static, changes never or infrequently) Tip 8: Sharding (3) 25 woensdag 25 april 12
62. ‣ Define hot data (volatile, changes often) and cold data (static, changes never or infrequently) ‣ move to different tables Tip 8: Sharding (3) 25 woensdag 25 april 12
63. ‣ Define hot data (volatile, changes often) and cold data (static, changes never or infrequently) ‣ move to different tables ‣ UPDATE page_stats SET hit_count = hit_count + 1; Tip 8: Sharding (3) 25 woensdag 25 april 12
64. ‣ Define hot data (volatile, changes often) and cold data (static, changes never or infrequently) ‣ move to different tables ‣ UPDATE page_stats SET hit_count = hit_count + 1; ‣ Query cache is happy again Tip 8: Sharding (3) 25 woensdag 25 april 12
65. Tip 9 ‣ PK’S ARE ON EVERY INDEX 9) Don’t use a large Primary Key for InnoDB tables. 26 woensdag 25 april 12
66. Tip 9: Large primary keys (1) 27 woensdag 25 april 12
67. Tip 9: Large primary keys (1) ‣ InnoDB adds the primary key to EACH index. 27 woensdag 25 april 12
68. Tip 9: Large primary keys (1) ‣ InnoDB adds the primary key to EACH index. ‣ No primary key given? It uses an internal 6(!)-byte key. 27 woensdag 25 april 12
69. Tip 10 ‣ COUNT(*) => MYISAM = FAST ‣ COUNT(*) => INNODB = SLOW 10) Don’t “SELECT COUNT(*) FROM TABLE” on InnoDB. 28 woensdag 25 april 12
70. Tip 10: SELECT COUNT(*) (1) 29 woensdag 25 april 12
71. Tip 10: SELECT COUNT(*) (1) ‣ InnoDB implements MVCC (multi- version concurrency control). 29 woensdag 25 april 12
72. Tip 10: SELECT COUNT(*) (1) ‣ InnoDB implements MVCC (multi- version concurrency control). ‣ COUNT(*) must be counted and is not fetched from metadata. 29 woensdag 25 april 12
73. Tip 10: SELECT COUNT(*) (1) 30 woensdag 25 april 12
74. ‣ What do you want to COUNT(*)? Tip 10: SELECT COUNT(*) (1) 30 woensdag 25 april 12
75. ‣ What do you want to COUNT(*)? ‣ Just for displaying purposes (there are X amount of pages): do you need the EXACT amount? Tip 10: SELECT COUNT(*) (1) 30 woensdag 25 april 12
76. Tip 11 ‣ IT ISN’T THAT VARIABLE AS YOU MIGHT THINK 11) Don’t rely on the VARCHAR() 31 woensdag 25 april 12
77. Tip 11: VARCHAR() (1) ‣ IT DOESN’T SOLVE YOUR PROBLEMS 32 woensdag 25 april 12
78. ‣ DON’T WORRY ABOUT THE UTF-8, I’LL BASH THAT LATER Tip 11: VARCHAR() (3) 33 woensdag 25 april 12
79. ‣ DON’T WORRY ABOUT THE UTF-8, I’LL BASH THAT LATER Tip 11: VARCHAR() (3) 33 woensdag 25 april 12
80. Tip 12 ‣ DON’T EXCHANGE ONE PROBLEM FOR ANOTHER 12) UTF-8 is not the enemy, but it certainly isn’t your friend. 34 woensdag 25 april 12
81. Tip 12: UTF-8 (1) 35 woensdag 25 april 12
82. Tip 12: UTF-8 (1) ‣ Dr Jeckyl and Mr Hyde 35 woensdag 25 april 12
83. Tip 12: UTF-8 (1) ‣ Dr Jeckyl and Mr Hyde ‣ Solves all your multi-language problems! 35 woensdag 25 april 12
84. Tip 12: UTF-8 (1) ‣ Dr Jeckyl and Mr Hyde ‣ Solves all your multi-language problems! ‣ But gives back performance issues. 35 woensdag 25 april 12
85. Tip 12: UTF-8 (1) 36 woensdag 25 april 12
86. Tip 12: UTF-8 (1) 37 woensdag 25 april 12
87. Tip 12: UTF-8 (1) 37 woensdag 25 april 12
88. ALL temporary buffers are allocated for worst-case scenario’s. This means a varchar(255) in UTF-8 uses 255*3 + 2 = 767 bytes PER row, even if you have only 1 single char inside. Tip 12: UTF-8 (1) 38 woensdag 25 april 12
89. Tip 13 ‣ WHY LOOKUP DATA WHEN YOU ALREADY HAVE IT? 13) Know your cardinality & selectivity 39 woensdag 25 april 12
90. Tip 13: Cardinality & Selectivity (1) 40 woensdag 25 april 12
91. Tip 13: Cardinality & Selectivity (1) ‣ Cardinality: the number of unique entries inside the index. 40 woensdag 25 april 12
92. Tip 13: Cardinality & Selectivity (1) ‣ Cardinality: the number of unique entries inside the index. ‣ Selectivity: percentage of unique entries. 40 woensdag 25 april 12
93. Tip 13: Cardinality & Selectivity (1) ‣ Cardinality: the number of unique entries inside the index. ‣ Selectivity: percentage of unique entries. ‣ S(I) = cardinality / count * 100% 40 woensdag 25 april 12
94. 41 Tip 13: Cardinality & Selectivity (2) woensdag 25 april 12
95. Tip 13: Cardinality & Selectivity (3) 42 country_id (max +-200, but effectively +- 50, maybe less) woensdag 25 april 12
96. ‣ with 10 records: 5/10 * 100% = 50% Tip 13: Cardinality & Selectivity (3) 42 country_id (max +-200, but effectively +- 50, maybe less) woensdag 25 april 12
97. ‣ with 10 records: 5/10 * 100% = 50% ‣ with 1000 records: 75/1000 * 100 = 7.5% Tip 13: Cardinality & Selectivity (3) 42 country_id (max +-200, but effectively +- 50, maybe less) woensdag 25 april 12
98. ‣ with 10 records: 5/10 * 100% = 50% ‣ with 1000 records: 75/1000 * 100 = 7.5% ‣ with 10.000 records: 200/10000 * 100% = 2% Tip 13: Cardinality & Selectivity (3) 42 country_id (max +-200, but effectively +- 50, maybe less) woensdag 25 april 12
99. ‣ A selectivity < 30% ? Full table scan! ‣ ANALYZE TABLE frequently. Tip 13: Cardinality & Selectivity (4) 43 woensdag 25 april 12
100. Tip 13: Cardinality & Selectivity (5) 44 woensdag 25 april 12
101. ‣ Adding records changes your cardinality and selectivity. Tip 13: Cardinality & Selectivity (5) 44 woensdag 25 april 12
102. ‣ Adding records changes your cardinality and selectivity. ‣ Develop against a “real” dataset (10K records instead of 10 for instance). Tip 13: Cardinality & Selectivity (5) 44 woensdag 25 april 12
103. Tip 14 ‣ NOW(), RAND(), UUID(), CONNECTION_ID() ETC.. 14) Non-deterministic functions do not go well with query caching 45 woensdag 25 april 12
104. Tip 14: Query caching (1) 46 woensdag 25 april 12
105. Tip 14: Query caching (1) 46 woensdag 25 april 12
106. Tip 14: Query caching (2) 47 woensdag 25 april 12
107. Tip 14: Query caching (2) 47 woensdag 25 april 12
108. Tip 14: Query caching (3) 48 woensdag 25 april 12
109. Tip 14: Query caching (3) 48 woensdag 25 april 12
110. SELECT * FROM table WHERE YEAR(created_dt) < YEAR(NOW()); vs SELECT * FROM table WHERE YEAR(created_dt) < ‘2010’; Tip 14: Query caching (4) 49 woensdag 25 april 12
111. Tip 15 ‣ AND GET SOME NICE TITLES WHILE YOU’RE AT IT... 15) Certify yourself as a DBA and/or DBE. 50 woensdag 25 april 12
112. Tip 15: Certify yourself (1) ‣ THEY ARE NOT EASY EXAMS, BUT WELL WORTH IT ‣ Oracle Certified MySQL Associate ‣ Oracle Certified Professional MySQL 5.0 Developer ‣ Oracle Certified Professional MySQL 5.0 Database Administrator ‣ Oracle Certified Expert, MySQL 5.1 Cluster Database Administrator. ‣ Get them all! 51 woensdag 25 april 12
113. Let’s summarize (1) Know how to use explain. (2) Know the most basic my.cnf settings. (3) Backup on table level. (4) Don’t use “SELECT *” when you only need 1 or 2 fields. (5) Use triggers and stored procedures. (6) Don’t use FULLTEXT searches. (7) Wildcard searches (%item%) are bad for performance. (8) Shard your volatile and non-volatile data. (9) Don’t use a large Primary Key for InnoDB tables. (10) Don’t “Select COUNT(*)” on InnoDB. (11) Don’t rely on the VARCHAR(). (12) UTF-8 is not the enemy, but it certainly isn’t your friend. (13) Know your cardinality & selectivity. (14) Non-deterministic functions do not go well with query caching. (15) Certify yourself as a DBA and/or DBE. 52 woensdag 25 april 12
114. Shameless plug ‣ Enrise MySQL training/workshop ‣ Day of training into basics/DBA/DBE ‣ When, how much, what? ‣ Depends on interests. woensdag 25 april 12
115. Any questions? ∂ QUESTIONS? http://farm1.static.flickr.com/73/163450213_18478d3aa6_d.jpg woensdag 25 april 12
116. ‣ THANK YOU FOR YOUR ATTENTION 55 ‣ Please rate my talk: http://joind.in/talk/view/2947 woensdag 25 april 12