MySQL 8.0: Performance Tuning

Asseco Academy » Training » MySQL 8.0: Performance Tuning

Training OR_MYSQL8_PT_Z

MySQL 8.0: Performance Tuning

IT Solutions Database Administration Data, Analytics, AI and ML MySQL Oracle

Accreditation

Training description

The MySQL 8.0: Performance Tuning course provides database administrators and IT professionals with the knowledge and tools necessary to optimize MySQL database performance. Participants will learn how to identify performance bottlenecks, tune server configuration, optimize queries, and efficiently manage system resources.

The course covers essential tuning concepts, best practices for schema and index design, performance monitoring, and advanced configuration techniques. Attendees will also gain hands-on experience using MySQL Performance Schema, sys Schema, and MySQL Enterprise Monitor to diagnose and resolve performance issues.

Benefits:

Understand fundamental performance tuning concepts and how MySQL processes queries.
Benchmark MySQL server performance and identify areas for improvement.
Use diagnostic tools such as Performance Schema and sys Schema to analyze server activity.
Optimize queries, indexing strategies, and execution plans to reduce query response times.
Fine-tune MySQL server parameters to enhance memory, CPU, and disk performance.
Implement replication and high availability configurations optimized for performance.
Monitor and troubleshoot MySQL workloads using command-line and graphical tools.

Target audience

Database Administrators (DBA)
System Administrators
Cloud Database Administrators
DevOps Engineers
Technical Support Engineers

Prerequisites:

Experience with MySQL administration and SQL query optimization.
Basic knowledge of MySQL server configuration and database structures.
Familiarity with Linux command-line utilities.

Scope

Performance Tuning Concepts
- Improving Performance
- Areas to Tune
- Performance Tuning Terminology
- Response Time
- Measuring Response Times
- Throughput
- Scalability
- Queuing Theory
- Benchmarking
- Benchmarking Best Practices
- More Benchmarking Best Practices
- Benchmarking Bad Practices
- More Benchmarking Bad Practices
- 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
- General Tuning Procedure
Performance Tuning Tools
- 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
- 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
- Oracle Enterprise Manager for MySQL
- Oracle SQL Developer
- Community Monitoring Tools
- Linux Tools
- iostat
- vmstat
- sar
- top
- Benchmarking Tools
- MySQL BENCHMARK() Function
- Stress Tools
- mysqlslap Stress Tool
- sysbench
- Using SysBench
- Sample SysBench Output
Performance Schema
- Why Use Performance Schema?
- How Performance Schema Works
- Instrument Names
- Instrument Prefixes
- Instrument Suffixes
- Structure of the Performance Schema
- Configuring the Performance Schema
- Setup Tables
- Configuring Instruments
- Configuring Consumers
- Consumer Table Hierarchy
- Configuring Objects
- Configuring Actors
- Configuring Thread Monitoring
- Configuring Performance Schema System Variables
- Performance Schema Overhead
- How Performance Schema Uses Memory
- Instance Tables
- cond_instances Table
- file_instances Table
- mutex_instances Table
- rwlock_instances Table
- socket_instances Table
- Connection Tables
- Examining Raw Event Data
- Event Hierarchy
- Nested Events
- Querying Nested Events
- Summary Tables
- Prepared Statements
- Retrieving Statement Summary Information
- Retrieving Wait Summary Information
- Retrieving Memory Summary Information
- sys Schema
- Setting sys Schema Privileges
- sys Views
- Using sys for Statement Analysis
- Using sys to Identify Queries with Full Table Scans
- Using sys to Identify the Slowest Queries
- Using sys to Identify Unused Indexes
- Using sys as a Non-Blocking SHOW PROCESSLIST
- sys Stored Routines
- Configuring Performance Schema by Using sys
- Using sys Functions to Format Output
- MySQL Workbench Performance Dashboard
- MySQL Workbench Performance Reports
- MySQL Workbench Performance Schema Setup

General Server Tuning
- Major Components of the MySQL Server
- MySQL Memory Usage
- Tuning the MySQL Server
- CPU and I/O Saturation
- Global Buffers
- Dictionary Object Cache
- MySQL Thread Handling
- Per-Thread Buffers
- Displaying Memory Usage by Using sys
- 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
- Reusing Threads
- Other Thread Status Variables
- Scenario: Calculating Thread Cache Effectiveness
- Scenario: Setting thread_cache_size
- The MySQL Enterprise Thread Pool
Tuning Tables, Files, and Logs
- 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
- Tables and Files
- Managing the Number of Open Files
- Setting File Descriptors in the Operating System
- 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
Tuning InnoDB
- InnoDB Storage Engine
- Operating System Buffers
- How InnoDB Reads Data
- How InnoDB Writes Data
- 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
- 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

Optimizing Your Schema
- Schema Design Considerations
- Schema Design Tasks
- Normalization and Performance
- Denormalizing Specific Data for Performance
- Data Types
- Indexes
- Indexing
- Optimizing Indexes
- Index Types
- B+TREE Index
- InnoDB Table Compression
- Tuning Compression for InnoDB Tables
- Partitioning
- Partitioning Types
- RANGE Partitioning: Example
- LIST Partitioning: Example
- HASH Partitioning: Example
- KEY Partitioning: Example
- Partitioning and Performance
- Partitioning Limitations
- Retrieving Partition Information
Monitoring Queries
- Query Monitoring
- Identifying Queries That Require Optimization
- Identifying Frequent Queries
- General Query Log
- Slow Query Log
- Using mysqldumpslow to View Slow Query Log Entries
- mysqldumpslow Output: Example
- General Statement Status Variables
- SELECT Statement Status Variables
- 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 sys.statement_analysis View
- MySQL Enterprise Monitor Query Analyzer
- Query Analyzer Tab
- Query Analyzer Detailed Query View
- MySQL Workbench Query Statistics
- MySQL Workbench Visual EXPLAIN
Query Optimization
- 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
- 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
- Structured EXPLAIN
- Example: Standard EXPLAIN
- Example: EXPLAIN FORMAT=JSON
- Visual EXPLAIN in MySQL Workbench
- Tree Structured EXPLAIN
- EXPLAIN ANALYZE
- Optimizer Trace
- 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
Optimizing Locking Operations
- Locks in MySQL
- Implicit Locks
- Explicit Locks
- Avoiding Locks with InnoDB Multiversion Concurrency Control
- InnoDB Table Locks
- InnoDB Row Locks
- Metadata Locks
- Metadata Lock Example
- Identifying Locks in the Process List
- Example: Table Lock Information in SHOW PROCESSLIST
- Displaying InnoDB Lock Information with SHOW ENGINE INNODB STATUS
- 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
Tuning Replication
- MySQL Replication
- Replication Use Cases
- Replication Logs
- Replication Log Events
- Role of the Source Server
- Role of the Replica Server
- What Is Replication Lag?
- Common Causes of Replication Lag
- 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
- Resolving I/O Thread Lag
- Resolving SQL Thread Lag

Training schedule

Remote

… if you value the opportunity to learn with a trainer and other participants, from anywhere without having to commute.

Stationary

… if you want to learn while sitting in the same room with the trainer and other participants.

Online

… if you want to learn from materials available via the interenet anywhere at any time

Filters:

Date guarantee

location:

Training: OR_MYSQL8_PT_Z

MySQL 8.0: Performance Tuning

Currently, there is no trainings in the selected form.

Filters:

Date guarantee

location:

Training: OR_MYSQL8_PT_Z

MySQL 8.0: Performance Tuning

Currently, there is no trainings in the selected form.

Filters:

Date guarantee

form:

Training: OR_MYSQL8_PT_Z

MySQL 8.0: Performance Tuning

Currently, there is no trainings in the selected form.

Szukasz szkolenia w innym terminie?

Co nowego?

Newest
Uncategorized

Dołącz do Membership

Cykl online’owych spotkań, podczas których przybliżamy nowe kierunki rozwoju metod zarządzania usługami IT, jak również najciekawsze zagadnienia obszaru programowania, zarządzania Read more

Jak ugryźć innowacyjność

Cykl online’owych spotkań, podczas których przybliżamy nowe kierunki rozwoju metod zarządzania usługami IT, jak również najciekawsze zagadnienia obszaru programowania, zarządzania Read more

Nowoczesne metody supportu – testowy wpis

Cykl online’owych spotkań, podczas których przybliżamy nowe kierunki rozwoju metod zarządzania usługami IT, jak również najciekawsze zagadnienia obszaru programowania, zarządzania Read more

Chcesz z nami porozmawiać?

Zadzwoń do nas: tel. 801 30 30 30