Instant InnoDB

88 Pages·2013·3.194 MB·English

by  Matthew Reid

Instant InnoDB
A quick reference guide to walk you through the
setup of InnoDB, and help you start unlocking
the engine's potential
Matt Reid
BIRMINGHAM - MUMBAI
Instant InnoDB
Copyright © 2013 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy
of the information presented. However, the information contained in this book is
sold without warranty, either express or implied. Neither the author, nor Packt
Publishing, and its dealers and distributors will be held liable for any damages
caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: January 2013
Production Reference: 2240113
Published by Packt Publishing Ltd.
Livery Place
35 Livery Street
Birmingham B3 2PB, UK.
ISBN 978-1-84968-748-5
www.packtpub.com
Cover image by Deborah Lattimore ([email protected])
Credits
Author Project Coordinators
Matt Reid Shraddha Bagadia
Esha Thakker
Reviewers
Marc Delisle
Proofreader
Daniël van Eeden Mario Cecere
Shiv Iyer
Indexer
Rekha Nair
Acquisition Editor
Robin de Jongh
Production Coordinators
Aparna Bhagat
Commissioning Editor
Meeta Rajani Shantanu Zagade
Technical Editors Cover Work
Harsha Bharwani Aparna Bhagat
Jalasha D'costa
About the Author
Matt Reid is a Systems Architect and Engineer who has focused his career on
helping corporations solve large scale computing challenges. He is experienced
in a wide range of Internet architectures ranging from global application load
balancing to database clusters designed for serving millions of concurrent users.
His career has included work with geographically-distributed application and
database environments, enterprise analytics and monitoring, database performance
tuning, disaster recovery architectures, enterprise application programming, as well
as systems infrastructure automation. He has worked and consulted for a multitude
of companies including Riot Games, Adobe Software, British Sky Broadcasting,
AdMob, Business Objects/SAP, OpSource, Arc90, Mattel, and NTT Communications.
Matt currently functions as the CTO and Engineering Architect for his consulting
company MobileAxiom. His current projects include infrastructure automation
engineering for the video game League of Legends, working on the global VPN
architecture for startup Virtual Private Internet, and maintaining several open
source projects for the MySQL community.
I would like to thank the following people for having an
extraordinary impact on my career. Opportunity is everything and
without you I would not be where I am today: Jim Reid, Deborah
Lattimore, James Lester Reid, Molly Reid, Ryan Morgan, Monte
Sanford, Dawn Baker, Don Barlow, Bruce Soto, Steve Crusenberry,
and Dima Polyak. I'd also like to thank the Open Source community
on the whole for helping to keep knowledge and intellectual
resources free and transparent for the world.
About the Reviewers
Marc Delisle was awarded "MySQL Community Member of the Year 2009"
because of his involvement with phpMyAdmin, a web interface for MySQL.
He started to contribute to the project in December 1998, when he made the
multi-language version. He is involved with phpMyAdmin as a developer,
translator and project administrator, and enjoys meeting phpMyAdmin users
in person.
Marc is a System Administrator at Cegep de Sherbrooke, Québec, Canada.
In addition to the Mastering phpMyAdmin successive editions, Marc has written
Creating your MySQL Database: Practical Design Tips and Techniques and phpMyAdmin
Starter, all with Packt Publishing.
Daniël van Eeden lives in the Netherlands. He works as a Database Consultant
for Snow B.V. He blogs about databases at http://databaseblog.myname.nl.
Besides working as a DBA, he has also worked as a System Administrator and
Storage Administrator. Other areas of experience are High Availablity and
Security. He is a member of the IOUG MySQL Council.
Shiv Iyer has over 12 years of experience in architecturing very large volume/
transaction intensive database applications in Oracle, SQL Server, MySQL, and
PostgreSQL. Shiv specializes in Database Architecture, Performance Engineering,
Scalability, High Availability, Disaster Recovery, Database Migration, Benchmarking,
and Fine-Tuning and Database Security. He is the founder and Principal Database
Architect of Ask DB Experts, a boutique "Database Technologies" consulting
company based out of Bangalore, India.
Shiv has also worked on books such as Oracle Database 11g Release 2 Performance
Tuning Tips and Techniques by Richard Niemiec, published by McGraw-Hill
Osborne Media.
My parents, wife, family,friends, and Richard Niemiec
www.PacktPub.com
Support files, eBooks, discount offers
and more
You might want to visit www.PacktPub.com for support files and downloads related to
your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub
files available? You can upgrade to the eBook version at www.PacktPub.com and as a print
book customer, you are entitled to a discount on the eBook copy. Get in touch with us at
[email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a
range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
TM
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book
library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
• Fully searchable across every book published by Packt
• Copy and paste, print and bookmark content
• On demand and accessible via web browser
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access PacktLib
today and view nine entirely free books. Simply use your login credentials for immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following @PacktEnterprise on
Twitter, or the Packt Enterprise Facebook page.

Table of Contents
Preface 1
Chapter 1: Getting Started with InnoDB 5
Basic features of InnoDB 5
Referential integrity 6
Multiversion concurrency control 6
Transaction isolation 6
ACID compliance 7
Locking characteristics 7
System requirements and supported platforms 7
Downloading MySQL with InnoDB 8
Installing MySQL with InnoDB support 9
Verifying InnoDB support and transaction settings 9
Atomicity 10
Consistency 11
Isolation 11
Durability 11
Setting the transaction isolation level for InnoDB 11
Creating your first InnoDB table 12
Summary 13
Chapter 2: Basic Configuration Parameters 15
MySQL memory allocation equations 16
InnoDB memory and data file configuration 17
Static variables 17
innodb_data_home_dir 17
innodb_data_file_path 18
innodb_log_group_home_dir 18
innodb_log_file_size 18
innodb_log_buffer_size 19
innodb_log_files_in_group 20
innodb_buffer_pool_size 20