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 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

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

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

4 Introduction 4 The world’s most popular open source database

- 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

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

7 MySQL Performance Tuning 7 Elementary Guidelines

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

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

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

11 Monitoring 11 There is no control without measurement!

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

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

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

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

16 Performance Configuration Options 16

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.

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.

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?

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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)

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

54 Follow us on Social Media

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

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

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

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 …

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 | +----+-------------+---------+------------+--------+---------------+---------+---------+------------------------+------+----------+-------------+

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;

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

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

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript