High Performance MySQL and NoSQL

Transcript

1. High Performance SQL and NoSQL EffectiveMySQL.com - Performance, Scalability, Site Reliability Ronald Bradford http://ronaldbradford.com @RonaldBradford 2015.04
2. EffectiveMySQL.com - Performance, Scalability, Site Reliability OBJECTIVE Identify how you improve web performance Apply same principles to data access performance Independent of SQL/NoSQL
3. EffectiveMySQL.com - Performance, Scalability, Site Reliability ABOUT AUTHOR 16 years with MySQL / 26 years with RDBMS 10th year presenting MySQL / Open Source / Cloud / PHP Run NY MySQL Meetup Published author of 4 books MySQL Community Member of the year (09,13) Oracle ACE Director Ronald BRADFORD www.meetup.com/EffectiveMySQL/
4. EffectiveMySQL.com - Performance, Scalability, Site Reliability Web Performance
5. EffectiveMySQL.com - Performance, Scalability, Site Reliability Web Performance 1. Gzip assets 2. Make fewer HTTP requests 3. Add expires headers 4. Use a CDN 5. Optimized Images
6. EffectiveMySQL.com - Performance, Scalability, Site Reliability Actors
7. EffectiveMySQL.com - Performance, Scalability, Site Reliability Web 1. Consumer 2. Request 3. Container 4. Response
8. EffectiveMySQL.com - Performance, Scalability, Site Reliability 1. Consumer 2. Request 3. Container 4. Response DB
9. EffectiveMySQL.com - Performance, Scalability, Site Reliability Compression 1
10. EffectiveMySQL.com - Performance, Scalability, Site Reliability web container Load Module Add directive Reap benefits Web
11. EffectiveMySQL.com - Performance, Scalability, Site Reliability deflate conf $ cat /etc/apache2/mods-available/deflate.load LoadModule deflate_module /usr/lib/apache2/modules/mod_deflate.so $ cat /etc/apache2/mods-available/deflate.conf <IfModule mod_deflate.c> <IfModule mod_filter.c> AddOutputFilterByType DEFLATE text/html text/plain text/xml AddOutputFilterByType DEFLATE text/css AddOutputFilterByType DEFLATE application/x-javascript application/javascript ... </IfModule> </IfModule> Web
12. EffectiveMySQL.com - Performance, Scalability, Site Reliability Without Compression With Compression 98% compression Web
13. EffectiveMySQL.com - Performance, Scalability, Site Reliability Without Compression With Compression Web
14. EffectiveMySQL.com - Performance, Scalability, Site Reliability Data store Compress large text fields JSON content HTML/XML content Columns of type TEXT DB Applies equally to SQL and NoSQL
15. EffectiveMySQL.com - Performance, Scalability, Site Reliability field size mysql> SELECT MAX(LENGTH(column)) AS len FROM table; +----------+ | len | +----------+ | 162287 | +----------+ mysql> mysql> SELECT LENGTH(column) AS len, LENGTH(COMPRESS(column)) AS zip, FORMAT(100-(LENGTH(COMPRESS(column))/ LENGTH(column)*100),2) AS pct FROM column WHERE id=?; +--------+-------+-------+ | len | zip | pct | +--------+-------+-------+ | 162287 | 25670 | 84.18 | +--------+-------+-------+ DB 84% compression
16. EffectiveMySQL.com - Performance, Scalability, Site Reliability field size mysql> SELECT * FROM table PROCEDURE ANALYSE(5,2000)\G *************************** 5. row *************** Field_name: schema.table.column Min_value: ... Max_value: ... Min_length: 16465 Max_length: 162287 Empties_or_zeros: 0 Nulls: 0 Avg_value_or_avg_length: 64511.5000 Std: NULL Optimal_fieldtype: MEDIUMTEXT NOT NULL DB
17. EffectiveMySQL.com - Performance, Scalability, Site Reliability text to blob Table Size Before: +---------------------+--------+--------+---------+----------+----------+----------+ | table_name | engine | rows | avg_row | total_mb | data_mb | index_mb | +---------------------+--------+--------+---------+----------+----------+----------+ | xxxxxxxxxxxxxxxxxxx | InnoDB | 414171 | 66997 | 26561.72 | 26462.89 | 98.83 | Table Size After compression of data: | xxxxxxxxxxxxxxxxxxx | InnoDB 477347 | 10590 | 4919.83 | 4821.00 | 98.83 | 81% compression 81% less disk space DB
18. EffectiveMySQL.com - Performance, Scalability, Site Reliability when to compress Compression in SQL is too late Compress/Uncompress in Application Network request DB on disk DB replication DB
19. EffectiveMySQL.com - Performance, Scalability, Site Reliability compress here Benefits Benefits Benefits DB
20. EffectiveMySQL.com - Performance, Scalability, Site Reliability compress here Benefits Web
21. EffectiveMySQL.com - Performance, Scalability, Site Reliability CSS Sprites 2
22. EffectiveMySQL.com - Performance, Scalability, Site Reliability CSS spritES Each image is a HTTP request HTTP 1.1 Persistent Connections Limit based on browser https://developer.chrome.com/devtools/docs/network#resource-network-timing Web
23. EffectiveMySQL.com - Performance, Scalability, Site Reliability Web Without Sprites Without Sprites
24. EffectiveMySQL.com - Performance, Scalability, Site Reliability remove trip Customer PHP page 28 memcache requests 24 - 56 ms Combine to 1 request 4 - 7 ms DB 10x saving. All network trip time
25. EffectiveMySQL.com - Performance, Scalability, Site Reliability Remove Trip START TRANSACTION; INSERT INTO table(columns) VALUES(); INSERT INTO table(columns) VALUES(); INSERT INTO table(columns) VALUES(); INSERT INTO table(columns) VALUES(); INSERT INTO table(columns) VALUES(); COMMIT; START TRANSACTION; INSERT INTO table(columns) VALUES(), (), (), (), (); COMMIT; DB http://ronaldbradford.com/blog/the-value-of-multi-insert-values-2009-06-16/ Removes 4 network round trips
26. EffectiveMySQL.com - Performance, Scalability, Site Reliability reduce SQL Remove repeating SQL N+1 problem Do not understand JOINS RAT v CAT Row-At-a-Time Chunck-At-a-Time DB
27. EffectiveMySQL.com - Performance, Scalability, Site Reliability RAT v CAT SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes' SELECT option_value FROM wp_options WHERE option_name = 'aiosp_title_format' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_show_only_even' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_num_months' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_day_length' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_hide_event_box' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_advanced' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_navigation' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'ec3_disable_popups' LIMIT 1 SELECT option_value FROM wp_options WHERE option_name = 'sidebars_widgets' LIMIT 1 Wordpress Example http://ronaldbradford.com/blog/dp8-the-disadvantages-of-row-at-a-time-processing-2013-08-05/ http://ronaldbradford.com/blog/the-rat-and-the-cat-2006-08-24/ http://ronaldbradford.com/blog/optimizing-sql-performance-the-art-of-elimination-2010-07-08/ http://ronaldbradford.com/blog/simple-lessons-in-improving-scalability-2011-02-16/ DB
28. EffectiveMySQL.com - Performance, Scalability, Site Reliability RAT PROCESSING SELECT name FROM firms WHERE id=727; SELECT name FROM firms WHERE id=758; SELECT name FROM firms WHERE id=857; SELECT name FROM firms WHERE id=740; SELECT name FROM firms WHERE id=849; SELECT name FROM firms WHERE id=839; SELECT name FROM firms WHERE id=847; SELECT name FROM firms WHERE id=867; SELECT name FROM firms WHERE id=829; SELECT name FROM firms WHERE id=812; SELECT name FROM firms WHERE id=868; SELECT name FROM firms WHERE id=723; SELECT id, name FROM firms WHERE id IN (723, 727, 740, 758, 812, 829, 839, 847, 849, 857, 867, 868); ✘ DB ✔ Classic N+1 problem
29. EffectiveMySQL.com - Performance, Scalability, Site Reliability CAT PROCESSING SET PROFILING=1; SELECT ... SHOW PROFILES; +----------+------------+--------------------------------------------------------- | Query_ID | Duration | Query +----------+------------+--------------------------------------------------------- | 1 | 0.00030400 | SELECT name FROM firms WHERE id=727 | 2 | 0.00014400 | SELECT name FROM firms WHERE id=758 | 3 | 0.00014300 | SELECT name FROM firms WHERE id=857 | 4 | 0.00014000 | SELECT name FROM firms WHERE id=740 | 5 | 0.00012300 | SELECT name FROM firms WHERE id=849 | 6 | 0.00012200 | SELECT name FROM firms WHERE id=839 | 7 | 0.00011600 | SELECT name FROM firms WHERE id=847 | 8 | 0.00014300 | SELECT name FROM firms WHERE id=867 | 9 | 0.00013900 | SELECT name FROM firms WHERE id=829 | 10 | 0.00014000 | SELECT name FROM firms WHERE id=812 | 11 | 0.00012800 | SELECT name FROM firms WHERE id=868 | 12 | 0.00011700 | SELECT name FROM firms WHERE id=723 | 13 | 0.00031100 | SELECT id, name FROM firms WHERE id IN (723 ... +----------+------------+--------------------------------------------------------- SELECT 'Sum Individual Queries' AS txt,SUM(DURATI INFORMATION_SCHEMA.PROFILING WHERE QUERY_ID BETWE UNION SELECT 'Combined Query',SUM(DURATION) FROM INFORM QUERY_ID = 13; +------------------------+------------+ | txt | total_time | +------------------------+------------+ | Sum Individual Queries | 0.001311 | | Combined Query | 0.000311 | +------------------------+------------+ 4X longer processing for every page load DB
30. EffectiveMySQL.com - Performance, Scalability, Site Reliability DNS Lookups
31. EffectiveMySQL.com - Performance, Scalability, Site Reliability DNS Lookups Every hostname resolution requires a TCP network roundtrip 3 trip handshake Imposes latency Blocks requests Web
32. EffectiveMySQL.com - Performance, Scalability, Site Reliability connections Use one database connection Only use when needed Only open when needed Close when done Scale out reads DB
33. EffectiveMySQL.com - Performance, Scalability, Site Reliability TRANSACTIONS Use transactions Groups work together Don’t abuse transactions 3 transactions for one logical function DB
34. EffectiveMySQL.com - Performance, Scalability, Site Reliability log SQL Enable General Log (All versions) NOT FOR PRODUCTION USE Performance Schema (5.6+) events_statements_... DB [mysqld] general-log=1 general-log-file=/mysql/log/general.log https://dev.mysql.com/doc/refman/5.6/en/performance-schema-statement-tables.html
35. EffectiveMySQL.com - Performance, Scalability, Site Reliability transactions The general log tells all DB Time Id Command Argument 140803 14:50:04 1 Connect msandbox@localhost on 1 Init DB test 140803 14:50:52 1 Query INSERT INTO countries VALUES ('AU','Australia','Canberra') 140803 14:51:11 1 Query INSERT INTO countries (code, name,capital) VALUES ('US','USA','Washington DC') 140803 14:51:13 1 Quit Time Id Command Argument 140803 14:53:03 2 Connect msandbox@localhost on test 140803 14:53:22 2 Query START TRANSACTION 140803 14:53:29 2 Query INSERT INTO countries VALUES ('AU','Australia','Canberra') 140803 14:53:47 2 Query INSERT INTO countries (code, name,capital) VALUES ('US','USA','Washington DC') 140803 14:53:50 2 Query COMMIT What not to see
36. EffectiveMySQL.com - Performance, Scalability, Site Reliability Expiry Headers 3
37. EffectiveMySQL.com - Performance, Scalability, Site Reliability expire headers Get content from browser cache i.e. a more local copy http://gtmetrix.com/add-expires-headers.html Web
38. EffectiveMySQL.com - Performance, Scalability, Site Reliability caching Get data from cache persistent v non persistent local v distributed Get data more efficiently DB
39. EffectiveMySQL.com - Performance, Scalability, Site Reliability disadvantages Cache invalidation Write through or write back
40. EffectiveMySQL.com - Performance, Scalability, Site Reliability caching Memcache Redis DB
41. EffectiveMySQL.com - Performance, Scalability, Site Reliability mysql caching Query Cache PHP Connector cache DB
42. EffectiveMySQL.com - Performance, Scalability, Site Reliability NOSQL access Get Data more efficiently MySQL provides NoSQL access to data Think Key/Value store Uses Memcache protocol DB
43. EffectiveMySQL.com - Performance, Scalability, Site Reliability Content Delivery Network (CDN) 4
44. EffectiveMySQL.com - Performance, Scalability, Site Reliability CDN Deliver content based on geographical location Closer to consumer Protects from traffic spikes http://www.webopedia.com/TERM/C/CDN.html Web
45. EffectiveMySQL.com - Performance, Scalability, Site Reliability Remove Requests Remove unnecessary data calls Regardless of data or cache Reduces load Improves performance DB
46. EffectiveMySQL.com - Performance, Scalability, Site Reliability DATA CHANGE Customer Store Locations Change at best monthly SELECT FROM db Cache in central memcache “Robbing Peter to pay Paul” Place data in PHP include DB
47. EffectiveMySQL.com - Performance, Scalability, Site Reliability Images 5
48. EffectiveMySQL.com - Performance, Scalability, Site Reliability images Lossy Images Lossless Images Responsive Images Why? Reduce network payload Reduce disk footprint/cost Web
49. EffectiveMySQL.com - Performance, Scalability, Site Reliability Web
50. EffectiveMySQL.com - Performance, Scalability, Site Reliability Content Select only what you need Not SELECT * Not join all tables (ORM) DB SELECT * FROM table
51. EffectiveMySQL.com - Performance, Scalability, Site Reliability Conclusion
52. EffectiveMySQL.com - Performance, Scalability, Site Reliability conclusion Apply the steps you know Think about the data path Simplify, reduce and remove paths 100% guarantee opportunities exist
53. EffectiveMySQL.com - Performance, Scalability, Site Reliability Presentations Effective Software Development with MySQL Common MySQL Scalability Mistakes Other Presentations http://effectivemysql.com/presentation/common-mysql-scalability-mistakes/ http://ronaldbradford.com/presentations/ http://effectivemysql.com/event/developing-modern-applications-using-mysql-seminar/
54. EffectiveMySQL.com - Performance, Scalability, Site Reliability Other Examples 2,000ms to 45ms 175ms to 10ms to 3ms 24 queries to 3 queries http://ronaldbradford.com/blog/improving-performance-a-full-stack-problem-2015-03-06/ http://ronaldbradford.com/blog/improving-performance-with-better-indexes-2015-04-14/ Available now for consulting #Hire Me
55. http://ronaldbradford.com me@ronaldbradford.com @RonaldBradford Ronald Bradford