High Performance MySQL and NoSQL

Slide 1

Slide 1 text

High Performance SQL and NoSQL EffectiveMySQL.com - Performance, Scalability, Site Reliability Ronald Bradford http://ronaldbradford.com @RonaldBradford 2015.04

Slide 2

Slide 2 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability OBJECTIVE Identify how you improve web performance Apply same principles to data access performance Independent of SQL/NoSQL

Slide 3

Slide 3 text

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/

Slide 4

Slide 4 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Web Performance

Slide 5

Slide 5 text

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

Slide 6

Slide 6 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Actors

Slide 7

Slide 7 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Web 1. Consumer 2. Request 3. Container 4. Response

Slide 8

Slide 8 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability 1. Consumer 2. Request 3. Container 4. Response DB

Slide 9

Slide 9 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Compression 1

Slide 10

Slide 10 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability web container Load Module Add directive Reap beneﬁts Web

Slide 11

Slide 11 text

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 AddOutputFilterByType DEFLATE text/html text/plain text/xml AddOutputFilterByType DEFLATE text/css AddOutputFilterByType DEFLATE application/x-javascript application/javascript ... Web

Slide 12

Slide 12 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Without Compression With Compression 98% compression Web

Slide 13

Slide 13 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Without Compression With Compression Web

Slide 14

Slide 14 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Data store Compress large text ﬁelds JSON content HTML/XML content Columns of type TEXT DB Applies equally to SQL and NoSQL

Slide 15

Slide 15 text

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

Slide 16

Slide 16 text

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

Slide 17

Slide 17 text

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

Slide 18

Slide 18 text

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

Slide 19

Slide 19 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability compress here Benefits Benefits Benefits DB

Slide 20

Slide 20 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability compress here Beneﬁts Web

Slide 21

Slide 21 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability CSS Sprites 2

Slide 22

Slide 22 text

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

Slide 23

Slide 23 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Web Without Sprites Without Sprites

Slide 24

Slide 24 text

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

Slide 25

Slide 25 text

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

Slide 26

Slide 26 text

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

Slide 27

Slide 27 text

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

Slide 28

Slide 28 text

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

Slide 29

Slide 29 text

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

Slide 30

Slide 30 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability DNS Lookups

Slide 31

Slide 31 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability DNS Lookups Every hostname resolution requires a TCP network roundtrip 3 trip handshake Imposes latency Blocks requests Web

Slide 32

Slide 32 text

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

Slide 33

Slide 33 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability TRANSACTIONS Use transactions Groups work together Don’t abuse transactions 3 transactions for one logical function DB

Slide 34

Slide 34 text

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

Slide 35

Slide 35 text

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

Slide 36

Slide 36 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Expiry Headers 3

Slide 37

Slide 37 text

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

Slide 38

Slide 38 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability caching Get data from cache persistent v non persistent local v distributed Get data more efﬁciently DB

Slide 39

Slide 39 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability disadvantages Cache invalidation Write through or write back

Slide 40

Slide 40 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability caching Memcache Redis DB

Slide 41

Slide 41 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability mysql caching Query Cache PHP Connector cache DB

Slide 42

Slide 42 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability NOSQL access Get Data more efﬁciently MySQL provides NoSQL access to data Think Key/Value store Uses Memcache protocol DB

Slide 43

Slide 43 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Content Delivery Network (CDN) 4

Slide 44

Slide 44 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability CDN Deliver content based on geographical location Closer to consumer Protects from trafﬁc spikes http://www.webopedia.com/TERM/C/CDN.html Web

Slide 45

Slide 45 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Remove Requests Remove unnecessary data calls Regardless of data or cache Reduces load Improves performance DB

Slide 46

Slide 46 text

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

Slide 47

Slide 47 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Images 5

Slide 48

Slide 48 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability images Lossy Images Lossless Images Responsive Images Why? Reduce network payload Reduce disk footprint/cost Web

Slide 49

Slide 49 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Web

Slide 50

Slide 50 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Content Select only what you need Not SELECT * Not join all tables (ORM) DB SELECT * FROM table

Slide 51

Slide 51 text

EffectiveMySQL.com - Performance, Scalability, Site Reliability Conclusion

Slide 52

Slide 52 text

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

Slide 53

Slide 53 text

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/

Slide 54

Slide 54 text

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

Slide 55

Slide 55 text

http://ronaldbradford.com [email protected] @RonaldBradford Ronald Bradford