High performance MySQL: optimization, backups, and replication

✍ Scribed by Schwartz, Baron;Tkachenko, Vadim;Zaitsev, Peter

• Publisher: O'Reilly Media
• Year: 2012
• Tongue: English
• Leaves: 826
• Edition: 3e edition
• Category: Library

✦ Synopsis

How can you bring out MySQL's full power? With High Performance MySQL, you'll learn advanced techniques for everything from designing schemas, indexes, and queries to tuning your MySQL server, operating system, and hardware to their fullest potential. This guide also teaches you safe and practical ways to scale applications through replication, load balancing, high availability, and failover. Updated to reflect recent advances in MySQL and InnoDB performance, features, and tools, this third edition not only offers specific examples of how MySQL works, it also teaches you why this system work.;Table of Contents; Foreword; Preface; How This Book Is Organized; A Broad Overview; Building a Solid Foundation; Configuring Your Application; MySQL as an Infrastructure Component; Miscellaneous Useful Topics; Software Versions and Availability; Conventions Used in This Book; Using Code Examples; Safari' Books Online; How to Contact Us; Acknowledgments for the Third Edition; Acknowledgments for the Second Edition; From Baron; From Peter; From Vadim; From Arjen; Acknowledgments for the First Edition; From Jeremy; From Derek; ChapterŁ1.ŁMySQL Architecture and History.

✦ Table of Contents

Table of Contents
Foreword
Preface
How This Book Is Organized
A Broad Overview
Building a Solid Foundation
Configuring Your Application
MySQL as an Infrastructure Component
Miscellaneous Useful Topics
Software Versions and Availability
Conventions Used in This Book
Using Code Examples
Safari' Books Online
How to Contact Us
Acknowledgments for the Third Edition
Acknowledgments for the Second Edition
From Baron
From Peter
From Vadim
From Arjen
Acknowledgments for the First Edition
From Jeremy
From Derek
ChapterŁ1.ŁMySQL Architecture and History. MySQL's Logical ArchitectureConnection Management and Security
Optimization and Execution
Concurrency Control
Read/Write Locks
Lock Granularity
Table locks
Row locks
Transactions
Isolation Levels
Deadlocks
Transaction Logging
Transactions in MySQL
AUTOCOMMIT
Mixing storage engines in transactions
Implicit and explicit locking
Multiversion Concurrency Control
MySQL's Storage Engines
The InnoDB Engine
InnoDB's history
InnoDB overview
The MyISAM Engine
Storage
MyISAM features
Compressed MyISAM tables
MyISAM performance
Other Built-in MySQL Engines
The Archive engine. The Blackhole engineThe CSV engine
The Federated engine
The Memory engine
The Merge storage engine
The NDB Cluster engine
Third-Party Storage Engines
OLTP storage engines
Column-oriented storage engines
Community storage engines
Selecting the Right Engine
Logging
Read-only or read-mostly tables
Order processing
Bulletin boards and threaded discussion forums
CD-ROM applications
Large data volumes
Table Conversions
ALTER TABLE
Dump and import
CREATE and SELECT
A MySQL Timeline
MySQL's Development Model
Summary
ChapterŁ2.ŁBenchmarking MySQL
Why Benchmark? Benchmarking StrategiesWhat to Measure
Benchmarking Tactics
Designing and Planning a Benchmark
How Long Should the Benchmark Last?
Capturing System Performance and Status
Getting Accurate Results
Running the Benchmark and Analyzing Results
The Importance of Plotting
Benchmarking Tools
Full-Stack Tools
Single-Component Tools
Benchmarking Examples
http_load
MySQL Benchmark Suite
sysbench
The sysbench CPU benchmark
The sysbench file I/O benchmark
The sysbench OLTP benchmark
Other sysbench features
dbt2 TPC-C on the Database Test Suite
Percona's TPCC-MySQL Tool
Summary. ChapterŁ3.ŁProfiling Server PerformanceIntroduction to Performance Optimization
Optimization Through Profiling
Interpreting the Profile
Profiling Your Application
Instrumenting PHP Applications
Profiling MySQL Queries
Profiling a Server's Workload
Capturing MySQL's queries to a log
Analyzing the query log
Profiling a Single Query
Using SHOW PROFILE
Using SHOW STATUS
Using the slow query log
Using the Performance Schema
Using the Profile for Optimization
Diagnosing Intermittent Problems
Single-Query Versus Server-Wide Problems
Using SHOW GLOBAL STATUS
Using SHOW PROCESSLIST.

✦ Subjects

Relational databases;SQL (Computer program language);Electronic books