High Performance MySQL: Proven Strategies for Running MySQL at Scale

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