Book description
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.
Publisher resources
Table of contents
- High Performance MySQL, 2nd Edition
- Foreword
- Preface
-
1. MySQL Architecture
- MySQL’s Logical Architecture
- Concurrency Control
- Transactions
- Multiversion Concurrency Control
-
MySQL’s Storage Engines
- The MyISAM Engine
- 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
- Storage Engine Summary
- Table Conversions
-
2. Finding Bottlenecks: Benchmarking and Profiling
- Why Benchmark?
- Benchmarking Strategies
- Benchmarking Tactics
- Benchmarking Tools
- Benchmarking Examples
- Profiling
- Operating System Profiling
- 3. Schema Optimization and Indexing
-
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
- 5. Advanced MySQL Features
- 6. Optimizing Server Settings
-
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
-
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
- 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
- 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
- 10. Application-Level Optimization
-
11. Backup and Recovery
- Overview
- Considerations and Tradeoffs
- Managing and Backing Up Binary Logs
- Backing Up Data
- Recovering from a Backup
- Backup and Recovery Speed
- 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
- 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
- Network Security
- Data Encryption
- MySQL in a chrooted Environment
- 13. MySQL Server Status
- 14. Tools for High Performance
- A. Transferring Large Files
- B. Using EXPLAIN
- C. Using Sphinx with MySQL
- D. Debugging Locks
- Index
- About the Authors
- Colophon
- Copyright
Product information
- Title: High Performance MySQL, 2nd Edition
- Author(s):
- Release date: June 2008
- Publisher(s): O'Reilly Media, Inc.
- ISBN: 9780596101718
You might also like
book
High Performance MySQL, 4th Edition
How can you realize MySQL's full power? With High Performance MySQL, you'll learn advanced techniques for …
book
High Performance MySQL, 3rd Edition
How can you bring out MySQL’s full power? With High Performance MySQL, you’ll learn advanced techniques …
book
Learning MySQL, 2nd Edition
Get a comprehensive overview on how to set up and design an effective database with MySQL. …
book
Efficient MySQL Performance
You'll find several books on basic or advanced MySQL performance, but nothing in between. That's because …