High Performance MySQL

Name: High Performance MySQL | Optimization, Backups, and Replication
Brand: O'Reilly
Price: 55.49 EUR
Availability: OnlineOnly

Optimization, Backups, and Replication

Baron Schwartz(Author)

O'Reilly (Publisher)

3rd Edition

Published on 5. March 2012

826 pages

E-Book

PDF with Adobe-DRM

System requirements

978-1-4493-3250-1 (ISBN)

€55.49incl. 7% vat

System requirements

for PDF with Adobe-DRM

E-Book Single Licence

Available for download

New edition available

Description

More details

Other editions

Content

Intro
Table of Contents
Foreword
Preface
How This Book Is Organized
A Broad Overview
Building a Solid Foundation
Configuring Your Application
MySQL as an Infrastructure Component
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 Third Edition
Acknowledgments for the Second Edition
From Baron
From Peter
From Vadim
From Arjen
Acknowledgments for the First Edition
From Jeremy
From Derek
Chapter 1. MySQL Architecture and History
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 InnoDB Engine
InnoDB's history
InnoDB overview
The MyISAM Engine
Storage
MyISAM features
Compressed MyISAM tables
MyISAM performance
Other Built-in MySQL Engines
The Archive engine
The Blackhole engine
The CSV engine
The Federated engine
The Memory engine
The Merge storage engine
The NDB Cluster engine
Third-Party Storage Engines
OLTP storage engines
Column-oriented storage engines
Community storage engines
Selecting the Right Engine
Logging
Read-only or read-mostly tables
Order processing
Bulletin boards and threaded discussion forums
CD-ROM applications
Large data volumes
Table Conversions
ALTER TABLE
Dump and import
CREATE and SELECT
A MySQL Timeline
MySQL's Development Model
Summary
Chapter 2. Benchmarking MySQL
Why Benchmark?
Benchmarking Strategies
What to Measure
Benchmarking Tactics
Designing and Planning a Benchmark
How Long Should the Benchmark Last?
Capturing System Performance and Status
Getting Accurate Results
Running the Benchmark and Analyzing Results
The Importance of Plotting
Benchmarking Tools
Full-Stack Tools
Single-Component Tools
Benchmarking Examples
http_load
MySQL Benchmark Suite
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
Percona's TPCC-MySQL Tool
Summary
Chapter 3. Profiling Server Performance
Introduction to Performance Optimization
Optimization Through Profiling
Interpreting the Profile
Profiling Your Application
Instrumenting PHP Applications
Profiling MySQL Queries
Profiling a Server's Workload
Capturing MySQL's queries to a log
Analyzing the query log
Profiling a Single Query
Using SHOW PROFILE
Using SHOW STATUS
Using the slow query log
Using the Performance Schema
Using the Profile for Optimization
Diagnosing Intermittent Problems
Single-Query Versus Server-Wide Problems
Using SHOW GLOBAL STATUS
Using SHOW PROCESSLIST
Using query logging
Making sense of the findings
Capturing Diagnostic Data
The diagnostic trigger
What kinds of data should you collect?
Interpreting the data
A Case Study in Diagnostics
Other Profiling Tools
Using the USER_STATISTICS Tables
Using strace
Summary
Chapter 4. Optimizing Schema and Data Types
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
Schema Design Gotchas in MySQL
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
Materialized Views
Counter Tables
Speeding Up ALTER TABLE
Modifying Only the .frm File
Building MyISAM Indexes Quickly
Summary
Chapter 5. Indexing for High Performance
Indexing Basics
Types of Indexes
B-Tree indexes
Types of queries that can use a B-Tree index
Hash indexes
Building your own hash indexes
Handling hash collisions
Spatial (R-Tree) indexes
Full-text indexes
Other types of index
Benefits of Indexes
Indexing Strategies for High Performance
Isolating the Column
Prefix Indexes and Index Selectivity
Multicolumn Indexes
Choosing a Good Column Order
Clustered Indexes
Comparison of InnoDB and MyISAM data layout
MyISAM's data layout
InnoDB's 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
Unused 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
Summary
Chapter 6. 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?
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 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
Query Optimizer Hints
Optimizing Specific Types of Queries
Optimizing COUNT() Queries
What COUNT() does
Myths about MyISAM
Simple optimizations
Using an approximation
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
Static Query Analysis
Using User-Defined Variables
Optimizing ranking queries
Avoiding retrieving the row just modified
Counting UPDATEs and INSERTs
Making evaluation order deterministic
Writing a lazy UNION
Other uses for variables
Case Studies
Building a Queue Table in MySQL
Computing the Distance Between Points
Using User-Defined Functions
Summary
Chapter 7. Advanced MySQL Features
Partitioned Tables
How Partitioning Works
Types of Partitioning
How to Use Partitioning
What Can Go Wrong
Optimizing Queries
Merge Tables
Views
Updatable Views
Performance Implications of Views
Limitations of Views
Foreign Key Constraints
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
Plugins
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
Full-Text Tradeoffs and Workarounds
Full-Text Configuration and Optimization
Distributed (XA) Transactions
Internal XA Transactions
External XA Transactions
The MySQL Query Cache
How MySQL Checks for a Cache Hit
How the Cache Uses Memory
When the Query Cache Is Helpful
How to Configure 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
Summary
Chapter 8. Optimizing Server Settings
How MySQL's Configuration Works
Syntax, Scope, and Dynamism
Side Effects of Setting Variables
Getting Started
Iterative Optimization by Benchmarking
What Not to Do
Creating a MySQL Configuration File
Inspecting MySQL Server Status Variables
Configuring Memory Usage
How Much Memory Can MySQL Use?
Per-Connection Memory Needs
Reserving Memory for the Operating System
Allocating Memory for Caches
The InnoDB Buffer Pool
The MyISAM Key Caches
The MyISAM key block size
The Thread Cache
The Table Cache
The InnoDB Data Dictionary
Configuring MySQL's I/O Behavior
InnoDB I/O Configuration
The InnoDB transaction log
Log file size and the log buffer
How InnoDB flushes the log buffer
How InnoDB opens and flushes log and data files
The InnoDB tablespace
Configuring the tablespace
Old row versions and the tablespace
The doublewrite buffer
Other I/O configuration options
MyISAM I/O Configuration
Configuring MySQL Concurrency
InnoDB Concurrency Configuration
MyISAM Concurrency Configuration
Workload-Based Configuration
Optimizing for BLOB and TEXT Workloads
Optimizing for Filesorts
Completing the Basic Configuration
Safety and Sanity Settings
Advanced InnoDB Settings
Summary
Chapter 9. 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?
Finding an Effective Memory-to-Disk Ratio
Choosing Hard Disks
Solid-State Storage
An Overview of Flash Memory
Flash Technologies
Benchmarking Flash Storage
Solid-State Drives (SSDs)
Using RAID with SSDs
PCIe Storage Devices
Other Types of Solid-State Storage
When Should You Use Flash?
Using Flashcache
Optimizing MySQL for Solid-State Storage
Choosing Hardware for a Replica
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
SAN Benchmarks
Using a SAN over NFS or SMB
MySQL Performance on a SAN
Should You Use a SAN?
Using Multiple Disk Volumes
Network Configuration
Choosing an Operating System
Choosing a Filesystem
Choosing a Disk Queue Scheduler
Threading
Swapping
Operating System Status
How to Read vmstat Output
How to Read iostat Output
Other Helpful Tools
A CPU-Bound Machine
An I/O-Bound Machine
A Swapping Machine
An Idle Machine
Summary
Chapter 10. Replication
Replication Overview
Problems Solved by Replication
How Replication Works
Setting Up Replication
Creating Replication Accounts
Configuring the Master and Replica
Starting the Replica
Initializing a Replica from Another Server
Recommended Replication Configuration
Replication Under the Hood
Statement-Based Replication
Row-Based Replication
Statement-Based or Row-Based: Which Is Better?
Replication Files
Sending Replication Events to Other Replicas
Replication Filters
Replication Topologies
Master and Multiple Replicas
Master-Master in Active-Active Mode
Master-Master in Active-Passive Mode
Master-Master with Replicas
Ring Replication
Master, Distribution Master, and Replicas
Tree or Pyramid
Custom Replication Solutions
Selective replication
Separating functions
Data archiving
Using replicas for full-text searches
Read-only replicas
Emulating multisource replication
Creating a log server
Replication and Capacity Planning
Why Replication Doesn't Help Scale Writes
When Will Replicas Begin to Lag?
Plan to Underutilize
Replication Administration and Maintenance
Monitoring Replication
Measuring Replication Lag
Determining Whether Replicas Are Consistent with the Master
Resyncing a Replica 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 Replica
Data Changes on the Replica
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 replication thread
Oversized Packets from the Master
Limited Replication Bandwidth
No Disk Space
Replication Limitations
How Fast Is Replication?
Advanced Features in MySQL Replication
Other Replication Technologies
Summary
Chapter 11. Scaling MySQL
What Is Scalability?
A Formal Definition
Scaling MySQL
Planning for Scalability
Buying Time Before Scaling
Scaling Up
Scaling Out
Functional partitioning
Data sharding
Choosing a partitioning key
Multiple partitioning keys
Querying across shards
Allocating data, shards, and nodes
Arranging shards on nodes
Fixed allocation
Dynamic allocation
Mixing dynamic and fixed allocation
Explicit allocation
Rebalancing shards
Generating globally unique IDs
Tools for sharding
Scaling by Consolidation
Scaling by Clustering
MySQL Cluster (NDB Cluster)
Clustrix
ScaleBase
GenieDB
Akiban
Scaling Back
Keeping active data separate
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 Replicas
Summary
Chapter 12. High Availability
What Is High Availability?
What Causes Downtime?
Achieving High Availability
Improving Mean Time Between Failures
Improving Mean Time to Recovery
Avoiding Single Points of Failure
Shared Storage or Replicated Disk
Synchronous MySQL Replication
MySQL Cluster
Percona XtraDB Cluster
Replication-Based Redundancy
Failover and Failback
Promoting a Replica or Switching Roles
Virtual IP Addresses or IP Takeover
Middleman Solutions
Handling Failover in the Application
Summary
Chapter 13. MySQL in the Cloud
Benefits, Drawbacks, and Myths of the Cloud
The Economics of MySQL in the Cloud
MySQL Scaling and HA in the Cloud
The Four Fundamental Resources
MySQL Performance in Cloud Hosting
Benchmarks for MySQL in the Cloud
MySQL Database as a Service (DBaaS)
Amazon RDS
Other DBaaS Solutions
Summary
Chapter 14. Application-Level Optimization
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
The Cache as an Infrastructure Component
Using HandlerSocket and memcached Access
Extending MySQL
Alternatives to MySQL
Summary
Chapter 15. 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
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
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
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 Crash Recovery
Causes of InnoDB corruption
How to recover corrupted InnoDB data
Backup and Recovery Tools
MySQL Enterprise Backup
Percona XtraBackup
mylvmbackup
Zmanda Recovery Manager
mydumper
mysqldump
Scripting Backups
Summary
Chapter 16. Tools for MySQL Users
Interface Tools
Command-Line Utilities
SQL Utilities
Monitoring Tools
Open Source Monitoring Tools
Commercial Monitoring Systems
Command-Line Monitoring with Innotop
Summary
Appendix A. Forks and Variants of MySQL
Percona Server
MariaDB
Drizzle
Other MySQL Variants
Summary
Appendix B. 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
InnoDB-Specific
Plugin-Specific
SHOW ENGINE 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 ENGINE INNODB MUTEX
Replication Status
The INFORMATION_SCHEMA
InnoDB Tables
Tables in Percona Server
The Performance Schema
Summary
Appendix C. Transferring Large Files
Copying Files
A Naïve Example
A One-Step Method
Avoiding Encryption Overhead
Other Options
File Copy Benchmarks
Appendix D. 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
Tree-Formatted Output
Improvements in MySQL 5.6
Appendix E. 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 InnoDB
Using the INFORMATION_SCHEMA Tables
Appendix F. Using Sphinx with MySQL
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
Summary
Index

System requirements

Save as PDF Copy link into clipboard

Schweitzer Fachinformationen

High Performance MySQL

Description

More details

Other editions

New editions

Additional editions

Previous edition

Content

System requirements