MySQL database design and tuning PDF
Preview MySQL database design and tuning
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 [email protected] For sales outside of the U.S., please contact International Sales [email protected] 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 [email protected]. 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
The list of books you might like
