The authoritative, hands-on guide to advanced MySQL programming and administration techniques for high performance is here. MySQL Database Design and Tuning is the only guide with coverage of both the basics and advanced topics, including reliability, performance, optimization and tuning for MySQL. This clear, concise and unique source for the most reliable MySQL performance information will show you how to:Deploy the right MySQL product for your performance needs.
Set up a performance management and monitoring environment using tools from MySQL.
Implement the right indexing strategy
Apply good performance strategy when developing software to work with the MySQL database.
Configure dozens of variable to correctly tune the MySQL engine.
If you deal with the intricacies and challenges of advanced MySQL functionality on a daily basis, you will be able to build on your knowledge with author Robert Schneider's real-world experiences in MySQL Database Design and Tuning.
Table of Contents
The Purpose of This Book.
Who Should Read This Book?
Benefits of Using This Book
How to Use This Book
Information About MySQL Software and Environments
Information About Examples
I. INTRODUCTION.
1. Setting Up an Optimization Environment.
Pretesting Preparation
Hardware
Connectivity
Software
Data
Your Test Plan
Change One Variable at a Time
Organizational Support
After Testing Is Under Way
Posttesting Implementation
Recording Your Results
Making Improvements
2. Performance Monitoring Options.
MySQL Tools and Utilities
Character-Based Tools and Utilities
Graphical Tools
Logging
Operating System Tools
Windows
Linux/Unix
3. MySQL Overview and New Performance-Related Features.
MySQL Products
MySQL Core Technologies
Database Storage Engines and Table Types
Distributed Computing Technologies
Graphical Tools and Assistants
Connectors
APIs
Utilities
Performance-Related Tools
About the Versions Used for This Book
MySQL Versions
Operating Systems
Performance-Boosting Features from Version 4.0 Onward
II. ADVANCED DATABASE DESIGN CONCEPTS.
4. Designing for Speed.
Choosing the Right Storage Engine and Table Type
InnoDB
MyISAM
MEMORY
MERGE
CSV
ARCHIVE
BerkeleyDB (BDB)
MaxDB
NDB
FEDERATED
Optimizing Table Structure
Specifying Row Format
Specifying Table Size
Index Key Compression
Checksum Integrity Management
Column Types and Performance
Using Views to Boost Performance
Reduced Returned Columns
Reduced Returned Rows
Reduced Inefficient Joins
Simpler Updating with Integrity Checking
When Not to Normalize
5. Using Constraints to Improve Performance.
UNIQUE Constraints
PRIMARY KEY
FOREIGN KEY
DEFAULT and NOT NULL
ENUM
SET
6. Understanding the MySQL Optimizer.
What Is an Optimizer?
Optimizer Diagnostics and Tuning
The ANALYZE TABLE Command
The OPTIMIZE TABLE Command
The EXPLAIN Command
Helping the Optimizer
7. Indexing Strategies.
Index Key Terms and Concepts
Index Reports and Utilities
SHOW INDEX
Assisting the Optimizer with Indexes
Index-Related Logging
MyISAM Indexing Features
Key Cache
The myisamchk Utility
Index and Table Compression
InnoDB Indexing Features
Index Structure and Storage
Buffer Pool
Memory Pool
Adaptive Hash Index
Automatic Foreign Key Index Generation
Indexing Scenarios
PRIMARY KEY
Filter Columns
Join Columns
Index Cardinality
Character Versus Numeric Indexes
Multicolumn Indexes
Partial Indexes
Ascending Versus Descending Indexes
Storing Tables in Column-Sorted Order
III. OPTIMIZING APPLICATION CODE.
8. Advanced SQL Tips.
Improving Searches
Leveraging Internal Engine Caches
Controlling Data Retrieval
Reducing Security Costs
Off-loading Processing Work
Boosting Join Performance
Substring Searches
Improving Temporary Table Performance
Managing View Performance
Subqueries
Using Math Within SQL
UNIONs
Sorting
HANDLER
Improving Data Modification
Improving INSERT
Improving DELETE
9. Developing High-Speed Applications.
Understanding Locking
General Locking Overview
InnoDB Locking Overview
Optimal Transactions
Key Transaction Terms and Concepts
Cost of Transactions
Transaction Performance Suggestions
Stored Procedures and Functions
Difference Between Stored Procedures and Functions
When to Use Stored Procedures or Functions
When Not to Use Stored Procedures or Functions
Stored Procedure Optimization
Triggers
Current Trigger Limitations
When to Use Triggers
When Not to Use Triggers
Writing Efficient Application Code
General Suggestions
IV. OPTIMIZING AND TUNING THE MYSQL ENGINE.
10. General Server Performance Parameters and Tuning.
Server Settings Scope
Connectivity
Creating a Connection
Managing a Connection
Memory Management
Locking mysqld in Memory
Thread Memory Settings
MEMORY Tables
Caching
Application Control
Resources
Optimizer Control
Concurrency
User Resource Control
11. MyISAM Performance Enhancement.
Optimal Data Storage
Table Reporting and Control
Table Maintenance and Repair
Controlling Sorting Resources
MyISAM and Memory
The Key Cache
Speeding MyISAM Operations
Loading Information
Improving FULLTEXT Searches
Concurrency Considerations
12. InnoDB Performance Enhancement.
InnoDB Storage Architecture and I/O
Indexes
Pages
Extents and Segments
Tablespaces
Built-in Performance Enhancers
Data Storage
Log Files
InnoDB and Memory
Buffer Pool
InnoDB Operational Control
Thread Performance
Improving Concurrency
Improving Large-Scale Operations
Speeding Up Shutdown
13. Improving Disk Speed.
General Suggestions
Deploying the Fastest Possible Disk Drives
Configuring Memory Caches Correctly
Implementing a RAID Strategy
Distributing Temporary Directories
MyISAM Suggestions
Symbolic Links
Table Compression
InnoDB Suggestions
Choosing the Right autoextend Setting
Using Raw Devices
Alternative Disk Writing Methods
Table Defragmentation
Operating System-Specific Disk Suggestions
Linux/Unix Suggestions
Windows Suggestions
14. Operating System, Web Server, and Connectivity Tuning.
Operating System Considerations
Assumptions
Linux/Unix
Windows
Web Server Performance Considerations
Choosing the Right Server Topology
General PHP Suggestions
Apache/PHP Suggestions
Internet Information Services (IIS)/PHP Tips
Connectivity Tuning
Protocol Choices
Costs of Hostname Resolution
Costs of Creating and Destroying Connections
Costs of SSL
15. Improving Import and Export Operations.
Speeding Data Exports
The Importance of Regular Exports
Avoiding Excessive Memory Consumption
Concurrency and Exports
Retrieving Subsets of Information
Copying Table Structures Without Data
Delaying Index Re-creation-MyISAM
Delaying Index Re-creation-InnoDB
Preparing for Data Reload
Accelerating Data Loading
Managing Concurrency
Handling Errors
Engine-Specific Tips
V. DISTRIBUTED COMPUTING.
16. Optimal Replication.
Introduction
How Does Replication Work?
Is Replication Right for You?
Creating a Replication Strategy
High-Performance Replication
Network Considerations
Master Server Performance Considerations
Implementing Best Tuning Practices
Managing Slave Resources
Slave Server Performance Considerations
17. Optimal Clustering.
Introduction
How Does Clustering Work?
Nodes
Shared-Nothing
Cluster Clients
Storage Engine
Transporter
Data Distribution
Checkpoints
Is Clustering Right for You?
Clustering Versus Replication
Creating a Clustering Strategy
Choosing the Right Version
Cluster Topology
Configuring MySQL Cluster for High Performance
General Clustering Best Practices
Network Considerations
Transports
Management Node Considerations
SQL Node Considerations
Data Node Considerations
VI. CASE STUDIES.
18. Case Study: High-Hat Delivers!
Problem Queries
Package Status Lookup
Shipping Option Lookup
Random Transaction Bottlenecks
Diagnosis
Solution
Implementing These Solutions
19. Case Study: Friends Fly Free-for-All-A Promotion Gone Wrong.
Server Availability
Diagnosis
Solution
Application and Transaction Issues
Diagnosis
Solution
20. Case Study 3: Practice Makes Perfect.
Data Importing
Diagnosis
Solution
Clustering
Diagnosis
Solution
Stored Procedures
Diagnosis
Solution
Index.
