High Performance MySQL, 4th Edition

High Performance MySQL, 4th Edition

by Silvia Botros, Jeremy Tinley
Released November 2021
Publisher(s): O'Reilly Media, Inc.
ISBN: 9781492080510

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

How can you realize MySQL's full power? With High Performance MySQL, you'll learn advanced techniques for everything from setting service-level objectives to designing schemas, indexes, and queries to tuning your server, operating system, and hardware to achieve your platform's full potential. This guide also teaches database administrators safe and practical ways to scale applications through replication, load balancing, high availability, and failover.

Updated to reflect recent advances in cloud- and self-hosted MySQL, InnoDB performance, and new features and tools, this revised edition helps you design a relational data platform that will scale with your business. You'll learn best practices for database security along with hard-earned lessons in both performance and database stability.

  • Dive into MySQL's architecture, including key facts about its storage engines
  • Learn how server configuration works with your hardware and deployment choices
  • Make query performance part of your software delivery process
  • Examine enhancements to MySQL's replication and high availability
  • Compare different MySQL offerings in managed cloud environments
  • Explore MySQL's full stack optimization from application-side configuration to server tuning
  • Turn traditional database management tasks into automated processes

Publisher resources

View/Submit Errata

Table of contents

  1. Foreword
  2. Preface
    1. Who This Book Is For
    2. What Is Different in This Edition
    3. Conventions Used in This Book
    4. O’Reilly Online Learning
    5. How to Contact Us
    6. Acknowledgments for the Fourth Edition
      1. From Silvia
      2. From Jeremy
      3. A Thank You to Tech Reviewers
  3. 1. MySQL Architecture
    1. MySQL’s Logical Architecture
      1. Connection Management and Security
      2. Optimization and Execution
    2. Concurrency Control
      1. Read/Write Locks
      2. Lock Granularity
    3. Transactions
      1. Isolation Levels
      2. Deadlocks
      3. Transaction Logging
      4. Transactions in MySQL
    4. Multiversion Concurrency Control
    5. Replication
    6. Datafiles Structure
    7. The InnoDB Engine
      1. JSON Document Support
      2. Data Dictionary Changes
      3. Atomic DDL
    8. Summary
  4. 2. Monitoring in a Reliability Engineering World
    1. The Impact of Reliability Engineering on DBA Teams
    2. Defining Service Level Goals
      1. What Does It Take to Make Customers Happy?
    3. What to Measure
      1. Defining SLIs and SLOs
      2. Monitoring Solutions
      3. Monitoring Availability
      4. Monitoring Query Latency
      5. Monitoring for Errors
      6. Proactive Monitoring
    4. Measuring Long-Term Performance
      1. Learning Your Business Cadence
      2. Tracking Your Metrics Effectively
      3. Using Monitoring Tools to Inspect the Performance
      4. Using SLOs to Guide Your Overall Architecture
    5. Summary
  5. 3. Performance Schema
    1. Introduction to Performance Schema
      1. Instrument Elements
      2. Consumer Organization
      3. Resource Consumption
      4. Limitations
      5. sys Schema
      6. Understanding Threads
    2. Configuration
      1. Enabling and Disabling Performance Schema
      2. Enabling and Disabling Instruments
      3. Enabling and Disabling Consumers
      4. Tuning Monitoring for Specific Objects
      5. Tuning Threads Monitoring
      6. Adjusting Memory Size for Performance Schema
      7. Defaults
    3. Using Performance Schema
      1. Examining SQL Statements
      2. Examining Read Versus Write Performance
      3. Examining Metadata Locks
      4. Examining Memory Usage
      5. Examining Variables
      6. Examining Most Frequent Errors
      7. Examining Performance Schema Itself
    4. Summary
  6. 4. Operating System and Hardware Optimization
    1. What Limits MySQL’s Performance?
    2. How to Select CPUs for MySQL
    3. Balancing Memory and Disk Resources
      1. Caching, Reads, and Writes
      2. What’s Your Working Set?
    4. Solid-State Storage
      1. An Overview of Flash Memory
      2. Garbage Collection
    5. RAID Performance Optimization
      1. RAID Failure, Recovery, and Monitoring
      2. RAID Configuration and Caching
    6. Network Configuration
    7. Choosing a Filesystem
      1. Choosing a Disk Queue Scheduler
      2. Memory and Swapping
      3. Operating System Status
      4. Other Helpful Tools
    8. Summary
  7. 5. Optimizing Server Settings
    1. How MySQL’s Configuration Works
      1. Syntax, Scope, and Dynamism
      2. Persisted System Variables
      3. Side Effects of Setting Variables
      4. Planning Your Variable Changes
    2. What Not to Do
    3. Creating a MySQL Configuration File
      1. Minimal Configuration
      2. Inspecting MySQL Server Status Variables
    4. Configuring Memory Usage
      1. Per-Connection Memory Needs
      2. Reserving Memory for the Operating System
      3. The InnoDB Buffer Pool
      4. The Thread Cache
    5. Configuring MySQL’s I/O Behavior
      1. The InnoDB Transaction Log
      2. Log Buffer
      3. The InnoDB Tablespace
      4. Other I/O Configuration Options
    6. Configuring MySQL Concurrency
    7. Safety Settings
    8. Advanced InnoDB Settings
    9. Summary
  8. 6. Schema Design and Management
    1. Choosing Optimal Data Types
      1. Whole Numbers
      2. Real Numbers
      3. String Types
      4. Date and Time Types
      5. Bit-Packed Data Types
      6. JSON Data
      7. Choosing Identifiers
      8. Special Types of Data
    2. Schema Design Gotchas in MySQL
      1. Too Many Columns
      2. Too Many Joins
      3. The All-Powerful ENUM
      4. The ENUM in Disguise
      5. NULL Not Invented Here
    3. Schema Management
      1. Schema Management as Part of the Data Store Platform
    4. Summary
  9. 7. Indexing for High Performance
    1. Indexing Basics
      1. Types of Indexes
      2. Benefits of Indexes
    2. Indexing Strategies for High Performance
      1. Prefix Indexes and Index Selectivity
      2. Multicolumn Indexes
      3. Choosing a Good Column Order
      4. Clustered Indexes
      5. Covering Indexes
      6. Using Index Scans for Sorts
      7. Redundant and Duplicate Indexes
      8. Unused Indexes
    3. Index and Table Maintenance
      1. Finding and Repairing Table Corruption
      2. Updating Index Statistics
      3. Reducing Index and Data Fragmentation
    4. Summary
  10. 8. Query Performance Optimization
    1. Why Are Queries Slow?
    2. Slow Query Basics: Optimize Data Access
      1. Are You Asking the Database for Data You Don’t Need?
      2. Is MySQL Examining Too Much Data?
    3. Ways to Restructure Queries
      1. Complex Queries Versus Many Queries
      2. Chopping Up a Query
      3. Join Decomposition
    4. Query Execution Basics
      1. The MySQL Client/Server Protocol
      2. Query States
      3. The Query Optimization Process
      4. The Query Execution Engine
      5. Returning Results to the Client
    5. Limitations of the MySQL Query Optimizer
      1. UNION Limitations
      2. Equality Propagation
      3. Parallel Execution
      4. SELECT and UPDATE on the Same Table
    6. Optimizing Specific Types of Queries
      1. Optimizing COUNT() Queries
      2. Optimizing JOIN Queries
      3. Optimizing GROUP BY with ROLLUP
      4. Optimizing LIMIT and OFFSET
      5. Optimizing SQL_CALC_FOUND_ROWS
      6. Optimizing UNION
    7. Summary
  11. 9. Replication
    1. Replication Overview
      1. How Replication Works
    2. Replication Under the Hood
      1. Choosing Replication Format
      2. Global Transaction Identifiers
      3. Making Replication Crash Safe
      4. Delayed Replication
      5. Multithreaded Replication
      6. Semisynchronous Replication
      7. Replication Filters
    3. Replication Failover
      1. Planned Promotions
      2. Unplanned Promotions
      3. Trade-Offs of Promotion
    4. Replication Topologies
      1. Active/Passive
      2. Active/Read Pool
      3. Discouraged Topologies
    5. Replication Administration and Maintenance
      1. Monitoring Replication
      2. Measuring Replication Lag
      3. Determining Whether Replicas Are Consistent with the Source
    6. Replication Problems and Solutions
      1. Binary Logs Corrupted on the Source
      2. Nonunique Server IDs
      3. Undefined Server IDs
      4. Missing Temporary Tables
      5. Not Replicating All Updates
      6. Excessive Replication Lag
      7. Oversized Packets from the Source
      8. No Disk Space
      9. Replication Limitations
    7. Summary
  12. 10. Backup and Recovery
    1. Why Backups?
    2. Defining Recovery Requirements
    3. Designing a MySQL Backup Solution
      1. Online or Offline Backups?
      2. Logical or Raw Backups?
      3. What to Back Up
      4. Incremental and Differential Backups
      5. Replication
    4. Managing and Backing Up Binary Logs
    5. Backup and Recovery Tools
      1. MySQL Enterprise Backup
      2. Percona XtraBackup
      3. mydumper
      4. mysqldump
    6. Backing Up Data
      1. Logical SQL Backups
      2. Filesystem Snapshots
      3. Percona XtraBackup
    7. Recovering from a Backup
      1. Restoring Logical Backups
      2. Restoring Raw Files from Snapshot
      3. Restoring with Percona XtraBackup
      4. Starting MySQL After Restoring Raw Files
    8. Summary
  13. 11. Scaling MySQL
    1. What Is Scaling?
    2. Read- Versus Write-Bound Workloads
      1. Understanding Your Workload
      2. Read-Bound Workloads
      3. Write-Bound Workloads
    3. Functional Sharding
    4. Scaling Reads with Read Pools
      1. Managing Configuration for Read Pools
      2. Health Checks for Read Pools
      3. Choosing a Load-Balancing Algorithm
    5. Queuing
    6. Scaling Writes with Sharding
      1. Choosing a Partitioning Scheme
      2. Multiple Partitioning Keys
      3. Querying Across Shards
      4. Vitess
      5. ProxySQL
    7. Summary
  14. 12. MySQL in the Cloud
    1. Managed MySQL
      1. Amazon Aurora for MySQL
      2. GCP Cloud SQL
    2. MySQL on Virtual Machines
      1. Machine Types in Cloud
      2. Choosing the Right Machine Type
      3. Choosing the Right Disk Type
      4. Additional Tips
    3. Summary
  15. 13. Compliance with MySQL
    1. What Is Compliance?
      1. Service Organization Controls Type 2
      2. Sarbanes–Oxley Act
      3. Payment Card Industry Data Security Standard
      4. Health Insurance Portability and Accountability Act
      5. Federal Risk and Authorization Management Program
      6. General Data Protection Regulation
      7. Schrems II
    2. Building for Compliance Controls
      1. Secrets Management
      2. Separation of Roles and Data
      3. Tracking Changes
      4. Backup and Restore Procedures
    3. Summary
  16. A. Upgrading MySQL
    1. Why Upgrade?
    2. Upgrade Life Cycle
    3. Testing Upgrades
      1. Development Environment Testing
      2. Production Mirror
      3. Replica
      4. Tooling
    4. Upgrading at Scale
    5. Summary
  17. B. MySQL on Kubernetes
    1. Provisioning Resources with Kubernetes
      1. Carefully Scope Your Goal
      2. Choose Your Control Plane
      3. The Finer Details
    2. Summary
  18. Index
  19. About the Authors

Product information

  • Title: High Performance MySQL, 4th Edition
  • Author(s): Silvia Botros, Jeremy Tinley
  • Release date: November 2021
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9781492080510