MySQL 8.0: Performance Tuning

Introduction

• Objectives
• Course Goals
• Course Lesson Map
• Introductions
• Classroom Environment
• MySQL Powers the Web
• Database of the Year
• MySQL Enterprise Edition
• MySQL Database Service
• Oracle Premier Support for MySQL
• MySQL and Oracle Integration
• MySQL Websites
• Community Resources
• Oracle University: MySQL Training
• MySQL Certification
• Summary

Performance Tuning Concepts

• Topics
• Objectives
• Topics
• Improving Performance
• Areas to Tune
• Topics
• Performance Tuning Terminology
• Response Time
• Measuring Response Times
• Throughput
• Scalability
• Queuing Theory
• Quiz
• Topics
• Benchmarking
• Benchmarking Best Practices
• More Benchmarking Best Practices
• Benchmarking Bad Practices
• More Benchmarking Bad Practices
• Quiz
• Topics
• Before You Start: Establish a Performance Baseline
• Key Steps in Troubleshooting Performance Issues
• Establishing the Nature of the Problem
• Starting to Troubleshoot
• Identifying Possible Causes of Performance Issues
• Application Profiling
• Localizing Database Problems
• Topics
• Tuning Steps
• General Tuning Procedure
• Topics
• Deploying MySQL
• RAM Requirements
• Maintaining MySQL
• Summary

Performance Tuning Tools

• Topics
• Objectives
• Topics
• MySQL Monitoring Tools
• SHOW [SESSION | GLOBAL] STATUS
• Filtering SHOW STATUS Output
• Status Variables: Handler Operations
• Status Variables: Internal Temporary Tables and Files
• SHOW STATUS: Examples
• SHOW ENGINE INNODB STATUS
• SHOW PROCESSLIST
• Quiz
• mysqladmin
• Information Schema
• Performance Schema
• MySQL Workbench
• MySQL Enterprise Monitor: Overview
• MySQL Enterprise Monitor: Architecture
• MySQL Enterprise Monitor: Global Overview
• MySQL Enterprise Monitor: Query Analyzer
• MySQL Enterprise Monitor: InnoDB Performance
• Topics
• Oracle Enterprise Manager for MySQL
• Oracle SQL Developer
• Quiz
• Topics
• Community Monitoring Tools
• Topics
• Linux Tools
• iostat
• vmstat
• sar
• top
• Topics
• Benchmarking Tools
• MySQL BENCHMARK() Function
• Stress Tools
• mysqlslap Stress Tool
• sysbench
• Using SysBench
• Sample SysBench Output – 1
• Sample SysBench Output – 2
• Summary

Performance Schema

• Topics
• Objectives
• Topics
• Why Use Performance Schema?
• Performance Schema
• How Performance Schema Works
• Instrument Names
• Instrument Prefixes
• Instrument Suffixes
• Instrument Names: Example
• Topics
• Structure of the Performance Schema
• Table Groups
• Topics
• Configuring the Performance Schema
• Setup Tables
• Configuring Instruments
• Configuring Instruments: Examples
• Event Timing
• Configuring Consumers
• Consumer Table Hierarchy
• Configuring Consumers
• Configuring Objects
• Default setup_objects Configuration
• Configuring Actors
• Configuring Thread Monitoring
• Configuring Instruments and Consumers at Startup
• Performance Schema Overhead
• How Performance Schema Uses Memory
• Configuring Performance Schema System Variables
• Quiz
• Topics
• Instance Tables
• cond_instances Table
• file_instances Table
• Read/Write Locks and Mutexes
• mutex_instances Table
• Mutex Life Cycle in the Performance Schema
• rwlock_instances Table
• socket_instances Table
• Identifying Connections in the socket_instances Table
• Connection Tables
• Retrieving Detailed Connection Information
• Quiz
• Topics
• Examining Raw Event Data: Example 1
• Examining Raw Event Data: Example 2
• Examining Raw Event Data: Example 3
• Event Hierarchy
• Nested Events
• Querying Nested Events
• Summary Tables
• Prepared Statements
• Retrieving Statement Summary Information: Example
• Retrieving Wait Summary Information: Example
• Retrieving Memory Summary Information: Example
• Topics
• sys Schema
• Setting sys Schema Privileges
• sys Views
• Using sys for Statement Analysis: Example
• Using sys to Identify Queries with Full Table Scans: Example
• Using sys to Identify the Slowest Queries: Example
• Using sys to Identify Unused Indexes: Example
• Using sys as a Non-Blocking SHOW PROCESSLIST: Example
• sys Stored Routines
• Configuring Performance Schema by Using sys: Example
• Using sys Functions to Format Output: Example
• Quiz
• Topics
• MySQL Workbench Performance Dashboard
• MySQL Workbench Performance Reports
• MySQL Workbench Performance Schema Setup
• Summary

General Server Tuning

• Topics
• Objectives
• Topics
• Major Components of the MySQL Server
• Quiz
• MySQL Memory Usage
• Tuning the MySQL Server
• CPU and I/O Saturation
• Global Buffers
• Dictionary Object Cache
• MySQL Thread Handling
• Per-Thread Buffers
• Quiz
• Topics
• Displaying Memory Usage by Using sys
• Topics
• Simultaneous Connections in MySQL
• Administrative Connections in MySQL
• Connection Status Variables
• Monitoring Idle Connections with Performance Schema
• Scenario: Users Unable to Connect
• Diagnosing Network Problems
• Topics
• Reusing Threads
• Quiz
• Other Thread Status Variables
• Scenario: Calculating Thread Cache Effectiveness
• Scenario: Setting thread_cache_size
• The MySQL Enterprise Thread Pool
• Summary

Tuning Tables, Files, and Logs

• Topics
• Objectives
• Topics
• Reusing Tables
• How MySQL Caches Tables
• Setting table_open_cache
• Sizing the Table Cache
• Setting table_definition_cache
• Setting table_open_cache: Scenario 1
• Setting table_open_cache: Scenario 2
• Setting table_open_cache: Scenario 3
• Quiz
• Topics
• Tables and Files
• Managing the Number of Open Files
• Setting File Descriptors in the Operating System
• Quiz
• Topics
• Binary Logs
• ACID and Database Transactions
• Transactions and the Binary Log
• Sizing the Binary Log Caches
• Monitoring Binary Logs
• Binary Log Group Commit Settings
• Improving Binary Log Performance
• Scenario: Binary Log File Cache Effectiveness
• Quiz
• Summary

Tuning InnoDB

• Topics
• Objectives
• Topics
• InnoDB Storage Engine
• Topics
• Operating System Buffers
• How InnoDB Reads Data
• How InnoDB Writes Data
• Topics
• Sizing the InnoDB Buffer Pool
• Dumping and Restoring the Buffer Pool
• Sizing the InnoDB Redo Log Files
• Other InnoDB Redo Log Settings
• Disabling Redo Logging
• InnoDB Tablespace Settings
• Thread Concurrency
• InnoDB Buffer and Log File Flushing
• Adaptive Flushing
• Purge Behavior
• Undo Logs
• Recommended InnoDB Settings for OLTP and Benchmarking
• Recommended InnoDB Settings for Replication
• Linux Filesystem Recommendations
• Topics
• InnoDB Support in the Information Schema
• Information Schema InnoDB Metrics
• Example: Enabling InnoDB Monitoring
• Example: Displaying InnoDB Metrics
• Example: Resetting InnoDB Counters and Disabling Monitoring
• InnoDB Support in the Performance Schema
• Monitoring InnoDB Performance in MySQL Workbench
• SHOW ENGINE INNODB STATUS
• SHOW ENGINE INNODB STATUS: Semaphores and Transactions
• SHOW ENGINE INNODB STATUS: File I/O
• SHOW ENGINE INNODB STATUS: Insert Buffer and Adaptive Hash Index
• SHOW ENGINE INNODB STATUS: Log
• SHOW ENGINE INNODB STATUS: Buffer Pool and Memory
• SHOW ENGINE INNODB STATUS: Row Operations
• SHOW ENGINE INNODB STATUS: Latest Foreign Key Error
• SHOW ENGINE INNODB STATUS: Latest Detected Deadlock
• Quiz
• Summary

Optimizing Your Schema

• Topics
• Objectives
• Topics
• Schema Design Considerations
• Schema Design Tasks
• Normalization and Performance
• Denormalizing Specific Data for Performance
• Quiz
• Data Types
• Topics
• Indexes
• Indexing
• Optimizing Indexes
• Index Types
• B+TREE Index
• Quiz
• Topics
• InnoDB Table Compression
• Tuning Compression for InnoDB Tables
• Topics
• Partitioning
• Partitioning Types
• RANGE Partitioning: Example
• LIST Partitioning: Example
• HASH Partitioning: Example
• KEY Partitioning: Example
• Partitioning and Performance
• Partitioning Limitations
• Retrieving Partition Information
• Quiz
• Summary

Monitoring Queries

• Topics
• Objectives
• Topics
• Query Monitoring
• Identifying Queries That Require Optimization
• Identifying Frequent Queries
• Topics
• General Query Log
• Slow Query Log
• Using mysqldumpslow to View Slow Query Log Entries
• mysqldumpslow Output: Example
• Topics
• General Statement Status Variables
• SELECT Statement Status Variables
• Quiz
• Topics
• Finding Slow Queries with Performance Schema
• Example: Querying events_statements_current
• Example: Querying events_statements_summary_by_digest
• Identifying Slow Queries with the sys Schema
• Investigating Aggregated Statement Metrics with the sys.statement_analysis View
• Topics
• MySQL Enterprise Monitor Query Analyzer
• Query Analyzer Tab
• Query Analyzer Detailed Query View
• MySQL Workbench Query Statistics
• MySQL Workbench Visual EXPLAIN
• Summary

Query Optimization

• Topics
• Objectives
• Topics
• MySQL Query Processing
• Optimizer Main Stages
• Logical Transformations
• Example: Logical Transformation
• Cost-Based Optimization
• Cost Model Inputs
• Access Method
• Join Order
• Subquery Optimizations
• Plan Refinement
• Index Condition Pushdown Optimization
• Quiz
• Topics
• Understanding the Query Plan
• Example: EXPLAIN Output
• EXPLAIN Output
• EXPLAIN Output: select_type Column
• EXPLAIN Output: type Column
• EXPLAIN Output: key Column and Index Hints
• EXPLAIN Output: Extra Column
• EXPLAIN Output: Extra column
• Structured EXPLAIN
• Example: Standard EXPLAIN
• Example: EXPLAIN FORMAT=JSON
• Visual EXPLAIN in MySQL Workbench
• Tree Structured EXPLAIN
• EXPLAIN ANALYZE
• Quiz
• Optimizer Trace
• Topics
• Improving Query Performance
• Data Retrieval: Best Practices
• Filtering Data with WHERE
• Indexing for Query Performance
• Query Using WHERE Clause
• Query Using Covering Index
• Query Using Index Condition Pushdown
• Costs of Indexing
• Indexing: Best Practices
• Leftmost Prefixes
• Improving the Performance of SELECT Statements
• Optimizing Table Joins
• Optimizing Sorting Operations
• Order of Sort Operations
• Optimizing Bulk DML Statements
• Creating Summary Tables
• Quiz
• Summary

Optimizing Locking Operations

• Topics
• Objectives
• Topics
• Locks in MySQL
• Implicit Locks
• Explicit Locks
• Avoiding Locks with InnoDB Multiversion Concurrency Control
• Topics
• InnoDB Table Locks
• InnoDB Row Locks
• Topics
• Metadata Locks
• Metadata Lock Example
• Topics
• Identifying Locks in the Process List
• Example: Table Lock Information in SHOW PROCESSLIST
• Displaying InnoDB Lock Information with SHOW ENGINE INNODB STATUS
• Topics
• Viewing Metadata Lock Information
• Interpreting LOCK_STATUS
• Example: Querying the metadata_locks Table
• Viewing Table Lock Information
• Example: Querying the table_handles Table
• Viewing Table Lock Information in sys.innodb_lock_waits
• Quiz
• Summary

Tuning Replication

• Topics
• Objectives
• Topics
• MySQL Replication
• Replication Use Cases
• Replication Logs
• Replication Log Events
• Role of the Source Server
• Role of the Replica Server
• Topics
• What Is Replication Lag?
• Common Causes of Replication Lag
• Quiz
• Topics
• Diagnosing Replication Lag
• Binary Log File Name and Position of the Source Server
• Binary Log File and Position of the Replica Server
• Comparing Binary Log File Name and Position
• Timing Replication Lag
• Comparing GTID Sets
• Executed GTIDs
• Retrieved GTIDs
• Manipulating GTIDs
• Example: I/O Thread Lag
• Example: SQL Thread Lag
• Example: SQL Thread Lagging Behind I/O Thread
• Performance Schema Replication Table Hierarchy
• Performance Schema Replication Tables
• Replication Support in MySQL Enterprise Monitor
• Quiz
• Topics
• Resolving I/O Thread Lag
• Resolving SQL Thread Lag
• Summary

Conclusion

• Course Goals
• Oracle University: MySQL Training
• MySQL Websites
• Your Evaluation
• Thank You
• Q&A Session