MySQL Database Design and Tuning

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.

About the Author :
MySQL® Database Design and TuningAbout the Author Robert D. Schneider has more than 15 years of experience developing and delivering sophisticated software solutions worldwide. He has provided database optimization, distributed computing, and other technical expertise to a wide variety of enterprises in the financial, technology, and government sectors. Clients have included Chase Manhattan Bank, VISA, HP, SWIFT, and the governments of the United States, Brazil, and Malaysia. He is the author of Optimizing Informix Applications and Microsoft SQL Server: Planning and Building a High Performance Database. He has also written numerous articles on technical and professional services topics. He can be reached at Robert.Schneider@Think88.com.