Log In
Or create an account ->
Imperial Library
Home
About
News
Upload
Forum
Help
Login/SignUp
Index
MySQL Troubleshooting
SPECIAL OFFER: Upgrade this ebook with O’Reilly
Foreword
Preface
Audience
How to Solve a Problem
How This Book Is Organized
Some Choices Made in This Book
Conventions Used in This Book
Using Code Examples
Safari® Books Online
How to Contact Us
Acknowledgments
1. Basics
Incorrect Syntax
Wrong Results from a SELECT
When the Problem May Have Been a Previous Update
Getting Information About a Query
Tracing Back Errors in Data
Slow Queries
Tuning a Query with Information from EXPLAIN
Table Tuning and Indexes
When to Stop Optimizing
Effects of Options
Queries That Modify Data
No Silver Bullet
When the Server Does Not Answer
Issues with Solutions Specific to Storage Engines
MyISAM Corruption
Repairing a MyISAM table from SQL
Repairing a MyISAM table using myisamchk
InnoDB Corruption
Permission Issues
2. You Are Not Alone: Concurrency Issues
Locks and Transactions
Locks
Table Locks
Row Locks
Transactions
Hidden Queries
Deadlocks
Implicit Commits
Metadata Locking
Metadata Locking Versus the Old Model
How Concurrency Affects Performance
Monitoring InnoDB Transactions for Concurrency Problems
Monitoring Other Resources for Concurrency Problems
Other Locking Issues
Replication and Concurrency
Statement-Based Replication Issues
Mixing Transactional and Nontransactional Tables
Issues on the Slave
Effectively Using MySQL Troubleshooting Tools
SHOW PROCESSLIST and the INFORMATION_SCHEMA.PROCESSLIST Table
SHOW ENGINE INNODB STATUS and InnoDB Monitors
INFORMATION_SCHEMA Tables
PERFORMANCE_SCHEMA Tables
Log Files
3. Effects of Server Options
Service Options
Variables That Are Supposed to Change the Server Behavior
Options That Limit Hardware Resources
Using the --no-defaults Option
Performance Options
Haste Makes Waste
The SET Statement
How to Check Whether Changes Had an Effect
Descriptions of Variables
Options That Affect Server and Client Behavior
Server-related options
Replication options
Engine options
InnoDB options
MyISAM options
Connection-related options
Timeouts
Security-related options
Performance-Related Options
Buffers and maximums
Options that control the optimizer
Engine-related options
InnoDB options
MyISAM options
Calculating Safe Values for Options
Options set for the whole server
Thread options
Buffers allocated for a specific operation
4. MySQL’s Environment
Physical Hardware Limits
RAM
Processors and Their Cores
Disk I/O
Network Bandwidth
Example of the Effect of Latencies
Operating System Limits
Effects of Other Software
5. Troubleshooting Replication
Displaying Slave Status
Problems with the I/O Thread
Problems with the SQL Thread
When Data Is Different on the Master and Slave
Circular Replication and Nonreplication Writes on the Slave
Incomplete or Altered SQL Statements
Different Errors on the Master and Slave
Configuration
When the Slave Lags Far Behind the Master
6. Troubleshooting Techniques and Tools
The Query
Slow Query Log
Tools That Can Be Customized
The MySQL Command-Line Interface
Effects of the Environment
Sandboxes
Errors and Logs
Error Information, Again
Crashes
Core file
General log file
Information-Gathering Tools
Information Schema
InnoDB Information Schema Tables
InnoDB Monitors
Performance Schema
SHOW [GLOBAL] STATUS
Localizing the Problem (Minimizing the Test Case)
General Steps to Take in Troubleshooting
Testing Methods
Try the Query in a Newer Version
Check for Known Bugs
Workarounds
Special Testing Tools
Benchmarking Tools
mysqlslap
SysBench
Gypsy
MySQL Test Framework
Maintenance Tools
7. Best Practices
Backups
Planning Backups
Types of Backups
Tools
Gathering the Information You Need
What Does It All Mean?
Testing
Prevention
Privileges
Environment
Think About It!
A. Information Resources
Resources Containing Information That Is Usually Useful
Bug and Knowledge Databases
Expert Knowledge Online
Places Where You Can Ask for Help
Books
Index
About the Author
Colophon
SPECIAL OFFER: Upgrade this ebook with O’Reilly
← Prev
Back
Next →
← Prev
Back
Next →