Books & Videos

High Performance MySQL, 2nd Edition

Optimization, Backups, Replication, and More

By Jeremy Zawodny, Derek Balling, Peter Zaitsev, Vadim Tkachenko, Arjen Lentz, Baron Schwartz

Publisher: O'Reilly Media

Release Date: December 2008

Pages: 712

Read on Safari with a 10-day trial

Start your free trial now Buy on Amazon

Where’s the cart? Now you can get everything on Safari. To purchase books, visit Amazon or your favorite retailer. Questions? See our FAQ or contact customer service:

1-800-889-8969 / 707-827-7019
support@oreilly.com

View/Submit Errata

High Performance MySQL is the definitive guide to building fast, reliable systems with MySQL. Written by noted experts with years of real-world experience building very large systems, this book covers every aspect of MySQL performance in detail, and focuses on robustness, security, and data integrity.

High Performance MySQL teaches you advanced techniques in depth so you can bring out MySQL's full power. Learn how to design schemas, indexes, queries and advanced MySQL features for maximum performance, and get detailed guidance for tuning your MySQL server, operating system, and hardware to their fullest potential. You'll also learn practical, safe, high-performance ways to scale your applications with replication, load balancing, high availability, and failover.

This second edition is completely revised and greatly expanded, with deeper coverage in all areas. Major additions include:

Emphasis throughout on both performance and reliability
Thorough coverage of storage engines, including in-depth tuning and optimizations for the InnoDB storage engine
Effects of new features in MySQL 5.0 and 5.1, including stored procedures, partitioned databases, triggers, and views
A detailed discussion on how to build very large, highly scalable systems with MySQL
New options for backups and replication
Optimization of advanced querying features, such as full-text searches
Four new appendices

The book also includes chapters on benchmarking, profiling, backups, security, and tools and techniques to help you measure, monitor, and manage your MySQL installations.

Chapter 1 MySQL Architecture
1. MySQL's Logical Architecture
2. Concurrency Control
3. Transactions
4. Multiversion Concurrency Control
5. MySQL's Storage Engines
Chapter 2 Finding Bottlenecks: Benchmarking and Profiling
1. Why Benchmark?
2. Benchmarking Strategies
3. Benchmarking Tactics
4. Benchmarking Tools
5. Benchmarking Examples
6. Profiling
7. Operating System Profiling
Chapter 3 Schema Optimization and Indexing
1. Choosing Optimal Data Types
2. Indexing Basics
3. Indexing Strategies for High Performance
4. An Indexing Case Study
5. Index and Table Maintenance
6. Normalization and Denormalization
7. Speeding Up ALTER TABLE
8. Notes on Storage Engines
Chapter 4 Query Performance Optimization
1. Slow Query Basics: Optimize Data Access
2. Ways to Restructure Queries
3. Query Execution Basics
4. Limitations of the MySQL Query Optimizer
5. Optimizing Specific Types of Queries
6. Query Optimizer Hints
7. User-Defined Variables
Chapter 5 Advanced MySQL Features
1. The MySQL Query Cache
2. Storing Code Inside MySQL
3. Cursors
4. Prepared Statements
5. User-Defined Functions
6. Views
7. Character Sets and Collations
8. Full-Text Searching
9. Foreign Key Constraints
10. Merge Tables and Partitioning
11. Distributed (XA) Transactions
Chapter 6 Optimizing Server Settings
1. Configuration Basics
2. General Tuning
3. Tuning MySQL's I/O Behavior
4. Tuning MySQL Concurrency
5. Workload-Based Tuning
6. Tuning Per-Connection Settings
Chapter 7 Operating System and Hardware Optimization
1. What Limits MySQL's Performance?
2. How to Select CPUs for MySQL
3. Balancing Memory and Disk Resources
4. Choosing Hardware for a Slave
5. RAID Performance Optimization
6. Storage Area Networks and Network-Attached Storage
7. Using Multiple Disk Volumes
8. Network Configuration
9. Choosing an Operating System
10. Choosing a Filesystem
11. Threading
12. Swapping
13. Operating System Status
Chapter 8 Replication
1. Replication Overview
2. Setting Up Replication
3. Replication Under the Hood
4. Replication Topologies
5. Replication and Capacity Planning
6. Replication Administration and Maintenance
7. Replication Problems and Solutions
8. How Fast Is Replication?
9. The Future of MySQL Replication
Chapter 9 Scaling and High Availability
1. Terminology
2. Scaling MySQL
3. Load Balancing
4. High Availability
Chapter 10 Application-Level Optimization
1. Application Performance Overview
2. Web Server Issues
3. Caching
4. Extending MySQL
5. Alternatives to MySQL
Chapter 11 Backup and Recovery
1. Overview
2. Considerations and Tradeoffs
3. Managing and Backing Up Binary Logs
4. Backing Up Data
5. Recovering from a Backup
6. Backup and Recovery Speed
7. Backup Tools
8. Scripting Backups
Chapter 12 Security
1. Terminology
2. Account Basics
3. Operating System Security
4. Network Security
5. Data Encryption
6. MySQL in a chrooted Environment
Chapter 13 MySQL Server Status
1. System Variables
2. SHOW STATUS
3. SHOW INNODB STATUS
4. SHOW PROCESSLIST
5. SHOW MUTEX STATUS
6. Replication Status
7. INFORMATION_SCHEMA
Chapter 14 Tools for High Performance
1. Interface Tools
2. Monitoring Tools
3. Analysis Tools
4. MySQL Utilities
5. Sources of Further Information

Appendix Transferring Large Files
1. Copying Files
2. File Copy Benchmarks
Appendix Using EXPLAIN
1. Invoking EXPLAIN
2. The Columns in EXPLAIN
3. Visual EXPLAIN
Appendix Using Sphinx with MySQL
1. Overview: A Typical Sphinx Search
2. Why Use Sphinx?
3. Architectural Overview
4. Special Features
5. Practical Implementation Examples
6. Conclusion
Appendix Debugging Locks
1. Lock Waits at the Server Level
2. Lock Waits in Storage Engines
Colophon

Books & Videos

High Performance MySQL, 2nd Edition

Optimization, Backups, Replication, and More

Read on Safari with a 10-day trial

Table of Contents

Chapter 1 MySQL Architecture

MySQL's Logical Architecture

Concurrency Control

Transactions

Multiversion Concurrency Control

MySQL's Storage Engines

Chapter 2 Finding Bottlenecks: Benchmarking and Profiling

Why Benchmark?

Benchmarking Strategies

Benchmarking Tactics

Benchmarking Tools

Benchmarking Examples

Profiling

Operating System Profiling

Chapter 3 Schema Optimization and Indexing

Choosing Optimal Data Types

Indexing Basics

Indexing Strategies for High Performance

An Indexing Case Study

Index and Table Maintenance

Normalization and Denormalization

Speeding Up ALTER TABLE

Notes on Storage Engines

Chapter 4 Query Performance Optimization

Slow Query Basics: Optimize Data Access

Ways to Restructure Queries

Query Execution Basics

Limitations of the MySQL Query Optimizer

Optimizing Specific Types of Queries

Query Optimizer Hints

User-Defined Variables

Chapter 5 Advanced MySQL Features

The MySQL Query Cache

Storing Code Inside MySQL

Cursors

Prepared Statements

User-Defined Functions

Views

Character Sets and Collations

Full-Text Searching

Foreign Key Constraints

Merge Tables and Partitioning

Distributed (XA) Transactions

Chapter 6 Optimizing Server Settings

Configuration Basics

General Tuning

Tuning MySQL's I/O Behavior

Tuning MySQL Concurrency

Workload-Based Tuning

Tuning Per-Connection Settings

Chapter 7 Operating System and Hardware Optimization

What Limits MySQL's Performance?

How to Select CPUs for MySQL

Balancing Memory and Disk Resources

Choosing Hardware for a Slave

RAID Performance Optimization

Storage Area Networks and Network-Attached Storage

Using Multiple Disk Volumes

Network Configuration

Choosing an Operating System

Choosing a Filesystem

Threading

Swapping

Operating System Status

Chapter 8 Replication

Replication Overview

Setting Up Replication

Replication Under the Hood

Replication Topologies

Replication and Capacity Planning

Replication Administration and Maintenance

Replication Problems and Solutions

How Fast Is Replication?

The Future of MySQL Replication

Chapter 9 Scaling and High Availability