High Performance MySQL. 4th Edition (E-book) Racibórz

How can you realize MySQL's full power? With High Performance MySQL, you'll learn advanced techniques for everything from setting service-level objectives to designing schemas, indexes, and queries to tuning your server, operating system, and hardware to achieve your platform's 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, and new features and tools, this revised edition helps you design a relational data platform that will scale with your business. You'll learn best practices for database security along with hard-earned lessons in both performance and database stability.Dive into MySQL's architecture, including key facts about its storage enginesLearn how server configuration works with your hardware and deployment choicesMake query performance part of your software delivery processExamine enhancements to MySQL's replication and high availabilityCompare different MySQL offerings in managed cloud environmentsExplore MySQL's full stack optimization from application-side configuration to server tuningTurn traditional database management tasks into automated processes Spis treści: 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