Op werkdagen voor 23:00 besteld, morgen in huis Gratis verzending vanaf €20

High Performance MySQL

Proven Strategies for Running MySQL at Scale

Paperback Engels 2021 9781492080510
Verkooppositie 5169Hoogste positie: 5169
Verwachte levertijd ongeveer 8 werkdagen


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 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


Trefwoorden:MySQL, Data Warehousing
Aantal pagina's:793
Hoofdrubriek:IT-management / ICT


Wees de eerste die een lezersrecensie schrijft!

Geef uw waardering

Zeer goed Goed Voldoende Matig Slecht


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
Isolation Levels
Transaction Logging
Transactions in MySQL
Multiversion Concurrency Control
Datafiles Structure
The InnoDB Engine
JSON Document Support
Data Dictionary Changes
Atomic DDL

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

3. Performance Schema
Introduction to Performance Schema
Instrument Elements
Consumer Organization
Resource Consumption
sys Schema
Understanding Threads
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
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

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

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

6. Schema Design and Management
Choosing Optimal Data Types
Whole Numbers
Real Numbers
String Types
Date and Time Types
Bit-Packed Data Types
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

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

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 UNION

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/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

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
Managing and Backing Up Binary Logs
Backup and Recovery Tools
MySQL Enterprise Backup
Percona XtraBackup
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

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
Scaling Writes with Sharding
Choosing a Partitioning Scheme
Multiple Partitioning Keys
Querying Across Shards

12. MySQL in the Cloud
Managed MySQL
Amazon Aurora for MySQL
MySQL on Virtual Machines
Machine Types in Cloud
Choosing the Right Machine Type
Choosing the Right Disk Type
Additional Tips

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

A. Upgrading MySQL

About the Authors

Managementboek Top 100


Populaire producten



        High Performance MySQL