دانلود کتاب 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