MySQL database design and tuning

433 Pages·2005·7.587 MB·English

by  Schneider, Robert D

MySQL Database
®
Design and Tuning
This page intentionally left blank
MySQL Database
®
Design and Tuning
Robert D. Schneider
800 East 96th Street, Indianapolis, Indiana 46240 USA
MySQL® Database Design and Tuning
Copyright © 2005 by Pearson Education
All rights reserved. No part of this book shall be reproduced, stored in a retrieval system, or transmitted
by any means, electronic, mechanical, photocopying, recording, or otherwise, without written permis-
sion from the publisher. No patent liability is assumed with respect to the use of the information con-
tained herein. Although every precaution has been taken in the preparation of this book, the publisher
and author assume no responsibility for errors or omissions. Nor is any liability assumed for damages
resulting from the use of the information contained herein.
International Standard Book Number: 0-672-32765-1
Library of Congress Catalog Card Number: 2004098791
Printed in the United States of America
First Printing: June 2005
08 07 06 05 4 3 2 1
Trademarks
All terms mentioned in this book that are known to be trademarks or service marks have been appropri-
ately capitalized. Pearson cannot attest to the accuracy of this information. Use of a term in this book
should not be regarded as affecting the validity of any trademark or service mark.
Warning and Disclaimer
Every effort has been made to make this book as complete and as accurate as possible, but no warranty
or fitness is implied. The information provided is on an “as is” basis. The author and the publisher shall
have neither liability nor responsibility to any person or entity with respect to any loss or damages aris-
ing from the information contained in this book.
Bulk Sales
Pearson offers excellent discounts on this book when ordered in quantity for bulk purchases or special
sales. For more information, please contact
U.S. Corporate and Government Sales
1-800-382-3419
[e-mail]
For sales outside of the U.S., please contact
International Sales
[e-mail]
ASSOCIATE PUBLISHER MANAGING EDITOR INDEXER DESIGNER
Mark Taber Charlotte Clapp Chris Barrick Gary Adair
ACQUISITIONS EDITOR SENIOR PROJECT PROOFREADER TECHNICAL EDITORS
Shelley Johnston EDITOR Leslie Joseph Philip Antoniades
DEVELOPMENT EDITOR Matthew Purcell PUBLISHING Dean Ellis
Damon Jordan COPY EDITOR COORDINATOR Lachlan Mulcahy
Karen Annett Vanessa Evans Trudy Pelzer
Peter Zaitsev
MySQL Pressis the exclusive publisher of technology books and materials that have been
®
authorized by MySQL AB. MySQL Press books are written and reviewed by the world’s
leading authorities on MySQL technologies, and are edited, produced, and distributed by
the Que/Sams Publishing group of Pearson Education, the worldwide leader in integrated
education and computer technology publishing. For more information on MySQL Press
and MySQL Press books, please go to www.mysqlpress.com.
MYSQL HQ GERMANY, AUSTRIA, AND FRANCE
MySQL AB SWITZERLAND MySQL AB (France)
Bangårdsgatan 8 MySQL GmbH 123, rue du Faubourg St. Antoine
S-753 20 Uppsala Schlosserstraße 4 75011, Paris
Sweden D-72622 Nürtingen France
Germany
UNITED STATES
MySQL Inc. FINLAND
2510 Fairview Avenue East MySQL Finland Oy
Seattle, WA 98102 Tekniikantie 21
USA FIN-02150 Espoo
Finland
MySQL ABdevelops, markets, and supports a family of high-performance, affordable
®
database servers and tools. MySQL AB is the sole owner of the MySQL server source code,
the MySQL trademark, and the mysql.com domain. For more information on MySQL AB
and MySQL AB products, please go to www.mysql.comor the following areas of the
MySQL Web site:
n Training information: www.mysql.com/training
n Support services: www.mysql.com/support
n Consulting services: www.mysql.com/consulting
❧
To the Open Source movement, and the collaboration,
freedom, honesty, and pursuit of knowledge it
embodies and inspires.
❧
About the Author
Robert D. Schneiderhas more than 15 years of experience developing and delivering
sophisticated software solutions worldwide. He has provided database optimization, distrib-
uted computing, and other technical expertise to a wide variety of enterprises in the finan-
cial, 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 Applicationsand 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 [e-mail].
Acknowledgments
The author wants to acknowledge the following people for their invaluable assistance in cre-
ating and publishing this work. From Pearson Education: Shelley Johnston, Damon Jordan,
Matthew Purcell, Vanessa Evans, Chris Barrick, Leslie Joseph, and Karen Annett. Technical
editing by Philip Antioniades, Dean Ellis, Sara Golemon, Lachlan Mulcahy, Trudy Pelzer,
and Peter Zaitsev. And last, but not least: Lynn Z. Schneider, Nicole Sierra Schneider, and
Danielle Jolie Schneider for their patience and understanding.
Contents At a Glance
Introduction 1
PART I: Introduction
1 Setting Up an Optimization Test Environment 9
2 Performance Monitoring Options 19
3 MySQL Overview and New Performance-Related Features 43
PART II: Advanced Database Design Concepts
4 Designing for Speed 63
5 Using Constraints to Improve Performance 85
6 Understanding the MySQL Optimizer 95
7 Indexing Strategies 123
PART III: Optimizing Application Code
8 Advanced SQL Tips 153
9 Developing High-Speed Applications 185
PART IV: Optimizing and Tuning the MySQL Engine
10 General Server Performance Parameters and Tuning 211
11 MyISAM Performance Enhancement 233
12 InnoDB Performance Enchancement 259
13 Improving Disk Speed 283
14 Operating System, Web Server, and Connectivity Tuning 295
15 Improving Import and Export Operations 311
PART V: Distributed Computing
16 Optimal Replication 325
17 Optimal Clustering 341
PART VI: Case Studies
18 Case Study 1: High-Hat Delivers! 359
19 Case Study 2: Friends Fly Free-for-All—A Promotion Gone
Wrong 369
20 Case Study 3: Practice Makes Perfect 379
Index 389
Table of Contents
The Purpose of This Book 1
Who Should Read This Book?..........................................1
Benefits of Using This Book ............................................2
How to Use This Book..................................................3
Information About MySQL Software and Environments ........4
Information About Examples......................................4
PART I: Introduction
1 Setting Up an Optimization Environment 9
Pretesting Preparation ................................................10
Hardware........................................................10
Connectivity ....................................................11
Software ........................................................11
Data ............................................................12
Your Test Plan ..................................................13
Change One Variable at a Time ................................14
Organizational Support..........................................14
After Testing Is Under Way............................................16
Posttesting Implementation............................................17
Recording Your Results..........................................17
Making Improvements ..........................................17
2 Performance Monitoring Options 19
MySQL Tools and Utilities ............................................19
Character-Based Tools and Utilities..............................19
Graphical Tools..................................................28
Logging ........................................................30
Operating System Tools................................................35
Windows ........................................................35
Linux/Unix......................................................37
3 MySQL Overview and New Performance-Related Features 43
MySQL Products......................................................43
MySQL Core Technologies......................................44
Database Storage Engines and Table Types......................44