book

High Performance MySQL, 4th Edition

by Silvia Botros, Jeremy Tinley

November 2021

Intermediate to advanced

386 pages

11h 13m

English

O'Reilly Media, Inc.

Book available

Read now

Unlock full access

Who This Book Is ForWhat Is Different in This EditionConventions Used in This BookO’Reilly Online LearningHow to Contact UsAcknowledgments for the Fourth EditionFrom SilviaFrom JeremyA Thank You to Tech Reviewers
MySQL’s Logical ArchitectureConnection Management and SecurityOptimization and ExecutionConcurrency ControlRead/Write LocksLock GranularityTransactionsIsolation LevelsDeadlocksTransaction LoggingTransactions in MySQLMultiversion Concurrency ControlReplicationDatafiles StructureThe InnoDB EngineJSON Document SupportData Dictionary ChangesAtomic DDLSummary
The Impact of Reliability Engineering on DBA TeamsDefining Service Level GoalsWhat Does It Take to Make Customers Happy?What to MeasureDefining SLIs and SLOsMonitoring SolutionsMonitoring AvailabilityMonitoring Query LatencyMonitoring for ErrorsProactive MonitoringMeasuring Long-Term PerformanceLearning Your Business CadenceTracking Your Metrics EffectivelyUsing Monitoring Tools to Inspect the PerformanceUsing SLOs to Guide Your Overall ArchitectureSummary
Introduction to Performance SchemaInstrument ElementsConsumer OrganizationResource ConsumptionLimitationssys SchemaUnderstanding ThreadsConfigurationEnabling and Disabling Performance SchemaEnabling and Disabling InstrumentsEnabling and Disabling ConsumersTuning Monitoring for Specific ObjectsTuning Threads MonitoringAdjusting Memory Size for Performance SchemaDefaultsUsing Performance SchemaExamining SQL StatementsExamining Read Versus Write PerformanceExamining Metadata LocksExamining Memory UsageExamining VariablesExamining Most Frequent ErrorsExamining Performance Schema ItselfSummary
What Limits MySQL’s Performance?How to Select CPUs for MySQLBalancing Memory and Disk ResourcesCaching, Reads, and WritesWhat’s Your Working Set?Solid-State StorageAn Overview of Flash MemoryGarbage CollectionRAID Performance OptimizationRAID Failure, Recovery, and MonitoringRAID Configuration and CachingNetwork ConfigurationChoosing a FilesystemChoosing a Disk Queue SchedulerMemory and SwappingOperating System StatusOther Helpful ToolsSummary
How MySQL’s Configuration WorksSyntax, Scope, and DynamismPersisted System VariablesSide Effects of Setting VariablesPlanning Your Variable ChangesWhat Not to DoCreating a MySQL Configuration FileMinimal ConfigurationInspecting MySQL Server Status VariablesConfiguring Memory UsagePer-Connection Memory NeedsReserving Memory for the Operating SystemThe InnoDB Buffer PoolThe Thread CacheConfiguring MySQL’s I/O BehaviorThe InnoDB Transaction LogLog BufferThe InnoDB TablespaceOther I/O Configuration OptionsConfiguring MySQL ConcurrencySafety SettingsAdvanced InnoDB SettingsSummary
Choosing Optimal Data TypesWhole NumbersReal NumbersString TypesDate and Time TypesBit-Packed Data TypesJSON DataChoosing IdentifiersSpecial Types of DataSchema Design Gotchas in MySQLToo Many ColumnsToo Many JoinsThe All-Powerful ENUMThe ENUM in DisguiseNULL Not Invented HereSchema ManagementSchema Management as Part of the Data Store PlatformSummary
Indexing BasicsTypes of IndexesBenefits of IndexesIndexing Strategies for High PerformancePrefix Indexes and Index SelectivityMulticolumn IndexesChoosing a Good Column OrderClustered IndexesCovering IndexesUsing Index Scans for SortsRedundant and Duplicate IndexesUnused IndexesIndex and Table MaintenanceFinding and Repairing Table CorruptionUpdating Index StatisticsReducing Index and Data FragmentationSummary
Why Are Queries Slow?Slow Query Basics: Optimize Data AccessAre You Asking the Database for Data You Don’t Need?Is MySQL Examining Too Much Data?Ways to Restructure QueriesComplex Queries Versus Many QueriesChopping Up a QueryJoin DecompositionQuery Execution BasicsThe MySQL Client/Server ProtocolQuery StatesThe Query Optimization ProcessThe Query Execution EngineReturning Results to the ClientLimitations of the MySQL Query OptimizerUNION LimitationsEquality PropagationParallel ExecutionSELECT and UPDATE on the Same TableOptimizing Specific Types of QueriesOptimizing COUNT() QueriesOptimizing JOIN QueriesOptimizing GROUP BY with ROLLUPOptimizing LIMIT and OFFSETOptimizing SQL_CALC_FOUND_ROWSOptimizing UNIONSummary

Replication OverviewHow Replication WorksReplication Under the HoodChoosing Replication FormatGlobal Transaction IdentifiersMaking Replication Crash SafeDelayed ReplicationMultithreaded ReplicationSemisynchronous ReplicationReplication FiltersReplication FailoverPlanned PromotionsUnplanned PromotionsTrade-Offs of PromotionReplication TopologiesActive/PassiveActive/Read PoolDiscouraged TopologiesReplication Administration and MaintenanceMonitoring ReplicationMeasuring Replication LagDetermining Whether Replicas Are Consistent with the SourceReplication Problems and SolutionsBinary Logs Corrupted on the SourceNonunique Server IDsUndefined Server IDsMissing Temporary TablesNot Replicating All UpdatesExcessive Replication LagOversized Packets from the SourceNo Disk SpaceReplication LimitationsSummary
Why Backups?Defining Recovery RequirementsDesigning a MySQL Backup SolutionOnline or Offline Backups?Logical or Raw Backups?What to Back UpIncremental and Differential BackupsReplicationManaging and Backing Up Binary LogsBackup and Recovery ToolsMySQL Enterprise BackupPercona XtraBackupmydumpermysqldumpBacking Up DataLogical SQL BackupsFilesystem SnapshotsPercona XtraBackupRecovering from a BackupRestoring Logical BackupsRestoring Raw Files from SnapshotRestoring with Percona XtraBackupStarting MySQL After Restoring Raw FilesSummary
What Is Scaling?Read- Versus Write-Bound WorkloadsUnderstanding Your WorkloadRead-Bound WorkloadsWrite-Bound WorkloadsFunctional ShardingScaling Reads with Read PoolsManaging Configuration for Read PoolsHealth Checks for Read PoolsChoosing a Load-Balancing AlgorithmQueuingScaling Writes with ShardingChoosing a Partitioning SchemeMultiple Partitioning KeysQuerying Across ShardsVitessProxySQLSummary
Managed MySQLAmazon Aurora for MySQLGCP Cloud SQLMySQL on Virtual MachinesMachine Types in CloudChoosing the Right Machine TypeChoosing the Right Disk TypeAdditional TipsSummary
What Is Compliance?Service Organization Controls Type 2Sarbanes–Oxley ActPayment Card Industry Data Security StandardHealth Insurance Portability and Accountability ActFederal Risk and Authorization Management ProgramGeneral Data Protection RegulationSchrems IIBuilding for Compliance ControlsSecrets ManagementSeparation of Roles and DataTracking ChangesBackup and Restore ProceduresSummary
Why Upgrade?Upgrade Life CycleTesting UpgradesDevelopment Environment TestingProduction MirrorReplicaToolingUpgrading at ScaleSummary
Provisioning Resources with KubernetesCarefully Scope Your GoalChoose Your Control PlaneThe Finer DetailsSummary

Content preview from High Performance MySQL, 4th Edition

Preface

The official documentation maintained by Oracle gives you the knowledge necessary to install, configure, and interact with MySQL. This book serves as a companion to that documentation, helping you understand how best to leverage MySQL as a powerful data platform for your use case.

This edition also expands on the growing role of compliance and security as parts of operating a database footprint. New realities such as privacy laws and data sovereignty have changed how companies build their products, and that naturally introduces new complexities in how the technical architecture evolves.

Who This Book Is For

This book is first and foremost for engineers looking to grow their expertise in running MySQL. This edition assumes its audience is familiar with the basic principles of why you want to use a relational database management system (RDBMS). We also assume some experience with general system administration, networking, and operating systems.

We will offer you proven strategies for running MySQL at scale with a modern architecture and more up-to-date tooling and practices.

Ultimately, we hope that the knowledge you gain from this book of MySQL’s internals and scaling strategies will help you in scaling the data storage layer at your organization. And we hope that your newfound insight will help you to learn and practice a methodical approach to designing, maintaining, and troubleshooting an architecture that is built on MySQL.