MySQL Database Design and Tuning [NOOK Book]

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 ...
See more details below
MySQL Database Design and Tuning

Available on NOOK devices and apps  
  • NOOK Devices
  • NOOK HD/HD+ Tablet
  • NOOK
  • NOOK Color
  • NOOK Tablet
  • Tablet/Phone
  • NOOK for Windows 8 Tablet
  • NOOK for iOS
  • NOOK for Android
  • NOOK Kids for iPad
  • PC/Mac
  • NOOK for Windows 8
  • NOOK for PC
  • NOOK for Mac
  • NOOK Study
  • NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
$22.99
BN.com price
(Save 42%)$39.99 List Price

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.

Read More Show Less

Product Details

  • ISBN-13: 9780672332692
  • Publisher: Pearson Education
  • Publication date: 6/27/2005
  • Series: Novell Press
  • Sold by: Barnes & Noble
  • Format: eBook
  • Edition number: 1
  • Pages: 432
  • Sales rank: 885,611
  • File size: 8 MB

Meet the Author

MySQL® Database Design and Tuning About 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.

Read More Show Less

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.

Read More Show Less

Customer Reviews

Average Rating 4
( 1 )
Rating Distribution

5 Star

(0)

4 Star

(1)

3 Star

(0)

2 Star

(0)

1 Star

(0)

Your Rating:

Your Name: Create a Pen Name or

Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

Reviews should not contain any of the following:

  • - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
  • - Time-sensitive information such as tour dates, signings, lectures, etc.
  • - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
  • - Comments focusing on the author or that may ruin the ending for others
  • - Phone numbers, addresses, URLs
  • - Pricing and availability information or alternative ordering information
  • - Advertisements or commercial solicitation

Reminder:

  • - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
  • - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
  • - See Terms of Use for other conditions and disclaimers.
Search for Products You'd Like to Recommend

Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

 
Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing 1 Customer Reviews
  • Anonymous

    Posted July 24, 2005

    64 Terabytes of table space!

    Many of you might be tempted to go with the free MySQL, instead of possibly paying big bucks to Oracle or IBM for their databases. But what may have been holding you back could be a worry that when you go with MySQL, you are on your own. Unless you are already well versed with it, are there pitfalls in the implementation of table design or tuning that render the choice of MySQL a false economy? It's a reasonable worry. Schneider does well here to disabuse you of that notion. He addresses both issues. On the subject of table design, the advice is clear. Actually, it seems that the discussion is broadly applicable to any SQL/relational implementation. If you already know some SQL, you won't feel that MySQL is some weird variant. Which speaks well of its designers. Most of the book, however, deals with tuning aspects. Now here, you'll probably have to learn new things. Each SQL implementation seems to have specific tuning characteristics. There is little standardisation here across implementations. As what I hope is a reasonable guess, check out the sections on improving the efficiency of searches (ie. queries). This may be the most important thing to optimise in many applications. In passing, let me make one remark about MySQL's InnoDB data storage engine. It can hold a table space of as much as 64 Terabytes! For the vast majority of you, this should be adequate addressing. And it's a rejoinder to competitors who say MySQL is limited.

    Was this review helpful? Yes  No   Report this review
Sort by: Showing 1 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?
Comments (optional)