Overview

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.

Full Product Details

Author:   Robert D Schneider
Publisher:   MySQL Press
Imprint:   MySQL Press
Dimensions:   Width: 17.80cm , Height: 2.50cm , Length: 22.90cm
Weight:   0.676kg
ISBN:  

9780672327650

ISBN 10:   0672327651
Pages:   432
Publication Date:   28 June 2005
Audience:   College/higher education ,  Tertiary & Higher Education
Format:   Paperback
Publisher's Status:   Out of Print
Availability:   Out of stock  

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.

Reviews

Author Information

MySQL(R) 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. A(c) Copyright Pearson Education. All rights reserved.

Tab Content 6

Author Website:  

Customer Reviews

Recent Reviews

No review item found!

Add your own review!

Countries Available

All regions