MySQL Troubleshooting: What To Do When Queries Don't Work
Format: PDF / Kindle (mobi) / ePub
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
Where You Can Ask for Help MySQL High Availability (O’Reilly), Statement-Based Replication Issues, Backups MySQL optimizer, Wrong Results from a SELECT, When the Problem May Have Been a Previous Update, Tuning a Query with Information from EXPLAIN, Tuning a Query with Information from EXPLAIN, Table Tuning and Indexes, Table Tuning and Indexes, Options that control the optimizer, Options that control the optimizer forcing index use by, Table Tuning and Indexes joins used by, When the Problem
Problems with the I/O Thread, Problems with the I/O Thread, Problems with the I/O Thread, Problems with the I/O Thread not connecting to master, Problems with the I/O Thread, Problems with the I/O Thread repeated disconnects from master, Problems with the I/O Thread, Problems with the I/O Thread slave far behind master, Problems with the I/O Thread, Problems with the I/O Thread status of, Displaying Slave Status, Displaying Slave Status inconsistency of, on master and slave, Tracing Back
DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> SELECT * FROM t; +-----+ | a | +-----+ | 0 | | 256 | +-----+ 2 rows in set (0.00 sec) Now it runs in almost zero time! To find out what happened, we need to run SHOW PROCESSLIST during the time the query is running slowly. Note In an actual application environment, you would either need to manually run a diagnostic query during a busy time or schedule a job that will do it for you from time to time and save the results. mysql> SHOW
is always multithreaded, whereas the slave runs all updates in a single thread. This affects performance and consistency during replication, regardless of the binary log formats or options you use. The majority of replication issues involve data inconsistency, meaning that the data on the slave is different from that on the master. In most cases, MySQL replication takes care of data consistency, but sometimes you can still experience issues, especially if you use statement-based replication.
Qcache_total_blocks. table_definition_cache The number of table definitions that are stored in the cache. When you have a lot of tables, you can increase this value. Tune it if necessary to keep Opened_table_definitions smaller than or equal to Open_table_definitions since the most recent table flush (FLUSH TABLES query). table_open_cache The number of table descriptors that are stored in the cache. Try to tune this option so that Opened_tables remains smaller than or equal to