High Performance MySQL. 4th Edition

Spis treści książki

• Foreword
• Preface
  • Who This Book Is For
  • What Is Different in This Edition
  • Conventions Used in This Book
  • OReilly Online Learning
  • How to Contact Us
  • Acknowledgments for the Fourth Edition
    • From Silvia
    • From Jeremy
    • A Thank You to Tech Reviewers
• 1. MySQL Architecture
  • MySQLs Logical Architecture
    • Connection Management and Security
    • Optimization and Execution
  • Concurrency Control
    • Read/Write Locks
    • Lock Granularity
      • Table locks
      • Row locks
  • Transactions
    • Isolation Levels
    • Deadlocks
    • Transaction Logging
    • Transactions in MySQL
      • Understanding AUTOCOMMIT
      • Mixing storage engines in transactions
      • Implicit and explicit locking
  • Multiversion Concurrency Control
  • Replication
  • Datafiles Structure
  • The InnoDB Engine
    • JSON Document Support
    • Data Dictionary Changes
    • Atomic DDL
  • Summary
• 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
      • Commercial options
      • Open source options
    • Monitoring Availability
    • Monitoring Query Latency
    • Monitoring for Errors
    • Proactive Monitoring
      • Disk growth
      • Connection growth
      • Replication lag
      • I/O utilization
      • Auto-increment space
      • Backup creation/restore time
  • Measuring Long-Term Performance
    • Learning Your Business Cadence
    • Tracking Your Metrics Effectively
    • Using Monitoring Tools to Inspect the Performance
      • Say no to averages
      • Percentiles are your friend
      • Long retention period and performance
    • Using SLOs to Guide Your Overall Architecture
  • Summary
• 3. Performance Schema
  • Introduction to Performance Schema
    • Instrument Elements
    • Consumer Organization
      • Current and historical data
      • Summary tables and digests
      • Instances
      • Setup
      • Other tables
    • Resource Consumption
    • Limitations
    • sys Schema
    • Understanding Threads
  • Configuration
    • Enabling and Disabling Performance Schema
    • Enabling and Disabling Instruments
      • UPDATE statement
      • sys stored procedure
      • Startup options
    • 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
      • Regular SQL statements
        • Using performance_schema directly
        • Using sys schema
      • Prepared statements
      • Stored routines
      • Statements profiling
    • Examining Read Versus Write Performance
    • Examining Metadata Locks
    • Examining Memory Usage
      • Using performance_schema directly
      • Using sys schema
    • Examining Variables
    • Examining Most Frequent Errors
    • Examining Performance Schema Itself
  • Summary
• 4. Operating System and Hardware Optimization
  • What Limits MySQLs Performance?
  • How to Select CPUs for MySQL
  • Balancing Memory and Disk Resources
    • Caching, Reads, and Writes
    • Whats 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
      • The RAID stripe chunk size
      • The RAID cache
  • Network Configuration
  • Choosing a Filesystem
    • Choosing a Disk Queue Scheduler
    • Memory and Swapping
    • Operating System Status
      • How to read vmstat output
      • How to read iostat output
    • Other Helpful Tools
  • Summary
• 5. Optimizing Server Settings
  • How MySQLs 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 MySQLs I/O Behavior
    • The InnoDB Transaction Log
    • Log Buffer
      • How InnoDB flushes the log buffer
      • How InnoDB opens and flushes logfiles and datafiles
    • The InnoDB Tablespace
      • Configuring the tablespace
      • Old row versions and the tablespace
    • Other I/O Configuration Options
  • Configuring MySQL Concurrency
  • Safety Settings
  • Advanced InnoDB Settings
  • Summary
• 6. Schema Design and Management
  • Choosing Optimal Data Types
    • Whole Numbers
    • Real Numbers
    • String Types
      • VARCHAR and CHAR types
      • BLOB and TEXT types
      • Using ENUM instead of a string type
    • Date and Time Types
    • Bit-Packed Data Types
    • JSON Data
    • Choosing Identifiers
      • Integer types
      • ENUM and SET
      • String types
    • 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
      • Set up your partner teams for success
      • Integrate schema management with continuous integration
      • Source control for schema changes
        • Paid options
        • Using open source
      • Running schema changes in production
        • Native DDL statements
        • Using external tools to run your schema change
      • A CI/CD pipeline for schema changes
  • Summary
• 7. Indexing for High Performance
  • Indexing Basics
    • Types of Indexes
      • B-tree indexes
        • Adaptive hash index
        • Types of queries that can use a B-tree index
      • Full-text indexes
    • Benefits of Indexes
  • Indexing Strategies for High Performance
    • Prefix Indexes and Index Selectivity
    • Multicolumn Indexes
    • Choosing a Good Column Order
    • Clustered Indexes
      • InnoDBs data layout
      • Inserting rows in primary key order with InnoDB
    • 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
• 8. Query Performance Optimization
  • Why Are Queries Slow?
  • Slow Query Basics: Optimize Data Access
    • Are You Asking the Database for Data You Dont Need?
    • Is MySQL Examining Too Much Data?
      • Response time
      • Rows examined and rows returned
      • Rows examined and access types
  • 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 parser and the preprocessor
      • The query optimizer
      • Table and index statistics
      • MySQLs join execution strategy
      • The execution plan
      • The join optimizer
      • Sort optimizations
    • 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
      • What COUNT() does
      • Simple optimizations
      • Using an approximation
      • More complex optimizations
    • Optimizing JOIN Queries
    • Optimizing GROUP BY with ROLLUP
    • Optimizing LIMIT and OFFSET
    • Optimizing SQL_CALC_FOUND_ROWS
    • Optimizing UNION
  • Summary
• 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
      • Configuration
      • Redundancy
      • Caveats
    • Active/Read Pool
      • Configuration
      • Redundancy
      • Caveats
    • Discouraged Topologies
      • Dual source in active-active mode
      • Dual source in active-passive mode
      • Dual sources with replicas
      • Ring replication
      • Multisource replication
  • 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
• 10. Backup and Recovery
  • Why Backups?
  • Defining Recovery Requirements
  • Designing a MySQL Backup Solution
    • Online or Offline Backups?
    • Logical or Raw Backups?
      • Logical backups
      • 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
      • How LVM snapshots work
      • Prerequisites and configuration
      • Creating, mounting, and removing an LVM snapshot
      • Lock-free InnoDB backups with LVM snapshots
      • Planning for LVM backups
      • Other uses and alternatives
    • Percona XtraBackup
      • How XtraBackup works
      • Example usage
        • Basic backup to directory
        • Streaming backup
        • Backup with compression
        • Backup with encryption
        • Other important flags
  • Recovering from a Backup
    • Restoring Logical Backups
    • Restoring Raw Files from Snapshot
    • Restoring with Percona XtraBackup
    • Starting MySQL After Restoring Raw Files
  • Summary
• 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
      • Vitess architecture overview
      • Migrating your stack to Vitess
    • ProxySQL
      • ProxySQL architecture overview
      • Configuring ProxySQL
      • Using ProxySQL for sharding
        • Sharding by user
        • Sharding by schema
      • Other benefits of using ProxySQL
      • Other notable features in ProxySQL
  • Summary
• 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
      • CPU
      • Memory
      • Network performance
    • Choosing the Right Disk Type
      • Attachment types
      • SSD versus HDD
      • IOPS and throughput
    • Additional Tips
      • Dealing with host reboots
      • Separate operating system and MySQL data
      • Backing up binary logs
      • Auto-extend your disks
  • Summary
• 13. Compliance with MySQL
  • What Is Compliance?
    • Service Organization Controls Type 2
    • SarbanesOxley 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
      • Do not share users
      • Do not check production database credentials in code repositories
      • Choosing a secret management solution
    • Separation of Roles and Data
      • Sharding for compliance reasons
      • Separate database users
    • Tracking Changes
      • Data access logging
        • Undesirable options for tracking changes
        • Installing and tuning Percona audit logs
        • Ingesting and using audit plug-in logs
      • Version control for schema changes
      • Database user management
        • Use configuration management
        • Plan for credential rotation
        • Retire database users not in use
    • Backup and Restore Procedures
      • Running automated backups and backup tests
      • Centralized logs for backups and backup tests
      • Disaster-recovery planning through backups
  • Summary
• A. Upgrading MySQL
  • Why Upgrade?
  • Upgrade Life Cycle
  • Testing Upgrades
    • Development Environment Testing
    • Production Mirror
    • Replica
    • Tooling
  • Upgrading at Scale
  • Summary
• B. MySQL on Kubernetes
  • Provisioning Resources with Kubernetes
    • Carefully Scope Your Goal
    • Choose Your Control Plane
    • The Finer Details
  • Summary
• Index

pokaż cały spis treści

ukryj recenzje