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

MySQL 8.0 performance tuning tips - Implementing best practices will get you the scalability you need

MySQL 8.0 performance tuning tips - Implementing best practices will get you the scalability you need

It’s easy to find generic performance and tuning tips for MySQL, valid for many versions. However, if you are using MySQL 8, you would probably like to know and use the latest features that will optimize your environments.

Join this webinar to discover the MySQL 8 specific features that will help you improve your performance and tuning specific to MySQL 8 (Histograms, Resource Groups, Options for Document Store/JSON, and many others). We will also add some suggestions on how to improve specific workloads (e.g. write intensive, read intensive, asynchronous replicas, InnoDB Cluster).

Take this opportunity to learn directly from MySQL experts and have your questions answered by them!

Olivier DASINI

April 20, 2023
Tweet

More Decks by Olivier DASINI

Other Decks in Technology

Transcript

  1. MySQL 8.0 performance tuning tips
    Implementing best practices will get you the scalability you need
    Olivier Dasini
    MySQL Cloud Principal Solutions Architect EMEA
    [email protected]
    Blogs : www.dasini.net/blog/en
    : www.dasini.net/blog/fr
    Linkedin: www.linkedin.com/in/olivier-dasini
    Twitter : @freshdaz

    View full-size slide

  2. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    2
    Me, Myself & I

    MySQL Geek
     Addicted to MySQL for 15+ years
     Playing with databases for 20+ years

    MySQL Writer, Blogger and Speaker
     Also: DBA, Consultant, Architect, Trainer, ...

    MySQL Cloud Principal Solutions Architect EMEA at Oracle

    Stay up to date!
     Blog: www.dasini.net/blog/en
     Linkedin: www.linkedin.com/in/olivier-dasini/
     Twitter: @freshdaz
    Olivier DASINI

    View full-size slide

  3. Agenda
    1. Introduction
    2. MySQL Performance Tuning – Elementary Guidelines
    3. Monitoring
    4. Performance Configuration Options
    5. Schema Optimization
    6. Query Performance
    7. Tips For Common Workloads
    3
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  4. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    4
    Introduction
    4
    The world’s most popular open source database

    View full-size slide

  5. - 3x Better Performance
    - Replication Enhancements
    - Optimizer Cost Model
    - JSON Support
    - Improved Security
    - Sys & Performance Schema
    - GIS
    - ...
    2015
    MySQL 5.7
    2018
    MySQL 8.0
    - 2x Better Perfromance
    - Data Dictionary
    - Advanced SQL
    - Utf8mb4 & Unicode
    - JSON Document Store
    - MySQL X Protocol
    - CTE
    - Window Functions
    - Roles
    - ...
    MySQL HA
    - MySQL Group Replication
    - MySQL InnoDB Cluster
    - MySQL Router
    - MySQL InnoDB ReplicaSet
    - MySQL InnoDB ClusterSet
    MySQL Innovation: 5.7 -> 8.0
    2 Years in Development
    400+ Worklogs
    5000+ Bugs Fixed
    500 New Tests

    View full-size slide

  6. Performance improvements in MySQL 8.0
    2x Faster than MySQL 5.7 for RO and RW @scale
    • Scaling Read/Write Loads
    – Re-designing how InnoDB writes to Redo log
    • Utilizing IO Capacity
    – Removing file system mutex
    • High Contention Loads
    – Contention Aware Transaction Scheduling
    – CATS over FIFO
    • Resource Groups
    – Thread–CPU mapping can be managed
    • New tables that expose data locks in PS
    • UTF8MB4
    • Partial JSON/BLOB update
    • Information Schema
    • Performance Schema
    • Cost Model (mem/disk aware)
    6
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  7. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    7
    MySQL Performance Tuning
    7
    Elementary Guidelines

    View full-size slide

  8. Performance Tuning - Guidelines 1/2
     Think – consider what you are doing!
     Monitor your system
    – There is no control without measurement! - MySQL Enterprise Monitor is your best ally
    – https://www.mysql.com/products/enterprise/monitor.html
     Be wary about “best practices”
    – No two systems are the same
    – What was true earlier may no longer hold
     Ensure you test your changes before deploying to production
    – The testing must reflect your production usage
     Make incremental changes
    – One change at a time
    – Relatively small change
    8
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  9. Performance Tuning - Guidelines 2/2
     Be mindful of your requirements
    – Some options give the choice between performance or data safety – what do you need?
     Often the default value is the best value - we’ve made huge improvement in MySQL 8.0
     Ensure all tables have a PRIMARY KEY - very important for InnoDB
     InnoDB organizes the data according to the PRIMARY KEY:
    – The PRIMARY KEY is included in all secondary indexes in order to be able to locate the actual row
    – Smaller PRIMARY KEY gives smaller secondary indexes
    – A mostly sequential PRIMARY KEY is in general recommended to avoid inserting rows between existing rows
    9
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  10. Source of database performance problems
    10
    Copyright © 2023, Oracle and/or its
    affiliates
    Hardware
    Schema Changes
    Data Growth
    Indexes
    SQL
    90% of
    Performance Problems

    View full-size slide

  11. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    11
    Monitoring
    11
    There is no control without measurement!

    View full-size slide

  12. Monitoring
     Your database referential
    – Fact-based decision making
     Real-time performance monitoring → What is happening right now
    – Take the right decision at the right time
     Go back in time → What has happened
    – Useful to investigate performance issues
     Predict the future → Allows you to proactively handle potential issues
    – Capacity planning
     Visual Query Analysis
    – Examine the query execution plan, see sample queries, see the details of the query execution...
     Helps you to enforce best practices
     Easy to setup and configure
    12 © 2022 Oracle MySQL
    Expectations

    View full-size slide

  13. Monitoring Example – Finding Queries to Optimize
     Application metrics
     Manually executing query and determining it’s too slow or uses too many resources
     Slow Query Log
     MySQL Enterprise Monitor - Query Analyzer
     performance_schema.events_statements_summary_by_digest
     sys.statements_with_runtimes_in_95th_percentile
    13 © 2022 Oracle MySQL

    View full-size slide

  14. MySQL Enterprise Monitor – Query Analyzer
     Real-time query performance
     Visual correlation graphs
     Find & fix expensive queries
     Detailed query statistics
     Query Response Time index (QRTi)
    – "Quality of Service" (QoS) measurement for each
    query
    – QoS measurement for a server, group, or every
    instance
    – Single metric for query performance
    14 © 2022 Oracle MySQL
    Visual Query Analysis for troubleshooting
    “With the MySQL Query Analyzer, we were able to identify and analyze problematic SQL code,
    and triple our database performance. More importantly, we were able to accomplish this in
    three days, rather than taking weeks.”
    Keith Souhrada
    Software Development Engineer
    Big Fish Games
    https://www.mysql.com/products/enterprise/query.html

    View full-size slide

  15. Best Practice Advisors
     Provide a set of rules
    – Designed to enforce best practices
     14 Advisor categories
     250+ Advisors
     Threshold-based alerts
    – Exponential moving averages
    – Rate change detection
     Expert problem resolution advice
    15 © 2022 Oracle MySQL

    View full-size slide

  16. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    16
    Performance Configuration Options
    16

    View full-size slide

  17. 17
    MySQL Global Architecture
    Copyright © 2023, Oracle and/or its
    affiliates
    There are many components, each one with
    different characteristics and options.
    Application programmer and DBA have a
    consistent and easy application model.
    The MySQL pluggable storage engine
    architecture enables to select a specialized
    storage engine for a particular application need.

    View full-size slide

  18. 18
    InnoDB Architecture
    Copyright © 2023, Oracle and/or its
    affiliates
    InnoDB is a general-purpose storage
    engine that balances high reliability
    and high performance.
    Its DML operations follow the ACID
    model, with transactions featuring
    commit, rollback, and crash-recovery
    capabilities to protect user data.

    View full-size slide

  19. Configuration – Buffers & Caches
    • Some buffers are allocated in full each time they are used
    • One query may use several buffers, so large buffers can cause significant memory usage
    • Memory allocations are relatively expensive
    • For example Linux glibc malloc changes memory allocation algorithm when crossing threshold
    (typical 256k or 512k).
    – Algorithm for larger allocations can be 40 times slower than for smaller allocation
    19
    Copyright © 2023, Oracle and/or its
    affiliates
    Why is it important to use small global values?

    View full-size slide

  20. InnoDB Buffer Pool - innodb_buffer_pool_size
    • Size of the buffer pool, memory area where InnoDB caches table and index data
    • A larger buffer pool requires less disk I/O to access the same table data more than once
    • Optimal size:
    1. innodb_buffer_pool_size for InnoDB workloads
    2. How much memory does the host have?
    3. Subtract memory required by OS and other processes
    4. Subtract memory required by MySQL other then the InnoDB buffer pool
    5. Choose minimum of this and the size of the “working data set”
    • Since MySQL 5.7, innodb_buffer_pool_size can be changed dynamically
    20
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  21. Number of InnoDB Buffer Pools - innodb_buffer_pool_instances
    • Specifies how many instances to split the buffer pool into
    – Can reduce contention for concurrent workload ie improve the scalability on a busy server
    – This option takes effect only when you set innodb_buffer_pool_size to a size of 1GB or more.
    – For best efficiency, specify a combination of innodb_buffer_pool_instances & innodb_buffer_pool_size so
    that each buffer pool instance is at least 1GB
    – Max allowed value: 64
    • Dimitri suggestion
    – # BP instances = max ( 4, CPU cores / 2 )
    • But test with your workload!
    • Requires restart
    21
    Copyright © 2023, Oracle and/or its
    affiliates
    https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html

    View full-size slide

  22. Saving and Restoring the Buffer Pool State
    • To reduce the warmup period after restarting the server, InnoDB saves a percentage of the
    most recently used pages for each buffer pool at server shutdown and restores these pages
    at server startup
    – innodb_buffer_pool_dump_at_shutdown - default: ON
    – innodb_buffer_pool_dump_pct - default: 25 (percentage of the most recently used pages)
    – Page loading happens in the background, and does not delay database startup
    • You can also save and restore the buffer pool state at any time, while the server is running
    – SET GLOBAL innodb_buffer_pool_dump_now=ON; -- Save the state of the buffer pool
    – SET GLOBAL innodb_buffer_pool_load_now=ON; -- Restore the buffer pool state
    – SET GLOBAL innodb_buffer_pool_load_abort=ON; -- Abort a buffer pool load operation
    – SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status'; -- Display progress when saving the
    buffer pool state
    – SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'; -- Display progress when loading the
    buffer pool
    22
    Copyright © 2023, Oracle and/or its
    affiliates
    https://dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html

    View full-size slide

  23. InnoDB Redo Log - innodb_redo_log_capacity
    23
    Copyright © 2023, Oracle and/or its
    affiliates
    https://dev.mysql.com/doc/refman/8.0/en/optimizing-innodb-logging.html
    • Disk-based data structure used during crash recovery to correct data written by incomplete
    transactions
    • During normal operations, the redo log encodes requests to change table data that result from
    SQL statements or low-level API calls
    • Modifications that did not finish updating data files before an unexpected shutdown are
    replayed automatically during initialization and before connections are accepted
    • The redo log is physically represented on disk by redo log files. Data that is written to redo log
    files is encoded in terms of records affected, and this data is collectively referred to as redo
    • From MySQL 8.0.30, the innodb_redo_log_capacity system variable controls the amount of
    disk space occupied by redo log files
    • For optimizing redo logging, increase the size of your redo log files
    – When InnoDB has written redo log files full, it must write the modified contents of the buffer pool to
    disk in a checkpoint
    – Small redo log files cause many unnecessary disk writes
    – BUT it consume disk space and increases the recovery time in case of a restart
    (innodb_fast_shutdown=1) or a sudden crash. And it also slows down shutdown when
    innodb_fast_shutdown=0
    From MySQL 8.0.30

    View full-size slide

  24. Optimal InnoDB Redo Log Capacity
    24
    Copyright © 2023, Oracle and/or its
    affiliates
    • Since MySQL 8.0.30 it's possible to dynamically resize this log
    – Redo logs are now on a dedicated directory: #innodb_redo
    – 32 files (innodb_redo_log_capacity / 32)
    – innodb_log_files_in_group and innodb_log_files_size are now deprecated (ignored)
    • Calculating the optimal size (during your peak):
    – The rule of thumb is to make the Redo Log Capacity large enough to hold at least 1h of logs, so as
    not to force InnoDB to dump dirty pages too often in case the Redo Log Capacity is too small, and not
    to waste disk and take too long for recovery if it is too large
    – During peak traffic time, you can get an estimation of the required amount for the Redo Log Capacity
    Size by running the query below (all in one single line):
    SELECT VARIABLE_VALUE FROM performance_schema.global_status
    WHERE VARIABLE_NAME='Innodb_redo_log_current_lsn' into @a;SELECT sleep(60) into
    @garb ;SELECT VARIABLE_VALUE FROM performance_schema.global_status
    WHERE VARIABLE_NAME='Innodb_redo_log_current_lsn' into @b;SELECT
    format_bytes(abs(@a - @b)) per_min, format_bytes(abs(@a - @b)*60) per_hour;
    https://lefred.be/content/dynamic-innodb-redo-log

    View full-size slide

  25. InnoDB Configuration – Disabling Redo Logging
    25
    Copyright © 2023, Oracle and/or its
    affiliates
    • As of MySQL 8.0.21, you can disable redo logging using
    – ALTER INSTANCE DISABLE INNODB REDO_LOG
    • This functionality is intended for loading data into a new MySQL instance
    • Disabling redo logging speeds up data loading by avoiding redo log writes and doublewrite
    buffering
    • This feature is intended only for loading data into a new MySQL instance
    • Do not disable redo logging on a production system
    • Details:
    – https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html#innodb-disable-redo-logging
    https://dev.mysql.com/doc/refman/8.0/en/innodb-redo-log.html#innodb-disable-redo-logging

    View full-size slide

  26. innodb_flush_log_at_trx_commit
    26
    Copyright © 2023, Oracle and/or its
    affiliates
    • Controls the balance between strict ACID compliance for commit operations & higher
    performance
    • You can achieve better performance by changing the default value but then you can lose
    transactions in a crash
    • Possible values in order of data safety:
    – 1: Logs are written and flushed to disk at each transaction commit

    Is theoretically the slowest, but with fast SSD it may be around as fast as 2 and 0
    – 2: Logs are written after each transaction commit and flushed to disk once per second

    Transactions for which logs have not been flushed can be lost in a crash (host)
    – 0: Logs are written and flushed to disk once per second

    Transactions for which logs have not been flushed can be lost in a crash

    Not recommended in a production environment…

    Default to 1; Logs are written and flushed to disk at each transaction commit
    – Required for full ACID compliance - D in ACID
    – For this reason it is the recommended value

    Log flushing frequency is controlled by innodb_flush_log_at_timeout
    https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
    Controls the balance between strict ACID compliance for commit operations and higher performance

    View full-size slide

  27. innodb_flush_log_at_trx_commit
    27
    Copyright © 2023, Oracle and/or its
    affiliates
    • What if innodb_flush_log_at_trx_commit = 1 is too slow and you want the D in ACID?
    – Make sure the redo logs are on separate disks

    Do not be tempted to have the individual redo logs on different disks

    Consider SSD particularly if you have a high commit rate

    Battery-backed disk cache also makes flushes cheaper
    https://dev.mysql.com/doc/refman/8.0/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit
    Controls the balance between strict ACID compliance for commit operations and higher performance

    View full-size slide

  28. Automatically configure InnoDB - innodb_dedicated_server

    When innodb_dedicated_server is enabled, InnoDB automatically configures the following
    variables:
    – innodb_buffer_pool_size
    – innodb_redo_log_capacity
    – innodb_flush_method

    Explicit settings can always be set
    – Explicit configuration of one option does not prevent the automatic configuration of other options

    Only consider enabling innodb_dedicated_server if the MySQL instance resides on a
    dedicated server where it can use all available system resources
    – e.g. Running MySQL Server in a Docker container or dedicated VM that only runs MySQL

    Enabling innodb_dedicated_server is not recommended if the MySQL instance shares
    system resources with other applications

    Automatically configured settings are evaluated and reconfigured if necessary each time
    the MySQL server is started
    28
    Copyright © 2023, Oracle and/or its
    affiliates
    Enabling Automatic Configuration for a Dedicated MySQL Server
    https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

    View full-size slide

  29. Connections, Opened Tables and Opened Files
    • max_connections
    o Maximum permitted number of simultaneous client
    connections
    o Be careful setting this too large as each connection
    requires memory
    • max_connections affect the maximum
    number of files the server keeps open
    o If you increase it, you may contribute to run up against a
    limit imposed by your operating system on the per-
    process number of opened file descriptors
    • table_open_cache
    o Number of maximum allowed open tables for all threads
    o Increasing this value increases the number of file
    descriptors that mysqld requires
    o Each table can be open more than once
    o You can check whether you need to increase the table
    cache by checking the Opened_tables status variable
     If the value of Opened_tables is large and you do not use
    FLUSH TABLES often, then you should increase
    the value of the table_open_cache variable
    • Also table_open_cache affects the maximum
    number of files the server keeps open
    29
    Copyright © 2023, Oracle and/or its
    affiliates
    Remember to set
    ulimits and file
    descriptors in Linux
    servers

    View full-size slide

  30. 30
    Schema Optimization
    Copyright © 2023 Oracle and/or its affiliates

    View full-size slide

  31. 31
    Copyright © 2023, Oracle and/or its
    affiliates
    • Design your tables to minimize their space on the disk <=> use more caching, thus minimize
    disk seeks
    – Reduce the amount of data written to and read from disk
    – Smaller tables normally require less main memory
    – Smaller indexes that can be processed faster

    Use the most efficient (smallest) data types possible
    – e.g. MEDIUMINT vs INT (MEDIUMINT uses 25% less space than INT) or BIGINT, TINYINT (UNSIGNED ?)

    Declare columns to be NOT NULL if possible
    – It makes SQL operations faster, by enabling better use of indexes & eliminating overhead for testing
    whether each value is NULL
    – You also save some storage space
    Schema Optimization Tips 1/2
    Look for the most efficient way to organize your data

    View full-size slide

  32. 32
    Copyright © 2023, Oracle and/or its
    affiliates
    • The primary key of a table should be as short as possible
    – This makes identification of each row easy and efficient
    – For InnoDB tables, the PK columns are duplicated in each secondary index entry, so a short PK saves
    considerable space if you have many secondary indexes

    Create only the indexes that you need to improve query performance
    – Indexes are good for retrieval, but slow down insert and update operations
    – Indexes use disk & memory spaces
    – Sometime it can be beneficial to use column prefix key parts (especially for long string column)

    Declare columns with identical information in different tables with identical data types,
    charsets & collations
    – Speed up joins based on the corresponding columns
    Schema Optimization Tips 2/2
    Look for the most efficient way to organize your data

    View full-size slide

  33. Generated Invisible Primary Keys (GIPK)
    • You can force usage of PK with sql_require_primary_key=on
    • You can create an index (PK) based on an invisible column
    • MySQL supports also automatically generated invisible
    primary keys when running in GIPK mode (8.0.23+)
    • GIPK mode is controlled by the
    sql_generate_invisible_primary_key server system variable
    o GIPK mode: a primary key is added to a table by the server,
    the column and key name is always my_row_id
    o You can literally run all application with MySQL InnoDB Cluster!
    • Extra tip – Finding InnoDB tables without Pks:
    SELECT tables.table_schema , tables.table_name , tables.engine
    FROM information_schema.tables LEFT JOIN
    (SELECT table_schema , table_name FROM information_schema.statistics
    GROUP BY table_schema, table_name, index_name
    HAVING
    SUM(CASE WHEN non_unique = 0 AND nullable != 'YES' then 1 else 0 end ) = count(*) )
    puks ON tables.table_schema = puks.table_schema AND tables.table_name = puks.table_name
    WHERE puks.table_name IS null AND tables.table_type = 'BASE TABLE' AND Engine='InnoDB';
    33
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  34. 34
    Query Performance
    Copyright © 2023 Oracle and/or its affiliates
    Make the best use of your data

    View full-size slide

  35. Solving Query Performance Problems
    35
    Copyright © 2023, Oracle and/or its
    affiliates
    1 2 3 4
    • Visually Identify Slow Queries
    • Correlation Graphs
    • Query Response Time index (QRTi)
    • Execution Statistics
    • Tune Queries
    • Add Indexes
    • Tune Schemas
    • Improve Configuration
    • MySQL Explain Plan
    • Sample Query
    • Query profiling Better Performance
    (hopefully)

    View full-size slide

  36. 36
    Copyright © 2023, Oracle and/or its
    affiliates
    MySQL Optimizer (simplified)
    https://dev.mysql.com/doc/refman/8.0/en/cost-model.html

    View full-size slide

  37. 37
    Copyright © 2023, Oracle and/or its
    affiliates
    • The set of operations that the optimizer chooses to perform the most efficient query is called
    the Query Execution Plan or EXPLAIN plan
    • MySQL explains how it would process the statement, including information about how tables
    are joined and in which order
    • Returns a row of information for each table used in the statement
    • Lists the tables in the output in the order that MySQL would read them while processing the
    statement
    – You can see where you should add indexes to tables so that the statement executes faster by using
    indexes to find rows
    – You can check whether the optimizer joins the tables in an optimal order
    • EXPLAIN works with SELECT, TABLE, DELETE, INSERT, REPLACE, and UPDATE statements
    Analyzing Queries – EXPLAIN
    Provide information about how MySQL executes statements
    https://dev.mysql.com/doc/refman/8.0/en/explain.html

    View full-size slide

  38. 38
    Copyright © 2023, Oracle and/or its
    affiliates
    • The best way to improve the performance of SELECT operations is to create relevant
    indexes on one or more of the columns that are tested in the query
    • The index entries act like pointers to the table rows, allowing the query to quickly determine
    which rows match a condition in the WHERE clause, and retrieve the other column values for
    those rows
    • All MySQL data types can be indexed
    • Although it can be tempting to create an indexes for every possible column used in a query,
    unnecessary indexes waste space and waste time for MySQL to determine which
    indexes to use
    • Indexes also add to the cost of INSERT, UPDATE & DELETE because each index must be
    updated
    • You must find the right balance to achieve fast queries using the optimal set of indexes
    Indexes

    View full-size slide

  39. 39
    Copyright © 2023, Oracle and/or its
    affiliates
    • Indexed column is used as argument to function
    – YEAR(o_orderdate) = 1997
    – Use functional indexes: https://dasini.net/blog/2019/03/14/mysql-functional-indexes
    • Looking for a suffix :
    – name LIKE '%son'
    • First column(s) of compound index NOT used
    – b = 10 when index defined over (a, b)
    • Type mismatch
    – my_string = 10
    • Character set / collation mismatch
    – t1 LEFT JOIN t2 ON t1.utf8_string = t2. latin1_string
    Some Reasons Why Index can NOT be Used

    View full-size slide

  40. 40
    Copyright © 2023, Oracle and/or its
    affiliates
    • The server stores histogram statistics about column values, for use by the optimizer in
    constructing query execution plans
    • The optimizer applies histogram statistics to determine row estimates based on the selectivity
    (filtering effect) of column value comparisons against constant values
    • Histogram statistics are useful primarily for non-indexed columns
    – Adding an index to a column for which histogram statistics are applicable might also help the
    optimizer make row estimates
    – A histogram is created or updated only on demand, so it adds no overhead when table data is
    modified
    – On the other hand, the statistics become progressively more out of date when table modifications
    occur, until the next time they are updated
    • The optimizer prefers range optimizer row estimates to those obtained from histogram
    statistics
    – If the optimizer determines that the range optimizer applies, it does not use histogram statistics
    • To perform histogram management, use the ANALYZE TABLE
    Histogram
    https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html

    View full-size slide

  41. 41
    Copyright © 2023, Oracle and/or its
    affiliates
    -- Update (or create) an histogram on column salaries.to_date
    ANALYZE TABLE salaries UPDATE HISTOGRAM ON to_date;
    -- Extract values from the histogram objects
    SELECT
    TABLE_NAME,
    COLUMN_NAME,
    HISTOGRAM->>'$."data-type"' AS 'data-type',
    JSON_LENGTH(HISTOGRAM->>'$."buckets"') AS 'bucket-count'
    FROM INFORMATION_SCHEMA.COLUMN_STATISTICS;
    Histogram Tips

    View full-size slide

  42. 42
    Copyright © 2023, Oracle and/or its
    affiliates
    • InnoDB primary key is included in all secondary indexes in order to be able to locate the actual row
    – Smaller primary key gives smaller secondary indexes
    – A mostly sequential primary key is in general recommended to avoid inserting rows between existing rows
    – Alternative: create an AUTO_INCREMENT column as the primary key if your primary key is long, or index a
    prefix of a long VARCHAR column instead of the entire column
    • InnoDB automatically extends each secondary index by appending the primary key columns to it
    – The optimizer takes into account the primary key columns of the extended secondary index when
    determining how and whether to use that index
    – This can result in more efficient query execution plans and better performance
    – Could be disable with : SET optimizer_switch = 'use_index_extensions=off';
    • When you use a randomly generated value as a primary key in an InnoDB table, prefix it with an
    ascending value such as the current date and time if possible
    – When consecutive primary values are physically stored near each other, InnoDB can insert and retrieve
    them faster
    • It is usually better to create a small number of concatenated indexes rather than a large number of
    single-column indexes
    – If an index contains all the columns needed for the result set (ie covering index), the query might be able
    to avoid reading the table data at all
    InnoDB Tips
    https://dev.mysql.com/doc/refman/8.0/en/optimizer-statistics.html

    View full-size slide

  43. 43
    Copyright © 2023, Oracle and/or its
    affiliates
    • Rewrite problematic queries without the need to make application changes
    – Add hints
    – Modify join order...
    • Statements subject to rewriting: SELECT, INSERT, REPLACE, UPDATE & DELETE
    • Standalone statements and prepared statements are subject to rewriting
    • Statements occurring within view definitions or stored programs are not subject to rewriting
    • e.g. Adding Rewrite Rules:
    INSERT INTO query_rewrite.rewrite_rules (pattern, replacement ) VALUES
    ("SELECT sum(salary) FROM employees.salaries WHERE from_date BETWEEN ? AND ?",
    "SELECT /*+ INDEX(salaries idx_from_date) */ sum(salary) FROM employees.salaries
    WHERE from_date BETWEEN ? AND ?");
    • Information:
    – https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin.html
    – https://dasini.net/blog/2016/02/25/30-mins-with-mysql-query-rewriter/
    Query Rewrite Plugin
    https://dev.mysql.com/doc/refman/8.0/en/rewriter-query-rewrite-plugin.html

    View full-size slide

  44. 44
    Tips for common workloads
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  45. Read Intensive workloads
    • Remember to correctly dimension the memory
    • Sizing InnoDB Buffer Pool correctly
    o Controlled with the variable innodb_buffer_pool_size
    o Allocate 50-75% of free memory on the machine
    o Consider memory for additional caches
    • Learn how MySQL uses memory:
    https://dev.mysql.com/doc/refman/8.0/en/memory-use.html
    • Consider a different transaction Isolation Level
    o READ-COMMITED instead of the default REPEATABLE READ
    • Use Replication to split read/writes
    o This has to be controlled via the application or MySQL Router ports (ReplicaSet)
    • Use InnoDB Cluster to split read/writes
    o Controlled via read-only port in MySQL Router
    o Secondary nodes in super-read only mode already available in InnoDB Cluster
    45
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  46. Asynchronous Replication
    • Increase the value of replica_parallel_workers
    o Do not set it higher than available cores
    • Save bandwidth using binlog_row_image=minimal and
    binlog_transaction_compression
    • Place the relay logs on a separate disk using relay_log system variable
    • Disable binary logs on the replica(s) or control what is logged
    with log-replica-update
    • Increase the value of rpl_read_size
    o If heavy disk I/O activity for reads of the binary log and relay log
    • Consider replicating only some schemas with replication filter
    o replicate_do_db, replicate_do_table, replicate_ignore_db, etc...
    46
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  47. InnoDB Cluster and InnoDB Cluster Set
    InnoDB Cluster (Group Replication):
    • Deploy over a reliable, low latency network
    • If virtualized environment, deploy on different physical nodes
    • To save network bandwidth, set binlog_row_image=minimal
    • Consider compressing binlog using binlog_transaction_compression
    • From 8.0.27, improve performance and resiliency in single primary
    mode, using group_replication_paxos_single_leader
    InnoDB ClusterSet:
    • Increase the value of replica_parallel_workers
    o Do not set it higher than available cores
    • Save bandwidth using binlog_row_image=minimal
    and binlog_transaction_compression
    47
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  48. Write intensive workloads
    • Fast enough SSD or NVMe disks
    • Sizing of the redo logs with innodb_redo_log_capacity
    o Rule of thumb - maintain 1 hour of redo logs
    • Set innodb_flush_method = O_DIRECT
    o If supported by OS
    • Use dedicated disks for data and redo log
    • Consider using innodb_flush_log_trx_at_commit
    o Relax durability (from ACID). if it is ok to eventually loose some transactions
    • Consider using Compression
    o But remember it consumes CPU resources
    • Consider using Partitioning
    o It gives stable performance on INSERT (performance decreases with data growth)
    o Perfect for archiving/pruning
    48
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  49. MySQL Enterprise Thread Pool
    • Provides a highly scalable thread-handling model
    designed to reduce overhead in managing client
    connections & statement execution threads
    • The default thread-handling model in MySQL
    Server executes statements using one thread per
    client connection
    – As more clients connect to the server and execute
    statements, overall performance degrades
    • The thread pool plugin provides an alternative
    thread-handling model designed to reduce
    overhead and improve performance
    – The plugin implements a thread pool that increases
    server performance by efficiently managing
    statement execution threads for large numbers of
    client connections
    49
    Copyright © 2023, Oracle and/or its
    affiliates
    https://www.mysql.com/products/enterprise/scalability.html
    To meet the sustained performance and scalability of ever increasing user, query and data loads

    View full-size slide

  50. MySQL Heatwave for read OLAP workloads
    50
    • Single MySQL database for both OLTP, OLAP/Analytics and ML applications
    (No need to ETL)
    • All existing applications will work without any changes – incl. Tableau, Qlik, Looker
    • Enables running analytics and ML also for data stored on-premises using MySQL Replication
    • No need to learn new language or ML packages
    • Extreme performance: Accelerates MySQL by orders of magnitude
    Available on major cloud platforms: OCI, Azure, AWS
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  51. NDB Cluster: skyrocket read and write OLTP workloads
    What is it:
    • Leading database in Telco
    • NDB storage engine
    • In-Memory database
    • Fully Synchronous
    • Automatic sharding of data
    • SQL Access via MySQL with cross
    shard join support
    • Native access via several API’s
    When to consider:
    • You need Sharding, either due to size or write performance
    • You need Linear Scalabillity when adding more nodes
    • You need predictable Real-ime response times
    • SQL and cross shard join support
    • You want a ACID distributed in-memory database
    51
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  52. MySQL Shell: Dump & Load Utilities
    • MySQL Shell 8.0.21+ Logical Dump and Load Utilities
    • util.dumpInstance() , util.dumpSchemas() , util.dumpTables() , util.loadDump()
    • Utilities to create a logical dump and do a logical restore
    • With a focus on ease of use, performance & integration
    • Allow to restore logical dumps of MySQL databases
    o in parallel
    o with zstd or gzip
    o compression
    o optionally store them in an OCI Object Storage bucket
    • It also has features designed to help smoothly copy data from MySQL 5.7 / 5.6 and MySQL 8.0 servers to
    the new MySQL HeatWave Database Cloud Service
    • Details:
    o https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-dump-instance-schema.html
    o https://dev.mysql.com/doc/mysql-shell/8.0/en/mysql-shell-utilities-load-dump.html
    52
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  53. Summary
    53
    Monitor your databases Without monitoring you are blind. Monitor the database and OS. Monitor
    over time, monitor query statistics.
    Make small incremental changes Change one parameter, test/monitor, change the next one. Document
    changes in behaviour.
    Use InnoDB MyISAM have table level locks. InnoDB is ACID. InnoDB scales on modern
    HW. Online DDL.
    Understand “EXPLAIN” Query tuning is the holy grail of performance tuning and explain is the
    most important tool for query tuning.
    Delete/archive old data Large instances/tables are one of the most common problems for slow
    queries.
    Use SSD/NVMe drives If your working-set of data does not fit into memory make sure you have a
    fast disk system (for random IO operations).
    Use MySQL 8.0 MySQL 8.0 scales better than older versions of MySQL, the optimizer have
    also been improved with hash-joins and histograms.
    Avoid long running transactions Huge risk of locking contention, separate your OLTP and OLAP functionality
    on separate MySQL instances.
    Use foreign keys with care Foreign keys are good for consistency but in general bad for performance
    and takes more locks.
    Avoid complex SQL (for OLTP) Hard to understand, hard to optimize and in many cases slow.
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  54. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    54
    Follow us on Social Media

    View full-size slide

  55. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.
    55
    Merci!
    Q&R
    Olivier Dasini
    MySQL Cloud Principal Solutions Architect EMEA
    [email protected]
    Blogs : www.dasini.net/blog/en
    : www.dasini.net/blog/fr
    Linkedin: www.linkedin.com/in/olivier-dasini
    Twitter : @freshdaz

    View full-size slide

  56. Copyright © 2023, Oracle and/or its affiliates. All rights reserved.

    View full-size slide

  57. 57
    If you’re still reading… :)
    Copyright © 2023, Oracle and/or its
    affiliates
    Some additional information

    View full-size slide

  58. 58
    Copyright © 2023, Oracle and/or its
    affiliates
    Dimitri Kravtchuk
    MySQL Performance Architect
    https://dimitrik.free.fr/blog/
    The MySQL Performance Best Practice #1
    is… ???
    USE YOUR BRAIN !!! ;-)

    View full-size slide

  59. SET PERSIST: Persisted System Variables
    • Persistent runtime configuration changes written in mysqld-auto.cnf file (JSON format)
    o SET PERSIST variable_name = value; (RESET PERSIST removes persisted settings from mysqld-auto.cnf)
    o Any GLOBAL DYNAMIC configuration variable can be modified
    o New tables:
     performance_schema.persisted_variables: SQL interface to the mysqld-auto.cnf file that stores persisted global system variable settings
     performance_schema.variables_info: shows, for each system variable, the source from which it was most recently set, and its range of
    values
    59
    Copyright © 2023, Oracle and/or its
    affiliates
    https://dev.mysql.com/doc/refman/8.0/en/persisted-system-variables.html
    MySQL> SET PERSIST innodb_buffer_pool_size=268435456;
    MySQL> SET PERSIST innodb_redo_log_capacity=209715200;
    MySQL> SELECT * FROM performance_schema.persisted_variables;
    +--------------------------+----------------+
    | VARIABLE_NAME | VARIABLE_VALUE |
    +--------------------------+----------------+
    | innodb_redo_log_capacity | 209715200 |
    | innodb_buffer_pool_size | 268435456 |
    +--------------------------+----------------+
    Easier changes without server restart

    View full-size slide

  60. More about indexing & MySQL 8
    • Indexes can now be built simultaneously instead of serially
    o innodb_ddl_threads/innodb_ddl_buffer_size/innodb_parallel_read_threads
    o possibility to build multiple indexes for a table in the same alter statement in parallel
    • Descending Indexes
    o Support for ASC, DESC in index definitions
    mysql> CREATE TABLE t1 (a INT,b INT,INDEX a_desc_b_asc (a DESC, b ASC));
    o Multiple-column indexes with ascending order for some columns and descending order for others
    • New Index-Level Optimizer hints
    o FORCE INDEX and IGNORE INDEX (FOR GROUP/ORDER BY)
    have been deprecated
    • Replaced by equivalent INDEX/NO_INDEX,
    GROUP_INDEX, NO_GROUP_INDEX, ORDER_INDEX, etc.
    o More possibilities of combining index-level optimizer hints
    for more fine-grained control
    60
    Copyright © 2023, Oracle and/or its
    affiliates

    View full-size slide

  61. Better Handling of Hot Rows
    SELECT ... FOR UPDATE SKIP LOCKED
    Common problem:
    • Hot row contention, multiple worker
    threads accessing the same rows
    Solution 1:
    • Only read rows that are not locked
    • InnoDB skips a locked row, and the next
    one goes to the result set
    Example:
    • Booking system: Skip orders that are
    pending
    SELECT… FOR UPDATE NOWAIT
    Common problem:
    • Hot row contention, multiple worker
    threads accessing the same rows
    Solution 2:
    • If any of the rows are already locked, the
    statement should fail immediately
    • Without NOWAIT, have to wait for
    innodb_lock_wait_timeout (default: 50
    sec) while trying to acquire lock
    61
    Copyright © 2023, Oracle and/or its
    affiliates
    START TRANSACTION;
    SELECT * FROM seats WHERE seat_no
    BETWEEN 2 AND 3 AND booked = 'NO‘
    FOR UPDATE SKIP LOCKED;
    START TRANSACTION;
    SELECT * FROM seats WHERE seat_no
    BETWEEN 2 AND 3 AND booked = 'NO‘
    FOR UPDATE NOWAIT;
    ERROR 3572 (HY000):
    Statement aborted because lock(s) could not be acquired …

    View full-size slide

  62. EXPLAIN output changes in MySQL 8.0
    • EXPLAIN FORMAT=JSON and TREE provide add details on
    Used indexes, Pushed index conditions, Cost estimates,
    Data estimates
    62
    Copyright © 2023, Oracle and/or its
    affiliates
    mysql> EXPLAIN FORMAT=JSON SELECT city.name,
    city.population, country.name AS country
    FROM city, country
    WHERE city.CountryCode=country.Code
    AND city.name='Roma';
    +-------------------------------------+
    | {
    "query_block": {
    "select_id": 1,
    "cost_info": {
    "query_cost": "550.97"
    },
    "nested_loop": [
    {
    "table": {
    "table_name": "city",
    "access_type": "ALL",
    "possible_keys": [
    "CountryCode"
    ],
    "rows_examined_per_scan": 4035,
    "rows_produced_per_join": 403,
    "filtered": "10.00",
    "cost_info": {
    "read_cost": "369.40",
    "eval_cost": "40.35",
    "prefix_cost": "409.75",
    "data_read_per_join": "97K"
    },
    ...
    mysql> EXPLAIN FORMAT=TREE SELECT city.name, city.population, country.name AS country
    FROM city, country
    WHERE city.CountryCode=country.Code AND city.name='Roma';
    +---------------------------------------------------------------------------------------------------+
    | EXPLAIN |
    +---------------------------------------------------------------------------------------------------+
    | -> Nested loop inner join (cost=550.98 rows=404)
    -> Filter: (city.`Name` = 'Roma') (cost=409.75 rows=404)
    -> Table scan on city (cost=409.75 rows=4035)
    -> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=0.25 rows=1)
    |
    +---------------------------------------------------------------------------------------------------+
    mysql> EXPLAIN SELECT city.name, city.population, country.name AS country FROM city, country WHERE city.CountryCode=country.Code AND city.name='Roma';
    +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
    | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
    +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+
    | 1 | SIMPLE | city | NULL | ALL | CountryCode | NULL | NULL | NULL | 4035 | 10 | Using where |
    | 1 | SIMPLE | country | NULL | eq_ref | PRIMARY | PRIMARY | 12 | world.city.CountryCode | 1 | 100 | NULL |
    +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+

    View full-size slide

  63. EXPLAIN FORMAT=TREE/ANALYZE
    • EXPLAIN ANALYZE is built on FORMAT=TREE and adds
    o Time (in ms) to first row
    o Time (in ms) to last row
    o Number of rows
    o Number of loops
    mysql> EXPLAIN ANALYZE SELECT city.name, city.population, country.name AS country
    FROM city, country WHERE city.CountryCode=country.Code AND city.name='Roma';
    +-----------------------------------------------------------------------------------------------------------------------------------------------+
    | EXPLAIN |
    +-----------------------------------------------------------------------------------------------------------------------------------------------+
    | -> Nested loop inner join (cost=550.98 rows=404) (actual time=0.833..2.131 rows=1 loops=1)
    -> Filter: (city.`Name` = 'Roma') (cost=409.75 rows=404) (actual time=0.818..2.116 rows=1 loops=1)
    -> Table scan on city (cost=409.75 rows=4035) (actual time=0.037..1.333 rows=4079 loops=1)
    -> Single-row index lookup on country using PRIMARY (Code=city.CountryCode) (cost=0.25 rows=1)
    (actual time=0.014..0.014 rows=1 loops=1)
    |
    +-----------------------------------------------------------------------------------------------------------------------------------------------+
    mysql> SELECT city.name, city.population, country.name AS country
    FROM city,country WHERE city.CountryCode=country.Code AND city.name='Roma';
    +------+------------+---------+
    | name | population | country |
    +------+------------+---------+
    | Roma | 2643581 | Italy |
    +------+------------+---------+
    1 row in set (0.0022 sec)
    63
    Copyright © 2023, Oracle and/or its
    affiliates
    TimingIterator
    TimingIterator
    TimingIterator
    HashJoinIterator
    TableScanIterator TableScanIterator
    t1 t2
    EXPLAIN ANALYZE
    SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;

    View full-size slide

  64. JSON improvements in MySQL 8.0
    • New JSON functions added
    o JSON_ARRAYAGG(), JSON_OBJECTAGG(), etc..
    • Ranges in JSON Path expressions:
    SELECT doc->>"$[last]" AS last from t1;
    SELECT JSON_EXTRACT('[1, 2, 3, 4, 5]', '$[1 to 3]');
    • Optimizer Support for in-place update
    • Multi-value indexes
    • Performance Improvements
    • Partial Update of JSON values:
    o When apps frequently update small portions of large JSON documents
    JSON_SET(), JSON_REPLACE(), JSON_REMOVE()
    o Only write the change back to the database
    o Only replicate the change
    • Efficient Replication of JSON Documents:
    Replicate only changed fields of documents
    64
    Copyright © 2023, Oracle and/or its
    affiliates
    Entire JSON Partial JSON
    0
    10000
    20000
    30000
    40000
    50000
    60000
    Binary Log Space Per Transaction
    FULL MINIMAL
    Bytes per transaction
    • In this benchmark:
    – Tables have 10 JSON fields
    – Each transaction modifies around 10% of the
    data

    View full-size slide

  65. Data Locks
    • It's now easier to identify data locks with performance_schema.data_locks table
    65
    Copyright © 2023, Oracle and/or its
    affiliates
    mysql> SELECT thread_id, object_name, index_name, lock_type, lock_mode, lock_data
    FROM performance_schema.data_locks WHERE object_name = 'seats';
    +-----------+-------------+------------+-----------+-----------+-----------+
    | thread_id | object_name | index_name | lock_type | lock_mode | lock_data |
    +-----------+-------------+------------+-----------+-----------+-----------+
    | 33 | seats | NULL | TABLE | IX | NULL |
    | 33 | seats | PRIMARY | RECORD | X | 3, 5 |
    | 33 | seats | PRIMARY | RECORD | X | 3, 6 |
    | 33 | seats | PRIMARY | RECORD | X | 4, 5 |
    | 33 | seats | PRIMARY | RECORD | X | 4, 6 |
    +-----------+-------------+------------+-----------+-----------+-----------+
    5 rows in set (0.00 sec)

    View full-size slide