DOs and DON'Ts of MySQL Performance

Transcript

1. DOs DON’Ts of MySQL Performance and
2. BERG developer JEFF planning center
3. data centers of data years 3 191GB 8 rows >750M
4. DO USE MySQL
5. its solid
6. everyone uses it
7. DON’T BE BLIND
8. monitor it
9. scout
10. newrelic
11. newrelic
12. DO USE INDEXES
13. when? • On almost every column ending in _id • On any column in WHERE, GROUP or ORDER • Be careful about adding too many
14. left to right Index: first_name, last_name Query: 
 SELECT * FROM people WHERE first_name = ‘Jeff’ AND last_name = ‘Berg’
15. left to right Index: first_name, last_name Query: 
 SELECT * FROM people WHERE last_name = ‘Berg’ AND first_name = ‘Jeff’
16. left to right Index: first_name, last_name Query: 
 SELECT * FROM people WHERE first_name = ‘Jeff’
17. left to right Index: first_name, last_name Query: 
 SELECT * FROM people WHERE last_name = ‘Jeff’
18. DON’T USE LIKE WRONG
19. don’t use like wrong Index: first_name, last_name Query: 
 SELECT * FROM people WHERE phone_number LIKE ‘760%’
20. don’t use like wrong Index: first_name, last_name Query: 
 SELECT * FROM people WHERE phone_number LIKE ‘%1234’
21. DO USE EXPLAIN & PROFILE
22. do use explain EXPLAIN SELECT * FROM ccli_songs WHERE title LIKE ‘query%’
23. do use explain EXPLAIN SELECT * FROM ccli_songs WHERE title LIKE ‘%query%’
24. do use profiling SET PROFILING = 1; ! SELECT * FROM ccli_songs
 WHERE title LIKE ‘Title%'; ! SHOW PROFILE;
25. DON’T PAGINATE
26. don’t paginate SELECT * from people 
 LIMIT 100 OFFSET 100000; Query Time: 699ms
27. don’t paginate SELECT * from people WHERE id > 4560177 LIMIT 100; Query Time: 60ms
28. DO CHECK YOUR CONFIGURATION
29. check your config innodb_buffer_pool_size, innodb_log_file_size, max_connections, innodb_file_per_table, innodb_flush_log_at_trx_commit, innodb_flush_method, innodb_log_buffer_size, query_cache_size, log_bin, skip_name_resolve http://www.mysqlperformanceblog.com/2014/01/28/10-mysql-settings-to-tune-after-installation/
30. DON’T MESS UP PRIMARY KEYS
31. primary keys • data is organized via primary key • integer auto increment primary key puts rows on disk in order created • UUID primary key puts rows in random order • combined PK, puts items in order grouped
32. primary keys Primary Key: id
33. primary keys Primary Key: organization_id, id
34. DO HAVE THE RIGHT HARDWARE
35. the right hardware • SSDs are worth their weight in gold • Try to have enough RAM to hold your whole database in memory
36. DON’T USE MySQL
37. don’t use mysql • use percona server or maria db • many Google & Facebook patches applied • many percona ideas • ran by many of original mysql team after oracle acquisition
38. don’t use mysql • MySQL acquired by Sun • Sun acquired by Oracle • Oracle makes a lot of money charging for databases
39. DO HIRE SOMEONE
40. hire someone • Percona.com MySQL Consulting & Services • PSCE.com MySQL Consulting & Services
41. questions? jeff@pco.bz @TheBerg about.me/jeffberg ! slides at: https://speakerdeck.com/theberg