Always Learning

Advanced Search

MySQL Database Design and Tuning

MySQL Database Design and Tuning


Jun 2005, Paperback, 432 pages
ISBN13: 9780672327650
ISBN10: 0672327651
This title is ordered on demand which may result in extended delivery times.
  • Print pagePrint page
  • Email this pageEmail page
  • Write a reviewWrite a review
  • Share

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.

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.

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.

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.


© Copyright Pearson Education. All rights reserved.