How can you realize MySQL’s full power? With High Performance MySQL, you’ll learn advanced techniques for everything from choosing the right abstraction layer for databases to designing schemas, indexes, and queries to tuning your server, operating system, and hardware to achieve their 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, features, and tools, this revised edition helps you design a data platform that will scale with your business. You’ll learn the latest in cloud-hosted MySQL offerings, best practices for database security, and 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
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 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 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 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 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 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 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 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 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 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 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 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 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 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 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 About the Authors
How to download source code?
1. Go to:
2. Search the book title:
High Performance MySQL, 4th Edition, sometime you may not get the results, please search the main title
3. Click the book title in the search results
Publisher resources section, click
Download Example Code.
1. Disable the AdBlock plugin. Otherwise, you may not get any links.
2. Solve the CAPTCHA.
3. Click download link.
4. Lead to download server to download.