High Performance MySQL · 2nd Edition by Schwartz, Baron -- Read -- Imperial Library of Trantor

Index

High Performance MySQL, 2nd Edition

Foreword Preface

How This Book Is Organized

A Broad Overview Building a Solid Foundation Tuning Your Application Scaling Upward After Making Changes Making Your Application Reliable Miscellaneous Useful Topics

Software Versions and Availability Conventions Used in This Book Using Code Examples Safari® Books Online How to Contact Us Acknowledgments for the Second Edition

From Baron From Peter From Vadim From Arjen

Acknowledgments for the First Edition

From Jeremy From Derek

1. MySQL Architecture

MySQL's 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

AUTOCOMMIT Mixing storage engines in transactions Implicit and explicit locking

Multiversion Concurrency Control MySQL's Storage Engines

The MyISAM Engine

Storage MyISAM features Compressed MyISAM tables

The MyISAM Merge Engine The InnoDB Engine The Memory Engine The Archive Engine The CSV Engine The Federated Engine The Blackhole Engine The NDB Cluster Engine The Falcon Engine The solidDB Engine The PBXT (Primebase XT) Engine The Maria Storage Engine Other Storage Engines Selecting the Right Engine Considerations Practical Examples

Logging Read-only or read-mostly tables Order processing Stock quotes Bulletin boards and threaded discussion forums CD-ROM applications

Storage Engine Summary Table Conversions

ALTER TABLE Dump and import CREATE and SELECT

2. Finding Bottlenecks: Benchmarking and Profiling

Why Benchmark? Benchmarking Strategies

What to Measure

Benchmarking Tactics

Designing and Planning a Benchmark Getting Accurate Results Running the Benchmark and Analyzing Results

Benchmarking Tools

Full-Stack Tools Single-Component Tools

Benchmarking Examples

http_load sysbench

The sysbench CPU benchmark The sysbench file I/O benchmark The sysbench OLTP benchmark Other sysbench features

dbt2 TPC-C on the Database Test Suite MySQL Benchmark Suite

Profiling

Profiling an Application

How and what to measure A PHP profiling example

MySQL Profiling

Logging queries Finer control over logging How to read the slow query log Log analysis tools

Profiling a MySQL Server Profiling Queries with SHOW STATUS SHOW PROFILE Other Ways to Profile MySQL When You Can't Add Profiling Code

Operating System Profiling

Troubleshooting MySQL Connections and Processes Advanced Profiling and Troubleshooting

3. Schema Optimization and Indexing

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 Choosing Identifiers Special Types of Data

Indexing Basics

Types of Indexes

B-Tree indexes Hash indexes Spatial (R-Tree) indexes Full-text indexes

Indexing Strategies for High Performance

Isolate the Column Prefix Indexes and Index Selectivity Clustered Indexes

Comparison of InnoDB and MyISAM data layout Inserting rows in primary key order with InnoDB

Covering Indexes Using Index Scans for Sorts Packed (Prefix-Compressed) Indexes Redundant and Duplicate Indexes Indexes and Locking

An Indexing Case Study

Supporting Many Kinds of Filtering Avoiding Multiple Range Conditions Optimizing Sorts

Index and Table Maintenance

Finding and Repairing Table Corruption Updating Index Statistics Reducing Index and Data Fragmentation

Normalization and Denormalization

Pros and Cons of a Normalized Schema Pros and Cons of a Denormalized Schema A Mixture of Normalized and Denormalized Cache and Summary Tables

Counter tables

Speeding Up ALTER TABLE

Modifying Only the .frm File Building MyISAM Indexes Quickly

Notes on Storage Engines

The MyISAM Storage Engine The Memory Storage Engine The InnoDB Storage Engine

4. Query Performance Optimization

Slow Query Basics: Optimize Data Access

Are You Asking the Database for Data You Don't Need? Is MySQL Examining Too Much Data?

Execution 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 Cache The Query Optimization Process

The parser and the preprocessor The query optimizer Table and index statistics MySQL's 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

Correlated Subqueries

When a correlated subquery is good UNION limitations Index merge optimizations Equality propagation Parallel execution Hash joins Loose index scans MIN() and MAX() SELECT and UPDATE on the same table

Optimizing Specific Types of Queries

Optimizing COUNT() Queries

What COUNT() does Myths about MyISAM Simple optimizations More complex optimizations

Optimizing JOIN Queries Optimizing Subqueries Optimizing GROUP BY and DISTINCT

Optimizing GROUP BY WITH ROLLUP

Optimizing LIMIT and OFFSET Optimizing SQL_CALC_FOUND_ROWS Optimizing UNION

Query Optimizer Hints User-Defined Variables

Be Careful with MySQL Upgrades

5. Advanced MySQL Features

The MySQL Query Cache

How MySQL Checks for a Cache Hit How the Cache Uses Memory When the Query Cache Is Helpful How to Tune and Maintain the Query Cache

Reducing fragmentation Improving query cache usage

InnoDB and the Query Cache General Query Cache Optimizations Alternatives to the Query Cache

Storing Code Inside MySQL

Stored Procedures and Functions Triggers Events Preserving Comments in Stored Code

Cursors Prepared Statements

Prepared Statement Optimization The SQL Interface to Prepared Statements Limitations of Prepared Statements

User-Defined Functions Views

Updatable Views Performance Implications of Views Limitations of Views

Character Sets and Collations

How MySQL Uses Character Sets

Defaults for creating objects Settings for client/server communication How MySQL compares values Special-case behaviors

Choosing a Character Set and Collation How Character Sets and Collations Affect Queries

Full-Text Searching

Natural-Language Full-Text Searches Boolean Full-Text Searches Full-Text Changes in MySQL 5.1 and Beyond Full-Text Tradeoffs and Workarounds Full-Text Tuning and Optimization

Foreign Key Constraints Merge Tables and Partitioning

Merge Tables

Merge table performance impacts Merge table strengths

Partitioned Tables

Why partitioning works Partitioning examples Partitioned table limitations Optimizing queries against partitioned tables

Distributed (XA) Transactions

Internal XA Transactions External XA Transactions

6. Optimizing Server Settings

Configuration Basics

Syntax, Scope, and Dynamism Side Effects of Setting Variables Getting Started

General Tuning

Tuning Memory Usage

How much memory can MySQL use? Per-connection memory needs Reserving memory for the operating system Allocating memory for caches

The MyISAM Key Cache

The MyISAM key block size

The InnoDB Buffer Pool The Thread Cache The Table Cache The InnoDB Data Dictionary

Tuning MySQL's I/O Behavior

MyISAM I/O Tuning InnoDB I/O Tuning

The InnoDB transaction log How InnoDB opens and flushes log and data files The InnoDB tablespace The doublewrite buffer Other I/O tuning options

Tuning MySQL Concurrency

MyISAM Concurrency Tuning InnoDB Concurrency Tuning

Workload-Based Tuning

Optimizing for BLOB and TEXT Workloads

Optimizing for Filesorts

Inspecting MySQL Server Status Variables

Tuning Per-Connection Settings

7. Operating System and Hardware Optimization

What Limits MySQL's Performance? How to Select CPUs for MySQL

Which Is Better: Fast CPUs or Many CPUs? CPU Architecture Scaling to Many CPUs and Cores

Balancing Memory and Disk Resources

Random Versus Sequential I/O Caching, Reads, and Writes What's Your Working Set?

The working set and the cache unit

Finding an Effective Memory-to-Disk Ratio Choosing Hard Disks

Choosing Hardware for a Slave RAID Performance Optimization

RAID Failure, Recovery, and Monitoring Balancing Hardware RAID and Software RAID RAID Configuration and Caching

The RAID stripe chunk size The RAID cache

Storage Area Networks and Network-Attached Storage

Storage Area Networks Network-Attached Storage

Using Multiple Disk Volumes Network Configuration Choosing an Operating System Choosing a Filesystem Threading Swapping Operating System Status

How to Read vmstat Output How to Read iostat Output A CPU-Bound Machine An I/O-Bound Machine A Swapping Machine An Idle Machine

8. Replication

Replication Overview

Problems Solved by Replication How Replication Works

Setting Up Replication

Creating Replication Accounts Configuring the Master and Slave Starting the Slave Initializing a Slave from Another Server Recommended Replication Configuration

Replication Under the Hood

Statement-Based Replication Row-Based Replication Replication Files Sending Replication Events to Other Slaves Replication Filters

Replication Topologies

Master and Multiple Slaves Master-Master in Active-Active Mode Master-Master in Active-Passive Mode Master-Master with Slaves Ring Master, Distribution Master, and Slaves Tree or Pyramid Custom Replication Solutions

Selective replication Separating functions Data archiving Using slaves for full-text searches Read-only slaves Emulating multimaster replication Creating a log server

Replication and Capacity Planning

Why Replication Doesn't Help Scale Writes Plan to Underutilize

Replication Administration and Maintenance

Monitoring Replication Measuring Slave Lag Determining Whether Slaves Are Consistent with the Master Resyncing a Slave from the Master Changing Masters

Planned promotions Unplanned promotions Locating the desired log positions

Switching Roles in a Master-Master Configuration

Replication Problems and Solutions

Errors Caused by Data Corruption or Loss Using Nontransactional Tables Mixing Transactional and Nontransactional Tables Nondeterministic Statements Different Storage Engines on the Master and Slave Data Changes on the Slave Nonunique Server IDs Undefined Server IDs Dependencies on Nonreplicated Data Missing Temporary Tables Not Replicating All Updates Lock Contention Caused by InnoDB Locking Selects Writing to Both Masters in Master-Master Replication Excessive Replication Lag

Don't duplicate the expensive part of writes Do writes in parallel outside of replication Prime the cache for the slave thread

Oversized Packets from the Master Limited Replication Bandwidth No Disk Space Replication Limitations

How Fast Is Replication? The Future of MySQL Replication

9. Scaling and High Availability

Terminology Scaling MySQL

Planning for Scalability Buying Time Before Scaling Scaling Up Scaling Out

Functional partitioning Data sharding Choosing a partitioning key Querying across shards Allocating data, shards, and nodes Fixed allocation Dynamic allocation Explicit allocation Rebalancing shards Generating globally unique IDs Tools for sharding

Scaling Back

Keeping active data separate

Scaling by Clustering

Clustering Federation

Load Balancing

Connecting Directly

Splitting reads and writes in replication Changing the application configuration Changing DNS names Moving IP addresses

Introducing a Middleman

Load balancers Load-balancing algorithms Adding and removing servers in the pool

Load Balancing with a Master and Multiple Slaves

High Availability

Planning for High Availability Adding Redundancy

Shared-storage architectures Replicated-disk architectures Synchronous MySQL replication

Failover and Failback

Promoting a slave or switching roles Virtual IP addresses or IP takeover The MySQL Master-Master Replication Manager Middleman solutions Handling failover in the application

10. Application-Level Optimization

Application Performance Overview

Find the Source of the Problem Look for Common Problems

Web Server Issues

Finding the Optimal Concurrency

Caching

Caching Below the Application Application-Level Caching Cache Control Policies Cache Object Hierarchies Pregenerating Content

Extending MySQL Alternatives to MySQL

11. Backup and Recovery

Overview

Terminology It's All About Recovery Topics We Won't Cover The Big Picture Why Backups?

Considerations and Tradeoffs

What Can You Afford to Lose? Online or Offline Backups? Logical or Raw Backups?

Logical backups Raw backups

What to Back Up

Incremental backups

Storage Engines and Consistency

Data consistency File consistency

Replication

Managing and Backing Up Binary Logs

The Binary Log Format Purging Old Binary Logs Safely

Backing Up Data

Making a Logical Backup

SQL dumps Delimited file backups Parallel dump and restore

Filesystem Snapshots

How LVM snapshots work Prerequisites and configuration Creating, mounting, and removing an LVM snapshot LVM snapshots for online backups Lock-free InnoDB backups with LVM snapshots Planning for LVM backups Other uses and alternatives

Recovering from a Backup

Limiting Access to MySQL Restoring Raw Files

Starting MySQL after restoring raw files

Restoring Logical Backups

Loading SQL files Loading delimited files

Point-in-Time Recovery More Advanced Recovery Techniques

Delayed replication for fast recovery Recovering with a log server

InnoDB Recovery

Causes of InnoDB corruption How to recover corrupted InnoDB data

Backup and Recovery Speed Backup Tools

mysqldump mysqlhotcopy InnoDB Hot Backup mk-parallel-dump mylvmbackup Zmanda Recovery Manager

Installing and testing ZRM

R1Soft MySQL Online Backup Comparison of Backup Tools

Scripting Backups

12. Security

Terminology Account Basics

Privileges The Grant Tables How MySQL Checks Privileges Adding, Removing, and Viewing Grants Setting Up MySQL Privileges Privilege Changes in MySQL 4.1 Privilege Changes in MySQL 5.0

Stored routines Triggers Views Privileges on the INFORMATION_SCHEMA tables

Privileges and Performance Common Problems and Solutions

Connecting through localhost versus 127.0.0.1 Using temporary tables safely Disallowing passwordless access Disabling anonymous users Remember to quote hostnames separately Don't reuse usernames Granting SELECT allows SHOW CREATE TABLE Don't grant privileges on the mysql database Don't grant the SUPER privilege freely Granting privileges on wildcarded databases Revoking specific privileges Users can connect even after REVOKE When you can't grant or revoke a privilege Invisible privileges Obsolete privileges

Operating System Security

Guidelines

Network Security

Localhost-Only Connections Firewalling

No default route

MySQL in a DMZ Connection Encryption and Tunneling

Virtual private networks SSL in MySQL SSH tunneling

TCP Wrappers Automatic Host Blocking

Data Encryption

Hashing Passwords Encrypted Filesystems Application-Level Encryption

Design issues Encrypting and decrypting inside MySQL

Source Code Modification

MySQL in a chrooted Environment

13. MySQL Server Status

System Variables SHOW STATUS

Thread and Connection Statistics Binary Logging Status Command Counters Temporary Files and Tables Handler Operations MyISAM Key Buffer File Descriptors Query Cache SELECT Types Sorts Table Locking Secure Sockets Layer (SSL) InnoDB-Specific Plug-in-Specific Miscellaneous

SHOW INNODB STATUS

Header SEMAPHORES LATEST FOREIGN KEY ERROR LATEST DETECTED DEADLOCK TRANSACTIONS FILE I/O INSERT BUFFER AND ADAPTIVE HASH INDEX LOG BUFFER POOL AND MEMORY ROW OPERATIONS

SHOW PROCESSLIST SHOW MUTEX STATUS Replication Status INFORMATION_SCHEMA

14. Tools for High Performance

Interface Tools

MySQL Visual Tools SQLyog phpMyAdmin

Monitoring Tools

Noninteractive Monitoring Systems

Homegrown systems Nagios Alternatives to Nagios MySQL Monitoring and Advisory Service MONyog RRDTool-based systems

Interactive Tools

innotop

Analysis Tools

HackMySQL Tools Maatkit Analysis Tools

MySQL Utilities

MySQL Proxy Dormando's Proxy for MySQL Maatkit Utilities

Sources of Further Information

A. Transferring Large Files

Copying Files

A Naive Example A One-Step Method Avoiding Encryption Overhead Other Options

File Copy Benchmarks

B. Using EXPLAIN

Invoking EXPLAIN

Rewriting Non-SELECT Queries

The Columns in EXPLAIN

The id Column The select_type Column The table Column

Derived tables and unions An example of complex SELECT types

The type Column The possible_keys Column The key Column The key_len Column The ref Column The rows Column The filtered Column The Extra Column

Visual EXPLAIN

C. Using Sphinx with MySQL

Overview: A Typical Sphinx Search Why Use Sphinx?

Efficient and Scalable Full-Text Searching Applying WHERE Clauses Efficiently Finding the Top Results in Order Optimizing GROUP BY Queries Generating Parallel Result Sets Scaling Aggregating Sharded Data

Architectural Overview

Installation Overview Typical Partition Use

Special Features

Phrase Proximity Ranking Support for Attributes Filtering The SphinxSE Pluggable Storage Engine Advanced Performance Control

Practical Implementation Examples

Full-Text Searching on Mininova.org Full-Text Searching on BoardReader.com Optimizing Selects on Sahibinden.com Optimizing GROUP BY on BoardReader.com Optimizing Sharded JOIN Queries on Grouply.com

Conclusion

D. Debugging Locks

Lock Waits at the Server Level

Table Locks

Finding out who holds a lock

The Global Read Lock Name Locks User Locks

Lock Waits in Storage Engines

InnoDB Lock Waits

Toward more usable lock output

Falcon Lock Waits

Index About the Authors Colophon

← Prev
Back
Next →

← Prev
Back
Next →