High Performance MySQL. 4th Edition - Helion

ebook

Autor: Silvia Botros, Jeremy Tinley
ISBN: 9781492080466
stron: 388, Format: ebook
Data wydania: 2021-10-18
Księgarnia: Helion

Cena książki: 194,65 zł (poprzednio: 226,34 zł)
Oszczędzasz: 14% (-31,69 zł)

Osoby, które kupiły tę książkę, wybierały także »

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

Osoby które kupowały "High Performance MySQL. 4th Edition", wybierały także:

Windows Media Center. Domowe centrum rozrywki 66,67 zł, (8,00 zł -88%)
Ruby on Rails. Ćwiczenia 18,75 zł, (3,00 zł -84%)
DevOps w praktyce. Kurs video. Jenkins, Ansible, Terraform i Docker 190,00 zł, (39,90 zł -79%)
Przywództwo w świecie VUCA. Jak być skutecznym liderem w niepewnym środowisku 58,64 zł, (12,90 zł -78%)
Scrum. O zwinnym zarządzaniu projektami. Wydanie II rozszerzone 58,64 zł, (12,90 zł -78%)

Spis treści

High Performance MySQL. 4th Edition eBook -- 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