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

Chapter 12. MySQL in the Cloud

In all likelihood, you won’t have much control over whether you move to a cloud provider or even which one your organization ultimately adopts. What you can control is how you build your database environment. There are two directions you can take: managed MySQL or building on VMs. Managed MySQL tends to be more hands-off, but it’s usually more expensive and gives you less control. Building on a VM means you get a lot more flexibility in how you build and how to observe your platform, but it requires more time and operational overhead.

In this chapter, we’ll outline the major options for managed MySQL and how they can be useful to you. We’ll also explain how to get started building a VM option, including selecting the right specs and disk types, and we will cover the operational complexities (like host reboots) you have to prepare for when running MySQL on VMs in a cloud.

Warning

We will not cover bugs in cloud-provider offerings. These offerings are ever-evolving products, so we recommend you keep up-to-date with dynamic sources like newsletters or bug boards rather than a point-in-time reference such as this book.

Managed MySQL

Offerings for managed MySQL among cloud providers bring a lot of convenience to teams looking to reduce the cognitive load of operating MySQL as their product grows and their feature set expands. Every public cloud has its own interpretation of what a managed SQL database should look like and how it should work. Amazon ...