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

View Slide

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 Slide

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 Slide

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

View Slide

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

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
affiliates

View Slide

7
MySQL Performance Tuning
7
Elementary Guidelines

View Slide

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
affiliates

View Slide

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
affiliates

View Slide

Source of database performance problems
10
affiliates
Hardware
Schema Changes
Data Growth
Indexes
SQL
90% of
Performance Problems

View Slide

11
Monitoring
11
There is no control without measurement!

View Slide

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 Slide

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

View Slide

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

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

View Slide

16
Performance Configuration Options
16

View Slide

17
MySQL Global Architecture
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 Slide

18
InnoDB Architecture
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 Slide

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
affiliates
Why is it important to use small global values?

View Slide

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
affiliates

View Slide

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
affiliates
https://dev.mysql.com/doc/refman/8.0/en/innodb-multiple-buffer-pools.html

View Slide

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
affiliates
https://dev.mysql.com/doc/refman/8.0/en/innodb-preload-buffer-pool.html

View Slide

InnoDB Redo Log - innodb_redo_log_capacity
23
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 Slide

Optimal InnoDB Redo Log Capacity
24
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 Slide

InnoDB Configuration – Disabling Redo Logging
25
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 Slide

innodb_flush_log_at_trx_commit
26
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 Slide

innodb_flush_log_at_trx_commit
27
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 Slide

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
affiliates
Enabling Automatic Configuration for a Dedicated MySQL Server
https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

View Slide

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
affiliates
Remember to set
ulimits and file
descriptors in Linux
servers

View Slide

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

View Slide

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

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

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
affiliates

View Slide

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

View Slide

Solving Query Performance Problems
35
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 Slide

36
affiliates
MySQL Optimizer (simplified)
https://dev.mysql.com/doc/refman/8.0/en/cost-model.html

View Slide

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

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

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

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

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

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

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

44
Tips for common workloads
affiliates

View Slide

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
affiliates

View Slide

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
affiliates

View Slide

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
affiliates

View Slide

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
affiliates

View Slide

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

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
affiliates

View Slide

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
affiliates

View Slide

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
affiliates

View Slide

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

View Slide

54
Follow us on Social Media

View Slide

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 Slide


View Slide

57
If you’re still reading… :)
affiliates
Some additional information

View Slide

58
affiliates
Dimitri Kravtchuk
MySQL Performance Architect
https://dimitrik.free.fr/blog/
The MySQL Performance Best Practice #1
is… ???
USE YOUR BRAIN !!! ;-)

View Slide

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

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
affiliates

View Slide

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

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

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
affiliates
TimingIterator
TimingIterator
TimingIterator
HashJoinIterator
TableScanIterator TableScanIterator
t1 t2
EXPLAIN ANALYZE
SELECT * FROM t1 JOIN t2 ON t1.a = t2.a;

View Slide

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

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

More Decks by Olivier DASINI

Other Decks in Technology

Featured

Transcript