book

MySQL Troubleshooting

by Sveta Smirnova

February 2012

Intermediate to advanced

266 pages

English

O'Reilly Media, Inc.

Read now

Unlock full access

Audience

Incorrect Syntax
Tuning a Query with Information from EXPLAINTable Tuning and IndexesWhen to Stop OptimizingEffects of OptionsQueries That Modify DataNo Silver Bullet
MyISAM CorruptionRepairing a MyISAM table from SQLRepairing a MyISAM table using myisamchkInnoDB Corruption
Locks and Transactions
Table LocksRow Locks
Hidden QueriesDeadlocksImplicit Commits
Metadata Locking Versus the Old Model
Monitoring InnoDB Transactions for Concurrency ProblemsMonitoring Other Resources for Concurrency Problems
Statement-Based Replication IssuesMixing Transactional and Nontransactional TablesIssues on the Slave
SHOW PROCESSLIST and the INFORMATION_SCHEMA.PROCESSLIST TableSHOW ENGINE INNODB STATUS and InnoDB MonitorsINFORMATION_SCHEMA TablesPERFORMANCE_SCHEMA TablesLog Files
Service Options
Options That Affect Server and Client BehaviorServer-related optionsReplication optionsEngine optionsInnoDB optionsMyISAM optionsConnection-related optionsTimeoutsSecurity-related optionsPerformance-Related OptionsBuffers and maximumsOptions that control the optimizerEngine-related optionsInnoDB optionsMyISAM optionsCalculating Safe Values for OptionsOptions set for the whole serverThread optionsBuffers allocated for a specific operation
Physical Hardware LimitsRAMProcessors and Their CoresDisk I/ONetwork BandwidthExample of the Effect of Latencies
Displaying Slave Status
When Data Is Different on the Master and SlaveCircular Replication and Nonreplication Writes on the SlaveIncomplete or Altered SQL StatementsDifferent Errors on the Master and SlaveConfigurationWhen the Slave Lags Far Behind the Master
The QuerySlow Query LogTools That Can Be CustomizedThe MySQL Command-Line Interface
Error Information, AgainCrashesCore fileGeneral log file
Information SchemaInnoDB Information Schema TablesInnoDB MonitorsPerformance SchemaSHOW [GLOBAL] STATUS
Try the Query in a Newer VersionCheck for Known BugsWorkarounds
Benchmarking ToolsmysqlslapSysBenchGypsyMySQL Test Framework
BackupsPlanning BackupsTypes of BackupsTools
What Does It All Mean?
PrivilegesEnvironment
Resources Containing Information That Is Usually Useful

Overview

Stuck with bugs, performance problems, crashes, data corruption, and puzzling output? If you’re a database programmer or DBA, they’re part of your life. The trick is knowing how to quickly recover from them. This unique, example-packed book shows you how to handle an array of vexing problems when working with MySQL.

Written by a principal technical support engineer at Oracle, MySQL Troubleshooting provides the background, tools, and expert steps for solving problems from simple to complex—whether data you thought you inserted doesn’t turn up in a query, or the entire database is corrupt because of a server failure. With this book in hand, you’ll work with more confidence.

Understand the source of a problem, even when the solution is simple
Handle problems that occur when applications run in multiple threads
Debug and fix problems caused by configuration options
Discover how operating system tuning can affect your server
Use troubleshooting techniques specific to replication issues
Get a reference to additional troubleshooting techniques and tools, including third-party solutions
Learn best practices for safe and effective troubleshooting—and for preventing problems