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

High Performance MySQL and NoSQL

High Performance MySQL and NoSQL

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

    View full-size slide

  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

    View full-size slide

  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/

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  6. EffectiveMySQL.com - Performance, Scalability, Site Reliability
    Actors

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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


    AddOutputFilterByType DEFLATE text/html text/plain text/xml
    AddOutputFilterByType DEFLATE text/css
    AddOutputFilterByType DEFLATE application/x-javascript
    application/javascript
    ...


    Web

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

  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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

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

    View full-size slide

  51. EffectiveMySQL.com - Performance, Scalability, Site Reliability
    Conclusion

    View full-size slide

  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

    View full-size slide

  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/

    View full-size slide

  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

    View full-size slide

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

    View full-size slide