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

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

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/

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

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

EffectiveMySQL.com - Performance, Scalability, Site Reliability Actors

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

EffectiveMySQL.com - Performance, Scalability, Site Reliability Web

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

EffectiveMySQL.com - Performance, Scalability, Site Reliability Conclusion

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

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/

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

http://ronaldbradford.com me@ronaldbradford.com @RonaldBradford Ronald Bradford