Upgrade to Pro — share decks privately, control downloads, hide ads and more …

High Performance MySQL and NoSQL

High Performance MySQL and NoSQL

Ef8a4161c1e7ce34ea50c491ad99a67e?s=128

Ronald Bradford

April 09, 2015
Tweet

More Decks by Ronald Bradford

Other Decks in Technology

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