Efficient MySQL Performance: Best Practices and Technique

Copyright
Table of Contents
Preface
	Conventions Used in This Book
	Using Code Examples
	O’Reilly Online Learning
	How to Contact Us
	Acknowledgments
Chapter 1. Query Response Time
	A True Story of False Performance
	North Star
	Query Reporting
		Sources
		Aggregation
		Reporting
	Query Analysis
		Query Metrics
		Metadata and the Application
		Relative Values
		Average, Percentile, and Maximum
	Improving Query Response Time
		Direct Query Optimization
		Indirect Query Optimization
	When to Optimize Queries
		Performance Affects Customers
		Before and After Code Changes
		Once a Month
	MySQL: Go Faster
	Summary
	Practice: Identify Slow Queries
Chapter 2. Indexes and Indexing
	Red Herrings of Performance
		Better, Faster Hardware!
		MySQL Tuning
	MySQL Indexes: A Visual Introduction
		InnoDB Tables Are Indexes
		Table Access Methods
		Leftmost Prefix Requirement
		EXPLAIN: Query Execution Plan
		WHERE
		GROUP BY
		ORDER BY
		Covering Indexes
		Join Tables
	Indexing: How to Think Like MySQL
		Know the Query
		Understand with EXPLAIN
		Optimize the Query
		Deploy and Verify
	It Was a Good Index Until…
		Queries Changed
		Excessive, Duplicate, and Unused
		Extreme Selectivity
		It’s a Trap! (When MySQL Chooses Another Index)
	Table Join Algorithms
	Summary
	Practice: Find Duplicate Indexes
Chapter 3. Data
	Three Secrets
		Indexes May Not Help
		Less Data Is Better
		Less QPS Is Better
	Principle of Least Data
		Data Access
		Data Storage
	Delete or Archive Data
		Tools
		Batch Size
		Row Lock Contention
		Space and Time
		The Binary Log Paradox
	Summary
	Practice: Audit Query Data Access
Chapter 4. Access Patterns
	MySQL Does Nothing
	Performance Destabilizes at the Limit
	Toyota and Ferrari
	Data Access Patterns
		Read/Write
		Throughput
		Data Age
		Data Model
		Transaction Isolation
		Read Consistency
		Concurrency
		Row Access
		Result Set
	Application Changes
		Audit the Code
		Offload Reads
		Enqueue Writes
		Partition Data
		Don’t Use MySQL
	Better, Faster Hardware?
	Summary
	Practice: Describe an Access Pattern
Chapter 5. Sharding
	Why a Single Database Does Not Scale
		Application Workload
		Benchmarks Are Synthetic
		Writes
		Schema Changes
		Operations
	Pebbles, Not Boulders
	Sharding: A Brief Introduction
		Shard Key
		Strategies
		Challenges
	Alternatives
		NewSQL
		Middleware
		Microservices
		Don’t Use MySQL
	Summary
	Practice: Four-Year Fit
Chapter 6. Server Metrics
	Query Performance Versus Server Performance
	Normal and Stable: The Best Database Is a Boring Database
	Key Performance Indicators
	Field of Metrics
		Response Time
		Rate
		Utilization
		Wait
		Error
		Access Pattern
		Internal
	Spectra
		Query Response Time
		Errors
		Queries
		Threads and Connections
		Temporary Objects
		Prepared Statements
		Bad SELECT
		Network Throughput
		Replication
		Data Size
		InnoDB
	Monitoring and Alerting
		Resolution
		Wild Goose Chase (Thresholds)
		Alert on User Experience and Objective Limits
		Cause and Effect
	Summary
	Practice: Review Key Performance Indicators
	Practice: Review Alerts and Thresholds
Chapter 7. Replication Lag
	Foundation
		Source to Replica
		Binary Log Events
		Replication Lag
	Causes
		Transaction Throughput
		Post-Failure Rebuild
		Network Issues
	Risk: Data Loss
		Asynchronous Replication
		Semisynchronous Replication
	Reducing Lag: Multithreaded Replication
	Monitoring
	Recovery Time
	Summary
	Practice: Monitor Subsecond Lag
Chapter 8. Transactions
	Row Locking
		Record and Next-Key Locks
		Gap Locks
		Secondary Indexes
		Insert Intention Locks
	MVCC and the Undo Logs
	History List Length
	Common Problems
		Large Transactions (Transaction Size)
		Long-Running Transactions
		Stalled Transactions
		Abandoned Transactions
	Reporting
		Active Transactions: Latest
		Active Transactions: Summary
		Active Transaction: History
		Committed Transactions: Summary
	Summary
	Practice: Alert on History List Length
	Practice: Examine Row Locks
Chapter 9. Other Challenges
	Split-Brain Is the Greatest Risk
	Data Drift Is Real but Invisible
	Don’t Trust ORM
	Schemas Always Change
	MySQL Extends Standard SQL
	Noisy Neighbors
	Applications Do Not Fail Gracefully
	High Performance MySQL Is Difficult
	Practice: Identify the Guardrails that Prevent Split-Brain
	Practice: Check for Data Drift
	Practice: Chaos
Chapter 10. MySQL in the Cloud
	Compatibility
	Management (DBA)
	Network and Storage…Latency
	Performance Is Money
	Summary
	Practice: Try MySQL in the Cloud
Index
About the Author
Colophon